CFADS and DSCR - Words from a Foreign Language?

Some of you might be wondering whether CFADS and DSCR are words from another language. Well we’re here to explain to you that they’re not. CFADS and DSCR are acronyms which are common place in project finance deals. We’ll look at each in turn. 

NOTE: You can follow the examples by downloading the accompanying Excel spreadsheet and YouTube video in the above Blog Downloads area. 

CFADS 

Cash is KING in project finance deals and CFADS is well, the heir to the throne. 

CFADS stands for cash flow available for debt service and is usually defined in the project finance loan documentation. It is used by financiers to calculate ratios and debt sizing. 

Although it can be defined in many different ways, the simplest definition is:

•  
  • Revenues
  • less: Expenses 
  • less: Capital Expenditure during Operations(1)
  • less: Tax
  • plus/minus: Net Working Capital Movements

(1) In some instances Video Financial Modelling has seen growth capital expenditure, excluded from CFADS. The reason for this is that it is a discretionary expenditure. 

In a large number of cases project finance deals exhibit:

1. A ramp-up period post construction completion. For example think of a toll road. As soon as it is completed it does not get the predicted baseline traffic. People don’t automatically switch to using the toll road from alternate routes, it takes time. 
2. CFADS usually grows over the life of the project. This is why project finance debt is often sculpted as opposed to repayments via a credit foncier profile. We will look at sculpted and credit foncier repayment profiles in the next blog tutorial. 

In the meantime let’s take a look at an example of CFADS. 

Example

Find the CFADS given the following information:

• Revenue = 50
• Expenses = (10)
• Capital Expenditure = (15)
• Tax = (5)
• Working Capital Movements = (2) 
• Depreciation = (10)

If you got a CFADS of 18, then you’d be correct. Don’t get fooled by the depreciation, which is a non-cash movement. 

Now let’s take a look at what the DSCR is. 

DSCR

DSCR stands for Debt Service Coverage Ratio and is defined in the project finance loan documentation. In most cases it is equal to the:

CFADS divided by Debt Service 

The DSCR can be taken over a forward or backward looking period, say 6 months, or simply during one individual period.  More often than not the DSCR is calculated during the operations phase of the project only. 

Debt Service is usually defined as the aggregate of senior debt interest, repayments/principal and in some circumstances may also include other fees.

As you probably already guessed the DSCR is a measure of the amount cash flows can cover debt. The higher the DSCR, the more cash flow is available for debt financiers as a safety net.  

There are usually two benchmarks DSCR’s, defined in the project loan documentation, which are compared to the calculated DSCR. The first is the lock-up DSCR and the second is the default DSCR. 

When the calculated DSCR is below the lock-up DSCR all cash is locked up and may not be distributed to equity, until such time as the calculated DSCR is above the lock-up DSCR again.

If the calculated DSCR goes below the default DSCR, then the company would be in default and there may be many mechanisms to deal with such default, such as the lenders stepping in to perform the company’s duties, selling the project etc. The default provision is done on a project by project basis. 

Let’s take a look at an example for calculating the DSCR.  

Example

If we have the following CFADS, Interest and Principal payments what is the DSCR for each period individually and also using a look-back of two periods.  

Figure 1: CFADS, Interest and Principal

Firstly let’s work out the Debt Service:

Figure 2: Calculating Debt Service

Now let’s look at finding the DSCR for each individual period and also the DSCR for a two period look-back.

Figure 3: Individual Period DSCR

Figure 4: Two Period Look-Back DSCR

You should notice something here. There isn’t sufficient cash in period 3 despite the look-back DSCR ratio being above one. Be careful of this and make sure your financial model has a check for cash flow shortfalls. 

Hopefully this blog clears up the fact that CFADS and DSCR are not words from foreign languages. 

If you like this blog tutorial, take a look at our Intermediate financial modelling courses.

As always we’d love your feedback, so that we can continue to provide content that is relevant to you. Also check out Video Financial Modelling's website, which has plenty more free resources and great training courses.  

 

 

 

 

 

 

 

 

Calling all Excel Logical Operators

Forget telephone operators, who almost always put you on hold, today we’re talking about Excel logical operators (also called Excel logical functions).
A logical operator can simply be defined as a function that returns certain values if a logical test is true or false.
We will look at three of the key Excel logical operators; IF, AND and OR.

Note: You can follow the examples by downloading the accompanying Excel spreadshset and YouTube video.

IF
The format of the IF statement is as follows:

= IF (Logical Test, Value if logical test is true, Value if logical test is false)

The IF statement checks if a logical test is true or false and returns a certain value based on the outcome. Let’s look at an example.
Example: Below is a table with people’s dates of birth. We want to find out which of the people are over 21. Let’s assume that today’s date is 31 December 2011.
Normal 0 false false false EN-GB X-NONE X-NONE

Person	Date of Birth
Bob	31 Aug 1990
John	30 Sep 1995
Bob	31 Dec 1992
Jane	30 Jun 1984
Sally	31 Dec 1991

See the formulas for the calculation in Figure 1 below.

Figure 1: Calculation for IF Function Example
If you found that Bob (the first Bob that is) and John are the only people over 21 then you’d be correct.
AND
The format of the AND statement is as follows:

= AND(Logical Test 1, Logical Test 2...)

The AND statement returns TRUE if all the logical tests are true and FALSE if any one of the logical tests is false. Let’s look at an example.
Example: We want to find whether the date 31 May 2011 is in between 30 June 2011 and 31 December 2011.
We can see the calculation for the above in Figure 2 below.

Figure 2: Calculation for AND Function Example
If you got FALSE you’d be correct.
This may seem like an easy example but its implications are far reaching. Imagine if you had lots of dates and you wanted to find which dates are between two particular dates, a start date and an end date. I bet you could copy this formula across or down a row to find which dates meet the AND criteria.

Remember our Blog tutorial on Car Indicators – How they can help you model in Excel? We did a similar thing in that Blog tutorial.
OR
The format of the OR statement is as follows:

= OR(Logical Test 1, Logical Test 2...)

The OR statement returns TRUE if any of the logical tests are true and FALSE if all of the logical tests are false. Let’s look at an example.
Example: The below is a table with test results for two tests recently taken by students. We want to find the students that either scored over 75% in one test or had an average of more than 65%.

Person	Test 1 (%)	Test 2 (%)
Bob	76.00%	65.00%
John	53.00%	74.00%
Bob	67.00%	65.00%
Jane	45.00%	65.00%
Sally	65.00%	60.00%

See Figure 3 for the OR calculation.

Figure 3: Calculation for OR Function Example
You'll notice that both the Bob's met the criteria.
If you like this article, there are plenty more tips, tricks and worked examples in our Excel Functions training course.
Check out our Excel and Financial Modelling training courses.

Excel NPV Function

What is an NPV?
Simply put an NPV or net present value is a way of determining an investment or projects value. It is probably the most often used formula for financial decision making. The below tutorial takes you through:

•  An introduction to present value
•  The decision to accept or reject a project based on NPV
•  The Excel XNPV function
•  Putting it all together – some examples

Present Value Introduction
Before we delve into the depths of NPV, we should first understand what a present value or PV is. Imagine that you were to receive $100 from the bank in five years time (unlikely I know). You know that the interest rate from the bank during that period of time is going to be constant at 5%. What is that $100 value today?
Think of investing an unknown amount y, for 5 years at 5%. We know that the first year we would get y x 5% in interest plus still have our principal y. i.e. we would have y x (1+5%). If we re-invest our interest and principal of y x (1+5%) for another year we would get y x (1+5%) x (1+5%) or y x (1+5%)².
As you can imagine the pattern repeats itself until we get to year 5, where we have y x (1+5%)⁵. Hence if we want $100 in five years time by investing y then the following relationship would have to hold.

100 = y x (1+5%)⁵

Rearranging this formula would bring about y = 100/(1+5%)⁵. Wow, look what just happened, we derived the PV formula.
Type the equation in the calculator and you get y = $78.3
NPV is simply an extension of the PV, taking into account your initial investment. Say for example we had an initial outlay of $50 and in return we got $100 in five years time. The NPV would be the PV of future cash flows (i.e. the PV of $100 or $78.3) less the initial investment at time zero ($50), or $28.3.
So what does that value mean?
The decision to accept or reject a project
Basically put if a project has a positive NPV then we should accept the project. The below outlines the accept reject decision.

•           NPV >0 – accept the project
•           NPV = 0 – indifferent between accepting and rejecting the project
•           NPV <0 – reject the project

In summary we should accept a positive NPV project because it adds value to our company.
NOTE: Some of you sharp tacks may be thinking. What happens if you have multiple projects with positive NPVs but only have a restricted budget? Well that is a topic for another week and relates to capital budgeting restrictions.  
The Excel XNPV function
As pointed out by Chandoo.org and Navigator Project Finance there are some limitations to the NPV function in Excel. Some of the main limitations include:

1)      The cash flows are at the end of the year (even your initial investment which is meant to occur at t=0); and

2)      cash flows must be equally spaced apart.

As a result of these limitations Video Financial Modelling prefers to use Excel’s XNPV function. Don’t be too worried, the Excel XNPV function is similar NPV function, but with one extra input, dates. The XNPV function looks like:
XNPV (rate, values, dates)
Let’s look at some examples for the Excel XNPV function.
Putting it all together – Some Examples
You can follow the examples by downloading the Excel spreadsheet and watching the following YouTube video.
Example 1
Let’s firstly look at the example we introduced in the Present Value Introduction. Imagine that the current date is 31 December 2011 and we want to find the present value of $100 given to us in 5 years time if we use a discount rate of 5%.
See Figure 1 for the XNPV calculation.

Figure 1: XNPV formula for Example 1


If you've done this correctly you should get a value of $78.3.
Example 2
You are thinking of investing in a machine. On 31 December 2011 the machine costs $150, and you require a return of 10%. If you have the following cash flows, would you invest in the machine?

•           31 December 2012: $50
•           31 December 2013: $40
•           31 December 2014: $70
•           31 December 2015: $10

Figure 2 shows the calculation for the above example.

Figure 2: XNPV formula for Example 2
Using Excel we come to the following conclusion, the net present value of the cash flows is -$12. If we use the decision rules we established in the last section we would reject the project as it has a negative NPV.
If you’re still struggling with the NPV concept check out some of these great links.

•           Wikipedia
•           Chandoo.org
•           Navigator Project Finance
•           (Note: don’t get bogged down with the nominal to real rate calculation in this video).

Like this article? Check out the Excel and Financial Modelling Training Courses at Video Financial Modelling.