Putting together a successful financial model is all about cutting out perfectly avoidable problems.
In Part One of this blog series, we looked at the danger of using incorrect references or applying inconsistent labelling when attempting to create an effective financial model for your business.
Here, we’re looking at more common pitfalls for you to fix and avoid in the future.
5. Sheet imbalance and how to fix it.
An imbalanced balance sheet is something most modellers encounter from time to time. It can be extremely difficult to find the cause, so the best way to build a model is to follow these rules:
- Don’t leave inputs blank with zero values, use dummy numbers if you don’t have real data, so numbers flow through your model. That way if there is an error, it will show up.
- Start with a skeleton model that has a balanced balance sheet. You only need cash and retained earnings to start with.
- Build calculations in small chunks and when you have completed a chunk, hook it up to the reports, and before moving on, check that the balance sheet balances.
6. Ad-hoc calculations being included in real results.
We’ve all built an ad-hoc calculation in a model – perhaps to check a result, aggregate a set of values, or check our work. Normally that’s not a problem, but occasionally they get incorporated into some real results, invalidating the subtotal value. Highlight any ad-hoc calculations in a bright colour to ensure you can remove them when they are no longer needed.
7. Wrong sign.
This error type can be a killer! A simple minus sign, or lack of one, causes an error normally with twice the value of the cell in question.
It’s an easy mistake to make, but there is a simple way to avoid it – have a sign convention and stick to it. At Numeritas, our favoured sign convention is to have positive values for income (whether cash or accrual), and for asset balances and increases. We use negatives for costs and cash outgoings, liability balances, and liability increases.
8. Incorrect inherent assumptions about timing.
Timing assumptions in models are important and are a frequent cause of error. The time periods in models are typically months or quarters, though we also see semi-annual or annual periods and occasionally others. The longer the time period, the greater the potential for error.
9. Summary formula does not include the whole timeline.
Example: a SUM formula needs to reference all the cells in a particular row, across the whole timeline. This might be to provide a simple check to find the maximum or minimum value, but if the formula doesn’t span the whole timeline, an error could result.
A common cause is a timeline being extended without the formula being updated.
How to extend a timeline safely
- Check the consistency of all formulae in the timeline section
- Select all sheets with the same timeline
- Insert columns before the last existing one
- Copy across to the end of the extended timeline
If you’re keen to find out more information on timelines and how to extend them, why not check out our video here.
10. Master error checks not including individual error checks.
The master error check does not take into account all of the individual error checks. This is problematic as it could falsely present good health for a sheet that has an error.
Prevention is a simple case of taking the time to incorporate each check into the master check as they are created.
There are, of course, many other errors that can occur in your model build, however, these are some of the most common we’ve encountered.
We hope this two-part series has given you a good idea of what pitfalls to look out for when creating an effective financial model.
If you’d like to discuss any of these in more detail or find out how we can support your financial models please get in touch below. You can also head over to our latest on-demand webinar to discover more on how to effectively test your financial models.