ICAEW.com works better with JavaScript enabled.
Exclusive

Getting your financial models right

Article

Published: 15 May 2019 Updated: 09 Nov 2022 Update History

Exclusive content
Access to our exclusive resources is for specific groups of students and members.

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.

Use of spreadsheet financial models has become ubiquitous in business, which would seem reason enough for a responsible organisation to create a best-practice document on them. But perhaps the need for guidance wasn’t pressing. While financial models are widely used to inform decision-making, there is evidence to suggest that many models have been created without attention, care or planning, likely put together by someone who is self-taught. Over time those models have been subject to a lack of review.

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

The first objective under definition and purpose is to understand the scope of the financial model. This means ensuring it is fit for purpose for the intended users. Determining the goals of the model is an objective concerned with establishing a clear purpose for the model with defined output. This should be designed with longevity in mind, with the ability to service the model built in for future users. The design should be carefully planned before any construction takes place.

Layout and structure

The flow of logic is important to users, making it possible for them to find all relevant components easily. This means segregating inputs, calculations and outputs wherever possible, using dashboards to summarise, maintaining a consistent structure for worksheets and setting the model up to read like a book. Assessing and planning the layout in advance is imperative to success. Simplicity is the watchword for navigation and locating inputs. Including a directory, contents and diagram explaining the logic is recommended.

Being consistent in both the use of formulas and the placement of timelines is beneficial in financial models.

Business & Management Magazine, May 2019

Interface and transparency

For transparency’s sake, any user guidance documents created should be embedded in a model. Contextual notes can also be added throughout the model if required. Explanatory documentation should not be stored separately, or else there is a danger of precious information going missing. Storing file duplicates in multiple locations is also frowned upon. It is important not to fill models with dummy data that could be mistaken for real data, or to hide worksheets and filled cells that use white text.

Consistency and clarity

Being consistent in both the use of formulas and the placement of timelines is beneficial in financial models. The data itself is often complex enough without having to fathom a series of codes that have been used to reference its contents. Cell anchoring can be used to help. Freezing panes is a good idea for fixing timelines. Where multiple timelines are necessary, they ought to be labelled appropriately, being consistent in both order and level of detail.

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

The Code acknowledges that errors are both natural and unavoidable, which means that introducing a review procedure to test any model is a preferable step to take. Checks in the construction of each section of a financial model can be made to flag up problems with inputs left blank by mistake, and to spot where calculations are mean to be equal. It is recommended to include tolerance for small acceptable variations, such as Excel rounding errors. A master check, perhaps using a check summary section, can help to identify any way that the model might be in breach. Restrictions can also be applied to reduce the possibility of incorrect or unreasonable inputs to be selected. Alert messages can be set to warn of restrictions.

Calculation techniques

The volume of formulas and calculations present in a financial model are susceptible to error if appropriate modes of checking have not been put in place. Large files that may run slow ought to have their formulas optimised for file size, and be easy to read and comprehend. Breaking formulas down into multiple stages helps, as does carrying out timing and logic calculations separately from their master data (using specific flags).

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

ICAEW stresses that the Code is not meant to be used for tickbox compliance. It is hoped people will use it to both inform conversations about financial modelling and design, or by anyone planning to create their own models. It is also considered useful for anyone using spreadsheet-based software that isn’t Excel, and models in other software altogether.

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

Download pdf article

Further reading

The ICAEW Library & Information Service provides full text access to leading business, finance and management journals and a selection of key eBooks from leading publishers. Further reading on financial modelling is available through the resources below.
Terms of use

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 eBooks
Can'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.

Changelog Anchor
  • 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.
Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250