Storing Multiple Scenarios in a Single Workbook
One of Excel's most valuable hidden features is its capability to store multiple scenarios within a single workbook. Creating scenarios helps you plan for a future in which you can't be certain that your worksheet model is accurate, especially when a change in one assumption will have a ripple effect on other values that affect the bottom line. Scenarios are useful in these two circumstances:
When the underlying assumptions depend on external factors out of your control, such as the weather or the economy. For example, if you sell heating oil or umbrellas, you might create a P&L forecast using multiple scenarios to compare the results of strong sales in cold, wet weather and weak sales if the winter is mild.
When you want to perform what-if analyses that test the bottom-line effect of price increases or capital expenditures. For instance, the classic demand curve from Econ 101 says that raising prices past a certain point might reduce demand so much that profits actually suffer, whereas cutting prices might cause sales to grow tremendously and swell the bottom line. Use scenarios in combination with market research to test the bottom-line impact of a 10% price cut, a 5% price hike, and a 10% increase.
Tip from 
You often can accomplish the same goal by creating multiple workbooks within a single worksheet, starting with a basic model and creating a copy for each scenario—best case, worst case, and so on. For simple analyses, where the underlying assumptions remain the same and you simply need to show revised totals, you also can add an extra row or column using slightly different formulas. Reserve scenarios for complex analyses where the changing assumptions include many related factors.
Adding scenarios to a worksheet is a three-step process. First, create a worksheet that includes all the data and formulas you want to use in your comparison. Next, select specific cells you want to change as part of each scenario. (The changing cells typically contain values used in one or more formulas in the sheet.) Finally, create named scenarios and enter the data in the changing cells for each one. By switching between scenarios, you can watch the bottom line change—and even change the bars and columns in charts based on data in the worksheet.
When putting together a fiscal forecast, for example, you might want to test the impact of various growth scenarios on your expenses. In the first scenario, you increase the number of employees (and thus salaries, payroll taxes, benefits, and other expenses tied to head count) to handle anticipated new business. In the second scenario, you freeze hiring and outsource the increased workload instead (keeping salaries at their current level but increasing expenses for contract labor). In the third scenario, you analyze what happens if you kick off a major hiring program and rent new office space (besides the increases in labor costs, you'll have to adjust for higher rent, utilities, and maintenance).
To create a worksheet that includes multiple scenarios, follow these steps:
Create the basic worksheet containing all the data and formulas you want to use in your first scenario.
Select up to 32 cells that will change in each scenario. Although you can select the changing cells and enter data at the same time you create a scenario, it is much, much easier to build scenarios in this order.
Select Tools, Scenarios. In the Scenario Manager dialog box, click the Add button to display the Add Scenario dialog box shown in Figure 26.14.
Figure 26.14. Give each scenario a name and add a descriptive comment; if you selected the changing cells before displaying this dialog box, your selection appears here.
Caution
There's no requirement that you select the same set of changing cells in each scenario; however, we strongly recommend that you do so. The purpose of creating scenarios is to show you various outcomes when you control specific input assumptions. If you use different changing cells in each scenario, your workbook essentially becomes an uncontrolled experiment, and the same data could result in different results for a given scenario depending on which scenarios you view first.
In the Scenario Name box, enter a name that describes the scenario you're creating. You'll use this name to view and edit scenarios later, so keep the name short and meaningful: Best case and Worst case, for example. By default, the Comment box at the bottom of this dialog box contains your name and the date you created the scenario. If your scenario includes assumptions that aren't readily apparent, add a detailed description here.
Select protection options for the scenarios on the current worksheet. (By default, the Prevent Changes option is checked and the Hide option is unchecked.) Click OK. The Scenario Values box appears, as shown in Figure 26.15.
Figure 26.15. Use this dialog box to enter values for the changing cells in each scenario.
If you've enabled protection for scenarios but other users are able to change your data, see the "Troubleshooting" note, "Protection Is a Two-Step Process," at the end of this chapter.
Check the values you want to use for each of the changing cells in this scenario, and change any of them, as necessary.
Tip from 
Normally, the Scenario Values dialog box displays cell references. However, if you use range names for the changing cells, those names will appear in this dialog box, making entering data and verifying that the values in each changing cell make sense much easier.
If your other scenarios are relatively uncomplicated, with only a handful of changing cells, click the Add button and enter the numbers for each scenario directly in this dialog box. If the underlying worksheet is complex, however, you'll probably find it easier to close the Scenario Manager dialog box, enter your data, and repeat steps 2–6 to create additional scenarios. When you finish, the dialog box should look something like the one in Figure 26.16.
Figure 26.16. Select a scenario and click the Show button to display the worksheet with that scenario's values.
To view different scenarios, open the Scenario Manager dialog box, select a scenario name, and click the Show button (or double-click the scenario name). You can drag the Scenario Manager dialog box out of the way to see the changed values in your worksheet. If you want to scroll or edit the worksheet, however, you must close this dialog box.
Tip from 
Viewing a worksheet that contains scenarios can be confusing. Unless the Scenario Manager dialog box is open, how do you know which scenario you're looking at? Use text labels to solve this problem elegantly. Although it's most common to select cells with numeric values as the changing cells in a worksheet with scenarios, you can also enter text in a changing cell. Set aside one cell on your worksheet to contain a description of the current scenario and make it a changing cell as well. When you create each scenario, be sure the contents of this cell are accurate. Using this technique, you'll always be able to see which scenario is on display, just by looking at this cell.
If you get a cryptic error when you try to view a scenario, there's probably a simple explanation. See "Merged Cells and Scenarios Don't Mix" in the "Troubleshooting" section at the end of this chapter.
After creating scenarios, you can add, remove, or change scenarios by using the same basic procedures. Two options in the Scenario Manager dialog box are particularly interesting for advanced users:
Merge Button— Click this to consolidate the data from changing cells into a new worksheet. Start in a new worksheet and open the workbooks that contain the scenarios you want to merge. The Merge Scenarios dialog box lists all open workbooks, and when you select one, you can pick from a list of worksheets within that workbook (the status bar tells you whether any scenarios exist in the sheet you've selected). Click OK to open the Scenario Manager dialog box, and then click Show to add the data from the changing cells to the current sheet. This technique is especially useful when you've passed out several copies of a worksheet template and you want to copy some but not all of the scenarios to the new sheet.
Summary Button— Click this to produce a report that arranges the values from the changing cells in all scenarios into a new sheet in the current workbook. Select the Scenario Summary option to display the data in a neatly formatted table; use the Scenario PivotTable option to display data in a format in which you can manipulate it further.