By Mary Pat Campbell
Back in Jan. 2008, I wrote an article for CompAct, the newsletter of the SOA Technology Section, titled, “To Err Is Human; To Correct, Divine,” coming from the point of view of an insurance company actuary. It opened thus:
“How error-riddled are your spreadsheets? How much can a simple Excel flub cost your company? When you do make a mistake, how likely are you to catch it?”
While it has been multiple years (and multiple Excel versions) since that article was published, many of the problems continue, and often for similar reasons. As of Sept. 2007, when I wrote the prior article, there were only 89 news stories about this topic.
I am a long-time member of the European Spreadsheet Risks Interest Group (EuSpRIG), a group composed of researchers, consultants, and practitioners. We’ve compiled not only research on spreadsheet error rates (distressingly high), but also stories of spreadsheets that have gone so wrong they make the news. I had been updating the list recently of the most notable stories, including these flubs:
- Spreadsheet error leaves state schools $25 million short (2012)
- $2 billion calculation error in Kansas budget (2013)
- Spreadsheet error costs shareholders $100M in a deal (2014)
In the first two cases, one may think, “Oh well, politicians were involved. What do they know?” But keep in mind that the EuSpRIG horror stories are based only on those screw-ups that become public. The ones involving political actors tend to be relatively small—that $25 million in the first story was less than 1 percent of the schools’ annual budget … and yet, it led to the very public resignations of two people who had worked on the budget.
The non-political stories, however, tend to be very large indeed, because they hit the news as lawsuits get filed. These errors tend to be more substantial than round-offs. In some cases, it’s a matter of huge embarrassment. Here is an example from the EuSpRIG website:
"In a paper, 'Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff,' Thomas Herndon, Michael Ash, and Robert Pollin of the University of Massachusetts, Amherst, criticize a 2010 paper by Harvard economists Carmen Reinhart and Kenneth Rogoff, 'Growth in a Time of Debt.' They find three main issues: … Third, there also appears to be a coding error that excludes high-debt and average-growth countries. All three bias in favor of their result, and without them you don't get their controversial result."
It turned out they missed two rows in their Excel formula. It was that simple.
Imagine the hit your own reputation would take if your clients thought your work error-filled.
So let us consider what practice and research has shown in avoiding these kinds of spreadsheet disasters. Much of these findings come from EuSpRIG members and academic researchers, some of whom I have listed in my references at the end of this article.
I have three starting principles for error-proofing your spreadsheets:
- Lose your spreadsheet ego;
- Follow best practice spreadsheet design principles; and
- Check, check, and check again.
Lose Your Spreadsheet Ego
I could use the Zen concept of “beginner’s mind,” but the idea here is to get over yourself, in terms of your expertise. Yes, you’re a technical expert. Very nice. You still make mistakes.
Indeed, those who are expert tend to make some really bad mistakes if they don’t get their spreadsheet egos under check.
The worst spreadsheet errors in the EuSpRIG Horror Stories tend to be from people who are expert in financial modeling, and if one thinks about it carefully, it’s obvious why:
- 1.Nobody gives billion-dollar business lines to interns to value (so they’re not given the chance to screw up to begin with); and
- 2.Experts are more likely to expect themselves to be error-free.
Think of other large non-spreadsheet-driven financial messes—you often get these “Smartest Guy in the Room” complexes, where the Smart Guys™ get to work on the big business deals … and they’re less likely to have people second-guessing them (and they certainly are not second-guessing themselves).
The spreadsheet research, whether under controlled conditions or spreadsheets “out in the wild,” bear this out. Novices do not create overly complicated spreadsheets, and the types of errors they make are very basic.
Experts, though, make extremely complicated spreadsheets, and have the confidence in the tools they built up over multiple years … and their errors can be much more difficult to find.
Errors are even more difficult to find if you’re not looking for them in the first place.
So instead of assuming you did all your calculations right, start from the assumption that something, somewhere, may be very wrong. Think of the spreadsheet creator as some alien and it is your job to figure out what they screwed up.
If you can separate your ego from your spreadsheet creation, and look at it with a dispassionate eye, you are more likely to find errors, and ultimately make your spreadsheets that much stronger.
Follow Best Practice Spreadsheet Design Principles
We’re all for “best practice,” aren’t we?
The issue is, with spreadsheets, there are some competing principles put out there. I have links to a couple (the references to the FAST Modeling Standard as well as the Institute of Chartered Accountants in England and Wales’ (ICAEW) spreadsheet principles at the end of the article), but there is much there with which I disagree. Part of this may be due to most of these people doing cash-flow modeling; actuarial modeling sometimes goes beyond that.
More to the point, as an entrepreneurial actuary, you may have to deal with other people’s spreadsheet standards and set-ups. What to do then?
Here is an excerpt from ICAEW’s principles:
The spreadsheet’s business environment
1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.
2. Adopt a standard for your organisation and stick to it.
3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.
4. Work collaboratively, share ownership, peer review.
Designing and building your spreadsheet
5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.
6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
7. Include an “About” or “Welcome” sheet to document the spreadsheet.
This all sounds very nice, but you may not be allowed to mess with the standards.
You may not even be able to control file names (oh yay).
I have some extremely high-level principles, which extend beyond spreadsheets specifically.
Principle 0: Think through your model before coding it.
Be mindful in all you do. Some of us are too quick in our thinking for our own good, and go about implementation before getting all the steps clear.
Principle 1: Be clear on the specifications.
This is a more detailed look at thinking through your model:
- What is the input and output? Are intermediate steps needed as partial output?
- What can change and what won’t change?
- What methods are being asked to be implemented? Is this a standard approach?
Principle 2: Document your work.
We all think about documentation, but usually only when we feel it is not usable or does not exist. Spreadsheets are interesting because of the multiple ways one can document, but here are the main points:
- Document at point of use.
- Out of sight, out of mind.
- Spreadsheet specific documentation tactics:
- Text labels next to outputs.
- Named cells, but be sparing—hundreds of named cells make for few friends.
- Comments—text boxes in worksheets, text comments in VBA code.
- Specific documentation tab in file.
- Model structure and logic needs to be documented.
- Data sources need to be documented.
- Checklists if there is an updating procedure.
Principle 3: Make your work understandable.
- Clear, logical flow from input to output, as well as geometric placement on the page.
- Understandable formulas.
- Use features that add complexity sparingly.
Principle 4: Make your spreadsheet updateable.
This is especially important for entrepreneurial actuaries who expect return customers. It is even more important if they want to encourage customers to return.
If you follow the earlier principles, this should flow naturally. If you clearly document what you are doing, have thought out your models with clear logical flow, have shown your inputs and outputs separately (being mindful not to hardcode any inputs into the spreadsheet), and have used formulas that are understandable, then adjusting your spreadsheet to be updated should be a snap.
I know some may be hesitant, because if one makes the spreadsheet too easily updateable, a client may update it on their own and cut you out. There are multiple ways to deal with this, of course, such as selling your spreadsheets outright to clients to use, as opposed to charging on an hourly basis.
But more to the point, I have found that one wants to have the client do minor adjustments, and when there is more substantive, serious work to be done, one can charge a premium for such premium work.
It’s sad, but true, that even when you make a gorgeously structured spreadsheet, people can demolish it into hideous creations once it leaves your hands. Making the spreadsheet as clear as possible means it will be easier for you to undo the damage (rather than having to start from scratch).
Principle 5: Check your work!
This I will cover in the next section.
Check, Check, and Check Again
The advice I gave in the earlier article was to work (and specifically, check) in groups. This is still good advice, and the research bears out that error-correction works best when you have three or four people working to find and correct spreadsheet errors. Unfortunately, people working in pairs did not do much better than single people working alone.
However, if you are an individual practitioner, this can be difficult to implement. One can try what some professional writers who publish independently do: they have informal groups where they review/edit each other’s work as part of a barter system. Of course, independent actuaries could bill each other for such checking. Perhaps the client is willing to do the peer review, but there can be reputational risks there.
But even if you can’t get a peer review by a third party, consider a few techniques, some of which are found in Patrick O’Beirne’s book Spreadsheet Check and Control.
- Anticipate the answer—before you calculate, estimate where the answer should land. Is it near expected?
- If you can do subtotals and crosstabs to check independently, try that.
- If there are ways to estimate the result using a different method, try that.
- Break your spreadsheet deliberately.
- If you don’t know what makes it break (and something should make it break, such as bad input), you don’t know how it works.
- Make your spreadsheet break inelegantly—it should “vomit” all over you (or at least display an error message). Silent errors are the deadliest.
- Test the boundaries of inputs.
- Find the most extreme inputs to test how your spreadsheet behaves—this may expose problems with your model’s logic or its implementation.
- Never stop testing!
- When you come back to the spreadsheet after a hiatus, try breaking it again.
This is just a start. One need not be paranoid to error-proof your spreadsheets, but one does need to be mindful.
Go and err less!
Getting Started:
O’Beirne, Patrick. Spreadsheet Check and Control. 2005.
Grus, Joel. Thinking Spreadsheet. 2011. Free online version
European Spreadsheet Risks Interest Group
Twenty Principles for Good Spreadsheet Practice.2014.
31 Days to Better Financial Modelling, free online course. Based on FAST Modeling Standard.
MPC Spreadsheet Articles (selection):
Review of Spreadsheet Check and Control, CompAct, July 2008
Spreadsheet Issues, Actuarial Practice Forum, 2010.
Excel Formula Rogue’s Gallery, Part 2,CompAct, October 2011.
A Deeper Dive into Spreadsheet Research and Examples:
Mary Pat Campbell,
FSA, MAAA, is vice president, Insurance Research at Conning in Hartford,
Connecticut. She also teaches courses on computing and business writing for
actuarial science students at the University of Connecticut. Some of her prior
writings on Excel can be found at her LinkedIn page. She can be reached at marypat.campbell@gmail.com.