Stop worrying about how sophisticated your spreadsheets are. What you (and your company) should worry about is how reliable your spreadsheet is. Here are ten things you can do to ensure your data is right and that you (and your company) aren’t depending on a unreliable spreadsheet.
Embarrassingly enough, I run both my personal finances and those of my consulting business from a couple of Excel spreadsheets. Obviously, I need those spreadsheets to be right — an unreliable spreadsheet (one with errors) could be disastrous to either my business or my personal credit rating. I really should go out and buy a real accounting package but, let’s face it, no accounting package is going to work in the peculiar way that I want. I have proof of that: Just ask my accountant who shows up every year to do my taxes and heaves a big sigh when he sees what I provide him with.
I’m not going to give up my spreadsheets, though. And I’m not alone in depending on spreadsheets: In one survey 85% of business leaders report they use spreadsheets for reporting, budgeting, and forecasting. Obviously, their businesses can’t afford for their spreadsheets to be unreliable.
The reason I find my dependence on spreadsheets embarrassing is because of the number of times I’ve made fun of people who have errors in their spreadsheets (if you’re interested here’s a list of errors that cost companies millions of dollars). Certainly, I’ve made some mistakes with my spreadsheets over the years (though my errors are in the “hundreds of dollars” range, not millions). I’ve entered expenses as revenue, for example, made changes in one part of the spreadsheet without making corresponding changes in another, and just entered some bad data.
I console myself by realizing that I’m not alone in making mistakes. Various studies have shown that error rates in spreadsheets range from 20% to 100%. I grant you that the upper limit of “100% of spreadsheets examined have at least one error” is only typical of spreadsheets with thousands of formulas – that’s not me and (hopefully) not you. However, that low number of “20% of spreadsheets examined have an error” is only achieved by ignoring errors that aren’t considered “serious.” In other words, the other 80% of the spreadsheets probably were unreliable, they just weren’t considered seriously unreliable.
Putting that altogether, it’s pretty much a certainty that your spreadsheets have errors. To use a Star Trek metaphor, spreadsheets are the Holodeck of business tools: You’re just as likely to shoot yourself in the foot as do something useful when you depend on unreliable spreadsheets. Recognizing that you have a problem is the first step in solving it and I’ve spent a lot of time making my spreadsheets into something I can trust.
Here are ten things you can do to make your spreadsheet more reliable (and if ten seems like a lot, don’t panic: You’re probably already doing several of these). I’ve saved the most important two to the end.
You may have noticed that I labelled that last section “Things You Can Start Doing.” That’s because keeping your spreadsheets from being unreliable is an ongoing battle.
For example, it took me two years of tinkering with one of my personal finance spreadsheets before I finally figured out how, for a sinking account that holds cash to be used for semi-regular expenses, I could check that the numbers were correct. It was only a month ago that I found, at Excel Help HQ, some code that I could use to ensure that all the items in my accounts receivable worksheet were correctly referenced in my cash flow worksheet. The tools we discuss in Learning Tree’s Excel Introduction course have been invaluable to me in helping me build good audit routines and eliminate opportunities for error.
Obviously, your company needs your spreadsheets to be right. Don’t let yourself or your company down. Think “reliable” before you think “sophisticated.”