Finding a balance sheet error

Despite your best efforts, despite trying to set up all your financial statement links and checks properly, your Balance Sheet is just not behaving.?Errors are triggering all over the place.

We’ve all been there.?The error could be caused by something as random as one wrong formula in one cell (or multiple wrong formulas in multiple different cells) and so could be really hard to track.?But your model is supposed to be picking up the cash flow impact of all the balance sheet movements.?If you think about the perfect model in those terms (where all balance sheet movements are being translated into cash flow) then there may at least be something you can do to investigate the source of the errors before breaking down in tears, ripping up your model, and starting again.

If you find yourself firmly in trouble-shooting mode, furiously trying to hunt down a balance sheet error, here’s how to track it down.

What kind of error is it?

Zoom in on the balance Ssheet check and calculate the size of the variance (the difference between lines 42 and 47 of the balance sheet, as shown at line 60 below).?Look at when it first arises and how it changes.?The variance might be:

• triggered once, staying constant thereafter – in that case it’s an error in the first year alone – one number in your model.?If it happens just in the first year of your forecast, you may have forgotten to bring an opening balance sheet figure into your figures
• growing – in which case it looks like it’s a whole line of numbers in your model that are causing an issue, just like line 60.?If the error is mounting up, subtract this year’s variance from last year’s to see how much it is growing (line 61) – that might flush out a number in your model that you recognise, allowing you to trace the error quickly
• a bit here and a bit there – if the variance has some other e.g. quarterly pattern to it that gives you a clue as to where the problem is coming from too. For example, if you have quarterly rent, principal, tax or VAT payments, it’s likely that the problem might be in one of those.

Subtract the whole balance sheet that’s causing the problem from last year’s balance sheet

Look at all the line by line differences between the balance sheet that’s got the problem in it, and the previous year.?If you can see that the variance arises from one particular line item in your balance sheet you’ll be well on your way to tracing the issue.

Halve the size of the variance

If you’ve added a number where you should have subtracted it (or vice versa) the variance that you’re seeing could be twice the size of a recognisable series of figures in your model.

In the example above:

• the check is out from the first year (D49)
• the variance is growing each year (line 60) but by a constant amount (line 61)
• the variance looks to be caused by an issue with fixed assets – I can see the detail of that by subtracting the first two balance sheets (D64)
• if I halve the amount it’s growing by at line 61, I realise depreciation is causing the issue: I have the sign around the wrong way
• I fix it quickly, with no crying.

Eyeball all the flows and signs in the model

Sometimes in a big model an error arises just from having a sign or a formula going in the wrong direction.?So run your eye across all the line items one at a time and make sure the size and the direction of them is as you’d expect.?In the example above I might have noticed that fixed assets at row 55 were growing at a greater rate than expected, before going to much more trouble with my error-tracking regime.

Start zeroing out inputs

Go section by section through your inputs and start zeroing them out (focussing on those with balance sheet implications).?At the point the variance disappears, you’re well on your way to tracing the problem.?In the example above, if I’d changed the inputs so depreciation became zero, that should have flushed out the issue.

Start stripping back

Just taking the inputs out should be enough but, if you’re starting to get desperate, perhaps you can get the model back to a point where the balance sheet does balance (e.g. by taking out line items or going back to an earlier version that you saved) then you’ve at least arrived at a good point from which to start moving forward again.

Tracking balance sheet errors is hard!

If building good Excel financial models requires a bit of skill and can be a bit tricky, tracking balance sheet errors within them is even harder!!?From the steps above you can see some kind of methodology for trying to find the source of the errors – but it’s a methodology that contains few easy answers.?Bottoming out these kinds of errors takes a bit of guesswork and a bit of luck.?If you’ve got multiple errors piling up on top of each other, tracking them can be very hard and time-consuming indeed!

If you get to the point where you sit down and feel the need to cry for a bit, and feel forced to go back to a prior version where you did have everything balancing, or rip up chunks of your model and start again (or even feel as if you should now pledge from this point forwards that you will always keep your balance sheet balancing for the rest of your life) you wouldn’t be the first!?Trying to track balance sheet errors can take you through a whole gamut of emotions!

Get your check in early - that’s the real lesson

There’s a few things you can try to trace balance sheet errors but, in truth, it’s really hard and (usually) takes lots of time.?That's why the only way to build a model is to establish an early check and maintain it throughout a build.?If this is the first time you’ve heard this message (balance early, don’t balance late) you’re very lucky because you’ve now saved yourself a lot of future grief.?If this isn’t the first time you’ve heard the message (don’t balance late, balance early) and you’ve suffered as I have with years spent tracing balance sheet errors before finally seeing the light and realising the importance of getting a check in up front: we should think ourselves lucky too.?We may have learnt the hard way but we get to appreciate the lesson all the more for the knowledge having been so hard won!!

There are a few things you can do to trace balance sheet errors as outlined above but, really, the best thing to do is get that balance sheet check into your model as one of the initial steps in your model framework.?If you can always keep your balances sheet balancing you’ll save a lot of time and frustration from having to find errors!

PS – here’s the full example