Written by Hedieh Kianyfard
“ Errors using inadequate data are much less than those using no data at all.” – Charles Babbage
It is common to hear people complaining about excel being prone to errors. Even if we take a very systematic and methodical approach to using excel modeling, we won’t eliminate errors . The benefits of using excel however, far outweigh the risks it potentially poses.
Instead of wasting time and resources building overly complex financial models, a good financial modeller can create a Flexible, Appropriate, Structured and Transparent (FAST) model – one that is “as simple as possible, but no simpler”. That way they can focus on what goes into the model as inputs and avoid the garbage in, garbage out problem.
One way to help reduce errors is to include error and integrity checks in your models. The FAST Standard advocates a Control Sheet that collects all the individual checks throughout the model into one sheet. This allows the model builder to check that the model, or section of the model, works correctly.
All these checks are then summed and a master error check and alert indicator can be created and included in the freeze pane in each worksheet within the model.
Here are a few examples of common checks to be included in your models:
If the sources and uses of funds do not match, then the flags for Uses and Sources Cross Check will be highlighted in red and the Master Error Check will also be automatically formatted in red background.
This list is just an example of how you might structure your control sheet and some of the questions you could ask.
I’d like to make this into a modelling guide for inclusion into the handbook. For this, I need your help.
What methods do you use? What are the most fundamental checks that should be made? How many checks is enough, is it possible to have too many?
I‘d love to know how you do this, please leave a comment and discuss, I’ll then collect everyone’s thoughts and ideas and create a modelling guide for this topic.
I might be missing the point but this whole debate seems bogus.
For example the BPM tool as demo’ed in the video, wraps an if around an existing formula error? That in my view is not adding much value – you get a summary and some navigation, but you can just check the model for errors in using the built in tools at any time. Looks like there are other error checking options in BPM but how they work was not clear. Maybe they are meatier?
However I think there is a more fundamental problem here. What we are doing is checking for error of only a narrow type, check sums and the like are good for doing this yes, and we should of course be checking etc., but these tend not to be the type of error that catch us out, at least they shouldn’t be.
Detecting the really catastrophic error types in excel is much harder, because there is generally nothing computed already to check against. This is the major problem?! These are the numbers as text, missing data out of/end of ranges, references out by 1 error, and other such nuanced problems.
Maybe there are tools out there that do this, maybe BMP does, I know of spreadsheetdetective, but have never used it. I for one think that Excel needs to bulk up on this sort of thing, but MS don’t seem to care, errors, don’t sell I guess!
Cheers
Ross
Dear Ross,
Thanks for participating in this debate.
We agree that spreadsheets are prone to errors and that’s the reason for this blog post. But I’ve avoided really catastrophic errors by using the simple checklist that I presented here. From my own experience, the application of financial modelling standards such as FAST standards can tremendously avoid some of the errors that take place in financial models but conceptual errors are sometimes difficult to spot.
Recently, I reviewed a financial model which was based on best practice financial modelling standards and was reviewed and used by many people and organizations during the project appraisal process. I did my first level check which consists of an overall check on model consistency and mechanical errors and I use Operis OAK analysis kit to check for formula consistency and other typical mechanical checks. Then in my second review, I did a more detailed check and went over formulas and calculations. I noticed that the model was using an end of period convention but the calculation of IDC was based on beginning balance of the loan and as a result IDC was underestimated. I captured this error while checking the delay sensitivity test and sensitivity test is another way of capturing mechanical or conceptual errors in models.
I sincerely believe that Excel is not to blame for spreadsheet errors and by adopting standards and performing sanity checks (using tools and human expertise) we can avoid most of the spreadsheet errors.
Best regards,
Hedieh
The approach taken by bpmToolbox is to have a binary system throughout. 1 = error. 0 = no error. These values are easy to apply consistent conditional formatting to and easy to amalgamate.
All errors are grouped onto a check summary. This is then included in the model name, which is shown on all worksheets. Every worksheet then includes a hyperlink to the check summaries and each check contains a hyperlink back to the check cell – all for ease of navigation.
The important thing in the methodology is that every new error check can be amalgamated into the summary on a systemmatic basis using four keystrokes. A centralised checking system is all good and well but if it takes the model builder 5 minutes to insert every check manually then they’re not going to do it as often (this is the psychology of safety adherence 101).
The key is to build models that are so systematic that the insertion of checks can be proceduralised…
Dear James, Thank you very much for your contribution. Using hyperlinks for checks is a great idea and is going to maximize navigation efficiency which is one of the cornerstones of the FAST Standard. Thanks,
I think the crucial point isn’t so much the chosen structure for your own checks system – there are many ways to skin a cat in Excel, admittedly some better than others. The more important point is that whatever your system for error checks, unless there is a very quick and simple way to insert them into your model then the frequency of insertion will be lower and the final model poorer for it.
This should illustrate the point: http://www.bestpracticemodelling.com/tv/bpmToolbox/getting_started_checks
So whilst someone might have the most flawless error checking design for their workbook, if it takes 5 minutes to add each check, then as a safety system that doesn’t add a great deal to the model, they’re less likely to add checks. Cheers. James
Any error check system is better than none, and if a modeller has chosen to include such a system in their model then at least they’re aware of the need and of the frame of mind to add further checks if they believe it is warranted.
Whilst the BPM software makes adding such a system, and expansion thereof, somewhat easier, there are a couple of mod’s I would make to it:
1. Add an alert on any sheet with an error check that immediately and very visibly alerts the user that there is an error on that sheet (rather than just somewhere in the model) I believe that displaying the result via the circuitous route of Error Summary and then back onto each sheet through the model name is just not adequate.
2. Move the model level error alert from within the plain black formatted model name where it can easily blend in, and give it the prominence it deserves, both in terms of location (like in a cell on its own) and format (bright bold red is good!).
It’s not like there is a shortage of real estate in current versions of Excel that a structured financial model cannot afford to devote a couple of cells at the top of each sheet to such critical information as the integrity status of the sheet/model (and that is more important than the model name.)
Dear Col, Many thanks for posting your feedback and suggestions. I totally agree with you on the importance of including the master error check in the freeze pane in each worksheet within the model. For the formatting, I use conditional formatting to highlight the master check in bright bold red if the conditions/checks are not satisfied.
Fantastic dialogue on what is often an overlooked subject in our haste to get the spreadsheet build and the answers out. So many people will benefit from this initiative, so please keep up the good work!
Thanks Jon. This feedback is really useful and much appreciated. I’m really looking forward to seeing what Hedieh does with all of this – great to have so many views contributed.
Reading what is already written I currently see nothing I would like adding. This is good stuff. I appreciate the summary into one error summary sheet and its result into a global error indice placed on (some) model result page(s).
I also like the distinction between error checks and lets call it ‘managerial’ alerts. The latter could be placed on its own page and potentially combined with a scenario management where ‘InpC-S’ [my naming idea] may supply Manager Input to be linked to. If a separate sheet is the only way or include into ‘Inp-C’ but clearly and visible distinct each other could be discussed. ( Before you ask: I have no model handy where I could show the solution)
Using hyperlinks is nice at the start but may become awkward and requires maintenance. The FAST linking and jump-back to source could be the easier way in the long run.
Regards,
Thomas
Hi Hedieh,
Great topic: this is something I need to do for my models, but haven’t sat down for long enough to adopt a structured approach to addressing it.
One consideration I’ve noticed from other people’s models which include such a check is Circular References causing excel to stop calculating, which can return an incorrect result.
For example:
Cell 1 = 2
Cell 2 = 2
Cell 3 = sum( Cell 1:Cell 2)
Cell 4 = if( Cell 3 = sum( Cell1:Cell2 ) , 1 , 0 )
Initially Cell 3 returns 4, Cell returns 1, all okay, but if Cell 3 is changed to include itself (returning a Circular Reference) then Cell 4 does not change, i.e. still shows “no error” even though there is…
In an ideal world (!) of course there would be no circular references, but in my experience human error can creep in, and it is possible for the Master check (model has no errors) to be misleading.
I don’t know if there is a fail-safe method of avoiding this, or if there are Excel settings which can stop this from happening…
Thanks Danny
Dear Danny. Thanks for your comment. I personally consider unintentional/intentional circular reference in a financial model as a deadly sin and is one the items in my review check-list as an issue to be resolved through using algebra, functions or macros.
Hi,
In the context of project finance modelling, I have taken the following approaches to checks:
1. One sheet
I only have one check sheet and the checks are calculated in the FAST style (i.e. calculation blocks with imported rows from other sheets). This means that I can verify why a check is failing by looking at the calculation block and “jump” to the relevant sheet using the imported rows.
To avoid the check sheet looking like any other calculation sheet I use grouping so the end result is similar to your example.
2. Checks versus alerts
I am going to blame Kenny or John or both for this one as I am sure they proposed a similar split during one of the modelling sessions!
I tend to differentiate between checks that are “checks” and checks that are “alerts”. In theory a failed “check” would highlight that the model was not working in the intended manner. On the other hand a failed “alert” would mean that the project was not meeting your constraints.
Making sure the balance sheet balances would be a check, having a large free cash balance may be an alert. The checks may include the balance sheet check, sources & uses check, tax/accounting reconciliation check, copy/paste check (if required). Typical alerts include minimum cover ratio and minimum return alerts.
3. Assume it is a fail unless it passes
When I create a check I make sure that the check will by default return an error. In order to pass the check must actively meet a test.
For example, I would tend to use a check like:
IF( [BalanceSheetDifference] = 0 , OK , ERROR ) rather than
IF( [BalanceSheetDifference] > 0 , ERROR , OK )
The same applies to the cell formatting, the cell is formatted RED unless the contents of the cell say OK, at which point the conditional formatting would make it GREEN. This way if the check is deleted by accident then the visual cue of a RED cell would remain.
4. 1 = OK
I make use of the units column to identify where there is a “check” or “alert” allowing the number of checks/alerts to be counted. I also make the check/alert return 1 if it passes/ok and defaults to 0 if it fails/error. This makes it a simple task to sum the checks/alerts that return 1 and everything else is assumed to fail.
As a summary the model is then able to display “3 of 5 checks passed” or similar, and checks/alerts can be added/removed quite flexibly.
Regarding the number of checks/alerts, it depends, not enough checks and you worry if you checked everything you should do after each model run and too many checks can lull you into a false sense of security.
Hope that contributes to the conversation, look forward to seeing your guide.
Kind regards,
Vish
Dear Vish,
Many thanks for your great comments. We will definitely include them in our modelling guide on this topic.
Best regards,
Hedieh
I use “OK” and “ERROR” (with red collor) values in check cells (not only collors). I use separate check list for whole model. I can show check list from my model but i don’t know how to insert pictures in this forum.
Thanks Andrey. Could you please send it to me by email? ( hedieh.kianyfard@gmail.com). Thanks. Hedieh
I always check that cash is not accidentally negative when calculated
Dear Carlos,
Good point. I will add it to the checklist. Thanks.
1. Individual error checks should display as “Error” or “OK”, preferably by returning underlying values of 1 or 0 and using custom formats to display the appropriate message.
2. All errors on each sheet should be summarised into a Sheet level error status so that the user can readily ascertain if there is a problem on the active sheet without having to go to the Master Control Sheet.
Agree. Thanks
Good article and this is the approach I adopt.
I do not think it is possible to have too many checks but avoid duplicating checks which ultimately are testing the same thing.
I would add (even if they should come automatically from the model design):
Do Summary reports agree to the underlying master or detailed report?
Where relevant, do cross cast totals agree to downcast totals?
Does the P&L reconcile to the reserves movement?
Does cashflow balance agree to balance sheet cash/overdraft?
Thanks Paul. Could you please clarify your first point on the check for the mismatch between summary report and master/detailed report?
Hi Hedieh,
No problem.
Where there is more than one report summarising the same data either in different ways or at different levels of detail but all of which should ultimately come to the same answer (e.g. a Summary profit and loss and a detailed profit and loss), I would check that the answers are indeed the same. e.g that Profit after tax in the detailed P&L equals the Profit after tax in the Summary P&L.
Regards.
Paul
Thanks Paul. but since the summary is linked to the detailed calculations, unless it is linked to a wrong range of cells, there shouldn’t be a problem. To avoid having too many checks in my models, I avoid creating checks for links between cells. Thanks.
Fair enough; I would certainly agree on avoiding too many checks. Personally I would always want a few checks on the overall consistency of the model. Things happen!
Hi,
Appreciate your efforts to summarise the importance of error checks. I have been building so many financial models and can understand how a master check (i call it global check) can prove to be a powerful tool to handle any potential error in a big model. I personally use checks at 4 levels: (1) Cell check, (2) Row check, (3) Sheet check, and (4) Global check.
For instance, a model would have check for balance sheet total (assets – liab. & equity) for EACH period. That’s the first level of check. If the model has 5 forecast years and accordingly 5 cell checks for balance sheet total for all years, then a summary of all these checks could be created; say sum of all cell checks to be zero. I would typically place this kind of row check in first column (I do not prefer to write anything in column A and reserve this column for row checks). Once all row checks have been placed (say in Column A), I would sum them up at the top of the sheet (say in cell B2) and would create similar sheet checks in all worksheets in same cell B2 in respective sheets. And finally a global check! A consolidated control check is the first point for a financial modeler. This is like a bird’s eye view for model control. The global check would be created by having a list of all worksheets and placing all sheet checks against them. Then all these sheet checks are summed up to get a global check. I prefer to compute & place this global check in contents sheet which would have a list of all sheets along with their description, a hyperlink and respective sheet checks.
While conducting a model review, one would always start with global check. If global check is ok, then it indicates that double entry rule of finance has been properly followed. In case of any error in the global check, one would move to look at sheet checks and identify the sheet containing error. Once that sheet is activated, the row check would higlight the rows which have contributed to errors. That’s how, a robust control check mechanism within the model helps to retain control of the model and identify error areas.
Cheers,
Amar
Hi Amar,
These are great comments! Thanks. I like the idea of having a hyperlink in the content sheet and I think it is a good idea to also include it in the control sheet. Thanks again.