Before analyzing how to use scenario manager in Excel 2013, it is better you get some idea about the limitations of one and two variables data tables.
Table of Contents
- 1 Limitations of data tables
- 2 Scenario Manager
- 2.1 Defining scenarios
- 2.2 Table: Company Production can face the following three Scenarios
- 3 Step by step procedure to create Scenario Manager
- 3.1 Adding a scenario
- 3.2 Adding values to scenarios
- 3.3 Displaying scenarios
- 3.3.1 Best Case Scenario
- 3.3.2 Worst Case Scenario
- 3.4 Modifying scenarios
- 3.5 Merging scenarios
- 3.6 Generating a scenario report
- 3.6.1 Scenario Summary report
- 3.6.2 Scenario PivotTable report
Limitations of data tables
Data tables are useful, but they have some limitations:
- In data tables, you can vary only one or two input cells at a time.
- Setting up a data table is not very easy.
- A two-input table shows the results of only one formula cell. To get results of more formula cells, we can create additional data tables.
- In maximum cases, we’re interested to see results of selected combinations, not the entire table where the table will show the all possible combinations of two input cells.
The Scenario Manager is an easy way to automate some inputs of our what-if models. We can store different sets of input values (they are called changing cells in Scenario Manager) for any number of variables and give a name to each set. We can then select a set of values by name, and Excel shows the worksheet by using those values. We can also generate a summary report that displays the effect of various combinations of values on any number of result cells. These summary reports can be an outline or a pivot table.
For example, you have a company and your company’s annual sales forecast may depend upon several factors. Moreover, you can define three scenarios: best case, worst case, and most likely case. You can then switch to any of these scenarios by selecting the named scenario from a list. Excel will substitute the appropriate input values in your worksheet and will recalculate the formulas according to scenario.
To introduce you to Scenario Manager, we have started this section with a practical example. The example is a simplified production model as shown in the following figure.
The above worksheet contains four input cells: the Printing cost per book (B9), Binding cost per book (B10), Packing cost per book (B11) and Transportation cost per book (B12). The company produces four products, and each product requires a different number of count.
Formulas calculate the total profit per product (F13:J13). The company management— trying to predict the total profit, but in uncertain situation when the above four costs will be different. The company has identified three scenarios listed in following Table.
Table: Company Production can face the following three Scenarios
|Scenario||Best Case||Worst Case||Most Likely|
|Printing cost par book||1,500||1,600||1,550|
|Binding cost par book||100||120||110|
|Packing cost par book||25||30||27|
|Transportation cost par book||12||15||13|
As expected, in the Best Case scenario the company will have the lowest cost. The Worst Case scenario will have the highest values for costs. The third scenario is the Most Likely case. It will have the intermediate values for costs. The company managers must be prepared for the worst case, however, and they will optimize their performance controlling the scenario under the Best Case scenario.
Step by step procedure to create Scenario Manager
Choose Data ➪ Data Tools ➪ What-If Analysis ➪ Scenario Manager. Scenario Manager dialog box will appear in the screen. When we first open this dialog box, it shows that No Scenarios defined. Choose Add to add scenarios.. It is not surprising because we’re just starting. When we shall add named scenarios, they will appear in the Scenarios list in the dialog box.
Creating names for the changing cells and all the result cells that you want to examine, is a good idea. Excel will use these changed names in the dialog boxes and in the reports that it will generate. Keeping track of what’s going on is easier, if you use names. Changed names also make your reports more readable.
Adding a scenario
Click the Add button in the Scenario Manager dialog box to add a scenario. Excel will display the Add Scenario dialog box, shown in the following figure.
The Add Scenario dialog box consists of four parts:
- Scenario Name: You can give any name for this Scenario name field. Given name should be something meaningful.
- Changing Cells: These are the input cells for the scenario. You can enter the cell addresses directly or point to them. If you’ve given names for the cells, type the name. Nonadjacent cells are allowed for this field. If you are required to point multiple cells, press Ctrl while you click the cells. Not necessarily, every scenario will use the same set of changing cells. Different scenario can use different changing cells. The number of changing cells is not unlimited for a scenario; it is limited to 32.
- Comment: By default, Excel shows the name of the person who created the scenario and the date when it was created. But you can change this text, add new text to it, or delete it completely.
- Protection: The two Protection options are preventing changes and hiding a scenario. These two are in effect only when you protect the worksheet and choose the Scenario option in the Protect Sheet dialog box. When you’re protecting a scenario, it will prevent anyone from modifying it; a hidden scenario doesn’t appear in the Scenario Manager dialog box.
Adding values to scenarios
In our example, we shall define the three scenarios that are listed in above Table. The changing cells are B9:B12.
For example, we entered the following information in the Add Scenario dialog box to enter the Best Case scenario. Entered “Best Case” in the Scenario name field, then selected B9:B12 cells and then edited “Created by Mostofa Monower on 1/19/2015” to the Comment box. By default Prevent Changes is check-marked under Protection option.
After you enter the information in the Add Scenario dialog box, click OK. Excel will now display the Scenario Values dialog box, shown in the following figure. This dialog box displays every field we’ve entered into the changing cell that we’ve specified in the previous dialog box. Enter the values for each cell in the scenario.
As we have more scenarios to add, we clicked Add button. When we’re done with entering all the scenarios we shall click OK and Excel will return us to the Scenario Manager dialog box, which then will display our entered scenarios in its list.
Now we have three scenarios (Best Case, Worst Case, and Most Likely) listed in the Scenario Manager dialog box. Select one of the listed scenarios and then click the Show button (or double-click the Scenario name) to display the results of the scenario. Excel inserts the corresponding values into the changing cells and the results of that scenario is showed in the worksheet. The following two figures show the example of selecting two scenarios (Best Case and Worst Case).
Best Case Scenario
Worst Case Scenario
Most Likely Case Scenario
It is possible to modify the scenario after we’ve created them. To do so, follow the steps below:
- From the Scenarios list, select the scenario that you want to change and then click the Edit button.The Edit Scenario dialog box will appear.
- Change whatever you need to do in Edit Scenario dialog box. You can change the name of the scenario. You can also change the Changing cells field as per your requirement. If you’re done, click OK. The Scenario Values dialog box will appear.
- Make your changes in the Scenario Values dialog box and then click OK to return to the Scenario Manager dialog box. Notice that Excel automatically updates the Comments box with new text that shows when the scenario was modified.
The company may have several people working on a spreadsheet model, and several people may have defined various scenarios. For example, the marketing department may have its opinion of what the input cells would be, the finance department may have another opinion, and the company CEO may have another opinion.
Excel makes it easy to merge these various scenarios into a single workbook. Before you merge scenarios, you’ve to make sure that the workbook from which we’re merging is open:
- Click the Merge button in the Scenario Manager dialog box. Merge Scenarios dialog box will appear.
- From the Merge Scenarios dialog box, choose the workbook from where you want to add the scenarios from Book drop-down list.
- Choose the sheet that contains the scenarios you want to merge from the Sheet list box. Notice that the dialog box displays the number of scenarios in each sheet as you scroll through the Sheet list box.
- Click OK. You will return to the previous dialog box, which now displays the scenario names that you merged from the other workbook.
Generating a scenario report
If you’ve created multiple scenarios, you may want to document your work by creating a scenario summary report. When you click the Summary button in the Scenario Manager dialog box, Excel displays the Scenario Summary dialog box.
You have two choices to create report:
- Scenario Summary: This summary report appears in the form of a worksheet outline.
- Scenario PivotTable: This summary report appears in the form of a pivot table.
For simple cases of scenario management, a standard Scenario Summary report is usually sufficient. If you have many scenarios defined with multiple result cells, however, you may find that a Scenario PivotTable provides more flexibility.
The Scenario Summary dialog box also asks you to mention the result cells (the cells that contain the formulas in which you’re interested). For this example, we’ve selected B13:D13 and B15 (a multiple selection) to make the report show the profit for each product, plus the total profit.
When you work with Scenario Manager, you may discover its main limitation: namely, that a scenario can use no more than 32 changing cells. If you attempt to use more cells, you will get an error message.
Excel automatically creates a new worksheet to store the summary table. The following two figures show the Scenario Summary and Scenario PivotTable form of the report. If you’ve given names to the changing cells and result cells, the table uses these names; otherwise, it lists the cell references.
Scenario Summary report
Scenario PivotTable report