26/09/2012 - 10:32

Common flaws in financial modelling

26/09/2012 - 10:32


Save articles for future reference.

IT’S safe to assume that, when you or your team set out to create a financial model, you’re trying to develop the most robust and reliable model that you can – one that allows you to make decisions with confidence.  

Yet despite this good intention, financial models are often poorly designed, causing all manner of costly mistakes. So what are the most common mistakes and how do you avoid them? 

You’ve made it too simple. Workings need to be included (and communicated clearly with a logical structure) so that it’s always possible to trace the numbers back to their original drivers. The problem of overly complex models is not usually just about the quantity of data; it’s about poor organisation and communication of the key information. So don’t be afraid to include the supporting detail, just index it in a separate category and make it clear what input data relates to what output result. 

You’ve made it too complex. A good model should be easily interpreted and managed by someone else. Long formulas can be extremely difficult and time consuming to follow if you haven’t written them yourself. No formula should be longer than about half the length of the formula bar.  And try not to use macros to perform lengthy calculations and manipulations as this reduces transparency. 

You’re not making your model flexible enough. You should be able to make changes to your model to see the overall impact on bottom line figures. For example, you may want to conduct sensitivity analysis to see the effect of a 10 per cent drop in the price of product X. The formula should easily adjust the other figures. If you have to make lots of manual adjustments then your model is poorly designed. 

You’ve got information left in your head. The ideal financial model stands strong on its own i.e. it does not need explanation or support from the creator. 

You’re hard coding numbers into formulas. This classic error is one of the biggest reasons financial models become unreliable and costly to fix. This means not using a value in a formula, unless the value is so common as to always remain constant e.g. 24 hours. Instead, link as much as possible so that when the inputs change, the outputs also change. By linking in this way, you make your model auditable, traceable and easy to validate.  What if you have no choice but to hardcode a formula? Then insert a comment against that cell explaining where that number came from. 

You have not included an executive summary. Best practice financial modelling is not just about ensuring robustness and reliability; it’s also about making the document as user-friendly as possible. If you can make the model less ambiguous and time-consuming to digest you’re allowing users to operate more efficiently. 

You’re being inconsistent with formatting. For both presentation and usability it helps a lot to have nicely presented and consistent formats throughout the model. Make sure all common column headings are identical on each sheet. Start from the same column, use the same bordering, fonts, shading, labels, descriptions and style – wherever possible. 

You’re not documenting your assumptions. This helps with validation and avoids misinterpretation. How many times have you worked on a spreadsheet model created by another person and wondered how or why they did something? The more detail the better. And it’s a good habit to get into documenting source data and unique calculations as you go, in order to make this process easier. 

You’re not getting someone to check your model. When you’re creating even a mildly complex financial model you get too close to identify the flaws. Once your brain has told you a section of the model or specific formula is correct, it becomes very hard to then question it later. The simple way to solve this is for a colleague or financial model auditor to check your work.

The quicker you accept that a majority of financial models contain errors – in part because they were poorly designed from the outset – the quicker you will move your organisation toward best practice in financial modelling.


Jeff Robson is CEO and principal business analyst at Perth company Access Analytic.

Contact Jeff on 08 6210 8500 | info@accessanalytic.com.au |www.accessanalytic.com.au/


Subscription Options