Control Accounts

Overview

This is an extract from our pre-course videos for our renewable energy and infrastructure project finance modeling course.

Video

Video Transcript 

If you listen to the lesson on binary flags, you may remember that there are three things that fundamentally changed my approach to modeling. The first was sensitivities and scenarios which are covered throughout the course. The second was binary flags that show when something occurs over a specific duration. And the third, which is the topic of this lesson and something dear to my heart are control accounts

And to be honest, of all of the best practice concepts, this is the one that actually changed the way that I tackle problems. From a modeling standpoint, it changed my life and you can’t be a model without them. This lesson will introduce you to control accounts and their benefits. We won’t cover the specific formula required as this will depend on the problem we are actually tackling. So that will be covered later in the course. Accounts present a way to tackle problems and are also a way to show information in a really simple and clear manner. Models are full of numbers and we’re constantly having to record volumes of something over time, such as the money we owe to someone or the amount of inventor I have on my balance sheet, or perhaps the volume of physical items such as the number of pens I may or may not steal from Dan’s desk…. My precious….

So let’s use a really simple example of a $100 million loan with four equal annual principal repayments of $25 million to introduce the concept of an account. Of course my inputs are separated from my calculations as learned in the introduction to best practice modeling. In the calculation section, I have control account calculations, which represents the balance of the loan over time. There are four labels in the section being the opening balance; that’s the balance of a loan at the start of the period, or how much we owe the lenders. The second draw downs, BOP, that’s any money I draw down on my loan, in this case in year one. BOP stands for beginning of period, so I’m assuming the drawdown occurs at the start of the year. Principal repayments, that’s the principle I’m paying back to the lenders. We would also assume that occurs at the end of the year and finally closing balance. That’s the balance of the loan at the end of the year. So, to the calculations: at the beginning of year one, I draw down $100 million. It is positive as it increases the amount of the loan I owe, it adds to my account balance. The $25 million repayment in year one reduces my balance. So we make this a negative.

You may also notice now the labeling of minus principal repayments being a reduction in them in the balance and plus drawdowns, adding to the balance. Be obvious. This helps the user. Who knows, maybe even I will understand it.

I have a closing balance of 100-25=75 million, and my opening balance in year two just equals the year one closing balance. Why? Well, the closing balance in the year one represented the balance at the very last second of year one, and the opening balance in year two is just the balance of the very first second of year two, nothing’s changed in that one second. So I drew this loan in year one and I repay another 25 million in year two to reduce my balance to 50 million. As calculations must be consistent across the row, I copy and paste the calculations across to year three and four and we see the loan pays down as expected to 0 at the end of year four. I’m not quite done though. I haven’t actually added, an opening balance in year one.

Because I need to copy one formula across the whole row, this should be the same calculation as the rest of the row, where the opening balances always refer to the previous periods closing balance, but if I do that, I’m linking to the closing balance in the totals column.

But Haydn, isn’t that a really bad practice?

Fair point Dan. Look, some people may not like this and best practice modeling really is subjective. But what we do here at Pivotal180 is we create a style called blank, shown as black on the screen with no value in the closing balance line under the total column. This is telling the user not to touch the cell. It is being used for something. So look, personally Dan, I’m okay with it. What’s particularly nice about this account is that I can clearly see my opening balance, my draw down, my principal repayments, and my closing balance. It’s uber clear, and even better, I can see the total drawdowns and the total repayments equal each other. That’s a great check. I want to know if I’ve repaid my loan. Look, accounts clearly show outputs in an easy to understand manner, but the real power will come as we build these throughout the course.

Just trust me with this one thing: if you have a volume to record of anything over time, start your calculations by building a control account.