The key to effortless financial modelling is to minimise the risk of errors so that your business can operate based on the correct information.
Having reviewed numerous models over the years, we see the same issues cropping up time after time. To help you avoid these pitfalls and create an effective financial model, we’ve compiled them into a summary of the most common errors you’ll come across during your build, including some tips to resolve them.
Here are the first four to look out for (with more available in Part Two):
1. Incorrect references.
Regular Excel references are based on the location of values. While it seems easy to point at the right cell, errors that refer to the wrong cell are surprisingly common.
Here are the main types to look out for:
Referring to the wrong timeline.
We discovered that a number of errors occur by referring to the right item, but on the wrong timeline. To prevent this, keep timelines clearly separated (e.g. in different columns and by colour coding a marker on each row to match its timeline, which appears within freeze panes).
If you’re keen to find out more information on timelines and how to extend them, why not check out our video here.
Right label, wrong section.
In models with multiple sections that are similar, it’s easy to point to the correctly labelled cell in the wrong section.
Consider concatenating labels in headers, grouping rows to make navigation easier, or colour-coding to distinguish sections more clearly.
Referring to a blank cell or blank row.
Referencing a blank row can be caused by deleting something, leaving references to the blank cell.
Here’s an Excel Hero tip – put a deliberate error in the cell (e.g. something divided by zero) and see if the error propagates through to other parts of the model.
Incorrect anchoring applied.
We often find errors that are a result of incorrect anchoring – when a reference should be absolute but isn’t.
To combat this, check the copied formulae using dependency arrows found in the Formulas tab – that way you can quickly see if the copy and original refer to the correct cells.
2. Inaccurate/inconsistent labelling.
One of the best ways to make a model sail through an audit quickly is to ensure the labelling is precise, concise and unambiguous. Here’s how:
Precise labelling.
Unclear units can make it very difficult to tell if a formula is correct. Include a units label on every row so there is no uncertainty.
Consistent labelling.
When reviewing models, we rely heavily on labelling. Be consistent with labelling and include a glossary of terms to enable the model auditor to understand calculations more easily.
Unambiguous labelling.
Make sure your labelling can’t be misinterpreted. If the label isn’t clear, users may input the wrong value. Where space is prohibitive, consider adding further explanation using cell comments on the Review tab.
3. CAGR calculated over the wrong number of years.
This is a common error we see across models. CAGR provides a metric to represent what the repeated annual growth rate would have to achieve as a resultant value after a certain number (n) of years.
When this is calculated, it appears that people often count the number of cells including the start and end date.
(EndValue/StartValue)^(1/n)-1
Whilst the impact of this error is reduced for long time periods, it becomes significant when only considering a few years.
To check, do a side calculation using the CAGR rate you have calculated as an annual increase (compounded) – the CAGR calc result should be equal to your final year value. In this example, the calculated CAGR of 5% in G14 using n=6 (years) gives the same answer as the compounded growth from C11 to I11).
4. Tests using “text” values.
This formula appeared in a model we reviewed recently:
=IF($G55=”Peak”, T44,T35)
Using text within a formula relies on the text to be an exact match, not mistyped, or more likely, followed by a trailing space – ie. “Peak “.
We recommend you use data validation to limit the choices. Using the ‘list’ option under Data Validation on the Home tab will enable you to force an input for users to choose from.
And there you have it, the first in our 2-part series on common issues found in models. We hope this blog gives you an insight into what problems to avoid, how to prevent them, and some easy fixes. Head over to Part Two to find out more.
If you’d like more information on how to create an effective financial model, 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.
