Route profitability analysis: revenue versus delivery cost

Route profitability analysis subtracts attributable driver, vehicle and delivery costs from delivery revenue for the same route and day. Calculate contribution before shared overhead, then show the allocation separately. That makes the result useful when deciding whether a weak route needs different pricing, a different service commitment or closer cost review.

Start with a route-day record

Take the dispatch export with route ID, service date, customer ID, completed deliveries, driver hours and miles. Bring the invoice or revenue export, payroll rates, fuel records, vehicle costs and tolls for the same service dates. A route name alone is not a reliable key because it repeats every day.

Use one row per route-day in the first summary. Group invoice lines to that level before adding driver or vehicle costs; otherwise, a route with several invoice lines can repeat the same shift cost. Retain the customer ID beneath the summary so you can investigate which deliveries produced the revenue.

Revenue should include delivery charges and earned accessorial charges, less credits. Exclude sales tax and amounts collected for someone else. Match credits to the original delivery where possible, rather than making the next day's route appear less profitable. The broader profitability analysis guide explains the distinction between contribution and company profit.

Define the cost rates before calculating

Document the contents of each rate. The example uses loaded driver labor, a fuel price, maintenance per mile and a vehicle cost per operating day. Loaded labor includes wages and the employment costs finance has assigned to productive driver hours.

The IRS discussion of vehicle expenses distinguishes variable operating costs, such as gas and oil, from fixed costs, such as depreciation or lease payments and insurance. That distinction also helps organize a management report. A tax mileage deduction is not a substitute for your company's route cost calculation.

For this worked example, put the following assumptions in column Q:

The vehicle rate excludes maintenance and fuel because those have their own lines. Treat it as an attributed capacity cost, not an amount automatically saved by canceling a trip. If drivers split a shift between routes, divide actual hours between them before using the labor rate.

Work three routes from the export

These are deliveries on October 21, 2026. Each route served one customer in this simplified example. Columns A through G contain the inputs; route and customer share the display label in A.

Enter these formulas in row 2 and fill down through row 4:

H2 = C2*$Q$2
I2 = E2*$Q$3
J2 = D2*$Q$4
K2 = G2*$Q$5
L2 = SUM(F2,H2:K2)
M2 = B2-L2
N2 = IF(B2=0,"",M2/B2)

H is labor, I fuel, J maintenance, K vehicle cost, L total attributable cost, M contribution and N contribution margin. Keep full precision in the cells. Display dollars to two decimal places when needed and percentages to one decimal place.

The total margin uses the totals, not the average of route percentages:

=SUM(M2:M4)/SUM(B2:B4)

Rural has the lowest contribution, but the table does not establish why its service is priced that way. Check contractual minimums, delivery requirements and the customers served before drawing a commercial conclusion. For a customer view across several routes, use the separate cost-to-serve calculation.

Allocate shared costs without hiding contribution

Suppose dispatch cost is the $600 pool in Q6. For this illustration, allocate it by recorded driver hours. That is a stated convention, not a claim that dispatch effort always follows hours. Use a different documented driver if your dispatch records support one.

O2 = $Q$6*C2/SUM($C$2:$C$4)
P2 = M2-O2

The proof is $4,650 revenue less $1,692 attributable cost less $600 shared cost equals $2,358. Changing the allocation driver can change each route's reported result, but it cannot change that total. Show unused vehicle capacity and other unallocated costs in a bridge to the company ledger.

Reconcile, then compare days and customers

Tie revenue to the delivery revenue accounts and route hours to the payroll or time export. Tie gallons, mileage and tolls to their source logs. Standard maintenance and vehicle rates may differ from actual ledger expenses; show those variances explicitly rather than forcing the route rows to match.

For a larger file, Excel's SUMIFS function sums amounts using multiple criteria. If a detail sheet has route ID in A, service date in B and net revenue in F, summarize the route in A2 and date in R2 with:

=SUMIFS(Detail!$F$2:$F$10000,Detail!$A$2:$A$10000,A2,Detail!$B$2:$B$10000,R2)

Use dates without timestamps for that exact-date match. Compare the same service scope across days; more profitable delivery windows can coexist with weaker delivery-window adherence. Covirage is not affiliated with Microsoft.

Where it goes wrong

• Repeating route costs on invoice lines. Summarize each source to the intended route-day before combining it.
• Counting fuel twice. A fully loaded mileage rate may already include fuel, maintenance and insurance.
• Ignoring waiting and return travel. Use attributable shift hours and miles, not only the loaded outbound trip.
• Reading allocation as avoidable cost. Shared dispatch expense can remain after a route stops operating.

Compare your route exports in Covirage

Bring a sample of your own dispatch and delivery revenue files with the cost rates and allocation basis. Covirage maps the columns, and its tools compute and check the route contributions; 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 industry context.