Error checks explained using the CRaFT methodology.
When to use
Many modellers add checks as an afterthought. Basically, it is too late to create them then. While building a model, a developer knows what situation might break a formula. That is when a check should be created. When the issue is foremost in your mind, create the check there and then.
- Error checks – the model contains flawed logic or prima facie errors, e.g. Balance Sheet does not balance, cash in cashflow statement does not reconcile with the balance sheet, or the model contains #DIV/0! errors etc;
- Balance Sheets have to balance so this example will put an error check in to ensure it balances
- The formula =C6=C10 is pretty straightforward, but it is not recommended. Most financial models will have many time periods and therefore will need a check for each period. Further, that’s just one check out of many. It would be difficult reading through all of the error checks and making sure they all equal TRUE?
- First thing is to put the formula in brackets and multiply the bracketed expression by 1:
- The Balance Check in this illustration, =(ROUND(C6-C10,5)<>0)*1 alerts when C6 (Net Assets) does not equal C10 (Total Equity) to five decimal places.
- In example, the reference in cell C5 no longer exists giving rise to an #REF! error. Unfortunately, this does happen in models.
- Therefore, if someone accidentally deletes a key reference, an error check could alert the modeler. The problem is, in the example above, while the check may show the alert as, #REF! is not necessarily the ideal way to display this. A better process is using the 1 / 0 system
- The Prima Facie check=IF(ISERROR(E6-E11),1,), provides the value 1 if Net Assets less Total Equity may not be evaluated.
- The Balance Check could now be updated to be =IF(E14<>0,0,(ROUND(E6-E11,5)<>0)*1)
Lesson notes are only available for subscribers.