Working with Scenarios

In what-if analysis, a particular set of input values that you plug into a model is called a scenario. Because most what-if worksheets can take a wide range of input values, you usually end up with a large number of scenarios to examine. Instead of going through the tedious chore of inserting all these values into the appropriate cells, Excel has a Scenario Manager feature that can handle the process for you.

As you've seen in this chapter, Excel has powerful features that enable you to build sophisticated models that can answer complex questions. The problem, though, isn't in answering questions, but in asking them. For example, Figure 2.7 shows a worksheet model that analyzes a mortgage. You use this model to decide how much of a down payment to make, how long the term should be, and whether to include an extra principal paydown every month. The Results section compares the monthly payment and total paid for the regular mortgage and for the mortgage with a paydown. It also shows the savings and reduced term that result from the paydown.

Figure 2.7. A mortgage-analysis worksheet.

Here are some possible questions to ask this model:

• How much will I save over the term of the mortgage if I use a shorter term and a larger down payment and include a monthly paydown?

• How much more will I end up paying if I extend the term, reduce the down payment, and forego the paydown?

These are examples of scenarios that you would plug into the appropriate cells in the model. Excel's Scenario Manager helps by letting you define a scenario separately from the worksheet. You can save specific values for any or all of the model's input cells, give the scenario a name, and then recall the name (and all the input values it contains) from a list.

Setting Up Your Worksheet for Scenarios

Before creating a scenario, you need to decide which cells in your model will be the input cells. These will be the worksheet variablesthe cells that, when you change them, change the results of the model. (Not surprisingly, Excel calls these the changing cells.) You can have as many as 32 changing cells in a scenario. For best results, follow these guidelines when setting up your worksheet for scenarios:

• The changing cells should be constants. Formulas can be affected by other cells, and that can throw off the entire scenario.

• To make it easier to set up each scenario, and to make your worksheet easier to understand, group the changing cells and label them (see Figure 2.7).

• For even greater clarity, assign a range name to each changing cell.

Adding a Scenario

To work with scenarios, you use Excel's Scenario Manager tool. This feature enables you to add, edit, display, and delete scenarios, as well as create summary scenario reports.

When your worksheet is set up the way you want it, you can add a scenario to the sheet by following these steps:

1.	Choose Tools, Scenarios. Excel displays the Scenario Manager dialog box, shown in Figure 2.8. Figure 2.8. Excel's Scenario Manager enables you to create and work with worksheet scenarios.
2.	Choose Add. The Edit Scenario dialog box appears. Figure 2.9 shows a completed version of this dialog box. Figure 2.9. Use the Add Scenario dialog box to define a scenario.
3.	Use the Scenario Name text box to enter a name for the scenario.
4.	Use the Changing Cells box to enter references to your worksheet's changing cells. You can type in the references (be sure to separate noncontiguous cells with commas) or select the cells directly on the worksheet.
5.	Use the Comment box to enter a description for the scenario. This appears in the Comment section of the Scenario Manager dialog box.
6.	Click OK. Excel displays the Scenario Values dialog box, shown in Figure 2.10. Figure 2.10. Use the Scenario Values dialog box to enter the values you want to use for the scenario's changing cells.
7.	Use the text boxes to enter values for the changing cells. Range Names for the Changing Cells You'll notice in Figure 2.10 that Excel displays the range name for each changing cell, which makes it easier to enter your numbers correctly. If your changing cells aren't named, Excel displays the cell addresses instead.
8.	To add more scenarios, click Add to return to the Add Scenario dialog box and repeat steps 37. Otherwise, click OK to return to the Scenario Manager dialog box.
9.	Click Close to return to the worksheet.

Displaying a Scenario

After you define a scenario, you can enter its values into the changing cells by displaying the scenario from the Scenario Manager dialog box. The following steps give you the details:

1.	Choose Tools, Scenarios to display the Scenario Manager.
2.	In the Scenarios list, click the scenario you want to display.
3.	Click Show. Excel enters the scenario values into the changing cells. Figure 2.11 shows an example. Figure 2.11. When you click Show, Excel enters the values for the highlighted scenario into the changing cells.
4.	Repeat steps 2 and 3 to display other scenarios.
5.	Click Close to return to the worksheet.