Amal Nagm

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 profit
• Annual revenue
• 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.