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.
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.
- Go to Data Tab > What-if Analysis > Scenario Manager.
- This displays Scenario Manager dialog box as follows.
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)
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.
Your First Scenario is done.
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.
Double click on one of the scenario names in the list. The sheet updates the respective values.
Step-4:View all the scenarios at once
- Click Summary Button.
- Check whether you want it in a pivot table format or scenario summary.
Click OK. That creates a new worksheet called scenario 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.