Your forwarder is overcharging you and your spreadsheet cannot prove it

Let us start with what this article does not say: that your freight forwarder is cheating you. Most of them are not. What does happen, constantly, is that they quote you a rate you have nothing to check it against, because your history is spread across twelve spreadsheets — one per year or one per customer — with the cost lines grouped differently in each. Without comparables there is no negotiation: there is an invoice and a due date.

The fix takes four steps and you get all of them below: normalise the cost to a comparable unit (per tonne and per container), group it by route, take the median of your own shipments and flag whatever sits above a threshold you set. From there you stop arguing about impressions and start arguing with your own numbers, which is the one thing a freight salesperson cannot talk their way around.

Why you cannot audit a forwarder invoice by reading it

A transport invoice is not a figure: it is six to fifteen lines, and almost none of them is named the same way by two providers. Base ocean freight travels alongside surcharges that come and go with the season, and several of them are perfectly legitimate: bunker adjustment, currency adjustment, terminal handling, port congestion, general rate increase. Reading that invoice and deciding whether it is expensive is impossible even if you understand every line, because the problem was never understanding it. The problem is having nothing to compare it to.

There is also a comparison trap that ruins many in-house audits: comparing the totals of two shipments. Two moves to the same port can differ in tonnage, in container count, in whether freight was prepaid or collect, and in which lines sat inside the forwarder’s price versus which you paid separately. The total is almost never comparable. The unit is.

The unit that actually compares

Convert every shipment to freight cost per tonne and per container, and never mix routes. Freight to Rotterdam and freight to Callao are not comparable in any year: different markets, different capacity, different seasonality. The right grouping is by port of discharge (POD); if your volume allows it, go finer and group by port pair and container type.

The median for the route is €100/t, so July comes in 32% above it. That figure proves nothing on its own — July may well be peak season on that lane — but it is the question you take to your forwarder, and it is a question you could not even formulate when all you had was three totals of €7,350, €5,000 and €13,200 that looked like three shipments of different sizes and nothing more.

Building the benchmark in your own spreadsheet

You do not need a system to start. You need a flat table with one row per shipment and these columns as a minimum: loading date, port of discharge, containers, tonnes, total freight and provider. One row per shipment, not one sheet per shipment: if every operation lives in its own tab, no formula can compare them.

1. 1Pour the last two years into that table. Invoiced shipments only, with the final transport invoice in hand: estimates poison the benchmark, and they are exactly what you are trying to audit.
2. 2Add a €/t column with = Total freight / Tonnes and a €/cont column with = Total freight / Containers.
3. 3Work out the reference per route with a conditional median and compare every shipment against it.
4. 4Sort by deviation, descending, and look at the top five rows. That is the whole conversation you are about to have with your provider.

=MEDIAN(IF(Table[POD]=$B$2, Table[€/t]))

Array formula: confirm with Ctrl+Shift+Enter in older Excel.
Deviation for each shipment:

= ([€/t] / route_median) - 1

How much deviation is suspicious

There is no universal number, and anyone who gives you one without seeing your history is guessing. What you can do is set a triage threshold: a percentage above which a shipment stops going unnoticed and somebody looks at it. In Granel that threshold is 40% above the route average, and it takes a minimum of three comparable shipments before the comparison runs at all.

Both numbers follow the same logic: a low threshold on thin history produces constant alerts that the team learns to ignore within a fortnight, and an alert that gets ignored is worse than no alert. Better to flag few shipments and have each one deserve a phone call.

What to ask for when the deviation holds

• The line-by-line breakdown of the expensive shipment and of a cheap one on the same route. Do not ask for explanations: ask for both invoices opened up and compare them yourself. The difference is almost always in one specific line, not spread around.
• Contracted rate versus applied rate. If you have a rate agreement, check which surcharges were inside it and which were not. That is where unreviewed amounts slip through most often.
• The effective date of each surcharge. A general rate increase applied to a booking made before it took effect is an ordinary billing error, and it gets corrected without argument.
• A benchmark quote from another provider for that lane, even if you have no intention of switching. The point is not to threaten: it is to find out whether your own historical median sits above the current market, in which case the problem is not the July shipment — it is all of them.

And one observation worth more than the four above: do this before you pay. The same argument, with the invoice still open, ends in a credit note; three months later it ends in a promise to make it up on the next shipment, which is another way of saying no.

When the spreadsheet stops coping

The method above works. What does not scale is maintaining it: every new shipment has to be typed into the flat table, every invoice broken down by hand, and the comparison only exists on the day somebody remembers to open the file. In practice it gets done twice — when you build it and six months later — and in between the invoices get paid unchecked.

The automatic version of this same calculation is what Granel does: as the costs of each deal are recorded, the freight line is identified by its label, normalised to euros per tonne and compared against your organisation’s average for that same port of discharge. When a shipment runs more than 40% above it — and there are at least three comparables on the route — a daily alert goes to the organisation’s owners and admins (alerts are part of the Growth and Enterprise plans). It is the table from this article, recalculated on its own every day across all your shipments rather than the twenty you had time to type.

Tool or no tool, the order is what matters: structure the history first, compare by route second, and only then argue about the invoice. Start with the argument and the only number on the table is theirs.