Logistics forecasting: shipments, capacity and costs

Logistics forecasting compares expected shipment workloads and transport costs with the loads that actually moved. Freeze the plan by lane and week, calculate the shipment miss, and explain the cost difference as volume and cost per load. That gives operations a workload question and finance a spending bridge from the same rows.

Start with one definition of a load

Use one row per lane, service type and forecast week in the review. Keep origin, destination, equipment and shipment unit consistent. A pallet shipment, a parcel and a full truckload cannot share one cost-per-load assumption merely because all three have shipment IDs.

Bring the forecast export with its creation date, forecast loads and planned cost per load. Bring shipment records with unique IDs and actual ship dates, plus freight charge detail linked to those IDs. Preserve canceled records with a status rather than silently deleting them from a historical forecast.

The worked example below uses full truckloads for a US distributor. Both planned and actual costs include the same freight charge categories. Actual costs include accrued charges for completed loads whose invoices have not arrived; otherwise invoice timing would look like a favorable transport result.

Compare this work with demand planning in logistics when the decision is where usable capacity is short. This page focuses on the completed-period shipment and cost reconciliation.

Build the lane comparison

An illustrative distributor froze these forecasts before the review week. Columns A through E are lane, forecast loads, actual loads, forecast cost per load and actual cost per load. Actual cost per load is the lane's total freight cost divided by completed loads, using unrounded values in calculations.

To assemble actual loads in Excel, suppose the Shipments sheet has lane in A, week-start date in B and load quantity in C. Put the selected week-start date in M1. The summary formula in C2, filled down, is:

=SUMIFS(Shipments!$C$2:$C$10000,Shipments!$A$2:$A$10000,$A2,Shipments!$B$2:$B$10000,$M$1)

This assumes the export has already been restricted to the matching service type and completed status. Microsoft documents SUMIFS as summing values that meet multiple conditions. Covirage is not affiliated with Microsoft.

Calculate the absolute shipment miss in K2 and fill down:

=ABS(C2-B2)

The lane misses are 10, 8 and 8 loads, totaling 26. Weighted absolute percentage error is =SUM(K2:K4)/SUM(C2:C4), or 26 / 250 = 10.4%. The net difference is only 250 − 240 = 10 loads because opposite misses cancel. Keep both measures; forecast bias by SKU and site explains the separate question of persistent directional error.

Split the transport cost difference

The bridge holds each lane's planned unit cost constant for the volume step, then applies its unit-cost change to actual loads:

Volume effect = (Actual loads − Forecast loads) × Forecast cost per load

Unit-cost effect = Actual loads × (Actual cost per load − Forecast cost per load)

Use columns F through J for forecast cost, actual cost, volume effect, unit-cost effect and total difference. Enter these formulas in F2, G2, H2, I2 and J2 respectively, then fill down:

=B2*D2
=C2*E2
=(C2-B2)*D2
=C2*(E2-D2)
=G2-F2

Every lane must satisfy forecast cost + volume effect + unit-cost effect = actual cost. The company check is:

=SUM(F2:F4)+SUM(H2:H4)+SUM(I2:I4)-SUM(G2:G4)

It returns zero: $142,000 + $7,600 + $3,380 = $152,980. Dollar calculations are exact here; the error percentage is shown to one decimal place. This is the same additive discipline used in a driver bridge, applied to transport spending.

Turn the bridge into a decision

Atlanta–Charlotte needs two conversations. Operations explains the additional 10 loads; the transport owner explains the $3,300 unit-cost effect. Chicago–Detroit's lower average cost offsets part of its higher volume. A company average would hide both lane stories.

Call the second step a unit-cost effect until its causes are verified. The EIA explains that fuel surcharges are privately negotiated and companies may use different calculation methods. A diesel price movement alone does not prove the surcharge on your invoice is correct. Inspect the applicable contract, charge base and shipping date.

Before updating capacity assumptions, compare next week's forecast loads with confirmed lane capacity for the same service window. Ask the carrier or site owner to validate constraints; a cost bridge does not establish that extra equipment or dock time is available. Shipping and logistics analytics puts these file comparisons in the broader operating review.

Where it goes wrong

• Invoice dates replace shipment dates. Late bills move spending between periods. Reconcile completed loads to invoices and accruals.
• Latest forecast replaces the frozen plan. A revision made after the week began weakens the test of the original assumptions.
• Shipment detail duplicates charges. Joining several invoice lines to several shipment lines can multiply both loads and cost. Aggregate charges by shipment first.
• Averages mix incompatible services. Equipment changes, expedited loads and accessorial charges can move unit cost without changing a comparable base rate.

Compare your lane files in Covirage

Bring your dated lane forecast, completed shipment export and freight charge detail to Covirage. Covirage maps the columns, its tools compute and check the shipment differences and cost bridge, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See Covirage for shipping and logistics teams for the supporting industry view.