Using Scenario Manager in Excel (Simple Guide)

In this article, you will learn what the Exel Scenario Manager is and how to use it.

‍

What is the Excel Scenario Manager?

In Excel, the Scenario Manager is a built-in tool that allows you to create and manage multiple scenarios of your data by substituting in different sets of values. It helps you analyze how changes in certain variables can affect the outcome of a particular situation or problem. 

‍

When is it helpful to use the Excel Scenario Manager?

Scenario Manager in Excel is especially helpful in the following situations:

• Financial and Budget Analysis: You can create different budget scenarios (e.g., best-case, worst-case, most likely) based on different levels of revenue, expenses, and other variables.
• Business Forecasting: You can use different scenarios to forecast future performance based on different inputs, like sales growth rates, cost changes, or market conditions.
• Risk Assessment: You can examine the potential outcomes of different risk scenarios, allowing you to identify and plan for potential issues.

These are only a few examples. The main advantage of the Scenario Manager is its ability to easily model and compare different scenarios within the same worksheet structure, providing a simple yet powerful tool for data analysis and decision-making.

‍

How to access and use the Scenario Manager in Excel

Here's a basic outline of how to use the Scenario Manager:

1. Open your Excel spreadsheet.
2. On the Ribbon, click on the Data tab.
3. In the Data Tools group, click What-If Analysis and then Scenario Manager.
4. In the Scenario Manager dialog box, click the +.
5. Enter a name for the new scenario in the Scenario name box.
6. In the Changing Cells box, specify the cells that will change for the scenario.
7. Click OK. Excel will then display the Scenario Values dialog box.
8. Enter the values you want for each changing cell and then click OK.
9. Repeat steps 4-8 for each scenario you want to create.

After creating the scenarios, you can view them by opening the Scenario Manager and clicking Show for each scenario. This will update the values and any calculations in your worksheet to reflect that scenario. You can also create a Scenario Summary and Scenario PivtotTable to get an overview of all scenarios, an explanation of these can be seen below.

‍

What are the Scenario Summary and Scenario PivotTable in Excel?

The Scenario Summary in Excel is a report that the Scenario Manager can generate, providing an overview of all the scenarios you've created. This report makes it easy to compare the results of different scenarios side by side. When you create a Scenario Summary, Excel generates a new worksheet that includes:

1. A table listing the names of your scenarios, along with the input values for each scenario, and the resulting values of your result cells for each scenario.
2. If you have selected cells that contain formulas that refer to your input cells, it will also show the resulting values of those formulas for each scenario.

The Scenario Summary is a beneficial tool when you have multiple scenarios and want to compare the results in a structured way. By looking at the summary, you can see all your scenarios at once, rather than having to click through each scenario individually in the Scenario Manager dialog box.

‍

Note: Scenario summaries do not recalculate automatically. If values are changed in the scenario manager you would need to create a new summary report for the changes in values to show up.

‍

The Scenario PivotTable report is an alternative to the standard Scenario Summary report in Excel's Scenario Manager. It provides a summary of the scenarios in a PivotTable format, which is an interactive table that can quickly summarize large amounts of data. After creating the Scenario PivotTable, you can interact with it as with any other PivotTable in Excel. This means you can change the arrangement of fields, apply filters, sort data, and more, allowing you to analyze your scenario data in a variety of ways that can be more dynamic and flexible compared to a Scenario Summary. 

‍

How to create a Scenario Summary or Scenario PivotTable:

1. Go to the Data tab on the Excel Ribbon, click What-If Analysis, and then Scenario Manager.
2. In the Scenario Manager dialog box, click Summary.
3. In the Scenario Summary dialog box, choose Scenario summary or Scenario PivotTable as the report type.
4. In the Result cells box, you can select the cells that contain the formulas you want to see results for.
5. Click OK. Excel will then generate the Scenario Summary on a new worksheet.

‍

Note: Result cells are not required for a Scenario Summary but are required for a Scenario PivotTable.

‍

Example using the Scenario Manager in Excel

Let's assume we have a simple company budget with three factors: Revenue, Cost of Goods Sold (COGS), and Operating Expenses, and from there, we calculate the Net Profit. Now suppose we want to analyze different scenarios in our budget, such as a best-case scenario, a worst-case scenario, and a most-likely scenario. This would be a situation in which you would want to use the Scenario Manager in Excel. Below is an image of your data set with the company budget.

‍

The three scenarios we will be looking at are:

• "Best Case Scenario": Revenue increases by 20% ($1,200,000), COGS decreases by 10% ($540,000), and Operating Expenses stay the same ($200,000).
• "Work Case Scenario": Revenue decreases by 20% ($800,000), COGS increase by 10% ($660,000), and Operating Expenses increase by 5% ($210,000).
• "Most Likely Scenario": Revenue increases by 5% ($1,050,000), COGS stay the same ($600,000), and Operating Expenses increase by 2% ($204,000).

‍

Here's how you would use the Scenario Manager for this example:

1. Use the steps from earlier to add a new scenario. Name the first scenario "Best Case Scenario" and have the Changing cells reference set to C3:C5. An image of this can be seen below.

2. Click OK. Now, input the values for the best-case scenario: $1,200,000 (C3, Revenue), $540,000 (C4, COGS), and $200,000 (C5, Operating Expenses). An image of this can be seen below.

3. Repeat steps 1-2 for the other two scenarios, inputting the respective values for each.

‍

After you've created your scenarios, you can switch between them in the Scenario Manager by selecting one and clicking Show. This will change the values in the worksheet to reflect the selected scenario, and the formula in C5 will automatically update to show the net profit for each scenario. An example of this can be seen below with the “Best Case Scenario”.

‍

For a better overview of all scenarios, you can generate a Scenario summary or Scenario PivotTable. Images of these can be seen below.

‍

‍

Overall, the Scenario Manager in Excel is a potent, flexible, and user-friendly tool for performing what-if analysis on various data sets. As a tool, the Scenario Manager can significantly streamline decision-making processes, offering an intuitive approach to examining various possibilities and outcomes, thereby aiding strategic planning and predictive analysis such as the example above.

‍

Go to the page LiveFlow‘s How to Guides to find more information about Excel and Google Sheets formulas and tips that were not covered here.

‍