Spreadsheet auditing (1 of many)

I went to a Eusprig conference a few years ago (highly recommended btw (I’ve been going ever since)) and was struck by the quoted error rates in spreadsheets. The evidence seemed overwhelming – 90-100% of all spreadsheets had material errors, and a lot of it was to do with developer overconfidence. Like catch 22, if I believed the figures my lifes work was riddled with errors (time to renew my professional indemnity insurance!), if I didn’t believe it, that was overconfidence – so then I’m a cocky error mongerer! blimey!

To slightly misqoute: Have you stopped building error ridden spreadsheets yet? Yes or No?

  • Yes (I used to but have stopped) or
  • No (I am still building error ridden spreadsheets)

My biggest issue was how could 100% of spreadsheets have findable ‘errors’? I can believe 100% of spreadsheets could be improved in the quality department, but not that 100% have errors so obvious even someone unconnected with the initial development could spot them.

XLAnalyst was my attempt to find out. The linked version there is getting on a bit now, and newer versions are on their way (as is a web site update). I took a look at the auditing tools around at the time, and some of the reported horror stories and personal experience. I then came up with a list of things that a workbook may contain, and what that might mean for risk of errors. What I wanted was a spreadsheet risk assessor, so I could point it at a file and see if it contained risky constructs (like 3 argument lookups, and external links). Key criteria was speed of analysis. I was trying to get a sense of the quality of the spreadsheets rather than some kind of error? yes/no over simplification.

The idea was that people would try it (its free (no cost), never expires etc), and maybe submit back the short text string containing the result of each test (but no identifying info). I was then hoping to present the findings to Eusprig. The proportion of people who submitted data back was less than 0.5%, which made the results statistically insignificant. In fact I think I had more hack attacks than data submissions.

But hopefully it was of some value to the people that used it, I know of a few cases where it spotted a real life error in production models, which is good. I downloaded over 500 workbooks as part of testing xlanalyst, but in general they seemed pretty trivial compared to the monsters I get to deal with. I may try again soon, but the ones in the public domain don’t seem to be that juicy. I’d quite like to get my hands on some government ones using the freedom of information act – anyone know how (UK specific I think)?

The Dartmouth team have been giving xlanalyst a good run out, and were going to modify some of my baseline risk weightings. I have not heard yet what their conclusions were/are.

I think Dons findings of around 14% is probably realistic. I’ll post another time about why either 100% or 14% will be hard to prove / or disprove as a general estimate.

Anyone here found a big error in one of your spreadsheets? Or had someone else find one? (I have (both options – I prefer the first!))

cheers

Simon

Advertisements

9 Responses to “Spreadsheet auditing (1 of many)”

  1. Ken Puls Says:

    LOL! The question should be “who has never found an error in their spreadsheets”. I’d say that they probably haven’t looked hard enough. ;)

    We’ve all made them. I can say that I’m only AWARE of one that was “significant” in my career, though. (Key word, significant. I’ve made many errors.) That particular one was based on developer overconfidence. Fortunately you take the lesson with you and try not to do it again. The thing is, though, we’re all human, right?

  2. Will Riley Says:

    Hmmmm… My eaxample here is a mixture of user error and “housekeeping I guess but it still counts and I have to take some of the blame as the “developer”!

    Just the other day most of the Finance Team were busy hunting down around £30million of a certain product balance from our balance sheet. It had simple disappeared!! Instead of around £60m of balances, they could only find £30m. So they raised an issue with IT saying that the new banking system was obviously wrong…

    Eventually I got to look at the Excel file, something that I had built around 2 or 3 years ago (Don’t you just hate looking at your old code – cringe!). It takes a daily GL import using ADO from SQL Server and also imports a maturity profile of call & loan accounts for our daily liquidity monitoring. I remember almost as an afterthought adding some simple code so that if the sum of these specific product imports did not balance to their corresponding GL balances, error messages are displayed. This is what had prompted the “alarm bells”.

    However, during the changeover of core banking platforms during July last year, I had developed an “interim” file that was used for around 10 days where the user had to manually import the “maturity” files. Stupidly, to save time I had left the old “text file import” code in place though. This was used until we correctly configured new output files and was then replaced.

    Somehow last week, the member of staff running the daily “model” picked up one of these old “interim” Excel file & ran the code, thus importing the correct GL, but the maturity files from around 5 July 2006 (the last outputs from the old system)!! Hence the difference.

    It took some working out I’ll say but I guess there are lessons to be learnt about :-

    1. Housekeeping – i.e. when files / code is no longer relevant or superceded, archive them – don’t leave them where they could accidentally creep back into “production”

    2. It also highlighted the value of the small piece of code I wrote to check the validity of the numbers. If those error messages had not popped up, it’s possible that nobody would have noticed that incorrect data was being imported into the file, rendering our Liquidity monitoring invalid. As any FS person knows – this accurate monitoring is a requirement of the FSA, so getting it wrong is not good!

    Kind regards,

    Will

  3. Simon Murphy Says:

    Ken
    We are indeed all human, and the research suggests we make errors around .5-5% of the time. In theory therefore any workbook with over a couple of hundred populated cells should have an error.
    I should have put this link in the main post:
    http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm
    Rays site is a wealth of information in this area.
    cheers
    Simon

  4. Simon Murphy Says:

    Thanks Will – great example, coding up ‘sense checks’ is always a good idea. I look after some FSA reporting stuff too, and yes, there are better places to make mistakes.
    I have a post planned about that whole lifecycle thing as I think that is a cause of many real world problems, and is something many researchers have not considered.
    cheers
    Simon

  5. Bob Phillips Says:

    Simon,

    I am working on a project that is based upon a spreadsheet that someone else started, and every time I add/change something, it blows up and takes time to resolve.

    So I thought I would give XLAnalyst a quick whirl on it. In many ways it just verifies what I thought, but a couple of points.

    XLAnalyst says the workbook size is ‘Above Limit’. AT 259Kb?

    This workbook uses many hidden sheets? I think you should blog on hidden sheets, and also on where intermediate calculations should be. In my example, the hidden sheets are doing the intermediate calculations, the final calcs are are ona hdden sheet, and the viewable results are linked into the hidden sheet. Takes some tracking down. My prference is to have hidden columns on the display sheet for intermediate calculations.

    As for my errors, I am afraid that my brain is programmed to block them out. I really cannot recall any howlers, even though I am positive there have been some.

  6. Simon Says:

    Bob
    you can change the size threshold over on the right, I set it to something very low 100kb? just to get people to look. I don’t actually recall ever working with such a small wb, in fact I rarely see stuff below 5Mb these days.
    I will do a post on hiding stuff, thats a good idea.
    cheers
    Simon

  7. Will Riley Says:

    Simon…

    Do you have comment moderation on ? Or maybe a comment limit on size? Just that I may well have posted about 6 versions of my comment above this mornin before realising that you might just not be around to approve tham :)

    Just let me know & I won’t cause you so much admin work next time & be more patient ;)

    Regards,

    Will

  8. Don Price Says:

    Simon,
    I know that my 14% is realistic because it’s a factual find. What I don’t know, and never will, is how many non-bottom line errors we find which could affect other parts of the business, so the 14% is a minimum figure.

    Re big errors, the last slide on my powerpoint to EuSpRIG showed some examples of errors we found in general terms. Sadly I don’t have the facility to convert tothat pdf for downloading, but the figures of extra tax revenue found due to spreadsheet errors are here:

    £129k from an incorrect formula
    £2.2million from one over-written formula
    £17k from a worksheet composed of numbers formatted as text
    £126k refund for a variety of input errors
    £89million value from missing rows in a total
    £157k from an unmonitored check total

    Keep up the good work!

    Don

  9. Book Review of - Simple Spreadsheets For Hard Decisions | The Money Saving Fifty (50) Says:

    […] Spreadsheet auditing (1 of many) « Smurf on Spreadsheets […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: