Jump to navigation Jump to search
This page is about automated checks that can be built into a financial model. It is not about model review or audit, although reviewers and auditors are likely to consider any automated checks built into a model [we should create pages on model review and model audit].
It is good practice for financial models to include automated checks, and to present a summary of the check results in a prominent location on most worksheets.
Checks are often split into different categories, with a common distinction being between error checks and alerts.
- If an error check flags it means that something is wrong with the model and users should not trust the results. The quintessential error check is to test whether the balance sheet balances in all periods. If the balance sheet does not balance then most likely something does not flow through to a required location or there is a double count, and either way results are not valid.
- Alerts draw users attention to things that they would probably want to know. A classic alert tests whether the forecast cash balance goes negative - users would often want to know if a business is forecast to run out of money.
There is no clear consensus on the way in which checks should be structured in a model.
- One approach is for the detailed check calculation to be spread throughout the model, close to the items being checked. For example, the balance sheet check sits at the bottom of the balance sheet, and a check that tests whether an input is valid sits alongside the input. The checks all then flow through to a check summary sheet or section. Proponents of this approach note that seeing the check beside the relevant item provides useful context, it facilitates a simple check summary area, and minimal effort is required to create each new check so it promotes extensive error checking. Partly rebutting arguments for different approaches, proponents also note that that formal audit effort and cost can be reduced by excluding checks implemented this way, which may be identified by labelling or cell style.
- Another approach is for all check calculations to be performed on a check sheet or section. Proponents of this approach note that it keeps other areas of the model clean, and it facilitates reduced audit effort and cost by allowing a single sheet or section to be simply excluded. Partly rebutting arguments for different approaches, proponents also note that checks often take consistent forms so check sheets made this way can be highly structured.
Common checks including [expand on the list and specify each one as far as an example formula].
- Does the balance sheet balance?
- More general, if two items should match - do they?
- Special case of the above: do lists match?
- Are there the right number of items?
- Are inputs valid?
- Are numbers of the expected sign (positive/negative)?
- Do portions add to 100%?
- Does debt get fully repaid?
- Does depreciation and amortization complete?
- Is there trapped cash?
- Is a value within expected bounds?
- Are any temporary settings still active (e.g. and override or developer mode)?
- Is the change log up-to-date?