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.