Chapter 15: Sensitivity Analysis with Data Tables

Overview

• 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.

• I am going to build a new house. The amount of money I need to borrow (with a 15-year repayment period) depends on the price I sell my current house for. I’m also unsure about the annual interest rate I’ll receive when I close. Can I determine how my monthly payments will depend on the amount borrowed and the annual interest rate?

• A major Internet company is thinking of purchasing another online retailer. The retailer’s current annual revenues are $100 million, with expenses of $150 million. Current projections indicate that the retailer’s revenues are growing at 25 percent per year and its expenses are growing at 5 percent per year. We know projections might be in error, however, and we would like to know, for a variety of assumptions about annual revenue and expense growth, the number of years before the retailer will show a profit.

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 Microsoft 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.

• 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.

• The work required for this analysis is in the file Lemonade.xlsx. (See Figures 15-1, 15-2, and 15-4.) Our input assumptions are 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. (Chapter 7, “Evaluating Investments by Using Net Present Value Criteria,” contains a discussion of how to estimate a demand curve.) I’ve created the names in C1:C7 to correspond to cells D1:D7.

  
  Figure 15-1: The inputs that change the profitability of a lemonade store

  
  Figure 15-2: One-way data table with varying prices

  
  Figure 15-4: One-way data table with varying prices

• 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 15-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 Analyis, and then click Data Table. Now fill in the Data Table dialog box as shown in Figure 15-3.

  
  Figure 15-3: Creating a data table

• 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.

• 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 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 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.

  
  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 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 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.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. When we study the Excel Solver in Chapter 70, “Estimating a Demand Curve,” you’ll learn how to determine (to the penny) the exact 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.

• I am going to build a new house. The amount of money I need to borrow (with a 15-year repayment period) depends on the price I sell my current house for. I’m also unsure about the annual interest rate I’ll receive when I close. Can I determine how my monthly payments will depend on the amount borrowed and the annual interest rate?

• The real power of data tables becomes evident when you combine a data table with one of the Excel functions. In this example, we’ll use a two-way data table to vary two inputs (the amount borrowed and the annual interest rate) to the Excel PMT function and determine how the monthly payment will vary as these inputs change. (The PMT function is discussed in detail in Chapter 9, “More Excel Financial Functions.”) Our work for this example is in the file Mortgagedt.xlst, shown in Figure 15-6 on the next page.

  
  Figure 15-6: You can use a data table to determine how mortgage payments vary as the amount borrowed and the interest rate change.

• Suppose we’re borrowing money on a 15-year mortgage, where monthly payments are made at the end of each month. I’ve input the amount borrowed in cell D2, the number of months in the mortgage (180) in D3, and annual interest rate in D4. I’ve associated the range names in cells C2:C4 with the cells D2:D4. Based on these inputs, we compute our monthly payment in D5 with the formula:

• We think the amount borrowed will range (depending on the price we sell our current house for) between $300,000 and $650,000 and that our annual interest rate will range between 5 percent and 8 percent. In preparation for creating a data table, I entered the amounts borrowed in the range C8:C15 and possible interest rate values in the range D7:J7. Cell C7 contains the output we want to recalculate for various input combinations. Therefore, I set cell C7 equal to cell D5. Next I select the table range (C7:J15), click What-If Analysis on the Data tab, and then click Data Table. Because numbers in the first column of the table range are amounts borrowed, the column input cell is D2. Numbers in the first row of the table are annual interest rates, so our row input cell is D4. After clicking OK, we see the data table shown in Figure 15-6. This table shows us, for example, that if we borrow $400,000 at an annual rate of 6 percent, our monthly payments would be just over $3,375. Our data table also shows us that at a low interest rate (for example, 5 percent), an increase of $50,000 in the amount borrowed raises our monthly payment by around $395, whereas at a high interest rate (such as 8 percent), an increase of $50,000 in the amount borrowed raises our monthly payment by about $478.

• A major Internet company is thinking of purchasing another online retailer. The retailer’s current annual revenues are $100 million, with expenses of $150 million. Current projections indicate that the retailer’s revenues are growing at 25 percent per year and its expenses are growing at 5 percent per year. We know projections might be in error, however, and we would like to know, for a variety of assumptions about annual revenue and expense growth, the number of years before the retailer will show a profit.

• We want to determine the number of years needed to break even, using annual growth rates in revenue from 10 percent through 50 percent and annual expense growth rates from 2 percent through 20 percent. Let’s also assume that if the firm cannot break even in 13 years, we’ll say “cannot break even.” Our work is in the file Bezos.xlsx, shown in Figures 15-7 and 15-8.

  
  Figure 15-7: You can use a data table to calculate how many years it will take to break even.

  
  Figure 15-8: A two-way data table

• I chose to hide columns A and B and rows 16–18. To hide columns A and B, select any cells in columns A and B (or select the column headings). Then display the Home tab. In the Cells group, click Format, point to Hide & Unhide, and select Hide Columns. To hide rows 16–18, select any cells in those rows (or select the row headings) and repeat the previous procedure, selecting Hide Rows. Of course, the Format Visibility options also include Unhide Rows and Unhide Columns. If you receive a worksheet with many hidden rows and columns and want to quickly unhide all of them, you can select the entire worksheet by clicking the Select All button at the intersection of the column and row headings. Now selecting Unhide Rows and/or Unhide Columns will unhide all hidden rows and/or columns in the worksheet.

• In row 11, I project the firm’s revenue out 13 years (based on the annual revenue growth rate assumed in E7) by copying from F11 to G11:R11 the formula E11*(1+$E$7). In row 12, I project the firm’s expenses out 13 years (based on the annual expense growth rate assumed in E8) by copying from F12 to G12:R12 the formula E12*(1+$E$8). (See Figure 15-7.)

• We would like to use a two-way data table to determine how varying our growth rates for revenues and expenses affects the years needed to break even. We need one cell whose value always tells us the number of years needed to break even. Because we can break even during any of the next 13 years, this might seem like a tall order.

• I begin by using in row 13 an IF statement for each year to determine whether we break even during a year. The IF statement returns the number of the year if we break even during the year or 0 otherwise. I determine the year we break even in cell E15 by simply adding together all the numbers in row 13. Finally, I can use cell E15 as the output cell for our two-way data table.

• I copy from cell F13 to G13:R13 the formula IF(AND(E11<E12,F11>F12),F10,0). This formula reflects the fact that we break even for the first time during a year if, and only if, during the previous year, revenues are less than expenses and during the current year, revenues are greater than expenses. If this is the case, we enter the year number in row 13; otherwise, we enter 0.

• Now, in cell E15, I can determine the breakeven year (if any) with the formula IF(SUM (F13:R13)>0,SUM(F13:R13),"No BE"). If we do not break even during the next 13 years, the formula enters the text string "No BE".

• I now enter our annual revenue growth rates (10 percent through 50 percent) in the range E21:E61. I enter annual expense growth rates (2 percent to 20 percent) in the range F20:X20. I ensure that the year-of-breakeven formula is copied to cell E20 with the formula =E15. Next, I select the table range E20:X61, click What-If Analysis on the Data tab, and then click Data Table. I select cell E7 (revenue growth rate) as the column input cell and cell E8 (expense growth rate) as the row input cell. We obtain the two-way data table shown in Figure 15-8.

• Note, for example, that if expenses grow at 4 percent a year, a 10-percent annual growth in revenue will result in breaking even in eight years, whereas a 50-percent annual growth in revenue will result in breaking even in only two years! Also note that if expenses grow at 12 percent per year and revenues grow at 14 percent per year, we will not break even by the end of 13 years.