How to Use Scenario Manager in Excel (with Easy Steps)

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? Today, in this article, we’ll learn five quick and suitable steps to use scenario manager in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to Scenario Manager

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.


4 Quick Steps to Use Scenario Manager with Example in Excel

Let’s get introduced to our dataset first. I have Sale Units, Price per Unit, and Variable Cost per Unit on my sheet and I’ll use Scenario Manager in Excel using this dataset.

use scenario manager in excel


Step 1: Create Dataset with Proper Parameters

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. The profit is dependent on Sale Units (Cell C2), Price per Unit (Cell C3), and the Variable Cost per Unit (Cell C5). Therefore, type the below formula in cell C9.

=C5*C6-C7-C5*C8


Step 2: Make Scenario Manager

Now let’s see how to set up a Scenario Manager. To do that, follow the instructions below:

  • First of all, from your Data tab, go to,

Data → Forecast → What-If Analysis → Scenario Manager

Make Scenario Manager

  • As a result, a Scenario Manager dialogue box will appear in front of you. From the Scenario Manage dialog box, click on Add option.

  • In the prompted Add Scenario dialogue box, fill in the required details. Enter a name (Worst Case) for the Scenario name 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 the CTRL key on your keyboard and select all the cells, one by one, that contain the input values. At last, press the OK option.

Make Scenario Manager

  • Hence, the Scenario Values dialogue box pops up. Fill in the Scenario Values dialog box with the input values that define the worst case, and press the Add option to add another scenario. Click on OK, and the Worst Case scenario will be successfully created.

  • Since we’d like to create another scenario, we click on Add 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. Please note that Excel has set Changing Cells for Worst-Case scenario as the default Changing Cells for Best Case scenario. The details give in the following screenshot.

Make Scenario Manager

  • With the same approach, create the Most Likely Case Here the below screenshot 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. Now, you can see that three scenarios have been successfully created and they are listed in sequence. Click on Close, and the Scenario Manager dialogue box will be closed.

Make Scenario Manager

Read More: How to Create Scenarios in Excel (With Easy Steps)


Step 3: 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 Forecast Group, then choose Scenario Manager in the drop-down, you will see the same Scenario Manager dialogue box as that is shown in the below screenshot.

Make Scenario Manager

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

For example, if we double-click on Worst-Case, the input values in the Excel worksheet will change into what has been filled for Worst Case, and the output value will be calculated automatically based on the formula in cell C9. 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 the below screenshot shows how the Excel worksheet looks like if we click on Best Case scenario and then click on Show. The dataset will change automatically.

View Different Scenarios

Read More: How to Do What-If Analysis Using Scenario Manager in Excel


Step 4: Create Scenario Summary Report in Excel

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. To do that, follow the instructions below:

  • First of all, from your Data tab, go to,

Data → Forecast → What-If Analysis → Scenario Manager

Create Scenario Summary Report in Excel

  • After that, a Scenario Manager dialogue box will appear in front of you. From the Scenario Manage dialog box, click on the Summary option.

  • After clicking on Summary, a Scenario Summary dialog box appears for you to put Result cells (C9 in this case) and choose between Scenario summary. At last, press the OK option.

Create Scenario Summary Report in Excel

  • As a result, you will be able to create the scenario summary report.

Read More: How to Do Scenario Analysis in Excel (with Scenario Summary Report)


Notes on Scenario Manager

  • 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. The below screenshot shows you how to choose the workbook in the Merge Scenarios dialog box.

Notes on Scenario Manager


Things to Remember

➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.

➜ The #VALUE! error occurs when any of the given inputs are non-numeric.


Conclusion

I hope all of the suitable methods mentioned above to use a scenario manager will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Zhiping Yan

Zhiping Yan

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 a 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. Excellent presentation.

Leave a reply

ExcelDemy
Logo