Subscriber only lesson.
Sign up to this course to view this lesson.
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.