Spreadsheet Errors (#193)
/In this podcast episode, we discuss how to deal with spreadsheet errors. Key points made are noted below.
The Nature of Spreadsheet Errors
You rely on a spreadsheet for a long time without really figuring out how it works, and then one day you realize that there’s a flaw, and it’s been churning out incorrect numbers all along. This is a wee bit of a problem if you’ve been using those numbers for journal entries, since that means the financials are wrong, too.
The worst case I ever saw was a small company that had been using an incorrect spreadsheet to figure out how much overhead to allocate to their inventory. When the auditors came in at year end, they threw out almost all of the inventory asset, which wiped out the company’s net worth. So there you go. This sort of thing can be critical.
So why do we create complicated spreadsheets? Some of the time, it’s because the accountant who created them was incredibly detail-oriented, and so he just kept on expanding and expanding it, and next thing you know, it’s a full-time job to maintain a spreadsheet. The person might even take pride in having created one of the seven spreadsheet wonders of the modern world. Unfortunately, that’s a pretty common personality trait in the accounting profession. It’s just the way we are.
Justifications for Complex Spreadsheets
I can think of only one situation where a really complex spreadsheet might be justified, which is if it contributes toward billing a customer for more money. For example, there might be a cost reimbursement contract, so the more cost you can allocate to a certain customer’s job, the better. And that might require some complexity. I once ran across someone whose entire job was using massive spreadsheets to allocate the overhead costs from an airline reservation system, so that the company could bill the supporting airlines as much as possible for it. OK, I can go with that as a justification, but I certainly wouldn’t want to have that job.
So let’s just say that there are a few good reasons for spreadsheet complexity, and a whole lot of reasons to have simpler systems. Let’s go with option number two, and see what we can do with it.
Spreadsheet Best Practices
Now, the worst case of spreadsheet errors is probably going to occur when a different person takes over a spreadsheet. This would be a good time to force the new person to dig right through the spreadsheet, figure out how it works, and then present it to someone who’s more experienced. Until they can defend the spreadsheet, they’re considered to be temporary in that job, because they don’t know how it works yet.
You could go a step further, and force them to write up the essentials of the spreadsheet in a document. That document can then go into the department’s procedures manual, which would be great if the auditors ever need information about spreadsheets.
The problem with that advice is that someone only reviews a spreadsheet at long intervals – when there’s a job change. To compress the review process a bit more, put a spreadsheet review on the department schedule at pretty long intervals – maybe once every year or two, and do it during a slow part of the year, so it won’t be skipped.
When scheduling these reviews on the calendar, pay particular attention to any spreadsheets that directly impact the financial statements. In other words, if a spreadsheet has the potential to really screw up the company’s numbers, review it before the end of the year, so that no spreadsheet errors end up in the year-end financial statements.
Another option is to require a complete spreadsheet review whenever you want to make a formula change to a spreadsheet. That means documenting what you want to change, and having someone else inspect the alteration to see if it does what you want it to do. This is quite a bit like a software coding project, where there’s a team review to go over code – except in this case, it’s a spreadsheet.
And another thing. When you want to change an existing spreadsheet, copy the old version to a separate worksheet, and then make adjustments. That way, if the new spreadsheet doesn’t work, you still have the original to fall back on.
When doing any of these reviews, take a hard look at the inputs to the spreadsheets. There’s a good chance that the report the information came from has changed over time, because no one told the person who wrote the report that it was being used as input to a spreadsheet. So if the report changes, that screws up the spreadsheet. And by the way, that also means adding information about spreadsheet inputs to that write-up of the spreadsheet that I mentioned earlier, that goes in the procedures file.
Now, these detailed reviews are way too extensive if you apply them to every possible spreadsheet, since every accountant on the planet has a few dozen of them, if not a few hundred. The point is to conduct a deep investigation on just the really massive or complicated ones. If a spreadsheet is only a simple list, then don’t bother with it.
I’d also like to make a case for the complete avoidance of complicated spreadsheets. When you dig into most types of accounting, there isn’t that much of a need for these types of spreadsheets. For example, keep cost allocations simple, because – why not? Does a slight refinement of an allocation really result in any actionable information? Probably not.
So. When all of these types of reviews I mentioned are going on, part of the dialog should be whether a spreadsheet is needed at all, or at least whether it can be simplified. Accountants being accountants, we all love complexity – but this is one case where I’m not sure we should.
My final word on the matter is that spreadsheet errors aren’t usually considered to be critical – until just after you realize that they’ve been feeding you incorrect results. So the usual approach to spreadsheets is to ignore them, and then have a crisis review, but of only the spreadsheet that was screwed up, and then everything returns to normal – until the next spreadsheet explosion. If that’s the way you want to manage it, just be aware of the downside.