Defining Multiple Alternative Data Sets

One great feature of Excel 2007 scenarios is that you're not limited to creating one alternative data setyou can create as many as you like and apply them at will by using the Scenario Manager. To apply more than one scenario by using the Scenario Manager, click the name of the first scenario you want to display, click the Show button, and then do the same for the second scenario. The values you defined as part of those scenarios will appear in your worksheet, and Excel 2007 will update any calculations involving the changed cells.

Tip

If you apply a scenario to a worksheet and then apply another scenario to the same worksheet, both sets of changes appear. If the second scenario changes a cell changed by the first scenario, the cell reflects the value in the second scenario.

Applying multiple scenarios gives you an overview of how the scenarios affect your calculations, but Excel 2007 also gives you a way to view the results of all your scenarios in a single worksheet. To create a worksheet in your current workbook that summarizes the changes caused by your scenarios, open the Scenario Manager and then click the Summary button. When you do, the Scenario Summary dialog box appears.

From within the dialog box, you can choose the type of summary worksheet you want to create and the cells you want to appear in the summary worksheet. To choose the cells to appear in the summary, click the button in the box, select the cells you want to appear, and then expand the dialog box. After you verify that the range in the box represents the cells you want included on the summary sheet, click to create the new worksheet.

It's a good idea to create an "undo" scenario named Normal with the original values of every cell before they're changed in other scenarios. For example, if you create a scenario named High Fuel Costs that changes the sales figures in three cells, your Normal scenario restores those cells to their original values. That way, even if you accidentally modify your worksheet, you can apply the Normal scenario and not have to reconstruct the worksheet from scratch.

Tip

Each scenario can change a maximum of 32 cells, so you might need to create more than one scenario to restore a worksheet.

In this exercise, you create scenarios to represent projected revenue increases from two rate changes, view the scenarios, and then summarize the scenario results in a new worksheet.

USE the Multiple Scenarios workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Alternatives folder.

OPEN the Multiple Scenarios workbook.

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Scenario Manager. The Scenario Manager dialog box appears.
2.	Click Add. The Add Scenario dialog box appears.
3.	In the Scenario name field, type 3DayIncrease.
4.	At the right edge of the Changing cells field, click the Collapse Dialog button. The Edit Scenario dialog box collapses.
5.	Click cell C4 and then click the Expand Dialog button. Excel 2007 restores the Edit Scenario dialog box.
6.	Click OK. The Scenario Values dialog box appears.
7.	In the value field, type 11.50.
8.	Click OK. The Edit Scenario dialog box disappears, and the Scenario Manager reappears.
9.	Click Add. The Add Scenario dialog box appears.
10.	In the Scenario name field, type Ground and Overnight Increase.
11.	At the right edge of the Changing cells field, click the Collapse Dialog button. The Edit Scenario dialog box collapses.
12.	Ctrl+click cells C3 and C6 and then click the Expand Dialog button. Excel 2007 restores the Edit Scenario dialog box.
13.	Click OK. The Scenario Values dialog box appears.
14.	In the $C$3 value field, type 10.15.
15.	In the $C$6 field, type 18.5.
16.	Click OK. The Scenario Values dialog box disappears, and the Scenario Manager dialog box reappears.
17.	Click the 3DayIncrease scenario and then click Show. Excel 2007 applies the scenario to your worksheet.
18.	Click the Ground and Overnight Increase scenario and then click Show. Excel 2007 applies the scenario to your worksheet.
19.	Click Summary. The Scenario Summary dialog box appears.
20.	Verify that the Scenario summary option button is selected and that cell E8 appears in the Result cells field.
21.	Click OK. Excel 2007 creates a Scenario Summary worksheet.

CLOSE the Multiple Scenarios workbook.