Five steps to error-proof your model
Building a financial model can be a long and complex process. In a world where accuracy is key, ensuring that your model is fit for purpose and error free when it’s launched is vital. Best practice principles and technical know-how go a long way towards the robustness we all strive towards, but there are some more fundamental steps you can take to help you maintain model integrity from the start. Here are our top five.
1. Plan your model
This may sound obvious, but less modellers do this than you might think. Start by defining exactly what your model needs to do, and how it will get done.
A good way to do this is to think of your financial models as buildings. The more floors and rooms you have, the more intricate the build becomes, making it harder to just start with a pile of bricks and see how things go.
We need to think about how everything will fit together and balance the requirements and constraints of the users. By sketching out our thoughts, thinking about chokepoints, and drilling down into them, we can plan the overall trajectory of the model, dealing with any issues that may arise before we even write a single formula. Often overlooked, this stage pays huge dividends when it comes to beginning the actual model build.
2. Spend time thinking about how things could go wrong
As humans, we can sometimes assume that others think in the same way we do. And this can extend to model building. If we build our models to be used in a specific way, this can create issues when an end user tries to use it in another.
A common area where this can happen is signage convention. Some people input costs as positive numbers, others as negative, and this can have real consequences. An incorrect minus sign was to blame for a $2.6 billion error at one of Fidelity’s funds. To avoid costly mistakes like this, take some time to think about how you can capture and accommodate alternative ways of thinking. Never embed assumptions within your calculations.
3. Design error checks to catch those errors, as you build the model
Say you have just put the finishing touches to your Revenue calculation section. Do not move on to the next task, stop and fill that section with error checks, while it’s still fresh in your mind. What assumptions have you made about the section that others may not agree with?
You could even build them in advance. Before you populate your balance sheet, insert the checks to ensure that it balances. Waiting to add your error checks later in the process can mean they slip through the net because clients don’t always necessarily see their value in comparison to the actual outputs.
4. Summarise the error checks by sheet and feed these into a single overall model check
This is commonly done in column A, but there are lots of ways to do this. How ever you choose to do it, just ensure that you’re consistent throughout the model and make sure that the summary check catches everything. The total-level check should be visible on each sheet.
Remember, we want it to be easy to identify errors. If we have to hunt just to find them, we’re losing valuable time that we could have used to resolve them.
5. Break your model
This may sound counter-intuitive, but it can provide some extremely useful insight in to how your model works. It can also be quite fun.
Once your model is complete, try and break it by changing inputs, and don’t be afraid to use ridiculous numbers. The possibilities are endless, but here are some ideas for dates and percentages:
Date Inputs:
- Dates that don’t fall within your model timeline.
- Dates which do not coincide with any period end within the model timeline.
Percentage Inputs:
- Negative values
- Values greater than 100%
See what happens when you try this. Have any errors been flagged? Should they have been?
Building an error-proof model is something we should all aspire to. While the value of peer or full third-party reviews cannot be underestimated, there is a lot that an individual modeller can easily do to minimise errors within their models.
If you’re looking to improve your financial modelling skills, you can browse our selection of courses here. Alternatively, if you have a model that you would like reviewed, contact us at info@pps.financial today.
