The hidden threat: spreadsheets in the supply chain

© PBNJ photo - Fotolia.com

But there is one glaring exception: supply chain management for tracking assets from mine to customer. All too often, companies rely on a patchwork of generic spreadsheets to manage the tonnage, quality and value of their coal or mineral supply chains from the point of extraction to the point of export, import, or consumption.

Ten years ago this spreadsheet dependence was common across industry sectors. But over the years, oil and gas multi-nationals, pharmaceutical companies, and automotive manufacturers among others have recognised the inherent limitations of spreadsheets as an enterprise solution. Instead, they have moved on to more sophisticated technology solutions that are designed with the particular requirements of their business in mind. It is these more sophisticated solutions that enable them to precision-match global networks of supply and demand, manage the logistics of international distribution and respond to the volatility in commodity prices and exchange rates.

Negative impact on profits

In contrast, firms mining for coal and minerals are all too often locked into idiosyncratic spreadsheets and obscure, one-off macros for tracking, monitoring and analysing critical operational processes. Spreadsheets are not appropriate for handling mine planning and scheduling, material tracking, logistical movements, stockpile modeling, grade control and blend management but are relied on to do precisely that. In the face of strong demand-driven growth, the negative impact on profits has largely gone unnoticed. Eventually the impact will become clear, either because the spreadsheets reach the limit of their capabilities, errors are discovered internally, or worse, an external auditor finds fault. But at that point it may be too late to sufficiently rectify the damage done.

The underlying problem with spreadsheets is that they provide a comforting illusion of automation, when in fact they are only a small step up from manual processing. The typical spreadsheet is often built as a personal tool by self-taught business users, addressing very specific needs in localised circumstances and not providing broad or deep insight into business activities. Spreadsheets provide none of the benefits of full automation such as enterprise-wide transparency, decision support, and straight through processing. Furthermore, because spreadsheets are not a truly automated solution, they render a business overly dependent on the initial creator. When that individual is on leave, or simply out of communication, the spreadsheet ceases to become a useful tool - and becomes, instead, an operational security risk.

The risk of having years of business operations and rules stored only in the brains of individual staff members - or in the spreadsheets they use - should be apparent. Instead of having an intuitive system that enables simple data extraction, transfer, visibility, performance measurement, and reporting, managers are struggling to find information that should be at their fingertips.

94% of spreadsheets contain errors

The risks and consequences of inappropriate spreadsheet use are well documented, both in studies conducted by Triple Point Technology and by independent academics at the University of Hawaii such as Raymond R. Panko, author of the report What We Know about Spreadsheet Errors. His statistics show that as many as 94% of spreadsheets contain errors, including input errors, logic errors, interface errors, and incorrect cell range errors. These errors, even those that are significant, often go undetected because spreadsheets are usually created by non-programmers that don't conduct formal and rigorous testing procedures.

Because of spreadsheet errors, mining companies may find themselves attempting to negotiate freight rates, shipping, and insurance costs or streamline vendor management on the basis of inaccurate data. Any attempts to improve mining model predictions that would impact supply chain performance such as densities, moisture, metallurgical analyses, stockpile quantity, plant quality, and yield forecasts are likely to fail, thanks to the inaccuracy of the underlying information.

The scariest part of these errors is that many mining companies function without the knowledge that a problem even exists, giving them a completely inaccurate picture of day-to-day operations and affecting all aspects of the supply chain including cost management, scheduling, and quality management. Without the necessary data validation and control, errors spread throughout key corporate processes that control hundreds of millions of dollars of inventory, putting the entire company at significant operational risk.

Considerable compliance risk

Furthermore, these companies are unknowingly putting themselves at considerable compliance risk. For example, regulations around the world require corporations to have well-controlled financial reporting systems. Just one instance of a company having an error in its reporting system will require independent auditors to give the company a negative opinion. This can result in lower stock prices and, in some cases, fines and prison sentences for executives. If this isn't enough, other spreadsheet-related problems include:

• Inadequate data capacity: The essential lack of automation means that spreadsheets are simply not enterprise systems. They do not have the capacity to deal with today's volumes of data, and their performance is compromised once those data limits are reached.
  
• Lack of integration: Spreadsheets do not provide integrated, timely feedback and communication loops across functions. Because they aren't designed to support integrated planning, they offer no opportunity to improve efficiency and throughput.
  
• Siloed information: Spreadsheets are not designed to be accessible to or used by large numbers of individuals. Instead of overcoming siloed information and processes, they encourage it. The goal of having visibility both upstream and downstream of the value chain remains an unfulfilled dream.
  
• Hampered business processes: Dependence on inadequate and inaccurate data makes basic business processes such as quality control, trading, scheduling, and reporting difficult to achieve. And in the mining world, companies need their systems to do much more than that. If a technology tool cannot be relied on to produce an accurately weekly report, then there is little chance that it can handle the more complex tasks such as supporting accurate ore volume and quality forecasts, or reconciling ore material and metallurgical accounting in order to understand losses and improve recoveries.

It's imperative that mining and mineral companies replace spreadsheets without delay to mitigate risk as well as effectively manage and capitalise on favourable economic conditions. Driven largely by rapid population and income growth in developing nations, worldwide coal consumption rose by 7.6% in 2010 and is expected to continue growing. In the past decade alone, China's coal consumption more than doubled. India also has large and growing energy requirements, relying on coal for 55% of its needs. To effectively compete for these markets, companies need efficient, streamlined, cost-effective, and reliable operations, which cannot be achieved with spreadsheets.

Dedicated supply chain management systems

To this effect, some of the biggest names in the industry now deploy dedicated supply chain management systems, designed to optimise and manage an end-to-end coal and mineral supply chain. These solutions empower them to mitigate risk and maximise profits by providing enterprise-wide access to accurate information, automating manual processes, optimising resource allocation, providing advanced decision support tools, and much more. These companies are ready and able to successfully manage the expected increases in demand, and are poised to significantly outperform those companies that continue to rely on spreadsheets. Spreadsheet-dependent companies that don't act now to upgrade their rusty tools and processes are putting their businesses at considerable risk. With the global demand for commodities expected to remain strong, these companies are in serious danger and will struggle to deliver profitability, if they survive at all.