Takeaways:
- The suitability of applications for spreadsheets involves two considerations: the consequences of mistakes and execution risk.
- Decide which applications are suitable for spreadsheets or other tools.
- Remove risk from existing spreadsheets.
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 notice 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.
With complicated calculation lasting through the eleventh hour, meaningful review and decision making was nearly impossible. Execution was compromised by the proliferation of versions – it was too easy to create new marching orders based on idiosyncratic assumptions or agendas. While this calamity floated on an undisciplined and process-poor operational ocean, the root cause mistake here was not quickly recognizing and addressing the need for scalable infrastructure. When building a handful of service nodes, spreadsheets seemed adequate. When deployment scale increased to thousands, millions of wasted dollars and months of delay in delivering service and revenue ensued. A new Supply Chain Executive had the daunting job to establish basic infrastructure during large-scale chaos.
Protecting Your Company
Protecting your company takes two steps:
- Deciding which applications are suitable for spreadsheets or other tools
- Removing risk from new and existing spreadsheet-based processes
The suitability of applications for spreadsheets involves two considerations, as in the table below: the consequences of mistakes, and execution risk.
The digital service site build-out example clearly belongs in the upper right (red) quadrant, where both consequences and execution risk are very high. Spreadsheets should not be used here, and existing ones should be phased out quickly. The other examples were situations where consequences were high, but execution risk was lower, and hence caution (yellow) was needed. With appropriate processes and reviews in place, forecasting Support Center demand was made to work well. In the case of over-ordering seasonal print materials, once again the demand forecasting piece can be made to work well with reviews and process control. However, it is almost always a better idea to limit the spreadsheet to forecasting, and manage planning and procurement activities within an ERP system where the benefits of integrated data and process safeguards outweigh the flexibility needed in forecasting.
Assessing spreadsheet-based execution risk is the mirror image of reducing those risks. The framework in Figure 2 facilitates both: the horizontal dimension depicts stages of spreadsheet-based operations, and the background represents the people and process context in which any process is run. This framework is a useful categorization and provides a checklist for assessment and mitigation efforts.
Using the framework above, several best risk management practices are listed below. Many of these apply beyond spreadsheets. Some truths about information and execution are universal.
- Establish systematic review of assumptions/changes – propagate understanding broadly
- Reduce the number of data dependencies and human touches
- Verify data integrity and accuracy early each cycle
- Establish procedure and automate steps, especially data handling
- Test outputs against independently known/verified results – and retest when changes are made
- Institute regular management reviews of spreadsheet results for business sensibility
- Impose appropriate degree of control on spreadsheet distribution/version proliferation
- Increase the number of people understanding the spreadsheet structure/methods
- Raise staff quality; spreadsheet-based processes require more broadly skilled people than enterprise applications, for the same risk
- Improve standardization and quality of surrounding processes
Spreadsheets are ubiquitous in business, and they are not going away. Ensure they are useful tools rather than hidden risks in your business.
Bill Schneiderman is the founder and CEO of The Results Group. Building on his experience as a university professor, line manager and consultant, Schneiderman and his team have built a client list that includes many of Silicon Valley's established and emerging companies, along with selected companies in traditional industries. The author can be reached at [email protected].
