How to Build in Error Checks in Your Excel Financial Model

Even if you’ve only just started modeling, you’re probably well aware how easy it is to make a mistake in a financial model! There are three ways to prevent errors in a financial model:

• Avoid making the mistakes in the first place. In this book, I describe several techniques that you can employ to avoid making mistakes in the first place, such as being consistent with your formulas.
• Check the model for errors. Despite your best efforts, errors inevitably may slip through, so check, double-check, and have someone else check your model after it’s complete.
• Include error checks. As you’re building the model, include error checks that prevent inadvertent errors from slipping into the model due to incorrect entries or user error.

The information here focuses on the first two points: techniques for model building to reduce error, as well as ways to check the model for errors.

Error checks are a critical part of a well-built financial model so that the user or modeler can see at a glance if the formulas are calculating correctly. For example, when creating management reports, check that the sum of each individual department’s report adds to the company-wide total. This can be done by inserting a simple IF function, among other methods.

In the example shown here, a capital budget has been built with estimated spend dates in column E. In the capital spend schedule shown in columns F through Q, the spend gets spread out over the full year. The modeler knows that the total capital spend amount of $124,700 shown in cell D17 should be the same as the total capital schedule amount shown in cell R17, and if the two amounts do not equal each other, then the model is not calculating properly. So the error-checking cell E1 contains the very simple formula =R17-D17.

Below, you can see that a user has entered an incorrect value in cell E4. Feb-20 is not a valid entry because the capital spend schedule only allows for dates during 2019. This means that the financial model below is incorrect — the user has entered $124,700 worth of capital expenditure into the model, but only $115,700 has been allocated across the year. The number showing in cell E1 (9,000) alerts the user to the fact that there is a problem. Download File 0401.xlsx and select the tabs labeled 4-9 and 4-10 to try triggering this error check for yourself.

This error check is very simple and quite discreet. To make it more obvious, you may prefer to include the description “error check” next to the error checking cell in cell D1, which would make it more obvious to the user what has happened when the error check is triggered.

The error check shown above is a preferred method of error checking, because it’s so simple and quick to build. Because it returns a value in the case of an error, it may be a little too discreet for your tastes — it doesn’t necessarily alert the user immediately that an error had been made. However, it’s certainly quick and easy to follow and, for this reason, a fairly common error check favored by many modelers.

If you use this kind of error check, format it using the Comma style (found on the Home tab in the Numbers group) and remove the decimal place and format it with a red font. This way, the zero won’t show if there is no error, and a red number will show if there is an error.

Alternatively, you may prefer the other error checking formula such as =D17=R17, which will return the value TRUE if they are the same or FALSE if they aren’t. However, this method is also subject to a false error.

Allowing tolerance for error

=IF(D17<>R17,”error”,0) is a superior error check, but every now and then it can return a false error result, even though the values are the same. This “bug” in an error check is caused by the fact that Excel carries calculations to 14 decimal places. After that, it truncates the value and can cause a minute discrepancy, which will report an error when it’s only 0.00000000000001 off.

To avoid this potential issue, you could use an absolute value formula, which would allow a tolerance for error. =IF(ABS(D17-R17)>1,”error”,0) will allow the values to be off by $1 before it reports an error. If you use the ABS function in Excel, this will take the absolute value of the result, so it doesn’t matter if it’s a positive or negative number.

There are many variations of this formula. Some modelers prefer to show the word OK if the numbers are right, and Check if they aren’t.

Applying conditional formatting to an error check

To make the error check even more prominent to the user, consider using conditional formatting to add a rule that makes the entire cell turn red if the error check has been triggered. On the Home Tab of the Ribbon, in the Styles group, click the Conditional Formatting button. Then hover the mouse over Highlight Cells Rule and select Equal To.

When the Equal To dialog box appears, type the word error into the Format Cells that are EQUAL TO box and click OK. By default, it will turn the cell to Light Red Fill with Dark Red Text, but you can change this in the drop-down box.

Conditional formatting is popular in error checks because it makes the error check stand out more when it has been triggered. And, with any luck, the user will realize that there is an error in the financial model. Conditional formatting is not limited to error checks — it can be useful for other models where you want to draw the user’s attention to abnormalities or highlights in the calculations.