If you have an Excel based financial model, it is likely being used to support one or more major decisions. It is common for lenders and investors to require a model to be audited or reviewed by a specialist model auditor before financial close. Increasingly, corporate governance procedures dictate that all but the most trivial models should be tested for internal risk management. There are a number of different approaches to auditing and reviewing models, and several components of testing, which provide assurance on different aspects of a model and the decisions it supports. Some or all of these may be required, depending on your circumstances.
Below we outline what we mean by a financial model and briefly explain why model testing is so important.
Whilst precise details vary from one project to another, the approach broadly adopted by Numeritas is summarised in the diagram below
Our use of the term “financial modelling” describes an Excel workbook that represents a business or similar organisation, in order to support decisions or to summarise financial reports. There is usually a time dimension, which is forecast into the future for anything from a few months to several years, depending on the nature of the decisions to be taken. Typical outputs will be a dashboard summarising key indicators, financial statements, graphs and other useful metrics. A model may be purely for forecasting, or it may contain historical figures, or both.
Financial models are effectively a very specialised calculator – you enter some ‘inputs’ - assumptions about what you think might happen, and the model processes these to give a set of outputs (these may be forecast financial statements, a price, a valuation). The way the inputs are processed to generate the outputs is defined by the logic contained within the model. You can think of most financial models as a business simulator – A model aims to represent how the operations and finances of an organisation behave, so that we can feed in ‘scenarios’ (ie sets of assumptions) and see what the financial outcomes would be. Whilst most of the models we deal with are to produce a financial forecast, we deal with other types of spreadsheets as well – this is described more under "data analytics".
Microsoft Excel is the tool of choice for millions of finance professionals the world over. This is due to its flexibility and ease of use. There is a downside that accompanies this however; Excel imposes very few constraints on how it is used. It has been amply demonstrated that humans make mistakes in most endeavours and that the incidence of errors increases with complexity. Since financial models are complex and are built by humans, the risk of error in spreadsheet models cannot be ignored.
Academic studies have repeatedly demonstrated that thorough, detailed testing of spreadsheets is the most reliable way to significantly reduce errors. This means checking each formula in the spreadsheet. Financial modellers have developed approaches that make this less onerous and that are designed to eliminate some types of error. This includes adopting sign conventions, accurately labelling each section of calculation, including the units appearing in each cell and keeping the logic consistent across the timeline dimension of a model.
There are a number of techniques that can be built into the logic of models that provide some checks of the model’s integrity – these commonly include accounting checks – for example checking that the balance sheet balances, and checking the reconciliation of cash and retained earnings in each of the financial statements. We offer training courses to teach you and your team the techniques for minimising errors in modelling.
Numeritas will test the logic of your model by identifying every ‘unique formula’ in the model. A unique formula is one that is not a copy of the formula immediately above it or to its left. Not all model audits are performed this way – see our whitepaper 10 questions to ask your model auditor to find out more.
Our model audit process checks the logic in each unique formulae. We note any actual errors, potential errors, queries and good practice infringements. These are reported back to the model builder for rectification or explanation.
The model builder addresses the issues, either by amending the model or explaining their approach. We will then compare a revised version of the model to the original version and check whether all the issues have been addressed. There may be a third and further iterations if necessary to ensure all issues have been addressed.
Once all the issues have been resolved, we issue our report letter, which is often a condition precedent for financial close.
There are a number of good practice principles that are widely accepted in financial modelling circles and this forms the basis of our testing approach. To find out more about best practice modelling, watch our video.
If you have a model audit you would like to discuss, please pick a time for a call using this link:
If you are comparing model audit providers, don’t forget to ask them these 10 questions:
Watch a video about best practice modelling