Get your balance sheet balancing every time

This is a new-to-me trick I played in Oxford this week (thanks John Gilligan – it was a total riot).

Put in a proper balance sheet check

I told the MBAs to put in a second balance sheet check.?Pretty much every messed up model I’ve seen has failed on this count (including one from some-bank’s star analyst I remember once which had cash flow of ￡300m but cash balances increasing by ￡1bn in the balance sheet).

Accountants know this.?It’s at the sacred heart of their religion I think.?This year’s equity equals last year’s equity plus this year’s bottom-line profits.?Just that knowledge (accountants are great) gives us enough to construct a second balance sheet check which makes sure the links within your forecast have some degree of integrity.

Don’t worry if you don’t get it.?You can think about it for a while if you wish.?Imagine a supplier from whom we buy ￡100 of pencils for our stationery cupboard.?￡100 goes through the P&L.?Imagine the supplier gives us time to pay.?￡70 now (cash flow), ￡30 later (balance sheet liability).?The change in the BS (minus ￡30 liability minus ￡70 cash) equals the change in profits (minus ￡100).?There’s something about our ‘system’ of accounts.?The change in this year’s equity is the change in profits.

Don’t worry if you don’t completely understand it.?It just is (religion is like that).?But knowing that this year’s equity equals last year’s equity plus this year’s change in profits gives us all the information we need to construct that vital balance sheet check, as per cell D52 in the example below.

The benefit of control accounts

I’ve known about this religious dogma and the importance of proper balance sheet checks for longer than I remember. I am guessing it was introduced to me in my accounting lectures in 1990.

But this week I decided to make use of something called control accounts.?I’m not sure if this is a proper accounting term or not but for modelling what we mean is that we separate things out.?So for fixed assets we would have the start position at the top.?Then the movements: depreciation in one line, cap-ex at the next.?The total closing position at the bottom (this year’s fixed assets) is the sum of all the above: open plus movements.

It just allows you to keep track of things.?The alternative would be to sandwich everything in one line but that would make it easier to make a mistake.?Control accounts (or “cork screws” as they also get called) aid clarity.?Why not??Lines are cheap in Excel.

Control accounts or corkscrews, like proper balance sheet checks, are not new to me either.?I’ve been using them for years e.g. for fixed assets or debt.

Going overboard on corkscrews

This is the new bit: building in a corkscrew for every blimmin’ single balance sheet line item.

What that allows us to do is track the balance sheet line item and the cash flow movement at the same time and never get anything muddled up, which could easily happen in a big model.

A huge source of balance sheet error is mismatching the balance sheet line items and the corresponding cash flow movement.?It’s too easy to get something around the wrong way, or double count, or miss something out.

An early balance sheet check

Previously on my teaching programmes I had always told people to get a balance sheet check in early with a simple version of your model.?That could save a lot of crying.

On past programmes we would probably get the balance sheet balanced at about day 1 ?.

Pretty much every time we would have to iron out the errors (see the regime below for some clues – it did involve the risk of breaking down crying in the middle).

An error-free balance sheet modelling regime

The Oxford MBA was different though.?Thanks to the control-accounts approach.?It allows you to get a balance sheet check in absolutely as early as possible.?Not on a complete balance sheet, just on one line item.

That’s what we did on the Oxford programme about ? in to the first day.?At that point we had a balance sheet with just one control account and just one line item: fixed assets.

That allowed us to divert to something more interesting (structuring scenarios) before coming back to create other control accounts around other line items.?Slotting them in one by one, wiring the line item into the balance sheet (check goes out) and wiring the cash flow movement into the cash flow statement (check comes back into line).

I can’t believe that I had a wasted past life where I spent so much time crying over balance sheet errors! This time I just cried at the end when I realised what a brilliant time we'd had and how glad I was I came (true story - age will do that to you: make you more emotional).

Yes folks, it is possible for an old dog to learn new tricks and, if you’re taking your modelling seriously I’m going to advise you learn this one now rather than X years into your career.

On our programme it enabled us to mess around with a few interesting topics before returning and crunching through the balance sheet line items on day two, with control accounts acting like the car seat belt.

Control accounts feel like a bit of a pain (they take more lines) but they save you a lot of grief in the long run.?Certainly on this week’s programme we were able to get further with our model build than I would normally expect, simply because we had fewer errors to iron out.

Control accounts are a bit of a pfaff but, once you get into the swing of it, you can pretty-much copy them down each time.?They save you a huge amount of?trouble around balance sheet errors (you shouldn’t have any at the end because you’ve been sorting them out as you go).

A proper balance sheet check, and control accounts, make your modelling experience fool proof.

Control accounts rock.