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.

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.

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…

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

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

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

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

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

• Select the Result cells:
• Click OK button

Here is the result that prepared by Scenario Manager.

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!