Data Analysis [Part 3 of 5] – Scenario Manager in Excel

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.

Download File

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:

Scenario Manager in Excel - Data set

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 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.Accessing Scenario Manager in Excel
  • In the Scenario Manager dialogue box, click on Add.Scenario Manager in Excel - 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.Scenario Manager in Excel - Add a Scenario Details
  • 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: 30Scenario Manager in Excel - Scenario Value
  • 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).Scenario Manager in Excel - Added Scenarios

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.Scenario Manager in Excel - Summary Button
  • 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)Scenario Manager in Excel - Scenario Summary OPtions
  • Click OK. Instantly a new tab is created with the summary of all the three scenarios.Scenario Manager in Excel - Summary

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 yourselfDownload File Pic

You May Also Like the Following Excel Tutorials:

  • Fredrick Kwapong Ofei says:

    Is it possible to do a forward looking analysis with is excel tool?

  • Fredrick Kwapong Ofei says:

    Sumit, Thanks for the presentation.

  • Fredrick Kwapong Ofei says:

    Hello

    I am Fredrick from Ghana

  • hubert says:

    Thank you Sumit. great to learn 😀

  • Ryan says:

    Good article. Now I know how I can do some sensitivity analysis in Excel. Keep up the good work.

  • >