Freight rate analysis on comparable lanes

Freight rate analysis compares prices for the same transportation service, keeping the lane, equipment, service level, and surcharge basis fixed. Match each supplied quote to the paid shipment records it covers, then compare both prices using the same quantities. A simple average across different lanes can confuse a pricing change with a change in work.

Define the rate before collecting numbers

Write down what one priced unit buys. For a truckload agreement, that might be a standard dry-van load between specified origin and destination areas. For another service, it may be a hundredweight, pallet, or mile. Preserve minimum charges and weight breaks rather than forcing unlike units into a single average.

Keep lane direction explicit. Atlanta-Dallas and Dallas-Atlanta are separate services for this analysis. Store equipment, temperature requirements, transit commitment, pickup constraints, and quote validity dates beside the price.

Decide whether the comparison covers linehaul alone or all applicable charges. The EIA's explanation of fuel surcharges states that shippers and transportation companies negotiate their own methods. A common diesel reference does not establish a common fuel charge.

Assemble the supplied files

Start with the rate workbook, accepted quote register, shipment register, and freight invoice export. Retain the quote ID and version, valid-from and valid-through dates, shipment ID, service date, invoice line ID, currency, units, and charge categories.

Match shipments to a valid quote using the full service key. Keep unmatched shipments separate with a reason, such as an expired quote, changed equipment, or spot move. Do not replace an unmatched rate with zero: that turns missing evidence into a favorable price variance.

Summarize billed linehaul at shipment level before averaging it. Then reconcile the paid linehaul, fuel, and accessorial totals to the selected invoice population. The neighboring freight cost analysis explains total spend movements once volumes and extra charges are included.

Work quoted and paid rates on the same shipments

An Ohio distributor reviews October 2026 dry-van shipments on two lanes. Each quote applies throughout the period, and the shipments meet the quoted service requirements. The table shows supplied linehaul quotes and average paid charges; all amounts are USD.

Put these fields in columns A:F, with lane rows in 2 and 3. In G2, calculate quoted linehaul for the actual shipment quantity. In H2, calculate paid linehaul; in I2, calculate the dollar difference:

=B2*C2
=B2*D2
=H2-G2

Fill down, then calculate the linehaul rate difference in J2:

=IF(C2=0,"Check quote",D2/C2-1)

Format J as a percentage and show two decimal places. The result compares linehaul only; the fuel and accessorial columns remain visible to prevent a scope mistake.

The Atlanta-Dallas price is above quote and Chicago-Columbus is below quote. Neither result proves an invoice error. Review the accepted terms and shipment details before seeking a correction.

Weight the total by the same basket

The correct portfolio comparison divides the two extended totals. It does not average the lane percentages:

=SUM(H2:H3)/SUM(G2:G3)-1

This returns 1.05%, rounded to two decimals. The average quoted linehaul is $22,800 / 30 = $760 per shipment. Average paid linehaul is $23,040 / 30 = $768. The $8 increase per shipment multiplied by 30 shipments proves the $240 difference.

For a direct check from the input columns, use SUMPRODUCT. Google Sheets function documentation describes its multiplication of corresponding ranges followed by summation:

=SUMPRODUCT(B2:B3,D2:D3)-SUMPRODUCT(B2:B3,C2:C3)

For comparisons between periods, choose a fixed basket such as the current month's shipments and apply both periods' valid rates to it. Keep discontinued or new lanes outside that comparison, then report their activity separately. Rate, volume, and mix analysis explains the broader decomposition when the quantity distribution itself is part of the question.

Covirage is not affiliated with Microsoft or Google.

Keep the all-in check separate

The paid fuel total is 12 shipments at $220 plus 18 shipments at $110, or $4,620. Paid accessorials are 12 shipments at $80 plus 18 shipments at $20, or $1,320.

Paid freight therefore totals $23,040 + $4,620 + $1,320 = $28,980. Reconstruct it directly from the input table:

=SUMPRODUCT(B2:B3,D2:D3+E2:E3+F2:F3)

Do not compare $28,980 with the $22,800 quoted linehaul total and call the difference a rate overrun. The quote scope excludes fuel and accessorials. A valid all-in comparison needs corresponding quoted allowances or actual contract calculations for those charges.

Where it goes wrong

• Mixed quote scope. Linehaul-only and inclusive quotes cannot be ranked on their displayed totals.
• Unweighted lane averages. Give each price the quantity of the same defined shipment basket.
• Expired or mismatched quotes. A price for different equipment or another service date does not establish the applicable rate.
• Ignoring minimums and bands. Reconstruct the agreement's charging rule before calculating a unit price.
• Equating acceptance with price. The tender rejection rate by lane measures a separate capacity outcome; a low quote is useful only in its operational context.

Compare your supplied freight rates in Covirage

Bring your quoted-rate workbook, shipment register, and paid invoice export. Covirage maps the columns; its tools compute and check comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

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