Flaw of average (FOA) – Excel Scenario Manager

In my previous blog Use Excel to handle flaw of average (FOA) – Data Table, I discussed what flaw of average was and how Excel Data Table could provide us a more comprehensive view about our business decisions.  However, Data Table has a limit, it only supports up to 2 variables.  Our world is quite complicated and quiet often, we need more than 2 variables.  This is why we have to use Excel Scenario Manager; it can support up to 32 variables.  Scenario Manager is a great tool to handle our demanding what-if models.

Let’s say we want to do a simple business projection.

image

The annual increment rates are our best estimate based on our experience.  But we want to study more scenarios and see what if blah, blah… happen.  That would be easy, all we have to do is to change the rates in column G.

image

We keep change the rates for all the scenarios that we have in mind; then we copy and paste the results to another worksheet.

Scenario Manager allows us to develop what-if analysis much easier and it is also very simple to setup.

  • We select Data tab and then select What-If Analysis
  • Click Scenario Manager…

image

  • Scenario Manager dialogue would be displayed.
  • Click Add… button

image

  • Enter Best Estimate into Scenario name:
  • Select $G$2:$G$6 in the Changing cells:
  • Click OK button

image

  • Scenario Values dialogue would be displayed
  • Click Add button to add more scenarios

image

  • Add 4 more scenarios by repeating the last 2 steps.

image

  • Now, we have 5 different scenarios.
  • Click the Summary… button

image

  • Select the Result cells:
  • Click OK button

image

Here is the result that prepared by Scenario Manager.

image

Scenario Manager can be quite useful when we do our business planning.  We want to study more scenarios and well prepare for what-if situations.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

One Response to Flaw of average (FOA) – Excel Scenario Manager

  1. Thanks for another very interesting and informative post, Andrew!

    I’ve never been a huge fan of the Scenario Manager, mainly because it’s not very obvious to the user opening the model. I much prefer to create a drop-down which makes it’s really obvious to the user that there are scenarios included in the model – although of course you can only view one scenario at a time. The Scenario Summary in Scenario Manager though is great and does come some way to reduce the limitations of the Data Table.

    I’ve done an video overview of Scenario Methods in Excel here: http://www.plumsolutions.com.au/videos/video-preview-%E2%80%93-scenario-analysis-methods

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: