What is lane profitability?
Lane profitability is the profit and margin you earn on one origin and destination pair, such as Dammam to Riyadh, after all the costs of serving it. A lane can look busy and well paid and still lose money once you count the empty return, the hours spent waiting at the customer and a fair share of the truck's fixed costs.
This load and lane profitability workbook in Excel records each load once and calculates profit per load, margin %, revenue per km, cost per km and empty km share. It then totals the loads by lane and by customer, so you can see which lanes to reprice, which customers cost too much to serve and where a backhaul would change the result.
How to use the lane profitability template in Excel
- Set your rates on Settings: choose the currency from the list, then enter the target margin, waiting cost per hour, vehicle fixed cost per km and the empty km threshold.
- List your lanes, customers and vehicles on Settings. They feed the drop-downs and the summaries.
- Record each load on Loads: date, customer, lane, vehicle, revenue, loaded km, empty km, fuel, tolls, driver cost, waiting time and other costs.
- Read the summaries. Set a date range on Lane summary and Customer summary, or leave it blank for all loads, and start with the Flag column.
- Act on the flags: reprice, look for a backhaul or charge waiting time on lanes and customers that lose money or miss the target margin.
How to calculate profit per load and cost per km
The lane and customer summaries add up revenue, cost and km with SUMIFS for the date range you choose, then work out margin and per-km figures from the totals. A lane's margin is therefore weighted by revenue, not an average of load margins.
Worked example: a lane that loses money
The workbook ships with five example loads in USD, a waiting cost of 10 per hour, a vehicle fixed cost of 0.30 per km and a 10% target margin. Load L-1003 runs Jeddah to Madinah for 320, with 420 loaded km and 420 empty km back.
| Item | Calculation | Result |
|---|---|---|
| Total km | 420 + 420 | 840 |
| Waiting cost | 4 h × 10 | 40.00 |
| Vehicle fixed cost | 840 × 0.30 | 252.00 |
| Total cost | 135 fuel + 90 driver + 40 waiting + 15 other + 252 fixed | 532.00 |
| Profit | 320 − 532 | −212.00 |
| Margin | −212 ÷ 320 | −66.3% |
| Revenue per km / cost per km | 320 ÷ 840 / 532 ÷ 840 | 0.381 / 0.633 |
The Lane summary flags Jeddah to Madinah as a loss-making lane. The two Dammam to Riyadh loads earn 254 on 900 of revenue, a 28.2% margin with 13.0% empty km. By customer, Red Sea Retail shows a 2.7% margin: its Riyadh to Jeddah load made 245, but the Madinah load wiped most of it out. The fix is a price that covers the empty return, a backhaul from Madinah, or both.
How to read the profitability flags
- Loss (or loss-making lane or customer): costs are higher than revenue. Review these first.
- Below target margin: profitable, but under the target margin on Settings.
- High empty km: margin is on target, but the empty km share is above the threshold on Settings. These lanes are the best candidates for backhauls.
- OK: profitable, on target and within the empty km threshold.
How to calculate a fuel surcharge in a transport contract
When diesel prices move, a fixed freight rate shifts margin from one side to the other. A fuel surcharge clause links part of the rate to the fuel price, so neither side has to renegotiate every month. The Fuel surcharge sheet sets out the terms to agree: the price source, the base price, the fuel share of the rate, a band in which nothing changes, and whether the rate also falls when fuel gets cheaper.
In the example the base price is 0.40 per liter and the current price 0.48, a 20% rise. With a 25% fuel share, the surcharge is 5% of the rate: 22.50 on the Dammam to Riyadh load priced at 450. A contract that uses a band table gives the same 5% here, because 0.48 falls in the 0.46 to 0.50 band. The check column compares the surcharge with the real change in fuel cost: on Jeddah to Madinah, 840 km at 35 L/100 km, fuel costs 23.52 more but the surcharge adds only 16.00, so the agreed fuel share is too low for that lane. All prices and shares are examples. Fuel pricing rules and any regulated freight rates differ by country: check your authority's current requirement and your contract.
Common mistakes when calculating load profitability
- Leaving out empty km. The return trip costs fuel, driver time and wear even with no cargo.
- Ignoring waiting time. Hours at a site cost money even when the truck is parked.
- Counting only fuel. Without a fixed cost per km, every lane looks profitable.
- Mixing directions. Riyadh to Jeddah and Jeddah to Riyadh are separate lanes with different prices and loads.
- Judging a lane on one load. Look at several weeks before you drop or reprice a lane.
- Inconsistent revenue. Record revenue the same way for every load, for example before VAT and including surcharges.
Limits of lane profitability analysis in Excel
Profit here is a contribution margin after direct costs and the fixed cost per km you set. It does not include office overhead unless you build it into the fixed cost per km, and it is not an accounting statement. The results are only as good as the km, waiting time and costs entered for each load. For a quick estimate of a single trip before you quote, use the truck trip cost calculator.