WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Scenario Manager in Excel

Scenario Manager in Excel

Scenario Manager is a what-if analysis tool available in excel which works on different scenarios provided to it, it uses a group of ranges which impact on a certain output and can be used for making different scenarios such as well bad and medium depending on the values present in the range which impact the result.

What is 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 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 scenarios can be stored so that you can apply them with just a click on the mouse.

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.

You can download this Scenario Manager Excel Template here – Scenario Manager Excel Template

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 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, you need to cut down 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 dialogue 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 dialogue box.

Scenario Manager - Step 4

By default, it shows the cell C10, which means that it is the currently active cell. 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. In this first scenario, nothing will be changing because this is my actual budget for the month. Still, we need to specify the cells will be changing.

Now try to reduce your Food expenses and Clothes expenses. These are in the cells B15 & B13, respectively. Now your add scenario dialogue box should look like this.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Scenario Manager - Step 4-2

Click, OK, and Excel will ask you for some values. Since we do not want any changes to this scenario, just 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. Create a second scenario, and this where you need to make changes to your Food & Clothes expenses.

Click the Add button one more time and give a scenario name as “Plan 2”. Changing the cell will be B15 & B13 (Food & Cloth expenses).

Scenario Manager - Step 4-5

Now, below Scenario Values dialogue box opens again. This time, we do 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, and now you are back to the Scenario Manager window. Now we already have two scenarios named after Actual Budget & Plan 2.

Scenario Manager - Step 4-7

Click the Add button one more time and give a scenario name as “Plan 3”. Changing the cell will be B15 & B13 (Food & Cloth expenses).

Scenario Manager - Step 4-8

Now, below Scenario Values dialogue box opens again. This time, we do want to change the values. Enter 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, and 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 1 and Plan 2. With Plan 2 selected, click the Show button at the bottom. The values in your excel sheet will change, and the new budget will be calculated. The image below shows what it looks like.

Scenario Manager - Step 4-11

Click on the Actual Budget, then click on the Show button to see the differences. Initial values will be displayed.

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 allows you to identify the significant changes from them.

How to Create a Summary Report in Excel?

After we are done with adding different scenarios, we can create a summary report in excel from this scenario manager in excel. 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 create the summary report in excel instantly 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 increase by 5 and all others remain the same.”

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 below one.

Scenario Manager - Summary Report 2

Recommended Articles

This has been a guide to what is the Scenario Manager in Excel. Here we walk through examples on how to use Scenario Manager in excel and create Summary Report along with downloadable excel templates. You may also look at these useful functions in excel –

  • Excel Substitute Function
  • Data Validation in Excel
  • Watch Window in Excel
  • List Box in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Scenario Manager Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More