Microsoft Office Excel 2007: Data Analysis and Business Modeling
By Wayne L. Winston
Wayne L. Winston is a professor of Decision Sciences at Indiana University’s Kelley School of Business and has earned numerous MBA teaching awards. For 20+ years, he has taught clients at Fortune 500 companies how to use Excel to make smarter business decisions. Wayne and his business partner, Jeff Sagarin, developed the player-statistics tracking and rating system used by the Dallas Mavericks professional basketball team. He is also a two time Jeopardy! champion.
To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.
In this article
This article explains how you can use Sensitivity Analysis with Microsoft Office Excel 2007 data tables to make important business decisions by computing outputs from certain assumed parameters or inputs. You will also learn how to use the Goal Seek feature in Office Excel 2007 to compute a value for a worksheet input that makes the value of a given formula match a specified goal. The best way to explain how to use these Office Excel 2007 features is through examples. With this in mind, we will use an example about opening a gourmet lemonade stand to show you how to use Sensitivity Analysis and the Goal Seek feature to analyze business results.
Sensitivity analysis with data tables
Question: I'm thinking of starting a store in the local mall to sell gourmet lemonade. Before opening the store, I’m curious about how my profit, revenue, and variable costs will depend on the price I charge and the unit cost.
Most worksheet models contain assumptions about certain parameters or inputs to the model. In our lemonade example, the inputs would include:
The price for which a glass of lemonade is sold.
The variable cost of producing a glass of lemonade.
The sensitivity of demand for lemonade to price charged.
The annual fixed cost of running a lemonade stand.
Based on input assumptions, we can compute outputs of interest. For the lemonade example, the outputs of interest might include:
Annual variable cost
Despite best intentions, assumptions about input values can be in error. For example, our best guess about the variable cost of producing a glass of lemonade might be $0.45, but it's possible that our assumption will be in error. Sensitivity analysis determines how a spreadsheet's outputs vary in response to changes to its inputs. For example, we might want to see how a change in product price affects yearly profit, revenue, and variable cost. A data table in Office Excel 2007 makes it easy to vary one or two inputs and perform a sensitivity analysis. With a one-way data table, you can determine how changing one input will change any number of outputs. With a two-way data table, you can determine how changing two inputs will change a single output. Our three examples will show how easy it is to use a data table and obtain meaningful sensitivity results.
The work required for this analysis is in the file shown in Figures 1, 2, and 4. Our input assumptions are given in the range D1:D4. We're assuming that annual demand for lemonade (see the formula in cell D2) equals 65000–9000*price. I've created the names in C1:C7 to correspond to cells D1:D7.
I computed annual revenue in cell D5 with the formula demand*price. In cell D6, I computed the annual variable cost with the formula unit_cost*demand. Finally, in cell D7, I computed profit by using the formula revenue–fixed_cost–variable_cost.
Suppose that I want to know how changes in price (for example, from $1.00 through $4.00 in $0.25 increments) affect annual profit, revenue, and variable cost. Because we're changing only one input, a one-way data table will solve our problem. The data table is shown in Figure 2.
To set up a one-way data table, begin by listing input values in a column. I listed the prices of interest (ranging from $1.00 through $4.00 in $0.25 increments) in the range C11:C23. Next, I moved over one column and up one row from the list of input values, and there I listed the formulas we want a data table to calculate. I entered the formula for profit in cell D10, the formula for revenue in cell E10, and the formula for variable cost in cell F10.
Now select the table range (C10:F23). The table range begins one row above the first input; its last row is the row containing the last input value. The first column in the table range is the column containing the inputs; its last column is the last column containing an output. After selecting the table range, display the Data tab of the Ribbon. In the Data Tools group, click What-If Analysis, and then click Data Table. Now fill in the Data Table dialog box as shown in Figure 3.
As the column input cell, use the cell in which you want the listed inputs — that is, the values listed in the first column of the data table range — to be assigned. Because the listed inputs are prices, I chose D1 as the column input cell. After clicking OK, Excel creates the one-way data table shown in Figure 4.
In the range D11:F11, profit, revenue, and variable cost are computed for a price of $1.00. In cells D12:F12, profit, revenue, and variable cost are computed for a price of $1.25, and on through the range of prices. The profit-maximizing price among all listed prices is $3.75. A price of $3.75 would produce an annual profit of $58,125.00, annual revenue of $117,187.50, and an annual variable cost of $14,062.50.
Suppose I want to determine how annual profit varies as price varies from $1.50 through $5.00 (in $0.25 increments) and unit variable cost varies from $0.30 through $0.60 (in $0.05 increments).
Because we're changing two inputs, we need a two-way data table. (See Figure 5.) I list the values for one input down the first column of the table range (I'm using the range H11:H25 for the price values), and the values for the other input in the first row of the table range. (In this example, the range I10:O10 holds the list of variable cost values.) A two-way data table can have only one output cell, and the formula for the output must be placed in the upper-left corner of the table range. Therefore, I placed the profit formula in cell H10.
I select the table range (cells H10:O25), and display the Data tab. In the Data Tools group, click What-If Analysis, and then click Data Table. Cell D1 (price) is the column input cell, and cell D3 (unit variable cost) is the row input cell. This ensures that the values in the first column of the table range are used as prices, and the values in the first row of the table range are used as unit variable costs. After clicking OK, we see the two-way data table shown in Figure 5. As an example, in cell K19, when we charge $3.50 and the unit variable cost is $0.40, our annual profit equals $58,850.00. For each unit cost, I've highlighted the profit-maximizing price. Note that as the unit cost increases, the profit-maximizing price increases as we pass on some of the cost increase to our customers. Of course, we can only guarantee that the profit-maximizing price in the data table is within $0.25 of the actual profit-maximizing price.
Here are some other notes on this problem:
As you change input values in a worksheet, the values calculated by a data table change, too. For example, if we increased fixed cost by $10,000, all profit numbers in the data table would be reduced by $10,000.
You can't delete or edit a portion of a data table. If you want to save the values in a data table, select the table range, copy the values, and then right-click and select Paste Special. Then choose Values from the Paste Special menu. If you take this step, however, changes to your worksheet inputs will no longer cause the data table calculations to update.
When setting up a two-way data table, be careful not to mix up your row and column input cells. A mix-up will cause nonsensical results.
Most people set their worksheet calculation mode to Automatic. With this setting, any change in your worksheet will cause all your data tables to be recalculated. Usually, you want this, but if your data tables are large, automatic recalculation can be incredibly slow. If the constant recalculation of data tables is slowing your work down, click the Microsoft Office Button, click Excel Options, and then click the Formulas tab. Then select Automatic Except For Data Tables. When Automatic Except For Data Tables is selected, all your data tables recalculate only when you press the F9 (recalculation) key. Alternatively, you can click the Calculation Options button (in the Calculation group on the Formulas tab), and then click Automatic Except For Data Tables.
The Goal Seek command
Question: For a given price, how many glasses of lemonade does a lemonade store need to sell per year to break even?
The Goal Seek feature in Office Excel 2007 enables you to compute a value for a worksheet input that makes the value of a given formula match the goal you specify. For example, in our lemonade store example, suppose we have fixed overhead costs, fixed per-unit costs, and a fixed sales price. Given this information, we can use Goal Seek to calculate the number of glasses of lemonade we need to sell to break even. Essentially, Goal Seek embeds a powerful equation solver in your worksheet. To use Goal Seek, you need to provide Excel with three pieces of information:
Set Cell Specifies that the cell contains the formula that calculates the information you're seeking. In the lemonade example, the Set Cell would contain the formula for profit.
To Value Specifies the numerical value for the goal that's calculated in the Set Cell. In the lemonade example, because we want to determine the sales volume that represents the breakeven point, the To Value would be 0.
By Changing Cell Specifies the input cell that Excel changes until the Set Cell calculates the goal defined in the To Value cell. In the lemonade example, the By Changing Cell would contain annual lemonade sales.
Our work for this section is shown in Figure 6. Once again I have assumed an annual fixed cost of $45,000.00 and variable unit cost of $0.45. Let's assume a price of $3.00. The question is how many glasses of lemonade we need to sell each year to break even.
To start, insert any number for demand in cell D2. In the What-If Analysis group on the Data tab, click Goal Seek. Now fill in the Goal Seek dialog box as shown in Figure 7.
The dialog box indicates that we want to change cell D2 (annual demand, or sales) until cell D7 (profit) hits a value of 0. After clicking OK, we get the result that's shown in Figure 6. If we sell approximately 17,647 glasses of lemonade per year (or 48 glasses per day), we'll break even. To find the value we're seeking, Excel varies the demand in cell D2 (alternating between high and low values) until it finds a value that makes profit equal $0. If a problem has more than one solution, Goal Seek will still display only one answer.