Financial models are vital tools in modern business, but are highly susceptible to being ruined by their creators' bad habits. ICAEW decided to turn this situation around by developing a new code of practice.
Additionally, those who buy in their models may have ended up with a product not suited to their needs, having failed to articulate them clearly at the outset to a developer or provider. Some modelling methodologies, ICAEW says, can even be “overly prescriptive or organisation-specific”.
In a bid to override these problems, ICAEW’s IT Faculty, working with the Excel Community, have written a Financial Modelling Code (the Code). Principles-based – indeed it references ICAEW’s own Twenty principles for good spreadsheet practice throughout – it grew from a review of seven methodologies, with input from more than 12 organisations.
The Excel Community looked at the methodologies used by those organisations and distilled the beliefs they had in common. The resulting code is what ICAEW considers to be “the universal tenets of best practice in the field”.
The Code is divided into seven sections: defining the model and its purpose; layout and structure; user interface and transparency; consistency; clarity; error reduction and calculation techniques. Within those categories, it gives a series of recommendations, as well as tips on what not to do when creating and using financial models. The overriding aims are to present complex information in the clearest way possible, and to avoid errors creeping in over time. Here is a brief overview of the points covered in the Code.
Model definition and purpose
Layout and structure
Being consistent in both the use of formulas and the placement of timelines is beneficial in financial models.
Interface and transparency
Consistency and clarity
Another watchword for creating effective financial models is clarity. Clear formatting, meaningful labels, use of standard units of measurement and range names are among the areas requiring order and sense to be applied. The Code recommends not showing too many decimal places, in case users are distracted from more important information. It adds: “Formatting for formatting’s sake is unnecessary. Formats do not automatically communicate their meanings to the user.” The Code also advocates the sparing use of Visual Basic for Applications (VBA) – the programming language of Excel and other Office programs – and other similar code on the grounds that it requires a higher technical knowledge on the part of its user. VBA ought to be well documented to counter any potential for misunderstanding.
Error reduction
Calculation techniques
Creators can help users by building in traceable references that help with comprehension of the formula, for example by referencing nearby cells so that they may be seen on screen simultaneously, and creating single-step references, or ‘call ups’. Hardcoding, where a formula has fixed values embedded (such as a rate of tax), is generally to be avoided.
Situations where this may be appropriate should be judged on a case-by-case basis, and only then when the use case is clear to the basic user of the model. Circular references, too, are considered avoidable, indicating that a formula is likely to be incorrect (referencing back to itself as an input). Unnecessary rounding is also flagged as a potential route to compromised accuracy in the model.
Judgement calls
The Code acknowledges that there is “no one way of building a model” to be right for every situation, and that readers should be aware that differences may be preferable in specific contexts. It is recommended to be read alongside the original Twenty principles, which are not to be confused with the Corporate Finance Faculty’s best-practice guideline on financial modelling.
Related resources
- Financial Modelling Code - pdf
- Webinar: Financial Modelling Code
ICAEW IT Faculty technical manager David Lyford-Smith and RSM corporate finance partner Alistair Hynd ran this webinar introducing the Code.
Download pdf article
- Getting your financial models right
Business & Management Magazine, Issue 274, May 2019
Further reading
You are permitted to access articles subject to the terms of use set by our suppliers and any restrictions imposed by individual publishers. Please see individual supplier pages for full terms of use.
Terms of use: You are permitted to access, download, copy, or print out content from eBooks for your own research or study only, subject to the terms of use set by our suppliers and any restrictions imposed by individual publishers. Please see individual supplier pages for full terms of use.
More support on business
Read our articles, eBooks, reports and guides on Financial management
Financial management hubFinancial management eBooksCan't find what you're looking for?
The ICAEW Library can give you the right information from trustworthy, professional sources that aren't freely available online. Contact us for expert help with your enquiries and research.
-
Update History
- 15 May 2019 (12: 00 AM BST)
- First published
- 09 Nov 2022 (12: 00 AM GMT)
- Page updated with Further reading section, adding related resources on financial modelling. These new articles and eBooks provide fresh insights, case studies and perspectives on this topic. Please note that the original article from 2019 has not undergone any review or updates.