How to Use Scenario Manager in Excel [with Examples]

I have introduced you with how to do sensitivity analysis using 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?

Introduction

Luckily, Excel provides us with 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 formula “=C2*C3-C4-C2*C5” in Cell C6.

Scenario Manager in Excel Img1

Figure 1.1

Now let’s see how to set up a Scenario Manager. Go to Data tab and click on What-If Analysis in Data Tools group. In the drop-down, choose Scenario Manager.

Scenario Manager in Excel Img2

Figure 1.2

In the Scenario Manager dialogue box, click on Add button.

Scenario Manager in Excel Img3

Figure 1.3

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 the 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.

Scenario Manager in Excel Img 4

Figure 1.4

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.

Scenario Manager in Excel Img 5

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 Worst Case scenario to build 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.

Scenario Manager in Excel Img 6

Figure 1.6 [click on the image to get an enlarged view]

With the same approach, create Most Likely Case scenario. Here Figure 1.7 presents the details.

Scenario Manager in Excel Img7

Figure 1.7 [click on the image to get an enlarged view]

You can also use the same above approach to creating other scenarios if you have other combination of input values. In this example, we assume that there are only 3 scenarios available and thus we click on the OK button in 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.

Scenario Manager Excel Image 8

Figure 1.8

How to view different scenarios

So far, you have saved all of those 3 scenarios in your workbook. If you go to 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 Excel worksheet will change into what have 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.

Scenario Manager in Excel Image 9

Figure 1.9 [click on the image to get an enlarged view]]

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 Data tab and click on What-If Analysis, then choose Scenario Manager in the drop-down to open Scenario Manager dialogue box. In the prompted Scenario Manager dialogue box, click on Summary button.

Scenario Manager in Excel Image 10

Figure 1.10

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.

Scenario Manager in Excel Image 11

Figure 1.11

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).

Scenario Manager in Excel Image 12

Figure 1.12 [click on the image to get an enlarged view]

Did you notice that minus (-) sign in left part of Figure 1.12? It is 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 sign to see what will happen.

Scenario Manager in Excel Image 12

Figure 1.12

Notes

  1. 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.
  2. Suppose that you send a file to several people and ask them to add his own scenarios. After you receive all workbooks, you can merge all the scenarios into one workbook. Open each person’s workbook and click 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 Merge Scenarios dialog box.
    Scenario Manager in Excel Image 13

    Figure 1.13

     

Read More…

Download working file

Download the working files from the link below.


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

3 Comments
  1. Reply
    Sam Glenn September 20, 2016 at 12:26 AM

    Excellent presentation.

    • Reply
      Zhiping September 20, 2016 at 12:30 PM

      Thanks for your recognition.

    • Reply
      Kawser September 20, 2016 at 1:28 AM

      Thanks, Sam! I will reach it to Zhiping 🙂

    Leave a reply