SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

Scenarios, Sensitivities, What-if Analysis – What’s the Difference?

01 May, 2010

What a great question.  A potential delegate for my upcoming course in Kuala Lumpur asked this very question and I liked it so much I decided to answer it in detail.  Scenario analysis, sensitivity analysis and what-if analysis are a very important part of financial modelling but are really only slight variations of the same thing.

A sensitivity analysis, otherwise known as a what-if analysis, in financial modelling refers to the process of tweaking one key input or driver in a financial model, and seeing how sensitive the model is to the change in that variable.  Scenarios, on the other hand, involve listing a whole series of inputs and changing the value of each input for each scenario.  For example, a worst case scenario could include interest rates increasing, number of new customers being less than expected, and unfavourable exchange rates.

Scenarios and sensitivity analysis are a great way to insulate your model from risk.  What would be the absolute worst that could happen?  If everything that can go wrong does go wrong, will my business still be OK?  There are usually effects and interactions between multiple variables which may change in the model.  With a well-built model which has all inputs linked to outputs, it is relatively easy to change inputs and watch the outputs change.  In fact – this is pretty much the whole point of financial modelling!

Scenarios can assist with decision analysis.  They are laid out in advance so that the decision makers can see the expected impact of each course of action.  How close to reality these scenarios are really depends on the accuracy of the assumptions implicit in the model – but that’s another story!

Scenarios should really focus around the area or assumption around which we have the least certainty.  For example, let’s say we are analysing the costs of reducing carbon emissions:

Scenario Fossil Fuel
Prices
Non-Fossil Fuel
Technology Cost
Best Case -10% +10%
Likely Case - -
Worst Case +10% -10%

In this very simple example, you could model the prices that you think are “most likely” and then tweak each of these up or down.  It is possible to create many more than three scenarios in a financial model.  I’m often asked how many scenarios should be in a model and it really depends on the amount of time and the degree of detail and certainty that is required for the model.  The three scenarios shown above should be a minimum.  I have seen models with around fifty scenarios modelled, but it does become rather unwieldy and confusing so unless you really need them, I’d probably recommend sticking to around twelve scenarios.

There are several Excel tools which can be used to create scenarios.  Scenario Manager is pretty basic, and not particularly useful for a reasonably advanced Financial Modeller.  Other Excel tools which can be used for scenarios are Data Tables and Solver.  Monte Carlo Simulation, which tests thousands of tiny variations in scenarios, is normally performed using third-party software although it is possible to do it in Excel.

The most commonly used method (and the one that I teach in my courses) is to use a combination of formulas and drop-down boxes.  To explain this briefly, we create a table of possible scenarios and their inputs such as the one above, and link the scenario names to an input cell drop-down box.  The inputs of the model are linked to the scenario table.  This way, for example, if the user selects the Best case scenario, the input value for Fossil Fuel Prices will decrease by 10%.  If the model has been well-built, and all the inputs are flowing through to the outputs, then the results of the model will change as the user selects different options from the drop-down box.

Scenarios and sensitivity analysis are covered in detail in the Financial Modelling in Excel course.  I’d love to see you there!

Comments

For a video by Plum Solutions on Scenario tools in Excel, and further discussion on this topic, see the Fimodo site:

http://www.fimodo.com/2010/02/including-scenarios-in-your-financial-mode...

Submitted by danielle on Mon, 10/05/2010 - 07:54.

I love your website great example for something that can be confusing for someone new to the material

Submitted by Anonymous on Mon, 11/11/2013 - 11:46.

Great examples which are easily grasped ! THNX

Submitted by Anonymous on Tue, 17/12/2013 - 13:26.

Please see a recent webinar recorded on different methods of scenario tools in Excel.  You can download files and transcript also at the link.

Submitted by danielle on Thu, 19/05/2016 - 02:41.

thanks for the clarification! simple and easy to understand!

Submitted by Anonymous on Wed, 16/11/2016 - 12:58.

Post new comment

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.