Excel: Using Checks

With increasing complexity, financial models become more prone to errors. While you can never be sure if a model is completely correct (let alone accurate), you can avoid mistakes with a few easy best practices.

What to check

Some things are obvious to check and applicable to almost every financial model - for example, comparing the sum of assets in a balance sheet to the sum of equity and liabilities. Other reasonable checks may come up during the financial model after you found the first errors. Sometimes, your models will require checks that are highly specific to the industry, region, project etc. Here are a few examples of common checks you should include in your model:

Check sums

  • Do the total assets in your balance sheet amount to the same as the total equity and liabilities?
  • Is the result of your cash flow statement equal to the movement of cash in your balance sheet?
  • Do percentages sum up to 100%?

Logical checks

  • Do user inputs make sense, e.g. does a project's construction phase finish before its operating phase?
  • Is the book value of tangible assets at the end of a project's construction phase the maximum over the whole project period? (This may not apply to all models, but may give a hint to errors for many.)
  • Are dividends only paid out when cash is positive?

Defining a tolerance level

When you have some experience with Excel, you have probably come across a lot of rounding errors when calculating decimal numbers - sometimes in the 8th, 9th, 10th digit after the decimal point. The reason is that computers calculate using binary representations, and sometimes this can lead to inaccuracies with decimal numbers. Without going into too much detail, there will be cases in which a sum should be 0, thus letting your check result in "OK" - but instead, due to Excel inaccuracies, the sum is something like 0.00001034, and you receive an "ERROR". In order to avoid this, it is highly recommended to define a tolerance level. This could be done on one of your general input sheets - you should then assign the range name Tolerance to that input cell. Having done that, you can now use that tolerance level in your checks.

Formulas

Checking for one specific period can be done like this:

=IF(ABS(B1-B2)<Tolerance;0;1)

With this formula, the error sum for the position will be 0 if the sum of all difference is below your tolerance level:

=IF(ABS(SUM(B3:M3))<Tolerance;0;1)

Whenever there is an error, your formulas will now show a 1. In order to increase usability of your financial model, you should assign a new number format, for example:

[Red]"ERROR";[Red]"ERROR";[Blue]"OK"

Now, errors will be shown as a flaming-red ERROR, whereas a blue OK indicates that your checks were okay. By using 1 and 0 instead of text strings, you can add up your check rows:

Excel: Summing Up Checks
(You might also want to take a look at the article Working with Flags.)

The "Master Check Sheet"

The principle of summing up checks does not only apply to single sheets. You can sum up your per-sheet sums on one "Master Check Sheet" and ultimately form a sum that tells you if all of your model checks result in "OK" or whether there are one or more errors.

Excel: Master Check Sheet

As you can see in the example, now the number of errors is shown, as well. This can be done by tweaking the number format for the cells a little bit:

[Red]#" ERROR(S)";[Red]#" ERROR(S)";[Blue]"OK"

By including the # sign, you tell Excel to show the number that is stored in the cell, followed by ERROR(S).

 

Showing the check results on every sheet

Having a master check is extremely helpful. However, it's a bit impracticable to always have to look on that particular sheet to see if something goes wrong, especially when your model grows into dozens or even hundreds of sheets. But there is an easy solution: In your check sheet, have one cell in the header that says "All checks OK" if your master check is OK, and "ERROR" if it's not:

=IF(MasterCheck=0;"All checks OK.";"There are " & MasterCheck & " errors!")

In this case, we assigned the range name MasterCheck to cell B1 in the Master Check Sheet that was shown above. We then link to that cell from every other sheet, preferably in the same position. Now, you can see the result of your master check from everywhere in your model instantly:

Excel: Referencing the Master Check

Distinguish between error checks and model outputs

When deciding which checks to include, you need to distinguish between actual error checks and model outputs. For example, when you model a project's business plan that includes taking up a bank loan, it is very common to show the interest cover (or sometimes called debt service coverage ratio, DSCR) for each period. For user convenience, your calculation should highlight critical values for the DSCR, i.e. when it comes close to or even falls below 1 - that would mean the project EBIT would not suffice to serve the bank interest. However, a check like this should not be shown as an error because it does not mean the model as such is incorrect: It only means that under the given assumptions, the project would not be profitable enough. Make sure to treat this and similar checks as model outputs, not as error checks.

2 Replies to “Excel: Using Checks”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.