In business it can often be beneficial to explore multiple scenarios for a given situation. This allows you to see what combination of factors gives the best results; in other words, test
various scenarios. With Excel 2010, you can easily explore multiple scenarios based on the same data.
In this lesson you will learn what a scenario is and how to create one. You will also learn about scenario summary reports and how to save multiple scenarios based on a given situation.
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
To create a scenario in Excel, begin with the worksheet that you want to add scenarios to. Then, click Data → What-If Analysis → Scenario Manager:
When you click on this option, the Scenario Manager dialog will appear:
To create a scenario, click the Add button in the upper right. This will display the Add Scenario dialog box:
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.
Next, 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.)
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:
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):
You can also describe the scenario in the “Comment” area of the dialog box if you wish. You will also see two check boxes 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.
If you check the “Hide” box, the scenario will be hidden if the corresponding worksheet is
Once you click OK, you will see the Scenario Values box.
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 textboxes in a dialog box by pressing the Tab key.)
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.
Click OK to create the scenario. You will then see the Scenario Manager Box with your newly created scenario available in the “Scenarios” area.
In this dialog box, you can see the changing cells in the “Changing cells” area, and any comments in the “Comment” area. 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.
Merging Scenarios
There are times when it could be useful to bring together scenarios from different worksheets.
For example, you could create a worksheet and then distribute copies to several co-workers.
Each person could create their own scenario (their own opinion of how the values will change) and then send the worksheets back to you. If you merged all of the scenarios from the different worksheets into one worksheet, you could compare all of the scenarios with a simple click of your mouse.
To merge scenarios from different worksheets, first open up each Excel workbook that contains a different scenario. It is important to note that merging scenarios works best if each worksheet has the same design and data layout, and if the changing cells in each scenario are in the same
Here is an example.
The preceding worksheet contains two scenarios: “Lower Wages” and “Actual Values.” For both of these scenarios, the changing cells are the cells with data in the “Wages” column.
The following image shows a similar worksheet from a different workbook. This worksheet has only one scenario, called “Increase wages.” The important thing to note is that the changing cells for the “Increase wages” scenario are the same as the changing cells for the two scenarios in the first worksheet.
To merge the scenarios from the two different worksheets, you should first open both of the respective workbooks at the same time. Next, you should decide which worksheet will be the destination for the merged scenarios and which worksheet will be the source. For this example, we will use the worksheet with the “Increase wages” scenario (preceding image) as the
destination for the merge.
Now, make the destination worksheet the active worksheet and then display the Scenario Manager dialog by clicking Data → What-If Analysis → Scenario Manager. When the dialog appears, click the Merge button:
A Merge Scenarios dialog will appear:
If you expand the drop list in the Merge Scenarios dialog you will see a list of the currently open workbooks. Select the workbook that contains the other scenarios and then select the
worksheet in that workbook that contains the specific scenario that you want to merge:
Once you choose the appropriate worksheet, click OK.
At this point, the Scenario Manager dialog for the active worksheet will appear. It will now contain the scenario(s) from the source worksheet that you chose in the Merge Scenarios dialog.
Now that the scenarios are merged into one worksheet, you can compare them easily. (You can repeat this process to merge additional scenarios from other open workbooks if you wish.)
Creating a Scenario Summary Report
A Scenario Summary report can be used to display and compare the scenarios stored with your worksheet. To create a summary report, open the worksheet containing the scenarios and click the Data tab. Next, click What–If Analysis → Scenario Manager:
When the Scenario Manager dialog appears, click the Summary button:
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 Wages, and Actual Values.)