The work required for this analysis is in the file Lemonade.xls. (See Figures 15-1, 15-2, and 15-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
Chapter 15 Sensitivity Analysis with Data Tables 117
I computed annual revenue in cell D5 with the formula demand*price. In c e l l D 6 , I c o m p u t e d t h e a n n u a l v a r i a b l e c o s t w i t h t h e f o r m u l a
unit_cost*demand. Finally, in cell D7, I computed profit by using the formula revenue - fixed_cost - variable_cost.
Figure 15-1 The inputs that change the profitability of a lemonade store.
Suppose that I want to know how changes in price (say, between $1.00 and $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 15-2.
Figure 15-2 One-way data table with varying prices.
To set up a one-way data table, begin by listing input values down a single column. I listed the prices of interest (ranging from $1.00 to $4.00 in $0.25 increments) in the range C11:C23. Next I go over one column and up one row from the list of input values, and there I list 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, select Data, Table and fill in the dialog box shown in Figure 15-3.
Chapter 15 Sensitivity Analysis with Data Tables 119
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 15-4.
Figure 15-4 One-way data table with varying prices.
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 com€ puted for a price of $1.25, and on through the range of prices. The profit-max€ imizing price among all listed prices is $3.75. A price of $3.75 would produce an annual profit of $58,125, 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 to $5.00 (in $0.25 increments) and unit variable cost varies from $0.30 to $0.60 (in $0.05 increments). Because we’re changing two inputs, we need a
two-way data table. (See Figure 15-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 val€ ues), and I list the values of the other input in the first column 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 formula for profit in cell H10.
Figure 15-5 A two-way data table showing profit as a function of price and unit variable cost.
I select the table range (cells H10:O25), and then choose 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 plugged in as prices, and the values in the first row of the table range are plugged in as unit variable costs. After clicking OK, we see the two-way data table shown in Figure 15-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. 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 our 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-maximiz€ ing price. When we study the Excel Solver in Chapters 24 and 25, you’ll learn how to determine (to the penny) the exact profit-maximizing price.
Chapter 15 Sensitivity Analysis with Data Tables 121
Here are some other notes on this problem:
■ As you change input values in a spreadsheet, 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 choose Edit, Paste Special. Select Values in the Paste area of the dialog box. If you take this step, however, changes to your spread- sheet 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 spreadsheet calculation mode to Automatic. With this setting, any change in your spreadsheet 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, select Tools, Options, and then click the Calculation tab. Select Auto€ matic Except For Tables. When Automatic Except For Tables is selected, all your data tables recalculate only when you press the F9 (recalculation) key.