Excel Functions

Scenario Manager in Excel

Pinterest LinkedIn Tumblr

What is Scenario Manager in Excel?

Scenario Manager in Excel is used to compare data side by side and also swap multiple sets of data within a worksheet. In simple words when you have multiple variables and you want to see their effect on the final result, and also want to estimate between two or more desired budgets you can use Scenario Manager. It is built-in functionality in excel and can be spotted under the head What-If Analysis. It allows users to change input values up to a maximum of 32 cells.

Let’s take an example of Event Management who wish to host an event. An important step is to decide the venue as it plays a major role in determining expenditure, revenues, profit, or loss. You can create various scenarios consisting of different venues and then compare them.

Step by Step Learn how to use Scenario Manager in Excel:

In this example, we will use the Scenario Manager to compare two sets of numbers, they are Small and Medium with relevant expenditure and revenue.

Scenario Manager in Excel-EventBudget

The above image shows estimates of Total expenditure i.e., the total of all costs. The next section is of Total Revenue.
Note: All the cells highlighted with pink colour background denote that these are calculated cells.

Make Your Scenario :

Step-1: Select the Cells

  • Select the cells that are not calculated i.e., (the inputs) C5, C6, C7, C8, C9, C10, C11, C14.
Select the cells
  • Go to Data Tab > What-if Analysis > Scenario Manager.
Scenario Manager in Excel
  • This displays Scenario Manager dialog box as follows.
Dialog Box-Scenario Manager in Excel

Step-2:Create First Scenario

  • In the scenario manager dialog box > Click Add.
  • Enter the name Small Venue.
  • Select your range.
  • Enter a Comment. ( optional)
  • your sheet can be protected if further changes. (optional)
Edit-Scenario Manager in Excel

Click Ok. The scenario manager Dialog box will show all the cells in the scenario and their respective current values. You can quickly create multiple scenarios by clicking on the Add and then enter values and click Ok.

Scenario Manager in  Excel 1

Your First Scenario is done.

First Scenario

The Manager has various options to add a new scenario, delete one, edit one, merge in a scenario from another open workbook, and create a summary.

You can create as many scenarios as required depending upon your requirements. We would be adding one more scenario for the medium scale.

Scenario Manager in  Excel 2
New Scenario

Step:3-Switch Between

Double click on one of the scenario names in the list. The sheet updates the respective values.

Switch Between

Step-4:View all the scenarios at once

  • Click Summary Button.
  • Check whether you want it in a pivot table format or scenario summary.
Scenario Manager in  Excel 3

Click OK. That creates a new worksheet called scenario summary.

Summary

Current values columns represent values of changing cells at time scenario report was created. Changing cells for each scenario are highlighted in grey colour.

Note: The values are not dynamic.Which means if you change the data on the original sheet, the values on the summary sheet will not change.

For more frequent excel updates visit our youtube channel and website.

Related Posts

Write A Comment