Spreadsheet errors - and how we can manage them

Spreadsheets are used by us, and by our clients, for important financial decisions. So it's important that we don't have errors in key models.

Extensive research has shown that people make mistakes – they will make every kind of mistake possible, over time. The error rate is something like 2-5% across all technology usage, from beginners to experts. I'll say it again - experts make as many mistakes as beginners - different mistakes, perhaps, but just as many.

So the first rule of spreadsheet design is to expect errors, and design to minimise error.

Research by Raymond Panko, an expert in spreadsheet errors, shows that errors are very diverse, and that simple typing errors are not as common as we might expect. Panko concludes “In general, the injunction to ‘keep it simple, stupid’ appears to be correct in the creation of spreadsheet models. We need to reduce developer overload as much as possible. Keeping things simple should also aid in code inspection. The author found that errors were detected much more frequently in short formulas than in long formulas.”
(Raymond Panko – Two corpuses of spreadsheet errors)

More from Panko: “In general, our error detection rate only approaches 90% for simple mechanical errors, such as mistyping a number. For logic errors, error detection is far worse, often 50% or less. For omission errors, where we have left something out, correction rates are very low.“ Source

The research found that errors vary very widely, and mistyping is not the dominant cause. There are logical errors (such as using the wrong formula) and omission (eg leaving tax out of the calculation), and many, many more. We humans can be very inventive when it comes to mistakes. Recent space missions show this - such as the parachute that was installed upside down, and the navigation correction that the programmers thought was in metric units - and wasn't. Oops.

Even when we do test…

Reading: Link1 Link2 Link3 Link4

Designing against error

So errors are going to happen,even to experts, and checking is only partly effective. What are we to do to ensure an error-free model?

A good place to look for ideas is how people build software and systems that cannot be allowed to fail. For example, the space shuttle has had its share of fatal quality problems, but its software development passed review, because it has so many checks and balances, and because they still (I believe) use very, very old programs and memory technology because it would be so expensive to rewrite and test it all over again. I don't think most businesses would go for that approach!

Industries which depend on absolutely accurate software have very stringent and thorough processes for building, testing and retesting the software. Again, this is too expensive for most businesses, and too much to expect of humble spreadsheet builders. One thing they do well, is to build models in small chunks, and test each chunk thoroughly, so that the testing is always manageable. In contrast, when they built the space shuttle, they built the main engine all at once because it was at the cutting edge of technology, and they had nightmares testing it.

The airline industry has to get it right all the time, or people die. One key approach is to have layered security, in other words, if one thing goes wrong, another system will cover for it - it's sometimes known as a swiss chees scrurity, because although there may bemany holes, (hopefully) no hole goes all the way through. A simple example is having four engines and being able to fly on just one one or two of them, being able to lower the landing gear by hand, and the way individual aircraft parts are labelled and monitored through their lifetime.

Managing complexity

There are many books which cover writing error-free software, none better than Code Complete. It says that the most important problem is managing complexity. For me, this rings a very big bell, because if anything, Excel magnifies complexity. Just think about it - if you look at a complex spreadsheet, you can only see one formula at a time, and it may refer to cells and sheets you can't see. What you can see is (often) a sea of numbers, which create visual clutter.

There are some simple, very practical things you can do to manage complexity.

Keep it simple

Build it in chunks

Where a model has a lot of different parts, it is much easier to check if each part can be built and tested separately. While this especially applies to VBA code, which can be broken up into separate routines that can be checked on their own before assembling them together, it can also be effective on worksheets. An obvious example is with the "building block" design (see here), which puts different parts of a model, such as tax calculations or loan schedules, on separate sheets.

Build it with the user in mind

If it is a one off model, and the user is only going to be your checker (or maybe someone new who might pick up your model a year from now, and try to use it), then focus not on making it beautiful, but on making it as easy as possible to follow and check. The

If it is going to have regular use, then you need to worry about the user interface, and what users could misuse your model.

Build in self-checking

There are obvious self-checks, such as ensuring percentages add to 100%, or making sure that row and column totals add to the same figure, or (say) using conditional formatting to highlight figures that look wrong.

Prove the model

All the way through the model, think about how you can prove (to your checker) that it is right. For example, there may be independent figures or calculations you can include, or link to, or documentation you can paste alongside your formulae.

Before you say this is nice motherhood stuff and nobody does it, I had to do it myself a couple of years ago, when I was asked to support a team in another office, and we sent spreadsheets back and forward by email. We quickly found it was essential to document them fully, and there was an unexpected bonus - the act of documenting makes you, as the builder, check your own work more carefully as you are writing out the logic, cutting and pasting, or whatever. To put the icing on the cake, we had to pick up a number of old spreadsheets from scratch, because the people who built them had left (that's why I was helping out!), and those old spreadsheets were badly documented and very tough to understand. We couldn't have had better real life evidence of the need for clear, provable models.

And, based on that experience, I can say it didn't take long to document our models, so I am totally sold on it now.

So, just to finish off, I'll say it again.

Errors will happen - good design will minimise their effect.

 

 

 

Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.