Implementing ‘Intelligent’ Model Checks
Download Excel file
The most common and arguably the most important check in a financial model is the balance sheet check (Net Assets = Shareholder Equity), but you would be surprised at how many models I’ve seen with this missing or with a plug number inserted so that the check is always “OK”. A project finance model will contain a checks message if the loan covenants are breached (DSCR, LLCR, ICR, Gearing). An M&A model may have an accretion/dilution check, a minimum cash balance check as well as loan covenant checks.
The accepted best practice is to have a ‘Checks’ sheet in a model which collates all the individual checks throughout the model into one sheet. All these checks are then summed and a master check is created usually stating “All Checks OK” or “Errors”. This cell is then referenced throughout the model so that the status of the checks can be seen on any sheet.
Where appropriate we use ‘intelligent checks’. Rather than just stating that there are errors, the checks message actually tells you what the errors are and places them in order of importance. For a small amount of upfront effort it is possible to see exactly what errors or warnings there are in a model from any worksheet. Implementing this methodology upfront will save time and effort during your models lifecycle.
The steps below detail how to incorporate ‘intelligent checks’ into a model.
Step 1 – Call up the checks throughout the model into ‘Checks’ Sheet
Include an input named Checks_Tolerance with a value of 0.1.
Call up the checks in your model into the checks sheet. When a check is called up wrap it in the formula above which allows the tolerance to be applied. (Excel rounds to 15dp so even though mathematically you should have zero, Excel may not calculate it to be so).
An “error” should be a 1 and a 0 if the check is “ok”. To get the “OK” & ” Err” format use custom format :
[Red]”Err”;[Red]”Err”;[Blue]”OK”
Step 2 – Sum each check across the timeline and create a “Master Check”
The “Master_Check” in E23 is the sum all the cells in column E below it.
Step 3 – Create an errors messages table to process the checks and create the messages
- Input the name of the checks in order of importance. Use data validation & list for a drop-down selection.
- Test whether the check is either “OK” or “Err”
- Create the error message you want to show. Here I’ve just referenced the name of the check but this can be replaced with a different message if required. The &” ” in the formula is there to space the error messages should there be more than one.
- Create the final checks message by either selecting “Checks OK” or listing the errors that have occurred.
Step 4 – Publish the checks message on every worksheet in the model
Use conditional formatting on the message cell to display the text in red if there are any errors. The formatting to use is below
Step 5 – Notify the user of any errors when they open the model
Insert the following code into the ‘ThisWorkbook’ module of your model, this notifies the user as to the status of the checks when they open the model.
