Show All

Hide All

This article was adapted from Microsoft Office System Inside Out, 2003 Edition by Michael J. Young and Michael Halvorson. Visit Microsoft Learning to buy this book. Michael J. Young is an award-winning author who’s written more than 15 books on using and programming computers, including Microsoft Office XP Inside Out, XML Step by Step and several editions of Running Microsoft Office. Michael Halvorson has written more than 20 computer books, including the classic Microsoft Visual Basic 6 Professional Step by Step and the popular Microsoft Office XP Inside Out (with Michael Young). He is a former technical editor, acquisitions editor, and localization manager for Microsoft.

In this article

Creating a scenario

Viewing a scenario

Creating scenario reports

Managing your scenarios

Sample files  You can download the sample files that relate to excerpts from Microsoft Office System Inside Out from Microsoft Office Online. This article uses the file Scenario.xls.

Although the Goal Seek and Solver commands are extremely useful, if you run several forecasts you can quickly forget the results for each forecast. More important, you have no real way to compare the results of the Goal Seek and Solver commands. Each time you change the data, the previous solution is lost. To address this limitation, the Scenario Manager helps you keep track of multiple what-if models. Using the Scenarios command on the Tools menu, you can create new forecasting scenarios, view existing scenarios, run scenario management commands, and display consolidated scenario reports. We’ll show you each technique in this section.

Creating a scenario

A scenario is a named what-if model that includes variable cells linked together by one or more formulas. Before you create a scenario, you must design your worksheet so that it contains at least one formula that’s dependent on cells that can be fed different values. For example, you might want to compare best-case and worst-case scenarios for sales in a coffee shop, based on the number of cups of coffee sold in a week. The following figure shows a worksheet that contains three variable cells and several formulas that can serve as the basis for several scenarios. In the following example, we will use this worksheet to show how to create a best-case and a worst-case sales scenario.

 Dependant scenario formula

 Variable cells

To create a scenario using the active workbook, follow these steps:

1. Click Scenarios on the Tools menu.

   The Scenario Manager dialog box opens.

   

2. Click the Add button to create your first scenario.

   The Add Scenario dialog box opens.

3. Type Best Case (or any other suitable name) in the Scenario Name box, and then press the TAB key.
4. In the Changing Cells box, specify the variable cells that you want to modify in your scenario.

   You can type cell names, highlight a cell range, or hold down the CTRL key while you click individual cells to add them to the text box. (If you hold down the CTRL key, Excel will automatically place commas between the cell names.) To follow our example, hold down the CTRL key while you click cells D5, D9, and D13.

   The dialog box should now look like this.

   

   Tip

   You might want to define cell names for your variable cells. That way, you’ll have an easier time identifying your variables when you create your scenarios and when you type in arguments later.
5. Click the OK button in the Add Scenario dialog box to add your scenario to the Scenario Manager. You’ll see the Scenario Values dialog box, which asks you for your model’s variables. The default values are the numbers that were already in the cells.
6. Type 150, press TAB, type 225, press TAB, and then type 125.

   Your screen should now look like this.

   

7. Click the Add button in the Scenario Values dialog box to return to the Add Scenario dialog box, where you’ll create a second scenario.
8. Type Worst Case in the Scenario Name box, and then click the OK button to open the Scenario Values dialog box again.
9. In the Scenario Values dialog box, type 50, 40, and 30 in the boxes for the variable cells, and then click the OK button. (These values represent our guess at the worst case.)

   The Scenario Manager dialog box opens, listing the Best Case and Worst Case scenarios that you just created. Now you’re ready to view the results of your forecasting models.

10. Click the Close button to close the Scenario Manager dialog box.

 Tip   You can save Solver problems as scenarios for future trials by clicking the Save Scenario button in the Solver Results dialog box when the Solver computes a new forecast. The Solver prompts you for a name, which you can use later to view the scenario in the Scenario Manager.

 Top of Page

Viewing a scenario

Excel keeps track of each of your worksheet scenarios. You can view them by clicking Scenarios on the Tools menu whenever your worksheet is open.

Inside Out Tip: Save your workbook before loading a scenario

To view a scenario, follow these steps:

1. On the Tools menu, click Scenarios.

   The Scenario Manager dialog box opens.

   

2. In the Scenarios list, click the scenario that you want to view.
3. Click the Show button.

   Excel will substitute the values in the scenario for the variables on your worksheet and display the results on your worksheet, as shown in the preceding figure. (The Scenario Manager dialog box will remain open. You might need to move it to view the results.)

4. Select additional scenarios, and click the Show button to compare and contrast the various what-if models on your worksheet.
5. When you’re finished, click the Close button to close the Scenario Manager dialog box.

   The last active scenario will remain on your worksheet.

 Top of Page

Creating scenario reports

Although you can easily compare different scenarios by switching between them using the Show button in the Scenario Manager dialog box, you might occasionally want to view a report that contains consolidated information about the scenarios on your worksheet. You can accomplish this quickly by clicking the Summary button in the Scenario Manager dialog box. Excel will automatically format the summary report and copy it to a new worksheet in your workbook.

To create a scenario report, follow these steps:

1. Activate the worksheet containing the scenario or scenarios you want to use for your report.
2. Click Scenarios on the Tools menu to display the Scenario Manager dialog box.
3. Click the Summary button to open the Scenario Summary dialog box.

   

   The Scenario Summary dialog box prompts you for a result cell to total in the report, and also for a report type. A scenario summary report is a formatted table that appears on its own worksheet. A PivotTable report is a special summary table whose rows and columns can be rearranged, or "pivoted."

4. Select the result cell that you want to total (cell G4 in this example), click the report option button that you want to use (accept the Scenario summary default if you’re not sure), and then click the OK button.

   After a short pause, a new Scenario Summary tab will appear in your workbook, as shown in the following figure. The outlining buttons in your report’s left and top margins will help you shrink or expand the rows and columns in your scenario summary.

   

    Excel uses the defined names for variable cells D5, D9, and D13, which results in a more comprehensible summary

    Note    Each time you click the Summary button in the Scenario Manager dialog box, Excel creates a new summary worksheet in your workbook. To delete unwanted summary reports, activate the worksheet containing the unwanted scenario summary, and then click Delete Sheet on the Edit menu.

 Top of Page

Managing your scenarios

Once you’ve defined a scenario by using the Add button, luckily you’re not stuck with that scenario forever. You can edit and delete scenarios by clicking the Edit and Delete buttons in the Scenario Manager dialog box. The Edit button lets you change the name of the scenario, remove existing variable cells, add new variable cells, or even choose a completely new group of variables. To assign different values to the variable cells in a scenario, clear the Prevent Changes option in the Edit Scenario dialog box. After you click the OK button, Excel will display the Scenario Values dialog box. Make any changes you want to make, and then click the OK button.

To remove a particular scenario permanently, simply select it in the Scenario Manager dialog box, and then click the Delete button.

Finally, you can copy scenarios into the active worksheet from any other worksheet in a currently open workbook. To do this, click the Merge button in the Scenario Manager dialog box, and then specify a source workbook and worksheet in the Merge Scenarios dialog box.

 Top of Page