Academic and commercial spreadsheet errors

[I just posted this on Eusprig – but I suspect it is too long to hold the interest in a list post]

I think there is a total chasm between
a. academic researchers whose main spreadsheet experience is the classic ‘student grades’ thing and
b. business spreadsheet jockeys who are in spreadsheets all day everyday.

group a think several hundred formulas is big, group b think several thousand is small.
group a think most commercial spreadsheets have material errors, group b rarely see any error effect.
a think b are over confident, b think a are inexperienced.

Within Eusprig I think we need to find a way to reconcile and explain these two completely opposed views of apparently the same thing. Otherwise neither side will ever gain any credibility from the other.

Personally I don’t believe many commercial spreadsheets have material errors, because most commercial spreadsheets are immaterial. They are a small piece of a bigger effort.

Yes I have seen spreadsheets wrong by millions, and 10+ % or whatever you want to call materiality. But did it change anything? no, not ever.

In a billion dollar, multi year, deal evaluation model, a multi million formula error can be dwarfed by inflation or interest rate assumptions. But whatever, if the price comes in at 1 billion and the client only wants to pay 900 million, then the whole analysis, errors and all, is largely irrelevant. Now the question is ‘are we prepared to take the risk that we can deliver this and survive for 900m?’ or slightly more cynically ‘will they ever tie cost overruns back to me and take back my bonus?’

In my experience spreadsheets are normally one of many inputs to important decisions, any inputs out of tune with the majority are either reviewed for credibility or rejected.

So I agree that most spreadsheets have defects, and I agree that very few lead to an erroneous outcome. And I agree that this is the Human element of spreadsheet interaction, ignored in much academic research. I also believe that the big issue is wasted time and effort, around ineffective spreadsheet use, not error impact.

Maybe we need some more holistic research that covers the whole person/spreadsheet system (in a commercial setting) rather than the spreadsheet in isolation.

I would highlight that in my experience when a spreadsheet changes hands (for holiday cover, job role change or whatever) there is a huge spike in wasted time and risk of nonsense outputs, and external support requests.

What’s is your experience? have you also found that the complete information system that includes these potentially erroneous spreadsheets is usually somewhat self healing? (and self learning – ‘x in reporting is useless, I now ignore everything they send me’)

cheers
simon

Advertisements

5 Responses to “Academic and commercial spreadsheet errors”

  1. Robert Bruce Says:

    I think we’ve all been told at one time or another that “The answer is x. Now make that model come up with x.” Who cares whether the spreadsheet contains errors in that kind of situation?

  2. Bob Phillips Says:

    In my experience spreadsheets are normally one of many inputs to important decisions,

    Not quite, it is the pie chart on the first sheet that is the input!

    Good points Simon, but they are important. Not in the big picture perhaps, in those circumstances facts rarely matter, but for the coal face worker, trying to get a budget, trying to prove something is on/off target, it matters.

  3. fastexcel Says:

    When I was managing a group of forecasters one key method we used was to do the forecasts (with spreadsheets of course) using at least 2 different methodologies and see if the results looked reasonably comparable.
    Similarly in my experience any sensible real-world decision needs to be (and usually is) based on multiple views. This tends to screen out large spreadsheet or other errors, and small errors are usually insignificant compared to the underlying probability spread of the assumptions.
    So yes, I pretty much agree with you Simon.

  4. Harlan Grove Says:

    Depends on the audience.

    I worked in insurance in the US for a few years, and anything submitted to the insurance departments in certain states would be gone over with the proverbial fine-toothed comb. In those situations, the bureaucrats could and did scrap entire rate filings if they found a single material error. That may be exclusive to government bureaucrats who measure productivity by how much commerce they stymie.

    One business person reviewing another business person’s spreadsheets, I’ve seen my share of off-by-one indexing errors in cashflow models which shouldn’t have had certain nonzero time zero cashflows but did. Were they material? Only to those who pay any attention to IRR (worst financial measure ever devised), which is so fragile small mistakes can change the sign of the result.

    Far & away the biggest spreadsheet problem I’ve seen repeatedly is printing without refreshing pivot tables or recalculating. IMO, any well-designed spreadsheet program should disable File-Print when calculation is set to Manual, or alternatively refresh all pivot tables and recalc before displaying the Print dialog. Not a problem with formula errors, but a common problem with spreadsheet programs.

  5. ross Says:

    save for spelling mistakes (i.e. ones i would have introduced) i could have written this myself. I agree totally.

    Errors are there, but are they material? , depends on how that’s defined, imho.

    Also what is an error – id say its only an error if it noticed up untill then its correct…

    One of the side effects of computing has been the reduction in the ability of people to make decisions, nothing can be done now without a model, but in many cases what value does the model add?

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: