Excels Scenario Manager

EXCEL LOGO

This post looks at Excel’s Scenario Manager function in 4 main sections.

  1. Setting up scenarios with the Scenario Manager
  2. Switching between scenarios
  3. Creating a summary report comparing scenarios
  4. Overcome one of the downfalls of scenario reports – making sense of “changing cells”

If you are familiar with the scenario manager summary reports already but want to know how to change the “changing cells” description to make more sense then you can skip to section 4.

If you are new to scenario managers, don’t worry we are going to start with the basics.

Section 1

Setting up scenarios with the Scenario Manager

We are going to use the example of estimating costs for next year based on percentage increases.

Image of data to be used in scenario manager example

  • Column A lists the cost descriptions
  • Column  B shows this year’s costs for each cost type
  • Column C shows the expected percentage increase next year
  • Column D contains a formula that calculates next year’s estimate costs based on the % increase

Image showing scenario manager on data ribbon

 

You can find the Scenario Manager on the DATA ribbon.

Click on “What-If Analysis” and then select “Scenario Manager”.

This opens the Scenario Manager dialog box.

Image of scenario manager dialog box

Initially, there are no scenarios to show.

We are going to add our first Scenario.

  • Click “Add…”

Second image of scenario manager dialog box

  • Give the scenario a name.  For the first one, I’m calling this “Most Likely”
  • For “Changing cells” I want to change the percentage values between cells C2 and C5, so I select this field and then highlight those cells.
  • Click the “OK” button

how to change cell values in scenario managers

  • The values for each of the changing cells are all at 0.1 (10%) which is what is already in each of the cells.  I am going to leave these values at 0.1 for now.
  • Click the “OK” button

Scenario manager dialog box listing scenarios

  • You can now see that you have 1 scenario created.  Create a second one by clicking “Add…”
  • Repeat the steps above again but with the following changes:
    • Scenario Name = “Worst Case”
    • All values to be changed to 20% (0.2)
  • Add a third scenario as follows:
    • Scenario Name = “Best Case”
    • All values to be changed to 5% (0.05)
  • Add a fourth scenario as follows:
    • Scenario Name = “Pay Increase”
    • The value for C3 to be changed to 25% (0.25) and all other values left at 10% (0.1)

list of four scenarios

 

  • You can close the Scenario Manager box by clicking the “Close” button
  • You can reopen it again by selecting Scenario Manager from the DATA ribbon again
  • You can edit any of the scenarios by selecting it and clicking the “Edit…” button.

In the next section we will see the results of some of our scenarios.

 

Section 2

Switching between scenarios

list of four scenarios

  • With the Scenario Manager dialog box open, select one of the scenarios listed (Pay Increase for example)
  • Click the “Show” button
  • The data in our file changes from this:

Image of data to be used in scenario manager example

  • To this:

Image showing scenario that has been changed

The staff amount has changed and all others are 10%.

Show the other scenarios and watch the percentage amounts change, and they in turn change the estimate values for next year.

This has so far allowed us to see one scenario at a time.

In the next section we will use the scenario manager summary function to compare all scenarios.

 

Section 3

Creating a summary report comparing scenarios

Creating a summary report is surprisingly easy.

  • If it is not already open, open the Scenario Manager dialog box
  • Click the “Summary…” button

dialog box image for scenario manager summary report

  • Select the Scenario summary as your report type
  • The result cells is D6 – this is the cell(s) with the formulas that are impacted by your various scenarios.  In our case, the total estimate costs for next year will be our result cell.
  • Click the “OK” button

Image showing summary report

In the scenario summary, you can see the percentage changes for each cell we change.

Below each list of percentages you can also see the result cells values, so for each scenario we created, this is the total value of costs for next year.

One of the downfalls of the scenario summary is that the changing cells and result cells refers to the cells (C2, C3, D6, etc.) and these are not very descriptive.

The next section shows you how to change these to be more descriptive.

 

Section 4

Making sense of “changing cells”

The easiest way to change this is to name your cells.

  • Click on the first cell to be named – C2
  • Above the column headers and to the left of the formula bar you can see “C2” in the Name Box.  This shows the cells that you are currently in.

Image showing where to name cells

  • Change the value in C2 to “Rent” and click return
  • Repeat for cells C3, C4 and C5, changing the name to match the cost description
  • Repeat for cell D6, naming it “Total”

 

Image showing file after naming ranges

Now, if you run the summary report again, you will get the reference to the names you gave cells, rather than the standard cell reference.

Final scenario manager summary report

This makes more sense and you can see quicker what each cost line is, rather than having to refer back to your original worksheet to see what C2, for example, referred to.

 

I would recommend going through these steps again.

Create your own scenarios, give them names, create your own summary reports, change the result cells, etc. to see what results you get.

The Scenario Manager is a very useful tool, sometimes it is overlooked or thought of as being more complicated than it actually is – as you can see (hopefully) it is actually relatively straightforward once you have tried it a couple of times.

 

email
Click Here to Leave a Comment Below 0 comments
Follow

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: