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 Scenario Manager in Excel.
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:
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 a one variable or two variable data table. But if you have 3 or more than 3 variable 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 scenario. 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.
You May Also Like the Following Excel Tutorials: