• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 14. Analyzing Data > What-If Analysis

What-If Analysis

You may have calculations with some component variables that you're not certain of, and perhaps you have several guesses as to what they might be. One way to handle the problem is to manually replace those numbers you're uncertain about with each of your guesses in turn, and let Excel recalculate the bottom line for each guess, one at a time. If you know you'd like to keep the sets of values for future use, Excel lets you do this with Scenarios. If you have no more than two variables that change between scenarios and you want to see the results for all your guessed values at once, Excel provides Data Tables.

To save your what-if scenarios:

Set up your spreadsheet with one of your guesses (Figure 14.18).

Figure 14.18. Set up the spreadsheet.

Choose Tools > Scenarios. The Scenario Manager dialog box appears (Figure 14.19).

Figure 14.19. Choose Tools > Scenarios to get the Scenario Manager dialog box.

Click Add. The Edit Scenario dialog box appears. Give this scenario a name, select the cells that change with each scenario, and add a comment if you like (Figure 14.20). Click OK.

Figure 14.20. Add your scenarios.

The Scenario Values dialog box appears. Specify the values for the changing variables for this scenario (Figure 14.21). Click Add to add the next scenario to the list.

Figure 14.21. Specify the values for each scenario.

Repeat Steps 3 and 4 until you have all your scenarios entered, and then click OK. The Scenario Manager dialog box appears (Figure 14.22), with all your saved scenarios listed in the pane on the left. To see the results of any one of them, click it in the left pane, and click the Show button. Excel substitutes those values for the variables in your spreadsheet and calculates the results.

Figure 14.22. Choose a scenario in the Scenario Manager.

To see a summary of all the scenarios in one new sheet, click Summary in the Scenario Manager dialog box. The Scenario Summary dialog box appears (Figure 14.23), giving you the chance to choose between a Scenario summary format and a Scenario PivotTable report. You can also specify which results cell you want reported, in case you have more than one.

Figure 14.23. Request a summary.

The report you chose appears in a new sheet in the same workbook (Figure 14.24).

Figure 14.24. The scenario summary appears in a new sheet.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint