Having reviewed a number of financial models, it immediately becomes clear when the model has not been prepared in accordance with best practices.
While there is no clear set of binding principles to apply when building a financial model, by keeping the end user in mind model builders are better positioned to create an easy to use, accurate and future-proof model.
Below are some common pitfalls which should be avoided when building a financial model in order to improve the quality of the financial model and reduce the likelihood of errors.
1. Hard-coded numbers
Hard-coded numbers should never be used in a formula, as this reduces the flexibility of the model which in turn may lead to errors. For items which can easily be hard-coded in a formula, such as days in the year or the tax rate, a strong financial model will summarise these items in a central location and refer to these cells in formulas throughout the model.
2. Too many tabs
When using a model prepared by someone else, it takes time to understand the structure of the model. An excessive amount of tabs only adds to this complexity and is often not required. Consider consolidating calculations onto one tab where possible to avoid complications arising from switching between tabs more often than required.
3. Inconsistency
Inconsistency in formatting and formulas throughout a financial model can become very troublesome, as this can increase the amount of time it takes to use the model and the model is more prone to errors. Each tab should follow the same format and structure for items such as the title and column headings. Within a tab, formulas in each row should always be the same.
4. Complex formulas
A financial model should strike a balance between expanding calculations over multiple rows and limiting calculations to as few formulas as possible. While it is not necessary to step out every component of a calculation, it is important to keep formulas simple so users can understand what is being calculated. As a rule of “thumb”, a formula should not be longer than the length of your thumb. If it is, consider separating the calculation over a few lines to reduce complexity and the likelihood of errors.
5. Inputs spread throughout model
When building a financial model, it is easy to identify what inputs are required in calculations and understand where these are located to update as required, however it is often a different case for the user. A strong financial model allows users to review and update inputs, therefore it must be clear where inputs are located. This is best achieved through organising all inputs into one or two tabs, and to format input cells so the user can clearly identify inputs used in the model.
6. Repeating calculations
Where possible, calculations should only be done once throughout the model. Items such as dates or CPI increases are particularly easy to replicate calculations for. It is best to centralise these calculations in one tab, and directly link to these calculations when required in other tabs, so any required changes are only done once.
HLB Mann Judd have extensive experience in building, improving and reviewing financial models. Please reach out if you have any questions or would like further information.