As we work through the 10 Rules of Financial Modelling, we now enter the arena of the healthy habit of error checking. This post looks at why error checking is crucial to getting the right outcome and how to go about it in both model development and model use.
Error checking is an imperative operation to a good analyst. They likely have a mental arsenal of error check techniques and systems that they like to apply. The arsenal will be applied to different areas such as formula checks, logic checks and consistency checks.
Although technology and software are incredible in what they can do, it’s still easy to make a human error. Every conscientious financial analyst will use various methods to ensure that error checking identifies formula issues or cell errors so that anybody can depend on the model’s accuracy.
Here’s how to create your error checking arsenal for your financial models:
Don’t be too relaxed. Make it a habit to error check.
It’s a common beginners mistake to be overly confident with what they produce. A fabulous looking spreadsheet, some simple formulas, the result looks perfect.
So what could go wrong?
Believe us when we say – plenty!
The wise financial modeller will always be looking for errors, even with the most basic output. The calculations must be looked at again and again on a cell by cell basis. It’s critical that you fully understand what you’ve written.
Add test numbers
An excellent approach to get started with error checking is to use test numbers. You may have a simple set of numbers for your model – perhaps your model is for production costs or department expenses.
By using test numbers such as 1, 10, or 1,000 you can quite often find what you wouldn’t find with the real numbers. You can see any patterns and trends. You’re more likely to spot what might be going wrong.
Zero everything out
This is another error checking approach. Change the inputs to zero andsee if you have any other numbers remaining in your model. If you do – where did they come from? However, be aware that zeros can also blank out everything to not see any error in the multiplication or addition as nothing is being multiplied. You can often find calculation errors quite rapidly – which is what you want.
Sum to zero
Summing calculations is another error checking approach. For example, you might have a loan. You draw down the debt, and you repay it. So the sum of the drawdowns and the repayments should be zero.
If you’re doing straight-line depreciation on your fixed assets, then the sum of the construction costs and the depreciation should be zero.
Working capital is another example. Receivables and payables represent the timings of the revenues and costs. Over time all revenues and costs will be received and paid in full. So the sum of the work capital change should be zero. You can see the same with VAT or sales tax. There are many checks like this, so add them in. You’ve got to develop instincts and intuition that helps you get a sense of the numbers.
Magnitude check
A magnitude check is more about testing your gut feeling on whether the numbers you get are correct. Do they look to be the right size – whether it’s thousands or tens of thousands or even millions?
Are the proportions right throughout the system? If it’s reasonable to have your answers in tens, but you have it in thousands, then it’s not unreasonable to believe something has gone awry.
Ratio checking
Ratio checking is about consistency with similar projects or businesses. For example, revenue divided by the costs should be roughly the same for all windfarm projects, so check your result with an existing model. You can also use any of the accounting or financing ratios. The results should be of the same order of magnitude.
Left to right consistency
Check the numbers as they go across the page. Do any major changes match events and expenditures that you are aware of? Is there a cluster of negative numbers where all the other values are positives?
Update a formula
A common mistake is to update formula and then getting lost in the woods, as it were, and forgetting to copy that formula across the row. So your balance sheet won’t balance, and you’re looking around trying to find the error.
It’s always worthwhile to look across the row to the middle somewhere and check that the formula is working, and you can see the type of number you’re expecting as the outcome.
Do one topic at a time
You can’t go wrong if you develop one topic at a time. For example, if you are about to start on Capex you would start with inputs for Capex, followed by the calculations for capex, depreciation and book value on the working sheet, and then you feed the results through to the output financial statements. There will be a balance sheet effect, so you don’t go any further until you’ve correctly reconciled the balance sheet check and got the lines of zeros.
Triangulate your error checking
So now you’ve got a couple of checks, and you’ll notice that some errors will begin to drop out, and you can use them collectively to triangulate the balance sheet.
You’ll find out what is causing the errors, and then you won’t start your next piece of work until you’ve resolved all the issues with the current topic.
Of course, it can be tempting to move on, but if you start darting around all over the model, you’ll lose track of where you are and won’t be working systematically. Not working methodically is likely to lead to a mess. You won’t be able to work out what has gone wrong and when it went wrong and why.
Adopting a step-by-step approach, working through topic by topic, doing all the checks, and resolving them all before you start the next activity will lead to success. Once you’ve developed a good habit like this, then it will support you as you move into more complex modelling work.
In conclusion
Those who perform error checks never regret the time spent. It’s a habit any financial modeller should cultivate. It will pay dividends when it comes to gaining a good reputation for accuracy and not wasting your or your users time.
