The Pitfalls of Using Excel to Manage Your Allocation
Date: 14 June 2022

The global supply chain continues to endure massive challenges, including lacking capacity and failure to utilize such capacity fully. According to the Drewry World Container Index, rates declined in early June 2022 to $7,625.56 per 40ft container (FEU). While that’s a positive movement, it’s still radically higher than the five-year average of $5,214. Meanwhile, demand for oil only continues to rise, and major news outlets have covered the continuing shortages in the industry, from baby formula to electronics. These rates also pose a problem for importers and exporters.

Higher rates mean that optimizing contract utilization, the actual use of contracted vessel capacity, is crucial. Improper utilization essentially leaves money on the table, and carriers are more likely to view those with under-utilization rates less favorably in future negotiations. 

Unfortunately, part of the problem rests with how these shippers use and apply data, with many still relying on legacy technology, particularly Excel or other spreadsheets, to track it all. That alone gives rise to other problems that compound in poor allocation management, but where? Let’s look at the pitfalls of using Excel for allocation management and why a better strategy depends on effective data management.

Data Is Static

Today’s supply chain is dynamic, generating massive changes to typical datasets every moment. Traditional supply chains, linear and straightforward, could leverage Excel to track allocation management, but rising costs mean optimization is everything. Yet, Excel data is static and only changes upon manual intervention. 

The Harvard Business Review noted, “One of the most important business lessons is also the simplest: success is often the result of making more good decisions than bad ones over time. The question is how to do that.”

In other words, the question is what systems help businesses make more effective decisions, and yes, there is automation available, conditional formatting, and online tools to collaborate within Excel. However, the root problem remains: the data is static and insurmountable to manage by hand. 

Data Can Be Missing or Inaccurate

Another critical flaw with using Excel comes from the mismanagement of data itself. Data decays at approximately 60-70% annually, depending on the survey source. Outdated data may seem trivial, but making decisions based on obsolete data is worse than making blind decisions. 


The answer is surprisingly simple and impactful. 

If a shipper were to assess cargo allocation at rates for February 2022 months ago, the cost per 2-TEU could quickly look closer to $10,500. Yet, today’s rates are approximately $2,000 lower, depending on trade lane and VOCC. 

Imagine the potential impact of planning 200 container moves at that premium cost; it’s a $40,000 higher expense. Suppose the shipper assumes that they must pay a $20,000 cost for failure to meet allocating space requirements while also seeing current rates lower with a carrier’s competitor. In that case, the ideal solution seems to swap carriers. 

However, the proper insight into today’s allocation data and cost structure would reveal that the secondary carrier’s costs are akin to today’s market, almost matching the secondary carrier while eliminating the risk of an added cost. 

Ergo, the total landed cost for the original carrier’s commitment would be closer to $100,000, while the secondary carrier cost would be $100,000 plus the $20,000. 

Worse still, the shipper believes that the established contract is out the window, resulting in higher costs to allocate more capacity to the secondary carrier. In a sense, the original ocean contract fails.

And this all happened due to poor accuracy of data and an assumed overextension of allocated capacity.

It’s Time-Consuming for Teams

Using Excel for managing freight allocation also comes with another cost—higher FTEs. If it takes the shipper’s in-house team 2 FTE positions at $20/hour to update the data based on the past month’s loads, that’s an expense of approximately $6,400 more per month or more than $80,000 annually. Still, that’s data running a month behind the present, resulting in more assumptions, poor visibility into actual costs, and potential fallout with preferred carriers.

The problem also worsens as human error is always present, resulting in mistyped buttons, incorrectly placed commas in numbers, and more. Again, data grows old and harmful to today’s needs. But better performance can start with understanding your available capacity, allocating your freight to it, and tracking everything with real-time data. 

Boost Allocation Management and Contract Performance With NYSHEX

The solution to the problems of using Excel relies on accurate, on-time, and complete datasets. In allocation management, accurate data can help shippers save tens of thousands of dollars, if not more. And it also allows shippers to manage their contracts, ensure carriers understand their obligations and figure out what’s best for downstream supply chain planning needs. It starts with evolving your tech stack into something more dynamic, complete, and ready-to-roll on day one. That’s the NYSHEX difference in allocation management and digital infrastructure.

Connect with an expert in allocation management and contract performance, and see NYSHEX in action by requesting a demo today.