Dr. Winston's Excel tip: sensitivity analysis with one-way data tables

Most spreadsheets have inputs and outputs. Spreadsheet inputs are assumptions that we make. Some examples of spreadsheet inputs follow:

• Unit sales of a product will grow by 10% per year.
• Our product will generate profit margins of 30%.
• Our product will sell for five years.
• Each year 90% of subscribers will renew their magazine subscription.

Spreadsheet outputs are quantities of interest that depend on the value of the spreadsheet’s inputs. Some examples of spreadsheet outputs follow:

• Present Value of the profits generated by a product.
• Profit generated this year by a coffee shop.
• Lifetime value of a magazine subscriber. Usually, we assume a particular value for a spreadsheet input. Assumptions are often wrong! For example, in 1980 McKinsey Consulting predicted the US would have 900,000 cell phones in the Year 2000. Actually, in 2000 the US had 109 million cell phones. Since assumptions are often incorrect, it is important to determine how sensitive spreadsheet outputs are to changes in spreadsheet inputs. In this article, we give two examples that show how one-way data tables can be used to conduct a sensitivity analysis which shows how changes in a single spreadsheet input change multiple spreadsheet outputs.

Sensitivity analysis in Excel

In this article, we give two examples that show how one-way data tables can be used to conduct a sensitivity analysis. When conducting a sensitivity analysis, Excel shows how changes in a single spreadsheet input change multiple spreadsheet outputs. 

Central Perk

Copy and paste the link to this file BeckerDecember20.xlsx in a new window to follow along in Excel. The worksheet Coffee of this workbook contains a simple model that computes annual profit at the Central Perk coffee shop. As shown in Figure 1 the annual demand for coffee is 65,000 -9000*price. This implies that an increase in the price of a cup of coffee will reduce annual sales by 9000 cups. In addition to the demand curve, the key inputs assumptions are the unit price (currently assumed to be $4.00) and the unit cost of producing a cup of coffee (currently assumed to be $0.45.) We want to determine how changes in the price input affect three outputs: profit, revenue, and variable cost.

Figure 1 One-Way Data Table based on Changing Price

Suppose Central Perk is considering a price of between $1.00 and $4.00 for a cup of coffee. To determine how a price change will affect profit, revenue and variable cost we begin by entering possible prices between $1.00 and $4.00 (in 25 cent increments) down a column (in C10:C22.) Then we move up one row and over to the right one column from our first input value (this takes us to D9) and then we enter in D9:F9 the formulas (not the values) for the output cells we want calculated for different prices. We have named these cells profit, revenue, and variable_cost so we enter =profit in D8; =revenue in E8, and =variable_cost in F8.

To construct our one-way data sensitivity table in Excel we proceed as follows:

• Select the table range (in this case C9:F22) that contains the values of the spreadsheet input, the formulas to be recalculated, and the range where the calculations will be completed. When the data table is complete, for example, cells D21-F21, respectively will show the profit, revenue, and variable cost corresponding to a price of $3.75.

• From the Forecast Groupon the Data Tab, select What-If Analysis and then choose Data Table.

• Then fill in the dialog box as shown in Figure 2. A one-way data table with the inputs going down a column has no row input cell. Defining D1 as the column input cell tells Excel to input each price in the 1stcolumn of the table range (cells C10:C22) into the Column Input Cell (D1) and then compute each of our output cells.
• We find, for example, that a price of $3.75 yields a profit of $58,125, revenue of $117, 187.50, and a variable cost of $14,062.50. We highlighted cell D21 to show that a price of $3.75 maximizes profit.

Figure 2 Dialog Box for sensitivity table in Excel

Remarks

By default, Data Tables recalculate whenever you change the spreadsheet. If you find that the recalculation of the Data Tables in a large spreadsheet annoys you, then from the Formulas tab go to Calculation Options and change the option to Automatic Except for Tables. Then your spreadsheets will not recalculate unless you hit the F9 key.

If you hit the Delete key when you are in any of the calculated cells in a data table, you will be told you can’t delete any part of a data table.

If you hover over any calculated cell in our data table, then you will see the formula ={TABLE(,D1). This formula tells you that the data table has no Row input cell and the column input cell for the Data Table is D1.

Sensitivity analysis in Excel is essential to avoid incorrect data assumptions. For further understanding, read our instruction on two-way data tables.