This is the third article of the five-part series on Data Analysis in Excel. In this section, I will show you how to use the Scenario Manager in Excel.
Other articles in this series:
Watch Video – Scenario Manager in Excel
Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.
Suppose you have a dataset as shown below and you want to calculate the profit value:
The Profit value is dependent on 3 variables – Sale Quantity, Price per Unit, and the Variable Cost per Unit. Here is the formula I have used to calculate the profit:
=B2*B3-B4-B5*B2 The idea is to see how this final result changes when we change these dependent variables.
As shown in the first 2 articles of this series, if you only have one or two variables changing, you can create one variable or two-variable data table. But if you have 3 or variables that can change then scenario manager is the way to go.
Setting up Scenario Manager in Excel
- Go to Data Tab –> Data Tools –> What-If Analysis –> Scenario Manager.
- In the Scenario Manager dialogue box, click on Add.
- In the Add Scenario dialogue box, fill in the following details:
- Scenario name: Worst Case
- Changing cells: $B$2,$B$3,$B$5 (you can also select it by pressing the CONTROL button and using mouse left-click).
- Comment: Any comment you wish you add. You can also leave this blank.
- Click OK. This opens the Scenario Values dialogue box.
- In the Scenario Values dialogue box, fill in the following values (since this is the worst case scenario, enter the values accordingly). If you create names for each cell, that name is visible instead of the cell address:
- $B$2: 50
- $B$3: 30
- $B$4: 30
- Click OK (Click on Add if you want to add another scenario).
This creates the Worst Case scenario for this data set. You can similarly follow these steps and create multiple scenarios (for example, Worst Case, Realistic, Best Case).
Once you have created all the scenarios, you can view the result from each of the scenarios by simply double-clicking on any of the scenarios. As you double click, the values would change based on that scenario.
Additionally, you can also create a summary of all the scenarios.
Create a Summary of all the Scenarios
- Click on the Summary button in the Scenario Manager dialogue box.
- In the Scenario Summary dialogue box, select Scenario Summary or Pivot Table (these are the 2 ways to show summary). Also specify the Result cells (the cell where you have the output of this calculation; B6 in this example)
- Click OK. Instantly a new tab is created with the summary of all the three scenarios.
Scenario manager in Excel is a great tool when you need to do sensitivity analysis. Simply create scenarios and a summary can be generated instantly, giving you a complete comparative overview.
Download File… Try it yourself
You May Also Like the Following Excel Tutorials:
10 thoughts on “Data Analysis [Part 3 of 5] – Scenario Manager in Excel”
A greatful thanks to you,
For giving this types of excel knowledge.
Hi, I have a simple but important question.
I’m trying to link and automate the scenario values. Instead of feeding it generic values, I’m using a cell reference, it works (instead of “50”, I’m using “=A1”). However, the values from that cell reference are being fixed and will not update when I change the scenario values in those cells.
The problem is once I give it the cell reference as input, e.g. = A1, it takes the generic value inside A1 and fixes it as the scenario figure. When I manually modify this value in A1, the scenario doesn’t update because it’s no longer referring to the cell A1.
Any ideas?
Thanks.
Thanks I got it interesting
Is it possible to do a forward looking analysis with is excel tool?
Sumit, Thanks for the presentation.
Hello
I am Fredrick from Ghana
Thank you Sumit. great to learn 😀
Thanks for commenting hubert.. Glad you found this useful 🙂
Good article. Now I know how I can do some sensitivity analysis in Excel. Keep up the good work.
Thanks for commenting Ryan.. Glad you found this useful 🙂