Eliminating $890K in annual freight variance through automated reconciliation

The controller knew the numbers were wrong but couldn't prove it. Freight costs represented roughly 8% of revenue — over $5M annually — but the company had no way to reconcile what carriers actually charged against what the ERP had estimated at the time of shipment.

The problem was structural. When an order shipped, NetSuite recorded an estimated freight cost based on rate tables that were updated quarterly at best. The actual carrier invoice arrived 2-6 weeks later, often with surcharges, dimensional weight adjustments, and accessorial fees that didn't match the estimate. These variances accumulated in a suspense account that the accounting team would manually reconcile once a quarter — a process that took two full-time staff members an entire week.

Worse, because freight costs were allocated at the company level rather than by customer or route, the sales team had no visibility into which accounts were profitable after shipping. A customer that looked like a 35% margin account might actually be running at 22% once accurate freight was allocated — but nobody knew.

The first challenge was connectivity. The company used six different carriers, each with their own portal and invoice format. We built automated connectors to pull invoice data from each carrier API, normalize the formats, and match them against the corresponding shipments in NetSuite.

The matching logic was the most complex piece. Carrier invoices reference tracking numbers, but NetSuite references sales orders. The WMS (Fishbowl) was the bridge — it tracked both the sales order and the tracking number for each shipment. We built a three-way reconciliation that matched carrier invoice → WMS shipment → NetSuite order, then calculated the variance between estimated and actual freight for each shipment.

With the reconciliation engine running, we built an allocation model that assigned actual freight costs to each customer, route, and product line. The output fed directly into Power BI dashboards that finance and sales could access in real time.

The first reconciliation, delivered in three weeks, identified $890K in annual freight variance — roughly 17% of total freight spend. The three largest sources were dimensional weight surcharges that the rate tables didn't account for ($340K), residential delivery fees on accounts incorrectly classified as commercial ($210K), and accessorial charges for liftgate and limited access deliveries ($180K).

The operational impact extended well beyond accounting. The sales team used the customer-level freight allocation to renegotiate pricing on twelve accounts that were unprofitable after true freight costs. The logistics team used the route-level analysis to consolidate shipments and switch two high-volume lanes to regional LTL carriers, saving an additional $120K annually.

The quarterly manual reconciliation was eliminated entirely. The two staff members who had previously spent a week each quarter on reconciliation were redeployed to accounts receivable collections, where they recovered an additional $85K in overdue invoices within the first six months.