Modelling an Arrangement Fee Circularity

Ok, in project finance deals you’re going to come across a particularly circularity around calculating senior debt arrangement fees. This blog tutorial will focus on how to model this circularity.

What is an Excel circularity?

In basic terms an Excel circularity is where a calculation refers to itself. In Excel you can allow circularities and the number of iterations which the calculation undergoes; however when you’re modelling and there are a few circularities it is best to break these up.

Why are arrangement fees circular?

Let’s think about this process. Firstly debt arrangement fees are calculated based on the total amount of debt. However debt arrangement fees are used to build up the total construction phase funding requirements, which are funded by debt. So basically as the arrangement fee amount goes up so too does the debt amount, which then causes the arrangement fee to increase. The arrangement fee and debt amount do converge though. Hopefully you see this circularity; however we will look at an example below.

An example of arrangement fees

For this example you will need to open up the pre-populated spreadsheet and youtube video attached to this blog tutorial. Now in this example we assume that all construction costs are fully debt funded. Arrangement fees are 2% of the debt amount and the other construction phase costs are as per the spreadsheet.

Now if we calculate the arrangement fees by multiplying the debt amount by the 2% arrangement fee, you’ll notice that we get a circularity as shown below.

Now to break this circularity we need to hardcode a debt amount. You’ll see why this is important soon. Copy and paste the calculated debt amount into the hardcoded amount as shown below. You’ll need to paste the value so push Home, then the Paste dropdown, then values to do this.

As shown above, create a check by subtracting the hardcoded debt amount from the calculated debt amount. Now when the model is solved this check amount will be zero. Link up the arrangement fee calculation to the hardcoded debt amount. Now, copy and paste values the calculated debt amount into the hardcoded debt amount, until the check amount is zero. This will occur when the arrangement fee converges and model is solved.

If you liked this, check out our free project finance modelling training by signing up to our newsletter.

 

 

Naming Cells and Ranges in Excel

At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an articlehttp://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/ which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking.

Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency.

Model Audit

It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model.

Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references.

Efficiency

If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet.

How to name cells and ranges

You can follow along with some of the below examples in the Excel spreadsheet and video tutorial contained in this blog.

Naming Cells

Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our Excel Functions training course.

Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell.

Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below.

Naming Ranges

Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts.
Now let’s do an example by naming the inflation index below.

Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar

Push CTRL+F3 and follow the prompts to name the row.

User beware – Naming Ranges

Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly.

1. You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date.
2. Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly.
3. Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day.

Like this video tutorial. Check out our Excel and financial modelling training courses.

Naming cells and ranges in Excel

 

 

At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an article (http://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/) which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking.

Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency.

Model Audit

It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model.

Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references.

Efficiency

If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet.

How to name cells and ranges

You can follow along with some of the below examples in the Excel spreadsheet and video tutorial contained in this blog.

Naming Cells

Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our Excel Functions training course.

Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell.

Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below. 

 

 

 

Naming Ranges

Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts.

Now let’s do an example by naming the inflation index below.

 

Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar

              

Push CTRL+F3 and follow the prompts to name the row.

User beware – Naming Ranges

Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly.

1)      You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date.

2)      Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly.

3)      Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day.

Like this video tutorial. Check out our Excel and financial modelling training courses.

 

 

 

 

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.