3 Eye-Opening Questions You Need to be Asking About Your Spreadsheets
Studies agree – about 88 percent of Excel spreadsheets contain significant errors. If your company is still using spreadsheets for financial data processing, there’s a very good chance most of your spreadsheets contain errors that are affecting your company. While a few errors in small spreadsheets might not have a significant affect on your business, you’ll see a higher financial impact from errors if your company is using more complex spreadsheets and/or a large number of unique spreadsheets.
1. Not “If,” but “How Many Errors?”
The introduction to “What We Know About Spreadsheet Errors” by Raymond R. Panko of the University of Hawai’i states that “errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists.”
Though errors only show up in a small percentage of all cells, most spreadsheets are complex enough to contain a high enough number of errors to significantly impact the spreadsheet’s reliability. It’s not unreasonable to assume that almost 90% of your company’s spreadsheets contain serious errors.
Of course, there is a snowball effect that goes along with spreadsheets. One small error at the start of a long series of formulas will amplify the mistake throughout the whole document. In a small simple calculation, it’s just a drop in the pond. At scale, things are very different.
For an oversimplified example, say you make a widget that requires 2 parts. Your spreadsheet says the parts cost $.55 and $.45 for a total cost of $1.00. After manufacturing, packaging, etc your total cost is $1.75. You’ve tested the market and found the ideal price to be $2.00, so you’re making a quarter per sale. Based on demand, you order 100,000 of them. There’s only one problem. The parts actually cost $.55 and $.75 for a total cost of $1.30. That bumps your total cost up to $2.05 per unit. Now you’re in a sticky situation. At the ideal price, you’re losing five cents per item, and if you raise the price, there isn’t enough demand to sell 100,000.
This example might be silly, and yet it highlights how even a simple typo could cost you. And that’s in a very simple system with very few calculations and low-cost items. In a more complex system where many formulas were dependent on that cell, the damage could literally cost billions! (yes, Billions with a “B”)
2. What Causes Spreadsheet Errors to Happen?
While the error rate for spreadsheets seems high, it is consistent with human error. It’s very easy to make a mistake when manually transferring data. Type one number wrong, use an incorrect formula, or leave something out, and it can compromise the entire document.
There are three main types of spreadsheet errors, and all are common. Mechanical errors occur when someone mistypes a number or points to the wrong cell. Logic errors result when incorrect reasoning leads someone to use the wrong formula. Omission errors mean that something was left out of the spreadsheet, and these errors are the most difficult to detect.
3. How Can Spreadsheet Errors Be Eliminated?
Ignoring spreadsheet errors can result in billions of dollars lost. For companies that persist in using spreadsheets, fact-checking and error correction are essential. This generally means at least two employees checking all the work done on each spreadsheet. User training can also cut-down on the amount of errors entered in a spreadsheet.
There’s a better solution, though, one that doesn’t rely on human employees being able to detect another human’s error. Enter your data into a system, not a spreadsheet. Unlike spreadsheets, business process systems automatically fact-check and update in real-time so all employees working on the system have access to the latest information entered. Tolerances can be set to identify values that are outside of the expected range and systems can cross reference to identify manual entry errors. Since critical formulas and calculations are programmed into the software, you eliminate many risk factors such as wrong formulas, mistaken logic, or data omission.
NextProcess’s software solutions automate many manual processes and are designed to reduce or eliminate the role of spreadsheets, work with existing ERPs, and let your company generate accurate, useful reports. Contact us today to learn more and schedule a demonstration.