Financial Modelling - Sensitivity and Scenario Analysis

If a financial model is setup using best practice modelling techniques then putting sensitivities and scenarios should be simple. What is the difference between a sensitivity and scenario you may be asking. Well a sensitivity only flexes of one input, whilst a scenario flexes multiple inputs.

About the Inputs/Assumptions

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 indicies.

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. 

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 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

You can follow along with the below example by downloading the YouTube video and spreadsheet.

Ok, this might be confusing right now, but once we look at the below example our methodology should become apparent.

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 rev of 55 and ops 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 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.  

MOD Function - Bridging the Time Gap in Excel

Say you had quarterly data and you want to collect this data onto a yearly page? How would you go about it? Probably a hundred different ideas came to your head when I raised that question. Video Financial Modelling has seen all of these. One sticks out ahead of the rest, the MOD function. 

Using the MOD function

The MOD function is quite simply awesome. In simple terms it takes a number and a divisor and spits out a remainder. The equation is shown below.

= MOD(number,divisor)

Does that bring back memories of school maths? Well for all of those people who can't remember, here is a couple of examples to help the mental juices:

• mod(11,3)   - 11/3 is 3, with a remainder of 2. So MOD gives us 2. 
• mod(12,3) - yes you guessed it. 0.
• mod(-11,3) - ok so this is getting a bit harder. The answer is 1. Why? Well because the MOD function gives the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case the largest integer multiple that doesn't exceed -11 is -12. Still confused. Have a play around with the function. 

MOD function Example

Ok, so I promised you that I would show you how to grab data from a quarterly page and aggregate it on a yearly page so let's get started. Download the associated YouTube video and Excel spreadsheet. 

We have the following quarterly dates and associated revenue numbers. 

We want to aggregate these numbers on an annual page. Let's firstly put in annual dates in. You can put these on a seperate page or keep it simple and have it on the same page. This is shown below.

Now the magic. Below the quartlery dates, let's put in the following formula. Don't worry we will explain the components soon. 

= EOMONTH(quarter end date, MOD(month(first period ending date)-month(quarter end date),12)

This should look something like this:

Copy this fomrula across. Wow magic... hopefully this formula gives you the year ending for each of the dates? It sure does, now how does it work. Let's break it down:

= EOMONTH(Component 1,Component 2)

• Component 1: quarter end date
• Component 2: MOD(month(first period ending date)-month(quarter end date),12)

The first component should be pretty self explanatory, so let's look at the second component by looking at some brief examples.

Ok given that the first period ending date is always 31 Dec 2012, the month(first period ending date) will always equal 12. Simplifying the formula we get:

= MOD(12-month(quarter end date),12)

There are 4 possible options for the month(quarter end date). 3, 6, 9 and 12. i.e. corresponding to March, June, September and December. Let's look at each in turn.

• March - MOD(12-3,12)=9 
• June - MOD(12-6,12)=6
• Sep - MOD(12-9,12)=3
• Dec - MOD(12-12,12)=0

If you're switched on you should see a pattern above. The result is equivalent to the number of months to the year end. So if we combine this with an EOMONTH formula we will get to the year end. 

Now if we do a sumif formula (as below) we can aggregate the revenue numbers into yearly results.

We should get the following:

 

Now you may be wondering why we didn't just use a Year formula and then a sumif. Well that is definitely another option, but say you now had a monthly construction page and you want to aggregate or lookup this data in a quarterly page. Trust me this happens all the time in project finance deals. Well the answer.... use the above methodology and you can't go wrong. 

If you liked this blog tutorial check out our new advanced training course, Advanced Toll Road Model Training Course. 

For more free blog tutorials go to Video Financial Modelling's Blog.