Freight cost analysis by lane and shipment

Freight cost analysis explains why transportation spend changed by separating shipment volume, linehaul rates, fuel, and accessorial charges on comparable lanes. Begin with reconciled invoices, then build a bridge whose components equal the total movement. A lower average carrier rate does not necessarily mean a lower freight bill.

Start with shipment rows and a control total

Export freight invoices for the periods being compared, including credit memos and later adjustments. Keep the invoice line ID, shipment ID, service date, posting date, carrier, origin, destination, equipment, service level, currency, charge code, and signed amount. Use USD throughout this example.

Keep invoice lines as the source detail, but aggregate them to one analytical row per shipment. Otherwise a shipment with linehaul, fuel, and detention lines appears to be several shipments. Count distinct shipment IDs after checking that the ID is unique within the relevant business unit.

Choose service month or accounting month explicitly. An accounting comparison ties directly to the ledger; a service-month comparison may need a separate bridge for late invoices and accruals. Do not silently combine the two.

Reconcile signed invoice amounts to the freight expense or payable population selected by finance. Investigate excluded tax, credits, intercompany moves, and missing invoices before grouping costs. The broader logistics cost analysis workflow adds warehousing and handling; this page keeps the boundary at freight invoices.

Make each lane comparable

Define the comparison key as origin area, destination area, equipment, and service level. Add weight band or shipment type when that changes the charging basis. A refrigerated expedited shipment does not belong in the same rate comparison as a standard dry-van move between the same cities.

Split charges into linehaul, fuel, and accessorials using a documented charge-code mapping. Keep an unmapped bucket visible until someone resolves it. Do not fold a detention charge into linehaul merely because the carrier describes it differently.

Fuel deserves its own column. The US Energy Information Administration explains that fuel surcharges are privately negotiated and can use different calculation methods. The published diesel price is therefore an input to some agreements, rather than a universal surcharge formula.

Build a small lane summary

Consider an Ohio distributor comparing September and October 2026. Each lane below uses the same dry-van service in both months. Charge columns are average amounts per shipment, calculated from the underlying invoices; the example deliberately uses whole-dollar averages so the arithmetic remains visible.

With the table in columns A:G, total cost in G2 is:

=C2*SUM(D2:F2)

Fill down through G5. September totals $27,500 and October totals $28,980. Both months contain 30 shipments, yet cost rises by $1,480, or 5.38%, rounded to two decimal places. Use these formulas for the period totals and percentage change:

=G2+G4
=G3+G5
=(G3+G5-G2-G4)/(G2+G4)

The overall shipment count conceals a shift toward the more expensive lane. Freight rate analysis isolates comparable quoted and paid rates; freight spend analysis must also explain that activity shift.

Separate the four causes without a residual

Use September charges to value the volume change, then use October shipment counts to value each charge change. This order assigns the interaction between volume and price to the charge components. Another documented order can work, but changing the order changes the attribution.

Volume effect = Change in shipments × September total cost per shipment

Charge effect = October shipments × Change in that charge per shipment

For Atlanta-Dallas, the exact spreadsheet formulas referencing the summary are:

=(C3-C2)*SUM(D2:F2)
=C3*(D3-D2)
=C3*(E3-E2)
=C3*(F3-F2)

Repeat with rows 4 and 5 for Chicago-Columbus.

The proof is $27,500 + $1,000 + $240 + $60 + $180 = $28,980. There is no unexplained balance. The $1,000 volume component reflects the changed lane distribution even though aggregate shipment count is unchanged.

For a separate total check, Google Sheets documents SUMPRODUCT as summing products of corresponding arrays. October spend can be reconstructed from the shipment and charge columns:

=SUMPRODUCT(C2:C5,D2:D5+E2:E5+F2:F5,--(B2:B5="October"))

Covirage is not affiliated with Microsoft or Google.

Turn the explanation into an operating review

The linehaul effect belongs with procurement and the transportation manager. Check rate agreements and invoice applications before treating it as a negotiated price change. Fuel changes belong against the applicable surcharge schedule and effective dates.

Accessorial averages combine event frequency and event price. Return to the invoice lines to distinguish additional detention events from higher detention charges. The tender rejection rate by lane can add context about accepted capacity, without proving the cause of an invoice increase.

Where it goes wrong

• Counting charge lines as shipments. Aggregate invoice detail before counting transportation activity.
• Pooling unlike services. Equipment, speed, shipment size, and lane changes can masquerade as rate changes.
• Mixing expense and service dates. Late bills create artificial period movements unless reconciled separately.
• Omitting negative charges. Credits and invoice corrections belong in the signed cost total.
• Calling every accessorial movement a rate increase. Extra events and higher prices require different actions.

Do this on your own freight file in Covirage

Bring your freight invoice export, shipment register, and charge-code mapping. Covirage maps the columns; its tools compute and check lane comparisons, while the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data after setting up your business.

See Covirage for shipping and logistics teams for the industry context.