By Craig Stinson and Mark Dodge

To model problems that are more complicated than data tables can handle, involving as many as 32 variables, you can call on the services of the Scenario Manager in Microsoft Office Excel 2003. A scenario is a named combination of values that is assigned to one or more variable cells in a what-if model. You can use the Scenario Manager to enter variable figures in your what-if model and watch the effect on dependent computed values.

Here are some of the things you can do with the Scenario Manager in Excel:

• Create multiple scenarios for a single what-if model, each with its own sets of variables. You can create as many scenarios as your model necessitates.
• Distribute a what-if model to members of your team so that they can add their own scenarios. Then you can collect the versions and merge all the scenarios onto a single worksheet.
• Using Scenario Summary, examine relationships between scenarios created by multiple users.

Imagine that you manage a grocery store whose profit picture is modeled by the following worksheet.

The numbers in cells D2 through D5, and E8 through E13 are historic averages. Column C contains the range names applied to the relevant cells in columns D and E. You're interested in testing the impact of changes in these cells on the bottom line in cell E16. Using Scenario Manager, you can define the scenarios you'd like to test, view the impact of each scenario on your worksheet, modify and delete scenarios, and create summary reports for all the scenarios created on a spreadsheet.

Defining scenarios

To define a scenario by using the sample worksheet, follow these steps:

1. On the Tools menu, click Scenarios.
2. In the Scenario Manager dialog box, click Add.

   

3. In the Add Scenario dialog box, type a name for your scenario.
4. In the Changing cells box, either enter the cell references that you plan to vary or place the insertion point in the Changing cells box. On your worksheet, select the cells that you want to change. Use the CTRL key to select nonadjacent cells and ranges. Separate cell references by using commas.

    Note   As soon as you enter cell references in the Add Scenario dialog box, the title of the dialog box changes to Edit Scenario.

   

5. Click OK to create the first scenario. The Scenario Values dialog box appears, displaying the original values for each of the cells that you want to change. If you named the changing cells on your worksheet, those names are displayed; otherwise, only the cell references are displayed.
6. In each text field, enter the revised values that you'd like to test and then click OK.

    Note   It's a good idea to create a scenario that defines the values that you begin with before changing the values in the Scenario Values dialog box. If you don't create a starting scenario, you'll lose your original what-if assumptions when you apply the new scenarios to your worksheet.

   

7. To create another scenario and return to the Add Scenario dialog box, click Add in the Scenario Manager dialog box. Follow steps three through six in this procedure.
8. To save the scenarios on a worksheet, click Save on the toolbar. Scenarios are saved with all other worksheet data.

Viewing your scenarios

After you've created the scenarios that you'd like to test, you can view the changes that each scenario produces on your worksheet.

1. In the Scenario Manager dialog box, select a scenario name.
2. Click Show. The Scenario Manager replaces your current worksheet values with the values that you specified when you created the scenario.

    Note   The Scenario Manager dialog box is displayed on the screen while you use the Show button.

   

3. Click Close or press ESC to close the Scenario Manager dialog box. The values from the last scenario that you viewed remain on the worksheet.

Modifying and deleting scenarios

You can modify and delete existing scenarios by using the Scenario Manager dialog box:

1. In the Scenario Manager dialog box, click Edit. The Edit Scenario dialog box appears. Change the name of a selected scenario, or add or remove changing cells, and then click OK.
2. To delete a scenario from your worksheet, select the scenario name in the Scenario Manager dialog box and then click Delete.

Creating summary reports

You can use the Scenario Manager to create as many scenarios as you want (or as many as your computer can handle), with up to 32 variables per scenario. The Scenario Summary can help you keep track of all your scenarios.

1. In the Scenario Management dialog box, click Summary. In the Scenario Summary dialog box, make sure that Scenario summary is selected.
2. In the Result cells box, enter the cells that you want to appear in the report, separate them by commas, and then click OK. Choose cells that are dependent on the most changing cells — in this case, the Operating Profit value in cell E16, as well as cell E6, which contains the yearly Gross Profit value.

   

3. The Scenario Summary worksheet compares the changing cell values of all the scenarios that you have created.

   

4. Click one of the plus symbols located both above and to the left of the summary report. The contents in the Comment box in the Scenario Manager dialog box appear, including the creation date and modification date of each scenario.

   

About the authors   Craig Stinson is a contributing editor for PC Magazine. Mark Dodge is a former senior technical writer for the Microsoft Office User Assistance group. Craig and Mark are the coauthors of Microsoft Office Excel 2003 Inside Out (Microsoft Press, 2004), from which this article is adapted.