A so-called Best Practice model should have many qualities, one indisputable feature being robustness, ie, the model should be free from (material?) errors. We may incorporate checks to assist error detection, but other types of checks may be incorporated, too. Indeed, there are three types of tests I would advocate are included in your spreadsheet modelling work.
These are:
- Error checks: These checks provide safeguards where the model contains flawed logic or prima facie errors, eg, the Balance Sheet does not balance, cash in the Cash Flow Statement does not reconcile with the Balance Sheet, or the model contains #DIV/0! errors.
- Sensitivity checks (sometimes referred to as scenario checks): These provide a safeguard when the model’s outputs are being derived from inputs that are not deemed to be part of the base case. They can prevent erroneous decisions being made using the “Best Case”; and
- Alert checks: These are everything else. They flag points of interest to users and/or developers’ issues that may need to be reviewed, eg, revenues are negative or debt covenants have been breached.
Many modellers add checks as an afterthought. However, it is usually too late to create them then. While building a model, a developer knows what situations might break a formula. That is when you should create the check. When the issue is foremost in your mind, create the check there and then. If you don’t, the fundamental risk is that certain tests are forgotten and critical mistakes slip through into your modelling work, leading to poor forecasting, bad planning, and other costly errors.
Let’s work through an example. I am going to create the world’s simplest Balance Sheet (see the screenshot “Simple Balance Sheet Example”).
One thing we all know: Balance Sheets have to balance. Therefore, let me put a check in to ensure it balances (see the screenshot “Simple Balance Sheet With Check”).
The formula,
=C6=C10
is pretty straightforward, but I don’t recommend it. Any financial model I build will have many time periods, and I will need a check for each period. Further, that’s just one check out of many. How do you feel about reading through all of your error checks and making sure they all equal TRUE?
It is true you could use a SUMIF formula to count all the TRUE responses, but surely there is an easier way. First thing is to put the formula in parentheses and multiply the parenthesised expression by one (see the screenshot “Formula in Parentheses”).
The requirement for parentheses (round brackets) is due to the order of operations in a calculation using the BODMAS principle:
That is, calculations in brackets are performed before raising numbers to powers (computing exponentials), before division, and so on. Be careful though: Excel doesn’t quite follow BODMAS, so I tend to use round bracketed formulae quite liberally sometimes.
The problem with this formula is that it will count all the times the Balance Sheet balances. Is it really that informative knowing that your Balance Sheet balances in 9,483 instances? Would it be preferable to learn that you have two errors? Of course it would. This is known as reporting by exception. The formula can be revised (see the screenshot “Simple Balance Sheet — Revised Formula”).
The “<>” symbol means “is not equal to” so,
=(C6<>C10)*1
flags (ie, displays a “1”) when Net Assets does not equal Total Equity. That sounds good, but this is not quite sufficient either. In additions and other calculations within Excel, sometimes Excel produces minor rounding errors simply due to the way the software has been programmed. This error may occur at the eighth or ninth decimal place and is not caused by the modeller’s formula per se; it is an anomaly in the coding of Excel itself. To circumvent this, I use the ROUND function:
ROUND(number, number_of_digits)
This rounds number to number_of_digits decimal places, eg, ROUND(2.928,2) equals 2.93 (see the screenshot “Using the ROUND Function”).
In this illustration, =(ROUND(C6-C10,5)<>0)*1 alerts when C6 (Net Assets) does not equal C10 (Total Equity) to five decimal places.
Just before I continue, I am going to be pedantic here — with good reason. There are three hard-coded values in that last formula: 5, 0, and 1. Some hard code (ie, typed in numbers) is acceptable whilst some is not:
- “5” is essentially a variable. In this example, I am rounding to five decimal places, but this could be argued as an arbitrary choice. It is better to have this as an input value and to make its reference clearer, by providing it with a range name, eg, Rounding_Factor.
- “0” is not a variable. It is a constant. I am testing to see whether the difference between two values (given a Rounding_Factor tolerance) is zero. It is not a value I wish to change, therefore, it is not considered to be a variable (which should never be hard-coded into a formula). Therefore, this value is acceptable in a formula.
- “1” is also a constant. This converts TRUE or FALSE values to 1’s and 0’s, respectively, so that they may be added together to determine the number of errors. For this reason, the use of the number 1 in this formula is also deemed acceptable.
In this example, I will leave hard code in the above formula alone, but perhaps a better practice version of the calculation might be:
=(ROUND(C6-C10,Rounding_Factor)<>0)*1
However, there is still a large issue. What if someone deletes a key reference? (See the screenshot “Example With Deleted Cell Reference”.)
In the example, the reference in cell C5 no longer exists, giving rise to a #REF! error. Unfortunately, this does happen in models. Even if you protect a worksheet (ALT+T+P+P), the end user may still delete the sheet. (Protecting the workbook — ALT+T+P+W — will prevent this, but the workbook can still be deleted.)
Therefore, if someone does manage to accidentally delete a key reference, I would want the error check to alert me accordingly. The problem is, in our example above, while our check may alert us, #REF! is not necessarily the ideal way to display this. I would prefer to be alerted using our 1/0 system already utilised (see the screenshot “Adding a Prima Facie Check”.)
Now the checks are becoming more sophisticated. In cell C12, I have added a check and modified the existing one in cell C13. The first check,
=IF(ISERROR(C6-C10),1,)
provides the value 1 if Net Assets less Total Equity may not be evaluated. This is not the same as the formula:
=IFERROR(C6-C10,1)
Whilst this formula will provide a value of 1 if the subtraction cannot be evaluated, the alternative is not necessarily 0. This formula is not intended to be my balance check, merely a check to ensure that my balance check will work. If I were to use IFERROR rather than IF(ISERROR), the values could be anything. I seek a Boolean Utopia: I want values of 0 and 1 only.
Consider the following variant of the formula in cell C13:
=IF(C12<>0,0,(ROUND(C6-C10,5)<>0)*1)
This is the formula I would probably use in a model, as it is easier for users to understand. This checks to ensure that the error check in cell C12 (the prima facie check) has not been triggered before checking whether Net Assets equals Total Equity. I wrote the formula in a different way above, ie, =IF(C12,,…), to demonstrate two “shortcuts”:
- Putting a cell reference or value in as the first argument in an IF statement is the same as checking whether the value is non-zero. All numerical values other than 0 are treated as if they were TRUE by Excel, whereas a value of precisely 0 is FALSE.
- Omitting a value (just putting two commas) in this instance is the same as assuming a Value_if_TRUE value of zero.
I don’t have to stop there — see the screenshot “Adding a Third Check”.
With this third check, it may be getting easier to see why the order of checks is so important.
The formula,
=IF(AND(C12=0,C13=0),(C6<0)*1,)
checks to see if Net Assets are negative, but only if there are no prima facie errors in the output and the Balance Sheet balances. In fact, this last check is a different type of check. The first two are error checks, ie, these highlight issues that must be resolved before the model may be relied upon. Materiality is not relevant. Until these issues are fixed, the model is not calculating correctly.
The simplistic insolvency check, on the other hand, is an example of an alert check. The model is calculating correctly, and there appear to be no prima facie errors. However, if actuality coincides with the forecast, your business will become insolvent (when you have more owed than you can pay).
This whole idea may be extrapolated further — see the screenshot “Incorporating Checks Across Periods of Time”.
In this illustration, I have demonstrated how checks may be incorporated across periods of time. The checks in cells E12:I14 are similar to those described already, but the checks in column C are new. These are aggregator checks, summarising issues across their respective rows. The formula used in cell C12 could be:
=MIN(SUM(E12:I12),1)
but it is simpler to use:
=MAX(E12:I12)
These checks could be linked to an overall Error Checks worksheet at this stage, or else there may be one additional step to take (see the screenshot “Creating an Overall Check”).
Here, I have created an overall check for this section. The check in cell C15 summarises the checks in column C above it, using a similar construct of the MAX function to create a check that may only ever be 1 or 0.
Adding conditional formatting, number formatting, and wingdings font whilst removing gridlines arguably makes for more elegant aesthetics (see the screenshot “Adding Conditional Formatting”).
It will be a preference call, whether to include checks on a row-by-row basis or rather on an overall section basis (just be consistent), but these checks may then be summarised on an overall error checks worksheet (see the screenshot “Overall Error Checks Worksheet”).
This screenshot is from a sanitised version of a real-life financial model. The first column of checks are merely links from checks built throughout the model — but hyperlinked, so that if the end user clicks on either this column or the second column of checks, they will be taken back to the source. Do remember that the destination cell of a hyperlink should be given a range name, eg, HL_Check01, in case the destination worksheet is renamed.
A Yes/No data validation list separates the two columns of checks, with a “No” making the check OK in all situations. This is fine during model development as construction can generate interim model errors (eg, the Balance Sheet does not balance), but all error checks should be switched on prior to a model being used operationally.
Finally, the summary of error checks at the foot of the screenshot is the overall check for the model. It is this one that is linked to the overall check at the top of each worksheet. This should also be a hyperlink, so not only do you have a hyperlink to the Table of Contents for each worksheet, but you also have immediate access to the Error Checks worksheet.
Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling and Continuing Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Oliver Rowe at Oliver.Rowe@aicpa-cima.com.
LEARNING RESOURCES
Excel — Advanced Financial and Statistical Calculations
Excel has over 500 functions that are built in and ready to use. You just have to figure out which one to use and when. In this webcast, we will focus on financial, statistical, and maths functions.
WEBCAST
Excel — Advanced Spreadsheet Troubleshooting Techniques
In this webcast, we will focus on debugging and troubleshooting complex formulas. You will learn various techniques that will assist you in creating complex compound formulas. You will also learn methods to resolve issues when you receive errors.
WEBCAST
AICPA & CIMA MEMBER RESOURCES
Articles
“How Excel Builds on Basic Principles to Assist Forecasting”, FM magazine, 28 August 2024
“6 Ways to Create Scalable Financial Models in Excel”, FM magazine, 21 June 2024
“ChatGPT: Use Cases and Limits to Its Reliability”, FM magazine, 2 May 2024