Analyze and optimize your supply chain efficiency with comprehensive performance tracking
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
Optimisation starts with knowing where the money and the time actually go. This template lays out cost-to-serve and lead time by node so the expensive parts of the chain are visible before anyone changes anything.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Lane | Mode | Lead (d) | Cost/unit | Volume | Annual cost |
| 2 | CN → EU (Rotterdam) | Sea | 34 | 0.84 | 412,000 | 346,080 |
| 3 | CN → EU (air) | Air | 6 | 4.10 | 38,000 | 155,800 |
| 4 | EU DC → FR | Road | 2 | 0.31 | 188,000 | 58,280 |
| 5 | EU DC → DE | Road | 2 | 0.29 | 164,000 | 47,560 |
Why each one is written the way it is
=[@[Cost/unit]]*[@Volume]
Annual cost per lane. Ranking lanes by total cost rather than unit cost is what identifies where effort is worth spending.
=[@[Lead (d)]]*[@[Daily demand]]*[@[Unit cost]]
Pipeline inventory value — capital tied up in transit. Long sea lanes look cheap per unit and are not once this is counted.
=SUMPRODUCT(Lanes[Lead (d)],Lanes[Volume])/SUM(Lanes[Volume])
Volume-weighted average lead time. The plain average flatters you by giving a rarely-used air lane the same weight as the main sea route.
=ROUNDUP(AVERAGE(Demand[Daily])*([@[Lead (d)]]+ReviewPeriod)+SafetyStock,0)
Order-up-to level per lane, which is where lead time turns into working capital.
A worked example with real numbers
An importer moved 8% of volume by air at five times the sea rate and considered it a failure of planning. Adding pipeline inventory to the comparison changed the picture: the 34-day sea lane tied up €71k of capital continuously against the air lane's €4k. The air share was not the problem — it was the only thing keeping stockouts down while the sea lead time went unaddressed. Negotiating a 26-day lane saved more than eliminating air freight would have.
Tab by tab
| Tab | Contents |
|---|---|
| Lanes | Route, mode, lead time, unit cost and volume. |
| Demand | Daily demand by destination, driving safety stock. |
| Inventory | Pipeline and safety stock value per lane. |
| Scenarios | Side-by-side comparison of routing options. |
| README | What is included in cost per unit and what is not. |
What is included, and what to look at next
Specific to this workbook, not generic
Freight, duty, insurance and handling — everything that varies with the shipment. Excluding duty is the most common omission and it can be 8–12% on its own.
By including the capital tied up in transit. Sea is cheaper per unit and holds far more of your money for far longer; the comparison is meaningless without it.
For comparing a handful of defined options, yes. For genuine multi-echelon optimisation across dozens of nodes, you need a solver — the spreadsheet is where you prepare and sanity-check the inputs.
"This optimization tool has helped us identify and eliminate inefficiencies throughout our supply chain. We've reduced costs by 25% and improved delivery performance significantly."