Modelling a Major Maintenance Reserve Account (MMRA)

In project finance deals, major maintenance is usually very costly and infrequent in nature. For example in a toll road the road surface may need to be overhauled every 10 years. This major maintenance or lifecycle capex is expensive and usually cannot be funded sufficiently with operational cash flows. To get around this problem a reserve called a major maintenance reserve account (MMRA) or lifecycle reserve account (LRA) is usually used. This MMRA reserves cash progressively up until a forecast major capex spend.

Modelling a MMRA

Ok, so how do we model a MMRA by reserving cash up to forecast capex spends? Let’s assume that we have a:

• CFADS of $20m pa (assume this is flat);
• Replacement capex of $25m every 10 years (assume two of these); and
• assume no interest is received on the MMRA.

You can download the Excel spreadsheet and video tutorial related to the example above.

Now we are going to use a simple corkscrew account to model cash inflows (reserving for capex) and cash outflows (major capex). We could add in interest, however we will keep it simple in this example. Link up the opening balance to the closing balance of the previous period as per below.

Now let’s put in our major capex amount of $25m every 10 years. This is a cash outflow from the reserve.

Let us now put in the final part of the equation. We need to reserve $25m every 10 years. Now, we can specify how many periods (years in this case) we want to reserve this over. Let’s say we do it over the 10 years. i.e. $2.5m per annum. Now we are going to use an offset formula as follows to get the right amount to reserve per period. This is shown below.

As you can see above there is a maximum of 15 years, which replacement capex can be reserved over. Hence we should put a note on the assumptions page that we cannot enter any number over 15 and the number should be an integer.

Finally we sum the total reserve amount up, and link it into the corkscrew account. If you have done everything correctly you should end up with something like the below.

Like this video tutorial. Check out our project finance training courses.

5 Must Know Excel Charting Tips

This is a quick video blog tutorial showing you some of the must know Excel charting tips. You can follow on with some examples by opening both the above Excel spreadsheet and the video tutorial.

Tip 1: F11 to quickly chart

Simply select data and then press F11 to chart the data. You can also select multiple rows by holding down the CTRL key down.

Tip 2: F4 redo

How many times do you have to change the formatting such as font sizes on a chart? Well this is a handy tip. Simply change the size of the font on one axis, then select another the other axis font and press F4. This will redo the previous action and change the size of this font.

Tip 3: Putting another axis on a chart

This is an easy one. Simply select the data on the chart which you want to put on the second axis. Then select Layout, Format Selection and Secondary Axis.

Tip 4: Adding a dynamic title

Once you have a chart, simply select the chart, press Layout, Chart Title then = the cell with the data label.

Tip 5: Adding new data to a chart

Now this is a great little tip. To add new data to an existing chart, simply select the data, press CTRL+c to copy the data, then select the chart and push CTRL+v to paste. It is that simple.

If you liked this blog tutorial check out our online Excel and financial modelling training courses. 

Checks and Balances – Excel 2007 Conditional Formatting

What is conditional formatting?

Conditional formatting in Excel ensures cells are formatted based on certain criteria. For example if we have a cell is equal to “No” and we want to colour all cells equal to “No” red, then we can do this using conditional formatting.

Why use it in financial modelling?

There are a number of reasons you would use conditional formatting in in a financial model. One of the major reasons is to ensure model integrity through checks and balances. Examples of this include:

• ensuring that your balance sheet balances; and
• there are no negative cash balances etc.

Conditional formatting gives the financial modeller a visual representation as to whether these checks are met and the financial model integrity is upheld.

Using conditional formatting for model checks and balances?

Ok, the best way to learn conditional formatting is probably to look at a couple of examples. If you haven’t already done so open up the spreadsheet and theyoutube video at the top of this blog tutorial.

Ok first how do we find the conditional formatting button? Go to the:

• Home menu; the
• go to Conditional Formatting; and then
• we are going to use the New Rule and the Manage Rules.

See below for a diagram showing the conditional formatting button.

Now if we had a cell with Check and Ok and we wanted to change the cells:

• so that when it equalled Check it would come up with a red background and white font; and
• when it equalled Ok it would come up with a green background and black font.

So, let’s select the Ok and Check by holding down the shift key and using your arrow keys (or your mouse) and holding down the left click.

Once selected let’s go to Home, click the Conditional Formatting button and select the New Rule button. You should come up with the following screen. Enter in Format only cells that contain. Then select equal to and Check.

Now select format and choose fill, select red and then go to font and select white under colour. Push Ok and then Ok again. The Check should now have a red background with white font as shown below.

Whilst the Ok and Check cells are still selected go back to the conditional formatting button and select New Rule. Repeat the same procedure as above using equal to and Ok then select a green background with black font. You should come up with something like the below figure.

Now have a play around by changing the Check to Ok and vice versa. You’ll notice that the cells change formatting based on the value.

If you like this article, check out our training course Excel Shortcuts which has plenty more tips and tricks.

5 Must Know Excel Shortcuts

There is one word for improving your efficiency in Excel. Shortcuts. In laymen terms shortcuts allow you to perform tasks quickly using your keyboard. This blog looks at 5 Must Know Excel Shortcuts which will help you improve your efficiency in Excel.

 NOTE: You can follow see how the shortcuts work yourself by downloading the accompanying Excel spreadsheet and YouTube video in the above Blog Downloads area. 

 Shortcut 1 - The Grandfather

The first must know shortcut is quite literally the grandfather of all shortcuts. It sits at the top of the family tree and gives you access to numerous 1^st and 2^nd generation shortcuts.

What is it?

ALT + LETTER – gives you access to the Menu Ribbon – See Figure 1

Figure 1: ALT+LETTER

Notice the letters which pop-up in Figure 1, once the ALT key is pushed. If you push anyone of these letters then you will go into that menu. For example pushing “N” will take you into INSERT menu.

If you follow on the initial letter with another letter corresponding to an action, then that action will be performed.

Shortcut 2 – Charting at the Touch of a Button

Want to insert an Excel chart at the touch of a button? Well, all you need to know is:

F11 – Charts the selected cells

 Select the cells you want to chart and then press F11. It really is that simple.

Shortcut 3 - Finding your way

Navigating through Excel sheets is made easy with this shortcut. Simply press:

    -       CTRL+PAGE  DOWN – move one sheet to the right

 -              -      CTRL+PAGE UP –move one sheet to the left

 No more having to click from sheet to sheet, ensures you’re saving time.

Shortcut 4 – The Go to Guy for Auditing

This is one of the best shortcuts for flying around the Excel layout and helping you audit your model or spreadsheet.

 F5 - (1) takes you to a highlighted formula; or (2) take you to a named range or cell

 For example if you have highlighted a particular input in a formula, you can press F5+ENTER and it will take you to that input. Also you can just press F5, select any named range or cell and press ENTER. Excel will navigate to that particular named range or cell.

You can also use F5 to perform another nifty trick. When tracing precedents or dependents you can press F5+ENTER and you will go back to the previous traced cell.

Shortcut 5 - We’re not sure if this is technically keyboard shortcut

Some might say that shortcut 5 is not technically a shortcut. They may be right; however it can cut down the time taken to perform tasks such as formatting. Enter the:

APPLICATION KEY (although we like to call it the LIST key) – basicallyacts like your right mouse button

Having the LIST key means no more having to take your hand off the keyboard. The list key is shown in Figure 2.

Figure 2: Picture of the Application Key (doesn’t it look like a LIST with a mouse cursor on it?)

Figure 3 shows the display after the LIST key is pushed. As you can see, there are a number of items which can be performed.

Figure 3: Display after LIST key is pushed

If you like this article, check out our Excel Shortcuts training course which has plenty more tips and tricks.

 

Untitled

Car Indicators – How they can help you model in Excel

How could flickering car indicators possibly help you model in Excel. All will be revealed below.

On, off, on

Think about what happens when you put your car indicator on. The light flicks on, then off, then on and so on, right? A similar concept can be used in Excel modelling to simplify often quite complex problems.
Introducing binary indicators. Similar to car indicators, binary indicators turn on and off. If you’re not familiar with the world of binary operators then below is a brief description. When a binary operator is equal to:

• 1 it is on – an illuminated car indicator
• 0 it is off – non-illuminated car indicator

Yeah, yeah, that is great, but how can these binary indicators help me in Excel? Well let’s take a look at a couple of examples.

NOTE: You can follow these examples yourself by downloading the Excel spreadsheet and watching the YouTube video.

Example 1

Imagine you’re asked to model construction costs for a project based on the following assumptions:

1. Construction costs are $500,000 per month
2. The construction period runs for 2 years from 1 January 2011 to 31 December 2012

How would you go about modelling this in Excel? Some of you may say that’s easy… I would just do the following formula:

=500,000 x IF(AND(1 January 2011<=31 December 2012),1,0) or IF(AND(1 January 2011<=31 December 2012),500000,0) – see Figure 1

 

Figure 1: Long and cumbersome formula for Example 1

Whilst this works, the formula is long and cumbersome. This can cause troubles for external parties such as model auditors, may cause you to lose your mind trying to work out what you did previously and the construction IF statement cannot be used again in other formulas (you will see what we mean in a second). Don’t despair we can break this down into much more manageable components as follows:

1. Input the assumptions Construction Cost per month, Construction Start Date and Construction End Date
2. Create a Construction Indicator line: =(1 January 2011<=31 December 2012). Notice that we have got rid of the AND logical function. The new formula will create a one when the formula is true for both of legs and a 0 when at least one of the legs is false. Note that this Construction Indicator can be used in other construction period formulas (hence this construction indicator can be referred to over and over again)
3. Construction Costs: Multiply the Construction Cost per month by the Construction Indicator created in (2)

See Figure 2 for the above workings.

 

Figure 2: Simpler and easier to understand solution for Example 1

What do you think? Is this a lot easier to understand than the original formula? Let’s take a look at another example.

Example 2

Our next example asks us to sum the number print media sources from an online survey. See Figure 3.

 

Figure 3: How’d you find out about us categories

How do we solve this problem? Let’s use our binary indicators (aka car indicators) in conjunction with an OR function. Noticing that there are only two print media sources we would input the following formula:

= OR(How’d you find out about us data=”Newspaper”,How’d you find out about us data=”Magazine”)*1 – see Figure 4

You’ll note that we have multiplied the OR function by one. This is because the OR function alone will only return TRUE or FALSE. If we multiply the OR function by 1 then we will get 1 for TRUE and 0 for FALSE.
Now copy and paste this formula down. Add all the 1’s using the SUM function and you’re done. You should end up with something similar to Figure 4.

 

Figure 4: Solution to Example 2 along with formula for OR function

You now know how many print media sources are included in the survey.

NOTE: Example 2 could have been solved using a COUNTIF function; however that’s a topic for another day.

So now you see. Those flashing things called car indicators really do help you to model in Excel.
If you like this Blog, check out our Excel Functions training course which has plenty more tips and tricks.

Mini-Perms in Project Finance

In this blog tutorial the Video Financial Modelling team dives into the world of mini-perms. And no we are talking about those 80’s hairstyles. 

What are Mini-Perms?

Mini-perms are lending instruments which are intended to be refinanced out after a short timeframe usually 5-7 years. A number of project finance and PFI deals have been done using a mini-perm financing structure given the lack of liquidity in the long term lending market post the GFC. In this tutorial we will look at why mini-perms are being taken up, the different types of mini-perms and the issues related to these instruments.
Why are mini-perms being taken up?

Mini-perms have been promoted by banks, in a large number of financial markets, including in the UK PFI market. The main reasons for their use include:

       i.           concerns over long-term liquidity – an example of this is the introduction of Basel III outlining a global regulatory standard for capital requirements;

      ii.          the banks’ inability to underwrite and syndicate deals, with the resultant dependence on bank clubs for funding. i.e. banks are being forced to lend and hold; and

     iii.          a certain degree of opportunism among those banks still in the market.

Where there is limited liquidity for deals, banks prefer mini-perms given the short term over which they can amortise their arrangement fees. In addition there are fewer active lending banks in the post GFC world (i.e. less competition), hence there where a process isn’t competitive or there is limited appetite banks can dictate there terms.

 

Types of mini perms

There are typically two types of mini-perms a hard mini-perm and a soft mini-perm. We will look at each in turn.

Hard Mini-Perm
A hard mini-perm is a short term loan that mimics a longer term amortisation profile, but with a bullet repayment at the end of the tenor. Legal maturity of this instrument is typically around 5-7 years, forcing the borrower to refinance before maturity or face default. The main disadvantage is the default and refinancing risk for all stakeholders (funders, borrower and Government), which in a PFI deal may mean the termination of the Concession.

Soft Mini-Perm
A soft mini-perm is a long term loan with a mechanism to incentivise the borrower to refinance after an initial short period usually 5-7 years. Two methods for incentivising the borrower include:

1)      ratcheting up margins post the initial period; and

2)      using a cash sweep post the initial tenor

A soft mini-perm is a structure without the default risk of a hard mini-perm, where the loan maturity remains long-term. The soft mini-perm has been used in UK PFI and continues to be promoted by banks on a number of projects.

  Mini-Perm Financial Modelling

Let’s look at some examples of mini-perms assuming the following inputs. You can follow along by downloading the spreadsheets and youtube video. Also note that this analysis assumes that there are no refinancing fees etc.
For all scenario below we use LIBOR of 400bps, and an ongoing CFADS of 1,300 per month.
Hard mini-perm: 5 year initial tenor, bullet at maturity, amortisation profile mimics 20 year tenor, margins 250bps and a credit foncier repayment profile. Note that a credit foncier repayment profile is like a home loan mortgage with fixed instalments. See our tutorial, CFADS and DSCR sculpting, which goes into the credit foncier repayment profile in depth.

Using the actual margins for the hard mini-perm we get the following.

 

As mentioned the hard mini-perm is only short term debt and hence has no margins post the initial tenor. Borrowers usually have to estimate the margins post this short term debt tenor. Note that given the short legal tenor of the facility, the borrower must refinance the loan at the end of the initial tenor.

If the borrowers were to assume that the margins continue at 250bps we would get a normal credit foncier profile as per the below. Given the constant CFADS we would get a DSCR that is constant.

 

Soft mini-perm: 20 years, margins 250bps then ratcheting up to 500bps post 5 years

As you can see in the below the rachet up in margins after the initial tenor of 5 years causes the debt service to increase in mid 2016. With a constant CFADS this margin rachet would decrease the DSCR and eat into potential equity distributions.

 

Given the high debt service post margin rachet (and the likelihood of these rates occurring) borrowers usually assume different margins post the margin rachet. If we assume that the margins continued at 250bps then we would have the exact same profile as in the hard mini-perm assumed rates case.

 

As you can see from the above although the dynamics of soft and hard mini-perms are quite different it could be that borrowers could assume the same financial forecasts, particularly with respect to debt service modelling. One should note however, that the hard mini-perm is inherently more risky given the refinance/default risk. Now let’s look at a number of issues which occur with both hard and soft mini-perms.

Issues with mini-perm structures
 
There are a number of issues which borrowers face with mini-perm structures. These include:

i)                 Hedging: do you put a short term swap in or long term swap? Short term swaps will face refinancing risk on the underlying rate, usually LIBOR. Long term swaps assume a certain repayment profile which may change over time;

ii)                Affordability: what assumptions do the stakeholder (predominantly the borrower and the Concession grantor) make about margins, underlying interest rates, tenor and amortisation profile;

iii)               Sharing of risks: who takes the risk on margins, underlying interest rates, tenor and amortisation profile?

The above issues are resolved on a project by project basis.

Why you don't have to be a VBA macro master to build a financial model - Part 2

 

In part 2 of our “Why you don’t need to be a macro master to build a Financial Model” we are going to look at the main types of macros you’ll need to produce an advanced financial model. The three types of macros we are going to look at are:

 

1)      A goal seek macro – can be used to break a circularity

2)      A single cell copy and paste macro – we covered this in part 1 of this series

3)      A multiple cell copy and paste macro

Goal Seek Macro

Ok, this is probably the easiest macro to implement. It i

s very similar to the single cell copy and paste macro. If you are not aware of what a goal seek is we will try and sum it up in a line or two.

A goal seek will try and solve a certain cell to a specified value by changing another. If you look at the below figure we have 50 sales, each with a profit margin of $3.5/unit giving a total profit of $175. We want to know what the profit margin would have to be to get a total profit of $200. Obviously the profit margin has to be $4 per unit. But look how we found it. We goal seeked it by setting the total profit cell to 200 by changing the profit margin per unit.  

 

Ok, now let’s cheat by going back to our spreadsheet in part 1. As per part 1 we are going to record a macro. Go to View, Macros, Record Macro. Let’s call the macro GoalSeek. Go to the DebtCheck value and select Data, What-if Analysis and then Goal Seek. We want to select to value 0, by changing cell DebtHard value as per the below figure. 

  

Stop the recording by pressing the stop button in the bottom left hand corner.

Now let’s edit the macro. Go to View then Macros, View Macros. Select the GoalSeek macro and press edit.

We are going to delete the Range("D13").Select at the top. And we are going to replace the cells with their actual names as per below.

 

Now go back to the spreadsheet. Put 12,500 into the DebtHard cell (G12). Go to Data, Macro and View Macro. Select GoalSeek and press Run. This should solve the macro and set the DebtCheck to 0.  

Single Cell Copy and Paste Macro

Now we looked at a single cell copy and paste macro in the first part of this series so we are not going to cover it here. If you want to check out that article, click here. 

Multiple Cell Copy and Paste Macro

Ok, we’re going to leave you to do a bit of homework on this one. Depending on how you go we might look at doing a third part to this course. Go to the spreadsheet and scroll down to question two.  See how you go with it. If you’d like us to do a third part to this macros series, leave us a comment or send us an email. Hint: In this question you might find that the countif function will come in handy.

We cover macros in detail in our advanced toll road training course. Click here to check it out.

 

 

 

 

Why you don't need to be a macro master to build a financial model - Part 1

Normal 0 false false false false EN-GB X-NONE X-NONE

Many self-proclaimed financial modeling experts will tell you that you need to have a good grasp of Visual Basic for Applications (VBA) and macros. Well at Video Financial Modelling we are here to tell you that you don’t. Well not if you’ve read this article.

Resources

YouTube Video

Starter Spreadsheet

Final Spreadsheet

Why do we need macros?

In simplistic terms a macro is a program that implements a task. VBA macros are often used to automate tedious financial modeling tasks or to break circularities.

Why don’t you need to be a macro master?

Well there are a number of reasons why you don’t need to be a VBA macro master.

1)      Firstly, we’re going to give you some common macros that you can simply copy and paste into VBA in your financial model

2)      Secondly, it is really easy to record your own macros and then manipulate them to suit your particular situation. We’ll look at an example a bit later

3)      Thirdly and finally in all the financial models we have seen (and we’ve seen a lot) there are only a few situations that require a macro. We will run through each below.

Firstly let’s look at an example on how to record a macro.

Example – Manually recording a macro and then manipulating it

In this example we are going to look at Firstly open the accompanying blog workbook. Also remember you can watch the blog youtube video to follow this example.

To start recording a macro, press View on the menu and then select Macros. Select Record Macro on the popdown menu. Let’s call the macro DebtSolve.

Select the Debt Amount – Calculated value and then copy the cell (CTRL+c) and paste special values (Home, Paste, Values) in the Debt Amount – Hardcoded cell. Press enter.

 

 

Now press the stop button in the bottom left hand corner.

 

 

Go to Data, Macro and push View Macro. Then select the DebtSolve macro and press edit. We are now going to put the named cells into the macro and add in a loop.

Ok, let’s firstly put the relevant named ranges in. Just copy both the DebtCalc and DebtHard and put them in place of the relevant cells in the VBA file.

Now let’s add the loop.

Simply copy and paste the following code:

Do While Range("DebtCheck") <> 0.

We’ll also have to put in:

Range(“DebtCalc”) to replace the Selection before copy.

Place it at the start of the code after the Sub Debt Solve() and the green commentary.

And now put a Loop before the End Sub. The final macro should look something like this.

This loop will keep copying and pasting the calculated debt amount into the hardcoded debt amount, until these values converge and the debt check becomes zero.

Now let’s go and select Insert on the menu, select Shape and then a rectangle. Draw it on the sheet. Then right click on the rectangle and select Assign Macro and press DebtSolve.

Now press the button you just created and voila you’ve created a macro that solves a common financial modeling circularity. Change the hardcoded value or the arrangement fee and press the button again. It should solve.

In the next part to this macro series we are going to look at the main types of macros you’re going to need when you’re financial modeling.