I have introduced you to how to do sensitivity analysis using a data table in one of my previous posts – Sensitivity Analysis In Excel Using One or Two Variables Data Table. It is useful but it only can tell how one or two different sources of uncertainty can affect the final output in a mathematical model. What if we want to vary more than two inputs? Obviously, this can happen in real life. What should we do?
Luckily, Excel provides us with a Scenario Manager to handle this situation. It allows you to vary as many as 32 inputs. To facilitate the management of so many various inputs, Excel provides a name – Scenario – to represent a set of different input values and the corresponding calculated output value. For each scenario, you need to give it a unique name. The scenarios can be saved as part of the workbook. You can switch between scenarios to see how the inputs can affect the final output by double-clicking on any of the scenarios in the Scenario Manager dialogue box. Moreover, with the saved Scenarios, Excel can also create a beautiful summary report containing the various sets of inputs and corresponding final outputs to facilitate review.
How to create scenarios
Suppose that we are going to sell a book and would like to know how the Sale Units, Price per Unit, and Variable Cost per Unit can affect the final profits. Figure 1.1 shows how the dataset is constructed. The profit is dependent on Sale Units (Cell C2), Price per Unit (Cell C3), and the Variable Cost per Unit (Cell C5). Therefore, we typed the formula “=C2*C3-C4-C2*C5” in Cell C6.
Now let’s see how to set up a Scenario Manager. Go to the Data tab and click on What-If Analysis in the Data Tools group. In the drop-down, choose Scenario Manager.
In the Scenario Manager dialogue box, click on Add button.
In the prompted Add Scenario dialogue box, fill in the required details. Enter a name (Worst Case) for the Scenario name field. Add any comment that you wish to into the Comment box. Or you can also leave it blank. As for the Changing cells, fill in all the reference cells (C2, C3, C5 in this case) that contain the input values. Please note that the references must be separated by commas. Or, just press CTRL key on your keyboard and select all the cells, one by one, that contain the input values.
Click OK to open the Scenario Values dialogue box. Fill in the Scenario Values dialog box with the input values that define the worst case, as shown in Figure 1.5.
Click on OK, the Worst Case scenario will be successfully created. Since we’d like to create another scenario, we click on Add button. After clicking on Add, another Add Scenario dialogue box will appear. Use the same approach that we applied when creating the Worst Case scenario to build the Best Case scenario. The details are shown in following Figure 1.6. Please note that Excel has set Changing Cells for Worst-Case scenario as the default Changing Cells for Best Case scenario.With the same approach, create the Most Likely Case scenario. Here Figure 1.7 presents the details. You can also use the same above approach to creating other scenarios if you have other combinations of input values. In this example, we assume that there are only 3 scenarios available and thus we click on the OK button in the Scenario Values dialogue box (in Figure 1.7). Now from Figure 1.8, you can see that three scenarios have been successfully created and they are listed in sequence. Click on Close, the Scenario Manager dialogue box will be closed.
How to view different scenarios
So far, you have saved all of those 3 scenarios in your workbook. If you go to the Data tab and click on What-If Analysis in Data Tools Group, then choose Scenario Manager in the drop-down, you will see the same Scenario Manager dialogue box as that is shown in Figure 1.8. The Scenario Manager dialogue box is no longer blank. Now you can view the result from each of the scenarios by simply double-clicking on any of the scenarios. For example, if we double click on Worst-Case, the input values in the Excel worksheet will change into what has filled for Worst Case (compare left part of Figure 1.9 against Figure 1.5 for details) and the output value will be calculated automatically based on the formula in cell C6. To view a specific scenario and its corresponding outputs, you can also click on that scenario and then click on the Show button at the bottom. The right part in Figure 1.9 shows how the Excel worksheet looks like if we click on Best Case scenario and then click on Show.
How to create a summary report
I have already told you in the introduction part that Excel can create a summary report based on the saved scenarios. Now let’s see how to make a summary report. Go to the Data tab and click on What-If Analysis, then choose Scenario Manager in the drop-down to open the Scenario Manager dialogue box. In the prompted Scenario Manager dialogue box, click on the Summary button.
After clicking on Summary, a Scenario Summary dialog box appears for you to put Result cells (C6 in this case) and choose between Scenario summary and Scenario PivotTable report.
The left part in Figure 1.12 shows that a Scenario summary report with all of the three scenarios is created with a new tab if you choose Scenario summary in Figure 1.11. Otherwise, a Scenario PivotTable report is created with a new tab (right part of Figure 1.12).Did you notice that minus (-) sign in the left part of Figure 1.12? It is an Excel Outline feature. If you click on it, the Scenario summary report will hide all the cells containing the input values and show only the result cells (see Figure 1.13). And the minus sign (-) will be replaced with a plus sign (+). You can also click on other minus or plus signs to see what will happen.
- It’s hard to create lots of scenarios with the Scenario Manager because you need to input each individual scenario’s input values. The work will be time-consuming and also expose you to a high risk of making a mistake.
- Suppose that you send a file to several people and ask them to add their own scenarios. After you receive all workbooks, you can merge all the scenarios into one workbook. Open each person’s workbook and click the Merge button in the Scenario Manager dialog box in the original workbook. In the Merge Scenarios dialog box, select the workbook containing the scenarios that you want to merge. Do the same things with all of the workbooks. Figure 1.14 shows you how to choose the workbook in the Merge Scenarios dialog box.
Download working file
Download the working files from the link below.