Many executives find out the hard way how damaging poorly managed spreadsheet applications are in critical business processes. Early attention is focused primarily on applications in Finance departments. But this is only the tip of the iceberg, as the following personally witnessed operational scenarios attest:
- A high-volume software company unwittingly threw $20 million annually into the scrap bin. The culprit was a hidden, and devastating, spreadsheet logical error that systematically triggered over-ordering seasonal printed materials.
- A financial services firm underestimated support center demand by over 250 agents, roughly 25 percent of total demand. Only very fast action was able to turn around customer comments like, “I can’t get tech support and I hate your company!” Formula errors and inappropriate assumptions had negated demand associated with deploying a new service platform.
- The national build-out of a digital service network was beset with months of delays and millions of dollars in lost revenue. Spreadsheet-based material planning and execution tools were overwhelmed with the size and complexity of the job.
The scenarios above took place in sizable public companies, some with “process excellence” departments, and “best places to work” awards. While many managers believe it couldn’t happen to them, it happens too frequently, even in good companies, to take safety for granted. Management is usually unaware of the issues until crisis is upon the company. To ensure that you and your company are protected, understand how spreadsheet calamities come about and what can and should be done to mitigate risks.
Why Spreadsheet Issues Arise
The origin of using spreadsheets for handling operational calculations is benign. These devices deliver management value through very quickly prototyping calculations for many business situations. Not only are they quick, but they are also flexible enough to easily consider multiple scenario variations. And with many logical, statistical, and other functions now built in, there is considerable power available as well. It should not be surprising that employees will turn to spreadsheets when they need to “just get the job done,” especially where the investment in more robust tools seems unattainable or too distant to help. While the initial spreadsheet impulse may be good, calamities incubate when an application is extended to problems that are too large and complex. The consequences of mistakes are too big and process controls are not pursued.
The high-volume software company mentioned above made several critical errors contributing to wasting $20 million annually. The spreadsheet not only included forecasting information, but extended all the way to procurement of materials. Spreadsheet authors neglected to include materials already on order as part of supply. The result was every time the spreadsheet was reviewed, it signaled yet another unnecessary buy. The misguided calculation method was only the initial problem. Why was it that no one noticed inventory piling up and that scrap costs were so high? Inexperience, incompetence and disengagement of the people, along with the lack of process controls, contributed to the outcome. When a new executive with better Supply Chain chops questioned the reasonability of what he was seeing, the root cause was discovered.
The financial services firm provides an equally stark demonstration of the need to bring business sense and a rigorous assumptions-vetting process to bear. Would demand for Support Center agents stay constant with a major service platform rollout?
The first two examples illustrate calculation, review and reasonableness checking; the digital service build-out case deals with a much broader set of issues and risks. Most of the Supply Chain ran on spreadsheets, with Purchasing execution and Financials done through ERP. With data either in many silo spreadsheets or in a disconnected ERP, data extraction/collection for weekly planning was lengthy and error-prone. Are those inventory spreadsheets current at the same moment, and how does that square with open purchase orders? Did we remember to include the open purchase requisitions and material in transit? Data collection and handling were major issues that caused many planning mistakes.