Getting your financial models right

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.

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.

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.

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.

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.

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.

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.

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.

• 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.

• Getting your financial models right
  Business & Management Magazine, Issue 274, May 2019

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.