The Checks in This Post

So you've built the model and you're about to hand it over to end users and / or third party model auditors. What could possibly go wrong? This article considers some simple checks you can employ to save face. By Liam Bastick, Director with SumProduct Pty Ltd.

Query

Are there any "standard checks" I can perform before I hand over a model I have been building to third parties? I want the model to be trusted and I want to protect my reputation too!

Advice

We all live in a time poor world where everything has to be done yesterday and has to be "right first time". It doesn't seem to occur to many that these two concepts might be at odds with each other.

Nevertheless, in this article I look at some standard "checks" you can perform as the model builder which will help eradicate some of the more basic issues that frequently blight Excel models.

Let me just explain some concepts first.

Four Key Concepts

In order to position my suggestions, I would just like to clarify the following four concepts:

1.  Best Practice Modelling – I have discussed the concept of “Best Practice” previously (please see >Model Construction Tips and also >Four Key Concepts for further details). Essentially, fewer mistakes will occur in models where the logic is transparent, calculations are consistent, assumptions are flexible (within reason) and the model is robust (e.g. no prima facie errors visible such as #DIV/0!, #N/A etc. and error checks incorporated into the model where appropriate). This occurs when a model is built in this fashion and therefore should be a congruent part of the model development process and not part of the checking process upon completion. Therefore, it may be too late for the query above, but always build models with these considerations borne in mind.

2.  Quality Control – The risk of errors in a model may also be mitigated by incorporating quality controls as you build the model. Example of quality control include:

• interim managerial review;
• continuing discussions with end users;
• inclusion of data books and / or documentation;
• version control; and / or
• maintaining up-to-date change logs.

It’s a great theory and unfortunately, over 20 years of being a modelling consultant has taught me that many of these simple procedures are adopted just after the snowballs have been collected from Hell.

Again, these are procedures that should be adopted throughout the model development process and are not really appropriate for checking just prior to model handover.

3.  Model Reviews / Audits – I specifically use these phrases to refer to handing models over to third party specialists with their raft of technical software, experience and expertise. It’s the sort of work my company and others perform from an independent, objective standpoint. Model builders cannot perform this work, as they are often swayed by their own – sometimes flawed – logic inherent in the model.

I keep suggesting to my clients that they should allow interim reviews in order to prevent large errors from occurring, but budget, resourcing and time constraints frequently prevail whereby all of this work is performed upon completion of the first draft of the model. This isn’t a sales pitch, but I cannot recommend strongly enough about getting third party professionals involved as early as possible in the model development process.

4.  Self-Review – This is what I am talking about here. What checks can be implemented to reduce the number of errors in a first draft model before handing it over to others to inspect / use?

Self-Review Checks: The Quick & Dirty Dozen

With the above in mind, I provide an initial list of checks model builders can perform on their own models. I assume modellers do not have access to specialist auditing software here. This list is not intended to be exhaustive (I can't give away all of my secrets!), but it's a good starting point:

• Use Excel's Background Error Checking - Strictly speaking, this should be instigated during the model development phase as it can assist the modeller throughout construction.
  
  To enable this functionality, go to Excel's Options (ALT + T + O) and in the 'Formulas' section, ensure that the 'Enable background error checking' tick box is checked. Once activated, the user can select which error checking rules should be catered for by inspecting the 'Error checking rules' section directly beneath this check box.

This functionality does not prevent errors from occurring, but potentially erroneous cells are highlighted by Excel in a fashion similar to cells that include comments, viz.

• The problem with this approach is it is easy to miss this annotation, but it is better than nothing.
• Use Excel’s Formula Auditing tools – In the ‘Formulas’ (sic) tab of the Ribbon, use the tools in the ‘Formula Auditing’ section of the toolbar. In particular, ‘Error Checking’ is useful (although it may only be applied to one worksheet at a time) as it highlights a lot of issues Excel is programmed to consider as “dubious” (e.g. inconsistent formulae, #DIV/0! errors).

• Find Prima Facie Errors – There are glitches in Excel and occasionally, a prima facie error may slip through. These obvious errors are particularly embarrassing to miss, as these are usually identified by end users picoseconds after a model has been handed over.
  
  There is a simple sure-fire check: CTRL + F (Excel’s ‘Find’ functionality).

• Simply type ‘#’ in ‘Find what’ (the obvious errors all begin with ‘#’), but then click on the ‘Options’ button to display the options and change the ‘Within’ setting to ‘Workbook’ and then look at ‘Formulas’, ‘Values’ and ‘Comments’ in turn using the ‘Find All’ button to correct any issues identified.
• Review inconsistencies in formulae – I have discussed this issue before (please see >Seeking Inconsistencies Consistently for further details).
• Look for errors in unintentional links in range names – Again, I have discussed this in a previous article (see Naming Names for more). The Name Manager in Excel (CTRL + F3) can be used to both identify links and range names containing errors.
• Locate unintentional links – This old chestnut simply refuses to die! I have discussed this twice previously (see Locating Links: Phantom of the Operator? and / or Locating Links #2: The Macro Strikes Back for more on this recurring theme).
• Perform high level analysis – Depending upon the purpose and scope of the model built, you can create a check list of items to review for each model (e.g. does your Balance Sheet balance? Does the cash in the Cash Flow Statement reconcile to the amount in the Balance Sheet? What is my forecast days receivable?). Using accounting ratios focusing on profitability, liquidity and gearing can be beneficial too.
• Create “quick” charts – For key outputs, you can graph the data momentarily. Simply highlight the data and press the ‘F11? function key, viz.

• Do the charts make sense? Are there unseemly ‘blips’ or inconsistent trends? Can dramatic changes be readily explained? These rough and ready charts can highlight calculation mistakes in an instant on occasion.
• Close and re-open – Do you get unexpected error messages upon opening? This is a frequent oversight made by modellers. Are calculations set to ‘Automatic’? Are there any unexpected links, circular arguments or other error messages (e.g. “Not enough memory to display”)? It is better that you discover these issues before your customers do.
• Spell check – Nothing looks less professional than opening a Dashboard Summary to look at the “Selas Turnover” or items labelled incorrectly. There is really no excuse for not spell checking a model (‘Review’ tab of the Ribbon has a spelling check).
• Printing and viewing – Not strictly speaking an error, how many times have you decided to print out a model sent to you only to find it print over several reams of paper that even Tolstoy would have been proud of? It is worth taking time to set up print margins and included headers and footers. Also, each page should be reset (CTRL + HOME) and saved on the front page so that models are not opened with the end user finding themselves in cell GG494 of a sheet called “ID_Rev_MR“. Been there, done that, bought the consequences.
• Protection – If as a modeller you have invested sleepless nights in getting a model to work, you do not really want an end user typing “17? over a sophisticated formula that has taken hours to get precisely right. These unowned hard codes often come back to haunt the modeller – unfairly – and cast doubt over the credibility of an otherwise robust model. Take the time to protect cells, worksheets and the workbook as required to avoid these issues.

Word to the Wise

At the end of the day, getting an independent, objective review of the model trumps all of my suggestions above. This may seem like blatant self-promotion, but I can provide many war stories concerning the value of a model audit. Many baulk at this option due to cost, but what is the cost for a business that makes decision based on flawed forecasts?