How to Use Excel Scenario Manager

Have you found yourself in a situation where certain scenarios could completely change your calculation? If so, Excel has a special tool called Scenario Manager that allows you to test multiple scenarios by inputting multiple values for the same reference.

Scenarios Manager is a part of Excel’s What-If Analysis. Using this utility, you can switch from multiple set values on the sheet, or generate a summary with all the possible scenarios in a new sheet.

How to Use Excel Scenario Manager

There are many cases where you can utilize the Scenario Manager. For instance, you have generated the total cost of importing 2000 roses from a city nearby. Your expenditure includes the cost of the roses, transportation, and refrigeration of the roses while they’re being transported.

Now, you did come up with an estimation, however, you also want to consider cases where these numbers could be at the minimum and the maximum. Instead of creating two entirely new data tables, you can simply use Scenario Manager to test these completely different inputs.

You can enter your minimum values in one scenario and the maximum values in the next. This way, you can switch your data conveniently. 

Step 1: Launch Scenario Manager

Here is a step-by-step breakdown of how you can use the Scenario Manager in Excel:

1. Select the data you wish to enter multiple values for.
2. Head to the Data tab.
3. In the Forecast section, choose What-If Analysis > Scenario Manager.
   

Step 2: Creating Minimum Cost Scenario

Let’s create a scenario where our cost price is at the minimum. Here, I will enter the lowest price I can enter for all of these elements without risking loss.

1. On the Scenario Manager window, click Add.
2. Under Scenario name, enter a name. I entered “Minimum”.
3. If you selected your data before opening the window, your range will be displayed under the Changing cells section. If not, select the collapse window button and select your range.
   
4. If you wish, enter a comment. I decided to not make any changes.
5. Under Protection, choose if you wish to prevent changes or hide the scenario.
6. Click OK.
7. Enter the minimum values for all cells.
   
8. Click OK.

Step 3: Creating Maximum Cost Scenario

We will now create another scenario where we will maximize all of our costs. This scenario is to calculate the highest possible cost price of each of these elements.

1. Click Add on the Scenario Manager window.
   
2. Enter a name for your scenario. I entered “Maximum”.
3. The same range will be selected under the Changing cells section.
4. Enter a Comment if you wish.
5. Enable or disable protection, to Prevent Changes or Hide.
   
6. Click OK.
7. Enter your Maximum values and select OK.

Step 4: Show Created Scenarios

Now that we created both of our scenarios, we can now display the result of each scenario. Under Scenarios, select one of the scenarios you created and click on the Show button.

In cell B6, I have used the SUM function to add these values. Notice how when I change the scenario, the value of B6 also changes.

You can also create a Summary of the scenarios you’ve created. This will create a report that includes all of the scenarios you’ve created using the Scenario Manager, including the current value you’ve entered on your sheet. To create a summary, you will also have to enter the cell that holds the result cells. In my case, this cell would be cell B6.

1. Head to Data > What-If Analysis.
2. Choose Scenario Manager.
3. Select the Summary button.
   
4. Choose a Report type then reference your Result Cells > OK.
   

Edit Values in Scenario Manager

As time changes, you may have to enter different values inside the Scenario Manager. In our example, let’s assume there’s inflation and the minimum cost has spiked up. You don’t have to create an entirely new scenario. Instead, you can simply edit the values.

1. Go to the Data tab.
2. From the Forecast section, navigate to What-If Analysis > Scenario Manager.
3. Select the Scenario you wish to edit under Scenarios.
4. Click Edit.
   
5. You can change the name, referenced cells, and comment in the first window.
6. Click OK.
   
7. Change the value of your referenced cells in the next window.
8. Select OK.

Delete a Scenario

Once the scenarios you’ve created aren’t of any use, you can delete them. I suggest you delete any setup you no longer use in your spreadsheet as, the more information you add to your Excel spreadsheet, the slower your file will be.

1. Navigate to the Data tab and select What-If Analysis.
2. Under Scenarios, click on the scenario you wish to delete.
3. Select Delete on the right.