The Case Study
Five-Step Walkthrough
A freight audit firm processes thousands of international invoices weekly across Ocean and Air modes. Each invoice contains multiple charge components — base freight, Bunker Adjustment Factor (BAF), Terminal Handling Charges (THC), documentation fees, and Peak Season Surcharges — that must each be individually verified against either a contracted rate or a spot quote.
Before automation, analysts manually cross-referenced every line against rate tables in separate files. There was no consistent demurrage calculation, no tolerance-based classification per account, and no standardised exception format across the team.
- Manual rate lookup: 8–12 minutes per invoice
- No automated demurrage — free days tracked by hand per account
- Tolerance rules varied wildly by account but weren't codified anywhere
- Short-pay and balance due items had no systematic tracking
- Spot quotes mixed into the queue with no differentiation from contracted rates
- Exception reports were inconsistent across analysts — no standard format
The workbook is structured across seven sheets, each with a distinct purpose. No manual data entry is required once the Invoice Log and Rate Tables are populated — every downstream sheet calculates automatically.
- 📋 Case Study Overview — full written narrative of the methodology (the sheet you read first)
- 🧾 Invoice Log — 60 simulated invoices with all charge components broken out separately: base freight, BAF, THC, documentation fee, PSS, demurrage days and amount, amount paid, short-pay amount, short-pay reason, and balance due reference
- 📊 Rate Tables — two sections: Contracted Rate Table (carrier + origin + destination + container type → base rate, with ~7% intentional gaps to simulate "rate not on file") and Spot Quote Register (15 spot quotes with unique IDs, carriers, lanes, rates)
- 🏦 Account Tolerances — per-account rules: tolerance type (flat dollar or percentage), threshold value, free days at port, carrier per-diem demurrage rates, and short-pay permission flag
- ⚙ Audit Engine — the automation core: rate matching, charge calculation, demurrage logic, tolerance classification, payment status flags, action notes
- 📈 Exception Report — filtered view of all flagged invoices only, colour-coded by exception type
- 🎯 Dashboard — KPI cards and breakdown tables for management reporting
Rate tables use a composite key (Carrier|Origin|Destination|ContainerType) as the lookup identifier — a deliberate design choice that prevents false matches when the same carrier operates multiple service types on the same lane.
The Audit Engine is built on five core formula patterns that chain together to process each invoice from raw input to actionable output:
A representative sample of records from the Audit Engine, showing the full output for different exception types — all figures are simulated:
| Invoice ID | Carrier | Mode | Container | Account | Billed ($) | Contracted ($) | Variance ($) | Status |
|---|---|---|---|---|---|---|---|---|
| INV-0003 | Maersk Line | Ocean | FCL 40' | GlobalTech Mfg | $6,142.00 | $5,890.00 | $252.00 | 🔴 OVERCHARGE |
| INV-0011 | Cathay Pacific | Air | Air Freight | Meridian Pharma | $3,218.50 | $3,218.48 | $0.02 | 🔴 OVERCHARGE |
| INV-0017 | CMA CGM | Ocean | LCL | Summit Retail | $1,940.00 | $1,905.00 | $35.00 | ✅ WITHIN TOLERANCE |
| INV-0024 | Evergreen Marine | Ocean | FCL 20' | Pacific Dist. | $3,650.00 | — | — | ⚠ RATE NOT ON FILE |
| INV-0031 | FedEx Intl | Air | Air Freight | Northland CG | $2,890.00 | $2,950.00 | -$60.00 | ✅ WITHIN TOLERANCE |
| INV-0038 | Maersk Line | Ocean | FCL 40' | Everest Ind. | $5,200.00 | $4,880.00 | $320.00 | 🔶 SHORT PAY |
| INV-0045 | CMA CGM | Ocean | LCL | Blue Horizon | $2,140.00 | $2,050.00 | $90.00 | 🔴 OVERCHARGE |
Note row INV-0011 — Meridian Pharma's $0.01 tolerance means a $0.02 variance on an Air invoice triggers a full overcharge flag. Same variance on a Northland Consumer Goods account ($100 tolerance) would pass as within tolerance. Same formula, different account threshold.
This simulation mirrors the actual audit workflow at Cass Information Systems, where the same core logic — composite key rate matching, multi-component charge verification, per-account tolerance classification, demurrage calculation against free-day allowances — is applied to real international freight accounts daily across 20+ Fortune 500 clients.
The $450K+ in annual carrier overcharge recovery cited in my professional experience is produced by this exact methodology at production volume. This workbook is the methodology, made visible and interactive.
Technical Skills
Tools & Techniques Used
Excel Formulas & Logic
Freight & Logistics Terminology
Download the workbook
Open it, change a rate in the Rate Table, and watch the Dashboard update in real time.
⚠ Replace download link with your hosted file URL once uploaded