Chapter 17: Using the Scenario Manager for Sensitivity Analysis

Overview

• I’d like to create best, worst, and most-likely scenarios for the sales of an automobile by varying the values of Year 1 sales, annual sales growth, and Year 1 sales price. Data tables for sensitivity analysis allow me to vary only one or two inputs, so I can’t use a data table. Does Excel have a tool I can use to vary more than two inputs in a sensitivity analysis?

The Scenario Manager enables you to perform sensitivity analysis by varying as many as 32 input cells. With the Scenario Manager, you first define the set of input cells you want to vary. Next, you name your scenario and enter for each scenario the value of each input cell. Finally, you select the output cells (also called result cells) that you want to track. The Scenario Manager then creates a beautiful report containing the inputs and the values of the output cells for each scenario.

• I’d like to create best, worst, and most-likely scenarios for the sales of an automobile by varying the values of Year 1 sales, annual sales growth, and Year 1 sales price. Data tables for sensitivity analysis allow me to vary only one or two inputs, so I can’t use a data table. Does Excel have a tool I can use to vary more than two inputs in a sensitivity analysis?

• Suppose we want to create the following three scenarios related to the net present value (NPV) of a car, using the example in Chapter 14, “The Auditing Tool.”

  Open table as spreadsheet

  	Year 1 sales	Annual sales growth	Year 1 sales price
  Best case	$20,000	20%	$10.00
  Most likely case	$10,000	10%	$7.50
  Worst case	$5,000	2%	$5.00

• For each scenario, we want to look at the firm’s NPV and each year’s after-tax profit. Our work is in the file NPVauditscenario.xlsx. Figure 17-1 shows the worksheet model (contained in the Original Model worksheet), and Figure 17-2 shows the scenario report (contained in the Scenario Summary worksheet).

  
  Figure 17-1: The data on which the scenarios are based

  
  Figure 17-2: The scenario summary report

• To begin defining the best-case scenario, I display the Data tab, and then click Scenario Manager on the What-If Analysis menu. Then I click the Add button and fill in the Add Scenario dialog box as shown in Figure 17-3.

  
  Figure 17-3: Data inputs for the best-case scenario

• I enter a name for the scenario (Best) and select C2:C4 as the input cells containing the values that will define the scenario. After I click OK in the Add Scenario dialog box, I fill in the Scenario Values dialog box with the input values that define the best case, as shown in Figure 17-4.

  
  Figure 17-4: Defining the input values for the best-case scenario

• By clicking Add in the Scenario Values dialog box, I can enter the data for the most-likely and worst-case scenarios. After I’ve entered data for all three scenarios, I click OK in the Scenario Values dialog box. The Scenario Manager dialog box, shown in Figure 17-5, lists the scenarios I created. When I click Summary in the Scenario Manager dialog box, I can choose the result cells that will be displayed in scenario reports. Figure 17-6 on the next page shows how I indicated in the Scenario Summary dialog box that I want the scenario summary report to track each year’s after-tax profit (cells B17:F17) as well as total NPV (cell B19).

  
  Figure 17-5: The Scenario Manager dialog box displays each scenario I defined.

  
  Figure 17-6: Use the Scenario Summary dialog box to select the result cells for the summary report.

• Because the result cells come from more than one range, I’ve separated the ranges B17:F17 and B19 with a comma. (I could also have used the Ctrl key to select and enter multiple ranges.) After selecting Scenario Summary (instead of the PivotTable option), I click OK, and Microsoft Office Excel 2007 creates the beautiful Scenario Summary report pictured earlier in Figure 17-2. Notice that Excel includes a column, labeled Current Values, for the values that were originally placed in the worksheet. The worst case loses money (a loss of $13,345.75), whereas the best case is quite profitable (a profit of $226,892.67). Because the worst-case price is less than our variable cost, the worst case loses money in each year.