Exploring Scenarios

What is a Scenario?

In Excel, a scenario can be described as a set of cell values that is saved and substituted into your worksheet at your convenience. If you have multiple scenarios saved, you can load different scenarios into your worksheet and compare and contrast them to see which particular set of values gives the best results. Since the worksheet will be fully calculated according to the given set of scenario data being used, you can compare and contrast the results of one set of data (scenario) with another by simply switching between different scenarios. Used in this way, Excel scenarios provide a powerful tool for performing what-if analysis.

You might use scenarios to represent different budget options, or perhaps evaluate different financial forecasts. You can use Scenarios to compare different data projections based on any number of factors.

This is how it works; 

• The data that makes up your scenario is saved with your workbook, but it remains hidden until you use the Scenario Manager to load the scenario data. 
• All of the scenarios you create are just versions of the same worksheet layout, with each different scenario having different values for certain key cells.

Creating a Scenario

1. To create a scenario in Excel, begin with the worksheet that you want to add scenarios too. Then, click Data → What-If Analysis → Scenario Manager.
2. When you click on this option, the Scenario Manager dialog will appear.
3. To create a scenario, click the Add button in the upper right. This will display the Add Scenario dialog box. 
   
4. To enter a scenario name, make sure that your cursor is in the “Scenario name” text box and type a name that describes the scenario you are creating. 
5. Decide which cells you want to change with this scenario, and then put your cursor in the “Changing cells” textbox. Select the cells that will be changing with your mouse. (Remember that you can use the Ctrl key for selecting non-adjacent cells.)
6. If the Add Scenario dialog box gets in the way when you are selecting cells, you can drag it to one side, or collapse it by clicking the small Collapse Dialog button. 
7. In the following image, you can see the name for the scenario is Change Wages, and the Changing Cells are G2 to G9 (the Wages column in the worksheet): 
   
   
   
8. You can also describe the scenario in the “Comment” area of the dialog box if you wish. You will also see two checkboxes at the bottom of the dialog box: “Prevent changes,” and “Hide.” If you check the “Prevent changes” box, changes to this scenario will not be permitted if the corresponding worksheet is protected.
9. If you check the “Hide” box, the scenario will be hidden if the corresponding worksheet is protected. 
10. Once you click OK, you will see the Scenario Values box. 
    
11. Here you will see the references for the cells that you selected for your scenario. When you use this scenario in the future, the values you enter here will be loaded into the corresponding worksheet. (Remember, you can move between text boxes in a dialog box by pressing the Tab key.) 
12. Notice that they are populated with the current worksheet data by default. If you want to save the current (actual) data as a scenario, you could just click OK at this point. Otherwise, you should enter the appropriate new value that you want for this scenario in each box. 
13. Click OK to create the scenario. You will then see the Scenario Manager Box with your newly created scenario available in the “Scenarios” area. 
    
14. In this dialog box, you can see the changing cells in the “Changing cells” area, and any comments in the “Comment” area. 
15. If there is an assortment of scenarios in the scenario manager, just click the one that you want and click Show to see the results of the given scenario in the worksheet.

Saving Multiple Scenarios

• To save multiple scenarios for a worksheet, click Data tab → What–if Analysis → Scenario Manager to display the Scenario Manager dialog. 
• Then, use the Add button in the Scenario Manager to create as many scenarios as you require (following the process as described previously). 
• All of the scenarios you create for a given worksheet will be available in the scenarios list in the Scenario Manager dialog. 
• In the following image, you can see four scenarios in the scenario list. These scenarios will be saved with this workbook when the workbook itself is saved. When you open the workbook, you can see the available scenarios by displaying the Scenario Manager. 
  
• To load a scenario into the worksheet, click to select it from the Scenarios list and click the Show button at the bottom of the dialog. To remove a scenario, click to select it in the list and then click Delete. Finally, to make changes to a scenario, select it, and then click Edit. 
• The Edit command will invoke the Edit Scenario dialog: 
  

• This dialog allows you to change the name, changing cells, cell values, and comments associated with the selected scenario. (This is essentially that same dialog that is used in the process of creating a scenario.)
• If you wish, you can have a different set of scenarios for each worksheet in your workbook. It is important to note that when you display the Scenario Manager, only scenarios for the currently active worksheet will be shown in the Scenarios list.

Creating a Scenario Summary Report

A Scenario Summary report can be used to display and compare the scenarios stored with your worksheet.

1. To create a summary report, open the worksheet containing the scenarios and click the Data tab. Next, click What-If Analysis → Scenario Manager.
2. When the Scenario Manager dialog appears, click the Summary button.
3. This will display the Scenario Summary box. Here you can choose to create a PivotTable (based on the scenario data) or a scenario summary by selecting the appropriate radio button. (You will learn more about PivotTables later in this manual.) 
   
   
   

In this example, the Scenario Summary radio button is selected. The next step is to select the result cells that you want to be shown in the summary report. You can do this by selecting cells with your mouse on the worksheet, just as before. Finally, click OK to create the summary: 

The scenario summary will appear on its own sheet. As you can see, it shows the changing cells and the results cells for the scenarios that are stored with your worksheet. The changing cells that have values different from the current values will be highlighted in grey.

Notice the row of scenario names across the top of the columns to identify which scenario a given column of values belongs to. (The scenarios in the report shown above are: Current values, Lower Increment, Ideal, High Increment and Original.)