What you'll need before you start
Four data sets, ideally for one client over one calendar month as your first attempt:
- The rate card. Usually a PDF or a contract document. Pricing for every activity type, plus any minimums, surcharges, and CPI clauses.
- WMS activity export. Every billable event in the period: receivals, picks, despatches, storage days, returns, value-added services. CSV or Excel from your WMS.
- Finance system invoice export. The invoice lines actually sent to the client for the same period, with quantities and dollar values.
- Surcharge and accessorial schedule. Often lives separately from the rate card. Fuel levy, after-hours, dangerous goods, residential delivery, wharf cartage.
Now the inconvenient bit. This is four to eight hours of work before you've matched a single line. Rate cards are often in PDFs from 2019. WMS exports come in three different schemas depending on which warehouse module generated them. Finance system data usually needs an extra column added to make it joinable to anything. Plan for the prep, not the analysis.
The five-column audit baseline
Open a new sheet. The structure that works for a single client over one month:
- Activity date and ID (from WMS)
- Activity type and quantity (from WMS)
- Rate card rate (looked up from the rate card)
- Calculated charge (column 2 quantity × column 3 rate)
- Actual charge from invoice (from finance system)
Use XLOOKUP (or VLOOKUP in older Excel) to pull the rate from a rate card lookup table you've built as a second sheet. Format example:
=XLOOKUP(B2,RateCard!A:A,RateCard!B:B,0)
The formula returns the unit rate for activity type B2. Multiply by the quantity, compare against the invoiced charge, and you've got a per-activity reconciliation. It's tedious. It does work for a single client over one month.
The six things to flag first
Six discrepancy types map cleanly to Excel filters. Run each filter, save the results to a new tab, then come back through them:
1. Activities with no rate card match (missed billings)
Filter for rows where the rate card lookup returned an error or zero. These are activities you performed for which no priced line exists in the rate card. Could be scope creep. Could be a data quality issue. Either way, flag them.
2. Charges below calculated rate (under-billing)
Filter where calculated charge is greater than actual charge. You billed less than the rate card says you should have. This is your most recoverable category.
3. Charges above calculated rate (over-billing)
Filter where actual charge is greater than calculated charge. Yes, both directions matter. Over-billing is a customer relationship risk. If the client finds it before you do, the conversation is harder.
4. Activities outside the contracted scope (scope creep)
Subset of category 1. Look at the activity types appearing most frequently among the no-match rows. If “Rework” or “Returns Processing” shows up 200 times and isn't on the rate card, that's scope creep, not a data error.
5. Minimum volume shortfalls
Separately, pivot the WMS activity by client. Compare monthly totals against contracted minimums in the rate card. Any shortfall where the minimum charge didn't apply is a billing gap.
6. Missing surcharges
Look at activities that should have triggered a surcharge (after-hours dispatches, DG-flagged SKUs, residential deliveries) and check whether the matching surcharge line item appears on the invoice. Most often it doesn't.
Where Excel actually breaks
Excel works fine for one client, one month, one rate card. It breaks when:
- You have 10+ clients, each with different rate cards
- Rate cards changed mid-period (CPI adjustments, surcharge updates, amendments)
- Activities span multiple cost centres, warehouses, or jurisdictions (AU vs NZ GST)
- Your WMS exports change schema between versions
- You need this to be repeatable monthly, not a once-a-year fire drill
- You need findings prioritised by severity and dollar impact, not just listed
Each factor multiplies the prep time and the error rate. A 15-client audit done properly in Excel is a full week of work. A 30-client audit isn't feasible quarterly. You'd never get out of the audit cycle.
What a complete audit actually looks like
The principle behind a proper audit is simple. Every activity logged is matched against the active rate card, every cycle, with findings surfaced as a triage queue rather than a spreadsheet. The matching is automated. The judgement (deciding which findings to action, which to dismiss, which to renegotiate) stays with you.
The logic doesn't change from the Excel version. Same six categories. What changes is the time cost. A continuous reconciliation layer turns a five-day audit into a fifteen-minute review of the prioritised queue.
When Excel is enough, and when it isn't
A simple decision tree:
- Five clients or fewer, simple flat-rate cards, under 200 activities per month per client. Excel is fine. Audit annually before renewal.
- Five to fifteen clients, surcharges, occasional amendments. Excel works but is painful monthly. Audit quarterly and accept some leakage.
- Fifteen or more clients, multi-tier rate cards, CPI clauses, minimum volumes. Excel can't keep up. You either accept the leakage or you invest in continuous monitoring.
Most ANZ mid-market 3PLs sit in the third bracket and run their audits at the cadence of the second. That gap (needed cadence versus actual cadence) is where 3–8% of revenue quietly leaves.
Frequently asked questions
How often should a 3PL audit its billing?
If you're doing it manually in Excel, annually before contract renewal is the realistic minimum. Quarterly is better if you have the team capacity. With a continuous reconciliation layer, every billing cycle is audited automatically and the audit becomes a 15-minute review rather than a multi-day project. The right cadence is monthly minimum if you want to catch leakage while it's still recoverable.
What's the most common billing error in 3PL operations?
Across the operators we've looked at, the most common single category is missed surcharges. Fuel levy and after-hours dispatch fees that exist on the rate card but never propagate to the actual invoice. The most expensive category is usually scope creep, because it accumulates silently for months before anyone notices.
Can a junior finance person run a 3PL billing audit?
Yes for the data work: pulling exports, building lookups, running the filters. No for the judgement calls. Deciding which findings are real, which are data quality issues, and which are commercially worth pursuing requires someone who knows the client relationships and the contract terms. The split that works is junior analyst does the mechanics, commercial lead reviews the findings.
How long does a manual audit take per client?
Six to ten hours of focused work for a clean first pass, assuming the rate card is digitised and the WMS export is clean. Most operators need to add an hour or two for data hygiene before the audit can even start. For a 15-client operator, that's a full week per quarter.
What's the difference between auditing in Excel and using software?
Excel does the same six categories of matching. But it's manual, periodic, and doesn't prioritise findings by severity or dollar impact. Software runs the same matching continuously, prioritises automatically, and surfaces findings as a triage queue. The categories of leakage you find are identical. What changes is how fast you find them, and whether you find them in time to actually do something about it.