Supply chain risks with spreadsheets.

Spreadsheets have conquered every office in the world and are used for a variety of purposes. From simple calculations to complete requirements schedules. Spreadsheets can be very useful, but also involve great risks for your supply chain.

Success spreadsheets.

The success of spreadsheets is fairly easy to explain. They are easy to use and clear. You don’t need weeks of training to use a spreadsheet. Click and work. Many users start with simple calculations and data recording and depending on the user, complex spreadsheets automatically develop that get a foothold within the organization.
Many ERP systems are too rigid to quickly produce specific information. Many ERP systems are a ‘one size fits all’ solution that offer too little functionality for specialist functions such as S&OP.

DIY attitude of many managers. Many managers like to be busy with data all day long. Slicing and dicing information looking for answers to their problems. Because they do it themselves and do not burden their organization, it is thought to operate effectively. Effectiveness that is negated because every manager creates his own reality with his own spreadsheets.

Risks spreadsheets.

The risks are great when using spreadsheets. especially if they are used by the entire organization. Some research facts:

• 90% of spreadsheets with 150 rows or more contain errors
• 94% of shared spreadsheets contain errors
• 57% of the authors of the spreadsheets did this without further training.

The most common errors found:

• Logical errors. wrong function or use of parenthesis.
• Copy formulas. Crossing out or copying formulas can lead to incorrect references that go unnoticed. Also use Dollar sign to fix formula
• Swap functions. Average ignores text and wrong entries. AverageA sees this as Null. This changes the number of arguments found in a row.
• Incorrect data entry. Enter numbers as text. Numbers become date or vice versa due to formatting errors.

Where formal software, computer programs are thoroughly tested, this rarely happens with spreadsheets. As soon as expected outcomes emerge, the spreadsheet is assumed to function well overall.

Another problem is that the spreadsheet designer is often the sole administrator. The moment that person leaves or leaves the organization, there is an immediate follow-up problem.

A large logistics service provider designed a spreadsheet for the largest customer to monitor costs and efficiency. The spreadsheet served as a settlement model against the standard rates. When I checked the spreadsheet, it turned out that labor costs had been entered as ‘hard’ input and the links to the calculations were broken. This has led to working with outdated collective labor agreement data for 3 years.

Supply chain management.

When you use spreadsheets to manage your supply chain, you run significant risks. Spreadsheets have great difficulty processing and analyzing large amounts of data. Even for medium size companies this is already a problem. And consider how complex the spreadsheet becomes when you start working with many SKUs. Often the data updates in spreadsheets are not properly implemented during ‘refresh’. What many don’t know is that you have to scroll through the data for the updates to take effect. This has to do with the size of your computer’s working memory. This does not generate any error messages. You simply don’t know. And then the problem of multiple storage locations, product categories and the different prices depends on your customer or distribution channel.

 

The use of spreadsheets also presents the necessary functional problems. There are organizations that place spreadsheets on a central server. It is not uncommon for spreadsheets to be linked in this way. But the management of the individual spreadsheets is not organized but individual dependent. Coordination, for example when it comes to data update and synchronization, is not or not properly arranged.

ERP systems can easily export data to spreadsheets, but reading spreadsheet information is often limited, time consuming and risky. This has to do with the preparation of records and control by the software itself. Some information is only read once by the system [during installation or full update].

The chain is only as strong as the weakest link. Your supply chain performance will depend on the weakest spreadsheet. If information is shared throughout the chain, you don’t want to depend on a malfunctioning spreadsheet, which you have no insight into.

Building in complex formulas and analyzes for processing large amounts of data is hardly possible with spreadsheets. Analyzes, forecasting of demand and stock, feedback of this to production and purchasing. But also analyzes for marketing and Sales with spreadsheets is a tricky business. This slows down your decision-making process. [time to decision]

Conclusion.

A standalone spreadsheet to quickly analyze limited data is easy to do with a spreadsheet. When it comes to using spreadsheets in the management of your supply chain, it is strongly recommended for every organization, large and small, to purchase specific software. You do not have to go for a one size fits all approach. Different software can work well with the right interface development. The use of cloud solutions is also worth considering, especially if your organization operates multinational. But whatever choice you make in this respect, don’t wait until a spreadsheet breaks down your supply chain and causes enormous damage.

 

Sources:
ClusterSeven, “Spreadsheet Risk Management within UK Organisations,” July 2011
Powell, Baker, and Lawson, “A Critical Review of the Literature on Spreadsheet Errors,” December 2007
Journal of Accountancy, “How to Make Spreadsheets Error-Proof”
KPMG Management Consulting, “Supporting the Decision Maker: A Guide to the Value of Business Modeling”
Ray Panko, Professor of IT Management, University of Hawaii, 2008, 2010.