Skip to main content
Home
The Online Resource for Modern FP&A Professionals
Please register to receive the latest FP&A news, updates and tips Login

Main menu

  • Home
  • FP&A Insights
    • FP&A Trends Digest
    • FP&A Trends Research
    • FP&A Trends Insight Paper
    • FP&A Trends Survey
    • Short Videos
    • Our Contributors
  • FP&A Events
    • International FP&A Board
    • FP&A Trends Webinars
    • Digital FP&A Circles
  • AI/ML Committee
    • Introduction
    • Members
    • Resources
    • Meetings
  • FP&A Tools
    • FP&A Trends Maturity Model
  • About Us
    • Company Policy
    • Privacy Policy
    • Editorial Guidelines
    • Our Ambassadors
    • Our Sponsors & Partners
    • Contact Us
image

FP&A Week: Breaking the Paradox

October 10-13, 2023

Register now

 

 

 

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

By Danielle Stein Fairhurst, Financial Modelling Specialist at Plum Solutions

 
 
FP&A Tags
FP&A Scenario Planning
Modelling and Forecasting
FP&A Analytics

Scenario analysis, sensitivity analysis and what-if analysis are very similar concepts and are really only slight variations of the same thing. All are very important components of financial modelling – in fact, being able to run sensitivities, scenarios and what-if analysis is often the whole reason the model was built in the first place.

So what’s the difference?

Let’s start with the definitions. In a financial modelling context, a sensitivity analysis refers to the process of tweaking just 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 and all these things happening at the same time. What-if analysis refers to answering the question “what-if we did this?” or “what-if we did that?” which can be answered by running scenarios and sensitivities.  This can be done by running pre-set scenarios or by testing sensitivities by changing the inputs either manually or using data tables. Some of the tools, such as data tables that we can use for performing what-if analysis are found on the tab in Excel helpfully labelled “What-if analysis” as shown in Figure 1. So what-if analysis is a general term which may refer to performing either sensitivities or scenarios, or both.

Figure 1: What-if Analysis tools in Excel

Why run scenarios and sensitivities?

Scenarios and sensitivity analysis are a great way to insulate your model from risk by seeing all the possible outcomes of the project or venture you are modelling.  What would be the absolute worst that could happen?  If everything that can go wrong does go wrong, can we still afford to pay staff?  There are usually interdependent effects and interactions between multiple variables which may change in the model, and this is why it’s so important to have links automatically calculating within a model.  For example, if units sold increases, then revenue increases, so profitability increases, so cash flow increases, so borrowing decreases, so interest payable decreases and so on…. 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 really depend on the accuracy of the assumptions implicit in the model – but that’s another story!

Which assumptions should be changed in each scenario?

To have the greatest impact, scenarios should really focus around the area or assumption around which we have the least certainty, or for which fluctuations would materially impact the business.  For example, Figure 2 shows one of the models for working out factory production profitability and cashflow funding.  The way that this model has been laid out, all possible inputs could change, however, we have only changed some inputs under each scenario and in some cases, the input does not change at all if the change is not considered likely, or something that will materially impact the business.  In this example, annual production capacity in row 4 is a pre-determined input not deemed likely to change under any scenario.  


Figure 2: Example of Scenario Layout

In this simple example, the entire model is built based on the inputs in column B and cell B3 is selected by the user to display the scenarios.  Column B contains formulas of course which will dynamically display the inputs picked up from the scenario table for the selected scenario.

How many scenarios should a model contain?

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.  At minimum, a model should contain at least three scenarios such as Best case, Base case and Worst case.  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.

What are the best tools for modelling scenario and sensitivity in Excel?

To summarise, there are many ways to create scenarios and run sensitivity analysis in Excel:

  1. Manual Scenario selection (Switches or Drop-downs) is the most commonly used method (and the first one that I teach in my courses) uses a combination of formulas and drop-down boxes as shown in Figure 2 above and is the easiest to build and understand.  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. Bear in mind that you cannot easily view scenarios side by side using this method. 
  2. Data Tables are incredibly powerful and best for running sensitivity analysis as shown in figure 3 below when you have a single output and one or two inputs. If you need to be able to see the outputs of your sensitivities analysis side-by-side, and if you’ve only got one or two inputs then consider using a data table. 
  3. Goal Seek is useful for what-if analysis if you have a formula, a hard-coded number that drives the formula and if you know the result the formula should show.  It’s particularly useful for running breakeven analysis; for example, how many customers do I need in order for profit to be zero?
  4. Scenario Manager is pretty basic, and not particularly useful for a reasonably advanced Financial Modeller. It has pride of place on the “What-if Analysis” tab in Excel right above the Goal Seek and Data Tables as shown in figure 1 but is nowhere near as useful.
  5. Monte Carlo (Stochastic Simulations)  which tests thousands of tiny variations in scenarios, is normally performed using third-party software although it is possible to do a rudimentary version of it in Excel using data tables.

Figure 3: One-input and Two-Input Data Tables [1]

[1] Image sourced from “Financial Modelling in Excel for Dummies”, Danielle Stein Fairhurst, John Wiley & Sons, 2017, Chapter 8, Figure 8.1
 

The full text is available for registered users. Please register to view the rest of the article.
  • Log In
  • or
  • Register

Giuseppe Capoderose

May 16, 2020

Very clear and informative! Many thanks!
  • Log in or register to post comments

Ikedikings

June 8, 2020

Great article with very deep insight.
  • Log in or register to post comments

Related articles

20 Key Financial Modelling Definitions
May 19, 2017

Financial model definitions can be tricky. Financial models are often dependent upon numerous functional areas and...

Read more
Applied Corporate Financial Modeling
February 16, 2018

During the recent years, the level of detail and precision which financial modeling in the business...

Read more
Financial Modeling Innovation: Predictive Analytics vs Financial Modeling
May 17, 2018

Although there are some substantial differences between Financial Modelling and Predictive Analytics, both help us cope...

Read more
Financial Modelling and Automation
December 6, 2018

Disclaimer: Financial Modelling has no strict “right” or “wrong” method of application. It does, however, have...

Read more
Analytic Model vs Spreadsheets
September 21, 2017

Spreadsheets are without doubt the ‘killer’ application that turned the PC into an indispensable business tool...

Read more
Modern FP&A and The Power of Scenario Planning
June 13, 2019

Most recently, I have been observing significant changes in business models in many organisations around the...

Read more
+

Subscribe to
FP&A Trends Digest

We will regularly update you on the latest trends and developments in FP&A. Take the opportunity to have articles written by finance thought leaders delivered directly to your inbox; watch compelling webinars; connect with like-minded professionals; and become a part of our global community.

Create new account

image

The FP&A Trends Webinar: Navigating Intelligent Transformation with FP&A Trends Maturity Model

Click here to view details and register

 

 

 

 

Pagination

  • ‹‹ Previous
  • September 2023
  • Next ››
Su Mo Tu We Th Fr Sa
27
28
29
30
31
1
2
 
 
 
 
 
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Moving from FP&A to Extended Planning and Analysis (xP&A)
 
Moving from FP&A to Extended Planning and Analysis (xP&A)
 
24
25
26
27
28
29
30
Creating Your Transformation Map with the FP&A Trends Maturity Model
 
Using AI to Enhance Your Predictive Planning and Forecasting
 
All events for the year
Data and Analytics
Data & Analytics
FP&A Case Studies
FP&A Case Studies
FP&A Research
FP&A Research
General
General
Integrated FP&A
Integrated FP&A
People and Culture
People and Culture
Process
Process
Technology
Technology

Future Meetings

The Face-to-Face London FP&A Board
The Face-to-Face London FP&A Board Creating Your Transformation Map with the FP&A Trends Maturity Model
September 27, 2023
The FP&A Trends Webinar
The FP&A Trends Webinar Using AI to Enhance Your Predictive Planning and Forecasting
September 28, 2023
The Power of AI and Human Intelligence in FP&A
The FP&A Trends Webinar Empowered Decision-making: Human-Led, AI-Backed Intelligence in FP&A
October 4, 2023
The Face-to-Face Amsterdam FP&A Board
The Face-to-Face Amsterdam FP&A Board Five Critical Roles for Building a World-Class FP&A Team
October 5, 2023
ZBB
The FP&A Trends Webinar A New Age of Zero-Based Budgeting
October 10, 2023
The FP&A Trends Webinar
The FP&A Trends Webinar Navigating Intelligent Transformation with FP&A Trends Maturity Model
October 11, 2023
The Face-to-Face New York FP&A Board
The Face-to-Face New York FP&A Board FP&A Storytelling in a Data-Driven World
October 12, 2023
The Face-to-Face Toronto FP&A Board
The Face-to-Face Toronto FP&A Board FP&A Storytelling in a Data-Driven World
October 17, 2023
Value Adding FP&A
The FP&A Trends Webinar Mastering the Art and Science of Value Adding FP&A
October 18, 2023
FP&A Storytelling in a Data-Driven World
The Face-to-Face San Francisco FP&A Board FP&A Storytelling in a Data-Driven World
October 19, 2023
The Face-to-Face Seattle FP&A Board: FP&A Storytelling in a Data-Driven World
The Face-to-Face Seattle FP&A Board FP&A Storytelling in a Data-Driven World
October 24, 2023
The Face-to-Face Boston FP&A Board
The Face-to-Face Boston FP&A Board Creating Your Transformation Map with the FP&A Board Maturity Model
October 26, 2023
Geneva FP&A Board
The Face-to-Face Geneva FP&A Board FP&A Storytelling in a Data-Driven World
November 7, 2023
Continuous Planning
The FP&A Trends Webinar Real-Time and Continuous Planning: What Does It Involve
November 8, 2023
Zurich FP&A Board
The Face-to-Face Zurich FP&A Board FP&A Storytelling in a Data-Driven World
November 9, 2023
The Face-to-Face Munich FP&A Board
The Face-to-Face Munich FP&A Board Moving from FP&A to Extended Planning and Analysis (xP&A)
November 14, 2023
Helsinki
The Launch of the Face-to-Face Helsinki FP&A Board Modern Financial Planning and Analysis (FP&A): Latest Trends and Developments
November 16, 2023
From Insights to Impact: Unveiling the Potential of FP&A Scenario Management
The FP&A Trends Webinar From Insights to Impact: Unveiling the Potential of FP&A Scenario Management
November 22, 2023
The Launch of the Face-to-Face Riyadh FP&A Board
The Launch of the Face-to-Face Riyadh FP&A Board Modern Financial Planning and Analysis (FP&A): Latest Trends and Developments
November 27, 2023

Please register to receive the latest FP&A news, updates and tips.

info@fpa-trends.com​

              

Foot menu

  • FP&A Insights
  • FP&A Board
  • FP&A Videos

Footer countries

  • Amsterdam
  • Austin
  • Boston
  • Brisbane
  • Brussels
  • Chicago
  • Copenhagen
  • Dubai
  • Frankfurt
  • Geneva
  • Hong Kong
  • Houston
  • Kuala Lumpur
  • London Board
  • London(Circle)
  • Melbourne
  • Miami
  • Munich
  • New-York
  • Paris
  • Perth
  • San Francisco
  • Seattle
  • Shanghai
  • Singapore
  • Stockholm
  • Sydney
  • Tokyo
  • Toronto
  • Washington D.C.
  • Zurich

Copyright © 2023 fpa-trends.com. All rights reserved.

0