Introduction to Microsoft Excel



Add-Ins

Some people claim that Excel is one of the most sophisticated and powerful pieces of software ever written (and these people don’t even work for Microsoft). Excel’s basic functionality can even be extended by using so-called “add-ins.” These are modules that cover specific areas (e.g., statistics, optimization, …) or offer other functions not included in basic Excel. Some of these add-ins (like the ones covered in this chapter) come with Excel, others can be purchased from third-party vendors. And, if you know how to use Visual Basic for Applications, you can even create your own add-ins.

Again, this is not the place to cover all add-ins, or even most of them. We can’t even get into the details of a few. But this chapter will introduce you to two add-ins that are particularly useful for business applications: the Analysis ToolPak, which offers a variety of statistical techniques, and the “Solver” which you can use to tackle many optimization problems in operations management and other related business functions.

1 Analysis ToolPak

The Analysis ToolPak, which comes with Excel, provides a set of statistical procedures that are grouped into 19 categories (see Table 6-1). The procedures range from basic descriptive statistics and histograms to fairly sophisticated procedures, including multiple regression analysis.

Table 6-1

|Tool |Brief Description |

|Anova: Single Factor |Single factor analysis of variance to test for equality of means |

|Anova: Two-Factor With Replication |Two-factor analysis of variance to test for equality of means |

| |(multiple observations per cell) |

|Anova: Two-Factor Without Replication |Two-factor analysis of variance to test for equality of means (one |

| |observation per cell) |

|Correlation |Calculates correlation matrix |

|Covariance |Calculates variance/covariance matrix |

|Descriptive Statistics |Calculates a variety of descriptive statistics (e.g., mean, standard |

| |deviation, etc.) |

|Exponential Smoothing |Performs exponential smoothing of a time series |

|F-Test Two Sample for Variance |Hypothesis test for ratio of two population variances |

|Fourier Analysis |Fast Fourier Transformation of data |

|Histogram |Generates histogram of data set |

|Moving average |Calculates moving averages for a time series |

|Random Number Generation |Generates random number from a variety of discrete and continuous |

| |probability distributions |

|Rank and Percentile |Calculates ranks and percentiles of a data set |

Table 6.1 (cont.)

|Tool |Brief Description |

|Regression |Performs simple and multiple regression analysis (including analysis |

| |of residuals) |

|Sampling |Randomly samples values from a data set |

|t-Test: Paired Two Sample for Means |Hypothesis test for the difference of two population means (matched |

| |samples) |

|t-Test: Two Sample Assuming Equal Variances |Hypothesis test for the difference of two population means |

| |(independent samples; population means assumed to be equal) |

|t-Test: Two Sample Assuming Unequal Variances |Hypothesis test for the difference of two population means |

| |(independent samples) |

|z-Test: Two-Sample for Means |Hypothesis test for the difference of two population means (assuming |

| |population standard deviations are known) |

1 Installing the Analysis ToolPak

As an add-in, the Analysis ToolPak might not be available at first. Click on the Tools menu. If you see a Data Analysis … item, you’re ready to go. If you don’t see the Data Analysis … item you need to make the Analysis ToolPak available (assuming that it was installed on your hard drive during the original installation of Excel; if not, you need to run the Excel setup again).

1. Choose Tools ( Add-Ins ….

2. In the Add-Ins dialog box make sure that the Analysis ToolPak item is clicked (the Analysis ToolPak VBA item makes the Visual Basic for Applications functions available as well).

[pic]

Figure 6-1

3. Click OK to close the dialog box.

The Analysis ToolPak should now be available.

2 Using the Analysis ToolPak: Simple Descriptive Statistics

As an example of how to use the Analysis ToolPak let’s calculate some basic descriptive statistics for the salary variable in your Employees worksheet.

1. Activate the Employees worksheet.

2. Choose Tools ( Data Analysis ….

3. Select Descriptive Statistics from the Data Analysis dialog box

[pic]

Figure 6-2

4. Click on OK to close the dialog box.

5. As the Input Range: specify C1:C26. Since this range contains a label in the first row, make sure that the Labels in First Row box is checked. Also check the Summary statistics box if you want to get the descriptive statistics (I still wonder whether this is a bug or a “feature”). Click on OK to close the dialog box.

[pic]

Figure 6-3

Excel calculates descriptive statistics for the salary data and puts them in a new worksheet.

[pic]

Figure 6-4

Note: the descriptive statistics procedure only pastes the results of the calculations in the new worksheet. In other words the results are not “linked” to the underlying data such that you would have to rerun the descriptive statistics procedure if any of your data change. You cannot “refresh” the results (like you can with PivotTables).

3 Using the Analysis ToolPak: Correlation Matrix

To calculate the correlation between salary and the number of years a particular employee has been with the company follow these steps (a correlation is a number between –1 and +1 which expresses the strength of the linear relationship between two quantitative variables):

1. Choose Tools ( Data Analysis ….

2. In the Data Analysis dialog box, choose the Correlation item.

[pic]

Figure 6-5

3. Click OK to close the dialog box.

4. Specify B1:C26 as the input range and make sure that Labels in First Row is checked.

[pic]

Figure 6-6

5. Click OK to close the dialog box.

Excel provides a correlation matrix (which in this case is very small since we just deal with a pair of variables) that shows that salary and years of experience are highly correlated (really close to one).

[pic]

Figure 6-7

2 The Solver Add-In

Many business decisions, especially those dealing with the management of operations, rely on finding the “best” solution under a variety of constraints. Many of these problems can be solved by using optimization techniques based on a technique called “mathematical programming” (e.g., linear programming, integer programming, etc.). The Solver add-in makes these techniques available. This is not the place to get into the details of what Solver can do, nor is it a place to even attempt to explain the ins and outs of math programming to you. Instead, we will use one very simplified example to show you the mechanics of using Solver.

1 Installing the Solver

As an add-in, the Solver might not be available at first. Click on the Tools menu. If you see a Solver … item, you’re ready to go. If you don’t see the Solver … item you need to make the it available (assuming that it was installed on your hard drive during the original installation of Excel; if not, you need to run the Excel setup again).

1. Choose Tools ( Add-Ins ….

2. In the Add-Ins dialog box make sure that the Solver Add-in item is clicked.

[pic]

Figure 6-8

3. Click OK to close the dialog box.

The Solver add-in should now be available from the Tools menu.

2 Using Solver: A Simple Example

The Solver add-in allows us to run optimization techniques based on mathematical programming. Such techniques are often used to find the best (i.e., most profitable) product mix, to find the best investment portfolio, to minimize shipping costs, etc. A common denominator of these problems are that all of them have the following three ingredients:

• a target value that you want to optimize (e.g., maximize profit, minimize cost);

• a set of decision variables that you can change and that have an impact on the target value;

• some constraints that you need to satisfy.

Again, this is not the place to explain even the basics of mathematical programming or all the features available in Solver (and there are quite a few; some aspects of Solver are quite demanding).

Let’s say your company produces three products A, B, and C. The profits per unit produced are $10, $12, and $15, respectively. The problem is to find the most profitable product mix, i.e., how many units of products A, B, and C should I produce? Excuse me? Why don’t we just produce product C, since it’s the most profitable? Well, that’s where the constraints come in. Let’s assume that we have to fill an existing order of 100 units of product A, and need to produce at least 50 units of B to be able to satisfy expected demand for B over the next planning period. Also assume that it takes 5, 6, and 7 hours, respectively, to produce 1 unit each of products A, B, and C. We have 5000 labor hours available over the next planning period.

This situation is represented in worksheet Product Mix as shown in the following figure.

[pic]

Figure 6-9

Column D contains formulas that calculate the profit for each product and the total profit. Column F contains formulas that calculate the required labor for each product and the total labor (in hours).

We would like to maximize the value in cell D5 (total profit) by changing the values in B2:B4 subject to the constraints that:

• the value of B2 be at least 100 (due to the existing order of 100 units of A)

• the value of B3 be at least 50 (to satisfy expected demand for B)

• the value of F5 be at most 5000 (due to the available labor hours constraint)

To use Solver to get the most profitable mix of products A, B, and C proceed as follows:

1. Start Solver by choosing Tools ( Solver ….

The Solver dialog box opens. This is where you specify the details of your “model” and set some other options for Solver.

2. Specify D5 as the target cell.

3. Select the Max option since we want to maximize profits.

4. Specify B2:B4 under By Changing Cells (to designate the three units as our decision variables).

The dialog box should look like this.

[pic]

Figure 6-10

5. Click on Add to add the constraints.

6. In the Add Constraint dialog box give B2 as the Cell Reference, choose >= as the comparison operator from the listbox in the middle, and specify a value of 100 as the Constraint. This specifies the constraint of having at least 100 units for product A.

[pic]

Figure 6-11

7. Click Add to add another constraint.

8. Now specify B3 as the Cell Reference, >= as the comparison operator, and 50 as the Constraint. This specifies the constraint that we need at least 50 units for B.

[pic]

Figure 6-12

9. Click Add to add our final constraint.

10. Specify F5 as the Cell Reference, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download