How to Use the Scenario Manager in Excel (2024)

Scenario Manager is one of the three data-analysis tools of Excel.

Do you have your dataset stored in Excel and you’re thinking what if the XYZ number was something else? 🧐

Well, if that’s the case, don’t just keep thinking. Create a scenario, change it and see how your data changes shape. How?

Oh, I will walk you through that in the guide below. Just stay tuned and make sure to download the free sample workbook for this guide here to tag along with the guide 🚀

What is the scenario manager in Excel?

The Excel scenario manager is one of the many data analysis tools available in Excel. It comes as a part of the three-in-one what-if-analysis tools of Excel (that are in-built into Excel).

Using it, you can evaluate multiple ranges of input values to see how they affect one output value. That means you can perform a scenario test 🐱‍🏍

You can make different scenarios (good, bad, worst, excellent, etc.). Feed input values for each of them and see how the results change for each of these scenarios.

You can alter the values for up to 32 input values using the Scenario Manager. Believe me, it sounds much more fun to see this in action. And we will see that just shortly.

When to use the scenario manager

You can use the Scenario Manager in Excel when you want to 👇

1. Manipulate your data for different changes.
2. Preserve each scenario and compare it.
3. Get a summary report for all the scenarios defined.
4. Merge scenarios from multiple worksheets and workbooks.

How to use the scenario manager

This will be the most fun section of this article. We are going to apply the scenario manager to our example and see how it works 👀

The image below shows the Profit & Loss account of a Company.

The P&L is calculated in two steps. The first step is where we calculate the gross profit (simply revenue less the cost of sales).

And then other expenses are deducted from the Gross Profit to reach the Net Profit.

Now, this is the actual position that the company expects to achieve by the end of the year. However, we want the Net profit to be more than this 💰

Needless to say, this can be achieved by either pumping up the revenues or cutting down the costs. How can we know which is the best position for the Company?

Let’s make scenarios to learn that.

Create a scenario

To create a scenario:

1. Go to the Data tab > What-if Analysis > Scenario Manager.

This will open up the Scenario manager dialog box as follows:

2. Click on Add to add a new scenario.

This will then take you to the Add Scenario dialog box shown here.

3. Write in the Scenario Name. The first scenario will be the same as the current position (Net Profit of $50).

So we are naming it as Net Profit of $50 💲

4. As Changing Cells, create a reference to those cells that will change under this scenario.

We will change the figure for Revenue (Cell B2), COS (Cell B3), Operating Expenses (Cell B8), and Selling Expenses (Cell B9) under each scenario.

So, we are creating a reference to all of the cells that contain these amounts.

5. Check the option for Prevent Changes.

6. Click Okay.

Now you’re taken to the dialog box for Scenario Values where you must add the values for each of the cells referred to above 📚

These will be the values that you want under each scenario.

For example, in the actual scenario, the figures will remain the same, as they are in their current form. So we are not changing the values right now.

7. Press Okay again and this scenario (with the same values) will be saved.

And yeah! That’s it. We have already created a scenario 🏆

Add other scenarios

Yes, we have created a scenario, but that’s not it. We need to create the potential scenarios for which we need to see the results.

1. So from the Scenario Manager dialog box above, again click ‘Add’.

This time, we will create a scenario to see how the results change if the Revenue is increased by $200. And the COS is also increased by $100.

2. Define the Scenario Name (we are setting it to “Increased Revenues”).

3. Under the box for Changing Cells, create a reference to the cell for Revenue (Cell B2), and COS (Cell B3).

That’s because we will only change these two figures under this scenario 🧠

4. Check the option for Prevent Changes.
5. Click Okay.
6. Under the Scenario Values box, add the desired values for this Scenario.

We plan on increasing the Revenue and COS by $200 and $100, respectively. So we are writing $1200 ($1000 + $200) as Revenue and -$800 (-$700 + (-$100)) as COS.

7. Click Okay, and a new scenario is added 🤩

8. To see how the results change for this Scenario, select this scenario (Increased Revenues) and press the Show button 🙈

The Gross Profit has already shot up to $400 (from $300 previously), and the Net profit is now $150 (from $50 previously). Wow!

Let’s create another scenario where the Operating Expenses and Selling Expenses are curbed down instead 📍

9. For that, again launch the Add Scenario dialog box.
10. Define the Scenario Name (we are setting it to “Decreased costs”).

11. Under the box for Changing Cells, create a reference to the cell for Operating Expenses (Cell B8), and Selling Expenses (Cell B9).

Under this scenario, we will only change the figures for these two expenses.

12. Check the option for Prevent Changes and click Okay.
13. Under the Scenario Values box, add the desired values for this Scenario.

We are decreasing writing the Operating Expenses (Cell B8) to be -$150 (instead of $-250). And Selling Expenses as -$30 (instead of -$50).

14. Click Okay to add the Scenario.

15. To see how the results change for this Scenario, select this scenario (Decreased Costs) and press the Show button 🤑

That’s how you can create multiple scenarios for a dataset and perform a What-if Analysis using the Scenario Manager.

For example, what if we curb the costs by $120? Just like we did above.

Scenario Manager of Excel tells you that if the costs are decreased by $150, the Net Profit shoots up to $200, whereas the Gross Profit remains the same at $300.

Merge scenarios

No, it doesn’t stop just here. What if you have different scenarios created for the same P&L account but across different worksheets (or workbooks)? And now, do you want to merge them all together in the same sheet 🌐

To your surprise, the Scenario Manager in Excel will let you do that too. See here:

Our Sheet has 3 scenarios created already. Let’s assume a colleague of mine also has a plan to improve the profits of the Company by reducing the Administrative Expense 🤔

He has also created a Scenario for the same. But that scenario sits in another worksheet prepared by him. So I need to merge that scenario from his worksheet into mine.

1. Go to the Scenario Manager Dialog box. Note that we can only see the scenarios created in this worksheet at the moment.

2. Click on Merge.
3. Select the sheet (or the book from the dropdown menu above) where the other scenario is created.

We are selecting the sheet P&L with Scenario

4. That’s where the scenario exists in our example.

5. Click Okay.
6. Excel merges all the scenarios (from both sheets together).

Note that there are now 4 scenarios available to be applied in our sheet. We have an additional Scenario to the list by the name “Decreased Admin Exp”.

Apply it just like you normally apply scenarios, and that’s it. Merging of scenarios all done and dusted ✅

Pro Tip!

Must note that for the merging feature of the Scenario Manager to work, the datasets across all the worksheets must be in the same format and structure.

For example, in our dataset above, Revenue was in Cell B2. If you are merging scenarios from another worksheet, make sure the dataset in that worksheet also has Revenue in Cell B2.

If not, the merged scenarios will not make any sense when merged 🥴

Scenario summary report

It is good to create multiple scenarios in Excel and scroll between them to see how the results change under each of them.

But what if you want to summarize all these scenarios in one place and see a summary report for them all 📂

You can do that too. See here

1. After you’ve created all the desired scenarios, go to the Data Tab > What-if Analysis > Scenario Manager.
2. Under the Scenario manager dialog box, look out for the Summary button as highlighted below.

This will take you to the Scenario Summary dialog box as below.

3. Choose the Result Cells. This is the cell that contains the final result of your dataset.

The results for our dataset primarily include the Gross Profit (Cell B4) and the Net Profit (Cell B14). We will create a reference to them both by holding down the Control key.

4. Click Okay, and Excel will create a Summary Report for you as below.

Self-explanatory, isn’t it? We see all the scenarios tabulated as different columns, and the result for each of them is populated at the bottom 💪

Other scenario managers use case examples

This was about the Profit and Loss account of a Company.

We saw how different figures that make up the entire P&L (Revenues, COS, Administrative Expenses, etc.) might need to be manipulated. And we created a scenario for each of them.

Where else might you need a scenario manager? Hard to list them all, but here are some other use cases of the Scenario Manager 📫

1. In making budgets. Keep tweaking the numbers until you have reached your desired result.
2. Forecasts & Feasibility reports.
3. Costing processes of entities to reach a targeted cost.
4. For drawing a sample from a given population.

That’s it – Now what?

That’s all about the Scenario Manager of Excel, folks! By now, we have learned how to use the Scenario Manager in Excel by creating multiple scenarios and switching between them.

Additionally, we have also seen how you may merge multiple scenarios from different workbooks or worksheets. And how you may create a summary report for all the scenarios 👩‍🏫

The Scenario manager is sure to help you with your analysis jobs. And not only the Scenario manager but many other functions and features of Excel will help you with that too.

There are just many of them. Excel functions library is close to an unending one. If you want to jump into learning these functions, I suggest you begin with the core functions.

Like the VLOOKUP, SUMIF, and IF functions. Enroll in my 30-minute free email course to learn these (and many more) Excel functions now.

Other resources

Scenario Manager is a great, but not the only data analysis tool of Excel. You can use so many more Excel features to perform data analysis in Excel.

Read our blog on Data Analysis Toolpak in Excel to learn more details about the same.

Frequently asked questions