Scenario Manager in Excel

Publication Date :

Blog Author :

Download FREE Scenario Manager Excel Template and Follow Along!
Scenario Manager Excel Template.xlsx

Table Of Contents

arrow

What is a Scenario Manager in Excel?

  • Scenario manager in Excel is a part of three what-if-analysis tools in Excel, which are built-in in Excel. In simple terms, you can see the impact of changing input values without changing the actual data. Like a Data Table in excel, you now input values that must change to achieve a specific goal.
  • Scenario manager in Excel allows you to change or substitute input values for multiple cells (maximum up to 32). Therefore, you can view the results of different input values or different scenarios at the same time.
  • For example: What if I cut down my monthly traveling expenses? How much will I save? Here, we can store scenarios to apply them with a mouse click.

How to Use Scenario Manager Analysis Tool in Excel?

Scenario manager is very simple and easy to use in Excel. Let us understand the working of the scenario manager tool in Excel with some examples.

Scenario Manager in Excel - Example #1

A simple example could be your monthly family budget. You will spend on food, travel, entertainment, clothes, etc., and see how these affect your overall budget.

Step 1: Create a below table that shows your list of expenses and income sources.

Scenario Manager - Step 1
  • In cell B5,you have total income.
Scenario Manager - Step 1-1
  • In cell B17,you have total expenses for the month.
Scenario Manager - Step 1-2
  • In cell B19,total money left.
Scenario Manager - Step 1-3

You are ending up with only 5,550 after all the expenses. So, it would help if you cut your cost to save more for the future.

Step 2: From the top of Excel, click the Data menu > On the "Data" menu, locate the "Data Tools" panel > Click on the "What-If-Analysis" item and select the "Scenario Manager" in Excel from the menu.

Scenario Manager - Step 2

Step 3: When you click on the Scenario Manager below, the dialog box will open.

Scenario Manager - Step 3

Step 4: You need to create a new scenario. So, click on the Add button. Then, you will get the below dialog box.

Scenario Manager - Step 4

By default, it shows cell C10, which means it is the currently active cell. So, first, type the scenario name in the box as the Actual Budget.

Scenario Manager - Step 4-1

Now, you need to enter which cells your excel sheet will be changing. Nothing will change in this first scenario because this is my actual budget for the month. Still, we need to specify the cells that will be changing.

Now, try to reduce the food expenses and clothes expenses. These are in cells B15 and B13,respectively. Now, the add scenario dialog box should look like this.

Scenario Manager - Step 4-2

Click "OK." Excel will ask you for some values. Since we do not want any changes to this scenario, click "OK."

Scenario Manager - Step 4-3

Now, you will be taken back to the scenario manager box. Now, the window will look like this.

Scenario Manager - Step 4-4

Now, one scenario is done and dusted. Next, create a second scenario where you must change your food and clothes expenses.

Click the Add button and give a ā€œScenario Nameā€ as ā€œPlan 2ā€. ā€œChanging the cellā€ will be B15 and B13 (food and cloth expenses).

Scenario Manager - Step 4-5

Now, below, the "Scenario Values" dialog box opens again. This time, we want to change the values. Enter the same ones as in the image below:

Scenario Manager - Step 4-6

These are the new values for our new scenario, Plan 2. Click "OK." Now, you are back to the Scenario Manager window. Now, we have two scenarios named after Actual Budget and Plan 2.

Scenario Manager - Step 4-7

Click the Add button and give a scenario name as ā€œPlan 3.ā€ "Changing  cells" will be B15 and B13 (food and cloth expenses).

Scenario Manager - Step 4-8

Now, below, the "Scenario Values" dialog box opens again. This time, we do want to change the values. Insert the same ones as in the image below:

Scenario Manager - Step 4-9

These are the new values for our new scenario, Plan 3. Click "OK." Now you are back to the "Scenario Manager" window. Now, you have three scenarios named after Actual Budget, Plan 2, and Plan 3.

Scenario Manager - Step 4-10

As you can see, we have our "Actual Budget," "Plan 2," and "Plan 3." With "Plan 2" selected, click the "Show" button at the bottom. The values in your Excel sheet will change, and we will calculate the new budget. The image below shows what it looks like.

Scenario Manager - Step 4-11

Click on the Actual Budget and the Show button to see the differences. It will display initial values.

Scenario Manager - Step 4-12

Do the same for "Plan 2" to look at the changes.

Scenario Manager - Step 4-13

So, scenario manager in Excel allows you to set different values and identify the significant changes.

How to Create a Summary Report in Excel?

After adding different scenarios, we can create a summary report in Excel from this scenario manager. To create a summary report in Excel, follow the below steps.

  • Click on the Data tab from the Excel menu bar.
  • Click on What-If-Analysis.
  • Under the what-if-analysis, click Scenario Manager in Excel.
  • Now, click on Summary.
Scenario Manager (Summary Report) - Step 1
  • Click "OK" to create the summary report in Excel.
Scenario Manager - Step 4-14
  • It will create the summary in the new sheet, as shown in the below image.
Scenario Manager - Summary Report
  • It shows the change in savings in three different scenarios. In the first scenario, the savings was 5,550. In the second scenario, savings are increased to 20,550 due to cost cut down in Food & Clothes section, and finally, the third scenario shows the other scenario.
  • All right, now we exercised a simple Family Budget Planner. It looks good enough to understand. Perhaps, this is enough to convince your family to change their lifestyle.
  • Scenario manager in Excel is a great tool when you need to do sensitivity analysis. You can instantly create the summary report in Excel to compare one plan with the other and decide the best alternative plan to get a better outcome.

Scenario Manager in Excel Example #2: Take the below data and create new scenarios.

Take the below data table and create new scenarios.

  • ā€œIf the Operating Cost Reduces by 10%.ā€
  • ā€œIf the Operating Cost Reduces by 15%.ā€
  • ā€œIf the Unit Price increases by five and all others remain the same.ā€

The formula used in cell B4 is =B2*B3 & in cell B11 is = B4 ā€“ B9.

Scenario Manager - example 2

In addition, your scenarios will look like the one below.

Scenario Manager - Summary Report 2