Optimized Network Capacity Planning

Behind Every Supply Chain Are Lots of Spreadsheets

Behind Every Supply Chain Are Lots of Spreadsheets, SupplyChainBrain (June 19, 2024)

It’s more than just a joke: Spreadsheets are still the go-to planning software in the supply chain industry. Whether in plain sight or behind closed doors, supply chain professionals rely on spreadsheets to navigate existing systems, devise new ones or just get the job done.

Following are some real-world scenarios that continue to require the use of spreadsheets.

Warehouse staff are challenged to determine when to release work to the floor and who should work on it. They also need to create a crew roster. Unfortunately, current tools such as warehouse management systems offer limited support. Managers gather information from various sources to bridge this gap, and use Excel to create manning schedules for the upcoming days. They also rely on spreadsheets to analyze current orders, and decide where to deploy staff and which shipments to prioritize.

Approaches to forecasting take several forms:

  • Maintaining an alternative forecast because the company-wide forecast has been driven more by sales targets than by reality.
  • Tweaking the numbers from the forecasting system to incorporate their own knowledge. This may be valid if the forecasting system does not have certain information that is difficult to translate into things it understands.
  • Producing a forecast manually where no automated system exists.
  • Using spreadsheets to determine how things should be deployed to customer-facing warehouses. One company using a very complex planning system found that despite involving all the experts, the system didn’t consistently handle specific products. In some cases, it didn’t plan the items at all. Under pressure from lost sales, managers were forced to turn to spreadsheets to create their planning system.
  • Load building. The same company mentioned above had planners create loads based on the simple approach of managing weight and pallets. Another used the Linear Programming solver in a spreadsheet to create an almost-optimized-shipment. It was better than manual, but not as good as the top-class systems on the market.

As Alan Rencher, chief technology officer at dental software provider Henry Schein, recently said in the Wall Street Journal: “There may be legacy users that are mistrustful of big data systems and want to completely control their data.”  (Author’s note: I think the word “may” here is misplaced. Some users want to hold onto their data.)

Why Are Spreadsheets Everywhere?

There are several reasons people head for spreadsheets. They trust a spreadsheet and can see exactly what has driven the results. Sometimes, they can create their own systems where none exists within their company or commercially. Looking at product deployment across its network, for example, a large consumer packaged goods company saw many problems because its planning system didn’t consider how full the receiving warehouses were. It used a spreadsheet to project site inventories over time and indicate each week that specific sites were in critical shape. This enabled the company to instruct planners only to ship products that were “most needed.”

Often, the systems that the spreadsheet augments already exist. However, they are seen as intractable to work with or incomprehensible, or the data is wrong, or the system is misconfigured. The example above, where a large, well-known planning system was, for no apparent reason, not planning a few products, is an excellent example of the “I must have a spreadsheet” mentality.

Unfortunately, in some instances, the IT department can be held responsible for the very thing it despises: the proliferation of spreadsheets. This can happen in multiple ways, but the most common are:

  • Buying the wrong system that doesn’t meet the users’ needs.
  • Not replacing an outdated system.  
  • Avoiding “system proliferation” by refusing to buy from system vendors who can meet the requirements because of the misguided belief that having fewer vendors is overall better for the corporation. Unfortunately, the IT department often makes this decision without fully understanding the impact on operations.
  • Placing modifications last on a long list of enhancements. Operations don’t get the needed functionality promptly, so a spreadsheet is created. By the time the fix is put in, the spreadsheet is a fixture that doesn’t go away.

This last point is important. People hate change, and as such, they prefer to stick with the familiar.

What’s Wrong With Using Spreadsheets?

Spreadsheets can have drawbacks that hinder efficiency and accuracy. They’re not good at data management. Following are some reasons that a system of record, where it exists and provides the needed functionality, is better:

  • Spreadsheets lack the reliability and structure provided by dedicated systems of record. (Did you always remember to save the data?)
  • Spreadsheets can contain potential errors due to manual data entry and formula misinterpretation. Have you ever seen people open a spreadsheet, only to dismiss mistakes like “circular reference”? This can lead to miscalculations and inconsistencies, which may propagate throughout the business processes. By contrast, systems of record offer data validation and standardized processes that minimize such errors. 
  • Spreadsheets often lack real-time data access and centralized management, making it challenging to maintain data consistency across departments. By contrast, systems of record provide a single source of truth, allowing for efficient collaboration and streamlined data sharing within an organization. 
  • Spreadsheets can become cumbersome and slow when handling large datasets, leading to performance issues and reduced productivity. Systems of record, on the other hand, are designed to handle large volumes of data effectively, providing faster and more reliable data processing and analytics capabilities. 

More recently, companies seeking to expand their use of artificial intelligence have been surprised that the data is only available in a spreadsheet. This is a significant blocker to modernization.

What’s the Alternative? No silver bullet acts as an alternative to spreadsheet use. Instead, it’s a matter of corporate-wide dedication to supporting users with tools that meet their needs, are correctly configured, can be trusted, and provide quick responses to the turbulent business environment or changing users’ needs. Software vendors have their part to play here, too. They must offer user interfaces that are much more like the beloved spreadsheet, and enable users to quickly tailor the information they need in the way they need it.

Like them or hate them, spreadsheets will be around for a long time, and will continue to be the primary tool planners use everywhere.

Tom Moore is chief executive officer and founder of ProvisionAi, and founder and chairman of AutoScheduler.AI

Download Our eBook

ebook about transportation planning