The Scenario Manager in Excel is useful if you have a formula with multiple variables and you want to see how the final result changes when the variables change.
Users can add new scenarios in order to determine which set of changes can maximize or minimize a particular result cell.
Let’s take a look at a practical use case where you can use the scenario manager.
Suppose you’re planning for an important project or event with a certain budget. In your spreadsheet, you’ve added a list of project expenses and the estimated budget allocation for each.
Since you don’t want the allocation to go over the project budget, you’ve also set up a cell that sums up all projected expenses and subtracts that amount from the given project budget.
After your first project budget draft, you’ve noticed that the expenses are nearly over the given budget. Other team members have suggestions on how to bring the expenses down.
We can use the Scenario Manager tool to consider each of these budget change suggestions and determine which plan works best for the team. The tool can even generate a summary that compares each budget suggestion.
Planning budgets is just one way to use the scenario manager in Excel. Anyone can use the Scenario Manager tool to help make important decisions backed by quantitative data.
Now that we know when to use the scenario manager, let’s take a look at how it works on an actual spreadsheet.
A Real Example of a Spreadsheet that uses the Scenario Manager
Let’s take a look at a real example of a spreadsheet that uses the Scenario Manager to compare different changes in a table.
In this example, we have a specified project budget and a list of project expenses. Cell B11 computes the remaining budget after taking out the current expense allocation.
Using the Scenario manager, we can design three different scenarios that reflect three possible changes to the current allocation. For example, Scenario C will try to modify the allocations for the supplies, website, and miscellaneous funds.
Once all scenarios have been added to the tool, the user can generate a summary showing which allocation gives the most leftover budget for the team. In this example, Scenario C is the best option out of all other suggested changes.
You can make your own copy of the spreadsheet above using the link attached below. Note that only the Desktop application supports sheets with the Scenario Manager.
If you’re ready to try out the Scenario Manager in Excel, read our guide in the next section!
How to Use the Scenario Manager in Excel
This section will guide you through each step needed to start using the Scenario Manager in Excel. You’ll learn how we can use this tool to compare the result of changing different variables.
In this guide, we’ll be looking into different plans for a project budget. We’ll use a Scenario Manager to determine how different plans can affect the remaining budget after all expenses are accounted for.
Follow these steps to start using the Scenario Manager function:
- First, head to the Data tab. Select the Scenario Manager option under What-If Analysis.
- In the Scenario Manager pop-up, you will notice that no scenarios have been defined at first. Click on the Add button to add a new scenario.
- For our first scenario, we’ll change our catering and supplies budget. Under Scenario name, you may label your current scenario. Under the Changing cells textbox, select the cells you plan on changing in this scenario. In this example, we’ll be changing cells B5 and B6. Click on OK.
- The next dialog box will ask for the values you want to use for each changing cell. Enter the required values and click on OK.
- After that, you should now have your first scenario appear in the Scenarios list. If you want to see how the scenario changes your result, click on the Show button below.
- As can be seen, the remaining budget should now change to reflect the changes made in the specified scenario.
- You may now proceed to add more scenarios to the scenario manager. If you wish to see how each scenario affects the final formula, then you may select the Summary option.
- Excel will shortly create a new worksheet with a table showing how each scenario affects the result cells.
This step-by-step guide should explain everything you need to know about how to use the Scenario Manager yourself in Excel. Our guide shows how this tool can help preview the results when certain cells change value.
In summary, the Scenario Manager tool is just one example of a forecasting feature available in Excel. Excel also includes other forecasting tools such as the Goal Seek, Data Table, and Forecast Sheet tool. With so many other Excel functions available, you can undoubtedly find one that suits your use case.
Are you interested in learning more about what Excel can do? Subscribe to our newsletter to find out about the latest Excel guides and tutorials from us.