Although many employees are not properly trained to use them, spreadsheets are used by the a huge number of businesses worldwide to manage data. In this article, Bursa explores the difficulties and issues with companies exclusively relying on spreadsheets to manage their data and supply chains, causing problems with key things like scale and real-time view.
Spreadsheet use pervades every corner of corporate life today. More than 200 million people worldwide use spreadsheet applications and about one-third spend more than half their working days using them.1
Spreadsheets provide great benefit and are relatively simple to use. While it may be easy to get an answer from a spreadsheet, it may not be easy to get the correct answer. Researchers estimate that 90% of spreadsheets with more than 150 rows contain errors. As a supply chain matures, spreadsheet-related risks such as scalability, integration, fragility, and siloing can have a significant impact on the business.
Supply chain management teams can find themselves severely hampered by chronic spreadsheet shortcomings when performing mission-critical activities such as demand planning, sales and operations planning, manufacturing, replenishment and supply planning, inventory optimisation, and value chain collaboration. (See Figure 1)
In this article I will explore the reason why we have such a reliance on spreadsheets, the inherent problems and limitations, and one company’s journey away from spreadsheets.
Where Did All These Spreadsheets Come From?
Most managers know how to build spreadsheets, and the application is on everyone’s computer, ready to go at a moment’s notice. Self-trained spreadsheet jockeys, when faced with the problem of organising data and calculating future requirements, grab the tool they know.
Eventually these spreadsheets proliferate, driven by:
• Inability of the organisation’s ERP system to meet the need;
• A ‘stop-gap’ urgency to gain control over supply chain data.
Inherent Problems and Limitations of Spreadsheets
Some problems with spreadsheets are universal, and can impact any business that relies on them. A study covering 1,500 people in the UK2 found 57% of spreadsheet users had never received formal training on the spreadsheet package they use. Users are not aware of the inherent problems with or the limits of spreadsheets. (See Figure 2)
Chronically Prone to ‘Pilot Error’
In supply chain management, the impact of a single error can derail a forecast, cause a supplier to ship too much or too little of a particular product, or cause service level nightmares that can lose a customer. Researchers Powell, Baker, and Lawson estimated that roughly 94% of spreadsheets deployed in the field contain errors. Compared to these findings, a University of Hawaii study that estimated 20% to 40% of all spreadsheets contain errors seems positively upbeat.
Unavoidably Hard to Maintain
When someone authors a spreadsheet layout, they are essentially creating a nonprocedural computer program. Nonprocedural programming offers as many chances for mistakes as procedural programming, yet spreadsheets are rarely checked or tested thoroughly, and are usually rolled out without a formal quality assurance process.
Over time, supply chain spreadsheets often gain features such as conditional formatting to flag exception conditions, charts and graphs to visually display data, analysis components to identify trends, and more. Multiple spreadsheets may be linked together in order to feed results upstream and downstream, or to drive high-level business decisions. Each enhancement adds risk, especially if the spreadsheet’s original design requires major revamping to add the new features.
Vulnerable to Single Points of Failure
A spreadsheet often reflects the work of essentially one person. If that person leaves the company or changes roles, that spreadsheet becomes an unsupported single point of failure. If there are multiple spreadsheet authors around the supply chain organisation, then there are multiple potential single points of failure.
Major Spreadsheet Problems in Supply Chain Management
Every time you turn to a spreadsheet in order to handle some aspect of supply chain management, you open a can of risk. Real-world supply chains have management challenges that cannot be adequately addressed by a spreadsheet approach – challenges such as variable lead-times, unpredictable demand, suppliers who miss due dates, production lines or outsourced sites that don’t always co-operate, multiple streaming sources of data each with its own impact on dynamic global business objectives, et cetera. Spreadsheets simply cannot comprehend these factors.
The Scalability Problem
Not only do today’s complex supply chains require businesses to anticipate market changes and respond quickly to real-world events; there are an ever-increasing number of users who need to share data, and a growing volume of stock keeping units (SKUs) to plan for. According to Chainlink Research, “Spreadsheets provide some capability for users to be creative and think through some ideas. For simple tasks, and for businesses with small SKU counts and low complexity, they might work to a degree. But they just don’t scale.” 3
Other issues related to scale include the growing size of supply and demand planning hierarchies, which cover huge numbers of items, from SKU location to product categories. Multiple forecast models are needed to successfully predict demand for each lifecycle stage of every product and market. Planning accuracy should be measured over multiple time intervals (three months for sales, eighteen months for finance, six to twenty-four months for supply planners, for example). This is well beyond the capability of spreadsheets.
The Functional Isolation Problem
Even organisations that place their spreadsheets on a central server can’t ensure that collaboration behaviour takes root across functional silos. Most organisations using the spreadsheet approach find that each department, geographical location, or even individual worker may be relying on a separately managed and maintained spreadsheet. When crucial supply chain activities like demand management, inventory management, production scheduling, supply and replenishment operations, and transportation planning are all performed in isolation with the results expressed in parochial terms, nobody’s numbers ever seem to agree.
To the extent that spreadsheet reliance increases isolation, it works against collaboration initiatives and demand-driven processes like sales and operations planning (S&OP). One of the first steps in any S&OP project should be to evolve away from departmental spreadsheets toward a unifying supply chain management solution that provides one accurate model of the supply chain, and one set of facts based on up-to-the-minute data.
The ERP Integration Problem
Planning tools must exchange information such as inventory levels, orders, supply-side information, and more with the company’s existing ERP system. The reality is that data loading into spreadsheets is always a non-real-time, manual process, very slow and prone to data entry errors. Today’s advanced supply chain solutions provide template-assisted integration tools that streamline set-up time and minimise implementation costs.
The benefits are worth the effort. A supply chain management solution can incorporate many streams of data (for example orders, shipments, inventories, bills of materials, manufacturing costs) from existing systems around the company, and provide a degree of supply chain optimisation far exceeding anything that can be accomplished with spreadsheets. Supply chain management (SCM) applications increase service levels, minimise inventory, improve planning efficiency, handle demand uncertainty and supply volatility, manage product life cycles for maximum profitability and availability, and drive fact-based S&OP decisions.
The Fragility Problem
To perform integrated supply chain planning, companies must connect multiple groups within an organisation (from planning and operations, down to supply chain and procurement). Global supply chain management requires extending supply chain visibility to the outside world (i.e suppliers, customers) to increase the speed of activities from asset recovery to recycling.
When functional groups attempt to feed information (such as demand forecasts) to the spreadsheets of other functions (like supply planning), the chain is only as strong as its weakest link. Linked spreadsheets become cumbersome to maintain. The information ‘conduits’ flowing from one sheet to another enable cascading error conditions that can prevent users from obtaining critical results on time. The probability that a link in the spreadsheet chain is bad exponentially increases the single-point-of-failure problem.
The Slow ‘Time-to-Decision’ Problem
To gather, assess, and evaluate information held in spreadsheets is a slow, manual process. Lacking real-time alerts triggered by exceptions in order to target value ranges for key performance indicators, SCM are forced to manually monitor hundreds, or many thousands, of items. They can literally spend all day perusing spreadsheets for signs of trouble, big and small.
Executives shouldn’t have to get their picture of what is going on through batch status reports in Microsoft Excel or PowerPoint, nor should you have to request frequent custom report runs to answer questions.
With spreadsheets, the risk is 100% that critical out-of-balance conditions will be missed or major deviations detected late. Crucial business decisions should be based on real-time access to up-to-date supply chain data, and management reaction times should be driven by automated alerts that let planners prioritise efficiently and manage by exception.
Moving Beyond Spreadsheets
A North America-based shoe company supply chain manager described their style-by-warehouse forecasting regime as a ‘complex nightmare,’ with 450 pages of spreadsheets to review every month. The litany of problems included error-prone manual data entry, limited forward visibility, unreliable information, no tracking of changes or why they were made, no collaboration with sourcing and manufacturing teams, and forecasts that were four weeks out-of-date when shared.
One typical error involved adding new products to the spreadsheet but forgetting to bring the subtotal up to the top. The new products were therefore not included in the forecast at all.
The global manufacturer and retailer left its spreadsheets behind and began a transformative process that included moving to an advanced supply chain solution from Logility.
The company was able to automate data flows for processes like inventory changes and orders, from ERP and other sources, deliver individual real-time views customised to each stakeholders’ role within the company (finance, sales, suppliers), create a forecast in three days (versus the over fourteen days it had previously taken) —including multiple forecasts for locations around the world fed automatically into the ERP system. The company saw several additional benefits including:
• Support for eighteen-month rolling demand plans;
• Weighted mean absolute percentage error (WMAPE) dropped by 15%;
• Data export for key suppliers completed in three minutes rather than several hours;
• Reporting time cut from hours to minutes.
Supply chain team members now spend their time as analysts, not data manipulators, getting accurate information faster and using their time more wisely. Relying on spreadsheets to perform supply chain management involves a host of pitfalls. While it may not make sense to completely remove spreadsheets from our professional lives, there are times when they can be more of a hindrance than a help. Companies that continue to rely on spreadsheets in order to power their supply chain will be left behind, and find themselves throwing away the opportunities to drive substantial improvements in forecast accuracy, visibility and service levels while also reducing working capital.
About the Author
Karin L. Bursa is a vice president at Logility, a provider of collaborative supply chain management solutions. Ms. Bursa has more than 25 years of experience in the development, support and marketing of software solutions to improve and automate enterprise-wide operations. You can follow her industry insights at www.logility.com/blog. For more information, please visit www.logility.com.
References
1.Kugel, R. (June 2012) ”Make Spreadsheet Competence a Priority,” Ventana Research
2. ClusterSeven (July 2011) “Spreadsheet Risk Management within UK Organisations,”
3. Grackin, A. (January 2014) “Is the Crisis in Demand Management Being Exacerbated by Spreadsheet Dependency?” ChainLink Research
