Financial Modelling – Sensitivity and Scenario Analysis

Blog Downloads:

If a financial model is setup using best practice modelling techniques then putting sensitivities and scenarios should be simple. In this blog tutorial we look at setting up a sensitivity/scenario analysis manager.

Before we start you might be wondering what the difference between a sensitivity and scenario is. Well a sensitivity only flexes of one input, whilst a scenario flexes multiple inputs. It’s that simple.

About the Inputs/Assumptions

As we mentioned setting up assumptions sheets using best practice modelling techniques will make your job of putting sensitivities and scenarios in much easier.

At Video Financial Modelling we usually have one assumption sheet for a simple financial model and two assumptions sheets for complex financial models. The two assumptions sheets for a complex financial model are:

  1. A static assumptions sheet – inputs that don’t change over time. For example a scheduled construction end date.
  2. A time-series assumptions sheet – inputs that can change over time. For example inflation indices.

Setting up a sensitivity or scenario analysis

Below are brief instructions to setup a sensitivity or scenario analysis manager.

Step 1: The first step in setting up a sensitivity or scenario analysis is to incorporate, what we like to call a scenario selector. The scenario selector is simply a number between 1 and the “maximum number of scenarios you want to run”. For example say you want to run ten sensitivities/scenarios, then the maximum number you’d put in your scenario selector would be ten.

A simple scenario selector would just look like this.

Not much to it is there? Bear with us though, we will get into the detail.

Step 2: Now that you’ve got a scenario selector let’s move onto incorporating a simple offset formula. The basis of this formula is to grab the relevant data from the selected scenario. The form of the formula we usually use for this is:

= offset(current cell reference, ,scenario selector) for static pages

= offset(current cell reference, scenario selector,) for time-series pages

Obviously if you have rows/columns in between the actual assumption you use in your model and your input, then you may need to adjust the above formula.

Step 3: So what happens if you want to use scenario 1’s input if you don’t have any data in the selected scenario? Well that is easy, we just add an IF statement in as shown below.

=if(offset(current cell reference, ,scenario selector)=””,scenario 1 cell, offset(current cell reference, ,scenario selector)) – for a static sheet

Obviously it is nearly the same for a time-series assumptions sheet.

The logic behind this is that we don’t have to put all inputs in for every different scenario we do. We just put in the inputs we want to change from the scenario 1 (usually called the base case).

Sensitivity and Scenario Example

Ok, this might be confusing right now, but once we look at the below example our methodology should become apparent. You can follow the example by downloading the accompanying spreadsheet and YouTube video.

Say we had the following inputs and we wanted to find the NPV of equity distributions. Let’s assume there are no taxes and our profit is fully distributed to shareholders as there is no debt in the company.

Now let’s put in a scenario selector (Step 1 above). Obviously this scenario selector will not work on its own, so let’s now put in a an offset formula as per Step 2 above. The result is shown below. Now all our inputs will be moved to the 1st scenario and the formula will be placed where we previously had inputs.

Ok let’s now run a scenario, say scenario 2. Let’s put in revenue of 55 and operating expenses of 40. What happens? Because we don’t have inflation (and haven’t incorporated Step 3 above) in we won’t have any inflation.

Let’s change the formula as per Step 3 above to get:

Now we have an active scenario manager which we can change and run scenarios on. Putting it all together you should get an NPV of equity distributions of 135 for the base case and 101 for scenario 2. See the below picture or download the spreadsheet to see the workings.

Now for all of you out there who think you’ve got it why don’t you try this one. Find the NPV of the equity distributions if the inflation is 2.5% for the first five years and 7% for the next five years. Hint: Your scenarios will need to go down the page as this is now a time-series assumption (i.e. changes over time). The NPV of the equity distributions you should come out to be 137.

If you liked this tutorial, try one of our Financial Modelling or Excel training courses today.

By | 2011-11-21T20:51:09+00:00 November 21st, 2011|Blog|3 Comments

3 Comments

  1. Michael November 21, 2011 at 9:48 pm - Reply

    Thanks for providing this tutorial, great work & quite useful!

    As a little suggestion, you may also want to include data tables in this subject matter, as these are typically used to show sensitivities.

    The Data tables are in Data > What-If Analysis > Data Tables.

    You input the metric to be analysed in the top left of the table, select the table and create data table, and select which variable the row and collumn should reflect from the model, and the table values will then automatically generate.

    Highly useful for presentations.

    Michael
    http://www.michaeldignam.com.au

    • admin November 25, 2011 at 9:35 pm - Reply

      Thanks for the comment Michael.

      I definitely agree data tables can be useful in some situations. Maybe this is a topic for another day.

  2. Investorpedia January 4, 2012 at 6:01 am - Reply

    Nice Tutorial..Thanks 🙂

Leave A Comment