ICAEW.com works better with JavaScript enabled.

Financial modelling series

Intro to Financial Modelling - Part 18: Sensitivities and Scenarios

Author: Alex Carse

Published: 23 Jul 2021

As the penultimate blog in this ICAEW modelling good practice blog series I will be moving away from the guidelines in the code and discussing how using sensitivity and scenario analysis is a key part of any financial model.

Sensitivity analysis and scenario analysis are sometimes referred to as interchangeable terms for the same thing, but they are two very clear and different concepts, and both are very useful.

Sensitivity analysis is used to gauge the impact of a change in specified individual variables. It answers questions such as how much does the NPV of equity returns change if sales decrease by 10%?

Scenario analysis is used to understand the impact on project or business outcomes of changing multiple inputs in tandem or broader changes such as an alternative decision, a contrasting future economic environment or a different financing structure.

The reason that both are important is two-fold:

  1. Risk assessment - Many of the inputs in a model will be imprecise, even those that you may feel are fixed, such as tax, could have changes in the medium to long term. Therefore, understanding how your project or business stands under these changing conditions is important.
  2. Decision making - Models are a tool to help decision makers make choices, without reviewing the outcomes of different courses of action you are not able to test these options.

Sensitivity analysis

Risk assessment

In sensitivity analysis you only change one variable at a time and so the results usually give you a clear understanding of the impact of each variable on the future performance of the project. This allows decision makers to make use of visualisation tools such as a risk matrix to better understand the challenges of the project.

You need to understand the true bounds of your assumptions and understand your project under them, especially the downside. I remember working for oil and gas companies in 2013 where their extreme downside scenarios on price were considered to be $80/barrel as, bar a brief drop in a “once in a generation recession”, it had not been below that in 8 years and was trending stably above $100/barrel for the past 3 years. A few months later the price dropped below $60/barrel and has never been back above its “extreme downside” level.

Always make sure that the sensitivities you run are appropriate, sensitivities of too low a scale up or down will either not meaningful or give a false comfort.

Decision making

Knowing the bounds of outcomes is useful to decision makers, especially when certain limits are hit such as negative cash or breaching a debt covenant. It might be that most of your costs for a new product are variable and a sudden drop off in sales, although not ideal, can be managed and cash kept positive; or it might be that you have quite a large fixed cost base and a sharp drop in sales will require urgent additional financing. Sensitivity analysis helps show the impact of these movements.

Scenario analysis

Risk assessment

Sensitivity analysis lets you know the risk to a project for a single input being outside its assumed value, which is very helpful, but in reality businesses and projects are hit by events which lead to multiple assumptions having different values. Scenario analysis lets you gather these changing values together to view the collective impact of multiple changes. Brexit and the coronavirus pandemic are recent examples of these types of events, where once you have identified a situation you may want to run different scenarios for how that impact will be felt.

Decision making

Scenario analysis is key to decision making as it sets up clearly defined options to be chosen between. By having defined scenarios set up in a model you are able to quickly compare and contrast their outcomes and sensitivities which allows consistent, quick and accurate information to be shared with decision makers.

What is coming next?

Now that you we have explained the concepts of sensitivity and scenario analysis and their importance, I know you will be super desperate to put it into action and unable to think about anything else again, ever. To assist with this, through the autumn we will be running a blog series and webinar covering best practice for scenario analysis and the mechanics for setting it up.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.