The Goal Seek and Solver commands are extremely useful, though if you run several forecasts you can quickly forget what your original values were. 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 to 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.
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 a best-case and a worst-case scenario for sales in a coffee shop based on the number of cups of coffee sold in a week. Figure 23-6 shows a worksheet that contains three variable cells and several formulas that can serve as the basis for several scenarios. (This coffee sales worksheet is the same one we used in the Solver example, without the constraints table.) In the following example, we'll use this worksheet to show how to create a Best Case and a Worst Case sales scenario.
ON THE WEB
The Scenario.xls example is in the Running Office 2000 Reader's Corner page.
To create a scenario, follow these steps:
Figure 23-6. Before you create a scenario, you'll need to build a worksheet that has one or more formulas dependent on variable cells.
You might want to use cell names when you define 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.
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 will prompt you for a name, which you can use later to view the scenario in the Scenario Manager.
Excel keeps track of each of your worksheet scenarios. You can view them by choosing the Scenarios command on the Tools menu whenever your worksheet is open. Before you view a scenario, however, it's a good idea to save your workbook so that you can restore the original values in your worksheet if you want to.
To view a scenario, follow these steps:
Figure 23-7. The Show button lets you compare the results of different what-if scenarios in your worksheet.
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 in 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:
The Scenario Summary dialog box opens, prompting you for a result cell to total in the report and also for a report type. A scenario summary report is a formatted table displayed in its own worksheet. A pivot table is a special summary table whose rows and columns can be rearranged, or pivoted:
For more information about viewing pivot table reports, see "Rearranging Fields in a Pivot Table"
After a few moments, a new Scenario Summary tab will appear in your workbook, as shown in Figure 23-8. The outlining buttons in the left and top margins of your report will help you to shrink or expand the rows and columns in your scenario summary if you find it necessary to hide or expand values.
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, click the unwanted scenario's summary tab in the workbook, and then choose Delete Sheet from the Edit menu.
Figure 23-8. The Summary button creates a scenario summary report in a new worksheet in your workbook.
Managing Your Scenarios
Once you have defined a scenario 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 remove a particular scenario permanently, simply highlight it in the Scenario Manager dialog box, and click the Delete button. Finally, you can copy scenarios from other open workbooks into your current worksheet by clicking the Merge button in the Scenario Manager dialog box and specifying a source workbook and worksheet in the Merge Scenarios dialog