It is the second week of the month and the plant controller has the freight bills open in one window and the dispatch register in another. The sheet says freight should be ₹10.2 lakh for the Nagpur lane. The bills add up to ₹11.2 lakh. Someone suggests fuel. Someone else blames the carrier. The tea goes cold while three people scroll the same columns looking for a difference nobody can find.
A freight cost analysis is only as honest as its cost formula, and most Excel sheets price the tonnes you shipped instead of the tonnes you paid for. Fix that, and the sheet starts explaining the bill. Teams that outgrow the sheet move to freight lane planning software that prices every trip with one landed-cost formula, but the same logic works in a workbook first, and this post shows how.
The unit of analysis is the trip, priced at landed cost
A rate card is a promise about price per tonne. A freight bill is what a truck actually cost. The gap between them lives in four places: the minimum billable load, the fuel surcharge, tolls and handling. In India, handling usually means loading and unloading labour at both ends, and tolls now arrive through FASTag statements rather than driver receipts.
So build the sheet one row per trip, not one row per lane. Lanes are a summary you create later with a pivot table. If you start from lane totals, you can never see which trips caused the gap.
The formula we use for every trip is simple to write down:
Landed cost = MAX(load, minimum billable) × rate × (1 + fuel %) + toll + handling × load
Detention sits in its own column on top of that. It is a real cost, but it has a different owner, so keeping it separate stops it from hiding inside the rate.
The template needs sixteen columns and one landed-cost formula
Here is the layout. Columns A to M are typed in or pasted from the dispatch register and the rate card. N to P are formulas.
| Col | Field | Where it comes from |
|---|---|---|
| A–B | Trip date, LR or trip number | Dispatch register |
| C–D | Plant, depot | Dispatch register, one fixed spelling each |
| E–F | Carrier, truck type | Dispatch register |
| G | Load (tonnes) | Weighbridge or invoice |
| H | Minimum billable (tonnes) | Rate card, per truck type |
| I | Rate (₹ per tonne) | Rate card, per lane and carrier |
| J | Fuel surcharge (%) | Carrier's monthly fuel notice |
| K | Toll (₹) | FASTag statement or agreed lump sum |
| L | Handling (₹ per tonne) | Loading and unloading rate |
| M | Detention (₹) | Freight bill |
| N | Landed cost | =MAX(G2,H2)*I2*(1+J2)+K2+L2*G2 |
| O | Total trip cost | =N2+M2 |
| P | Cost per tonne | =O2/G2 |
Two rules keep the sheet usable. First, every number in H to L must be a number. A cell that says “as per last month” breaks the formula without warning. Second, plant, depot and carrier names must be spelt one way everywhere, or the pivot table will split one lane into three.
If you also want cost per tonne-km to compare lanes of different lengths, add a distance column and divide P by it. Our guide on calculating freight cost per ton and per km covers that step.
Minimum billable load is where most sheets go wrong
Here is an illustrative example, not a customer figure. Say a plant ships to one depot on a lane where the rate is ₹1,800 per tonne, the minimum billable load is 15 tonnes, the fuel surcharge is 8%, the toll is ₹6,500 a trip and handling is ₹120 per tonne.
A full trip carrying 15 tonnes costs:
15 × ₹1,800 × 1.08 = ₹29,160. Add ₹6,500 toll and 15 × ₹120 = ₹1,800 handling. Landed cost: ₹37,460, or about ₹2,497 per tonne.
A part-loaded trip carrying 10 tonnes is still billed at 15:
15 × ₹1,800 × 1.08 = ₹29,160. Add ₹6,500 toll and 10 × ₹120 = ₹1,200 handling. Landed cost: ₹36,860, or ₹3,686 per tonne.
Now the month. The lane runs 20 full trips and 10 part-loaded trips, 400 tonnes in all.
| Sheet that uses actual tonnes | Sheet that uses MAX(load, minimum) | |
|---|---|---|
| 20 full trips | ₹7,49,200 | ₹7,49,200 |
| 10 part trips | ₹2,71,400 | ₹3,68,600 |
| Month total | ₹10,20,600 | ₹11,17,800 |
| Cost per tonne | ₹2,552 | ₹2,795 |
The naive sheet prices each part trip at 10 × ₹1,800 × 1.08 + ₹6,500 + ₹1,200 = ₹27,140. The real bill is ₹9,720 higher per trip, which is 5 empty tonnes × ₹1,800 × 1.08. Across ten trips that is ₹97,200 a month the sheet cannot see. Nobody overcharged anyone. The company paid for empty space and the analysis priced it as if it were free.
If your sheet prices the tonnes you shipped, it will never find the money you spent on the tonnes you did not.
Cut the sheet three ways before anyone argues
Once every trip is priced the same way, a pivot table answers most month-end questions in minutes.
- By lane: total cost, tonnes and cost per tonne for each plant–depot pair. This shows where the money goes.
- By carrier: the same figures per carrier on the same lane. This shows whether one carrier is dearer, or simply gets the awkward loads.
- By trip: sort by cost per tonne, highest first. The top rows are almost always part loads, unplanned spot trucks or trips with detention.
Add one helper column, “empty tonnes” =MAX(0,H2-G2), and sum it per lane. That single number usually starts a more useful conversation with the dispatch team than any rate negotiation.
Plan and actual must use the same formula
The analysis gets far more useful when you build next month's budget with the same sixteen columns. Plan trips go in one tab, actual trips in another, both priced with the identical formula. Then the difference between them can only come from two places: you moved a different volume, or each tonne cost a different amount.
That split is called volume and rate variance, and it only adds up exactly when plan and actual share one cost formula. Our guide to freight variance analysis, volume vs rate walks through the arithmetic, and the free volume and rate variance calculator does it for one lane.
A spreadsheet is enough for many networks, until the question changes
The fair objection is that this is all doable in Excel, so why bother with anything else. For a single plant shipping to a handful of depots through two or three carriers, we agree. A well-kept sheet with the formula above will tell you most of what you need. Do not buy software to fix a missing column.
The sheet starts to strain in three situations. The first is when the question shifts from “what did it cost” to “what should it have cost”. A sheet can price the trips you ran, but it cannot tell you that a depot would have been cheaper served from a different plant by a different carrier. That is a network question, and our comparison of lane-by-lane vs network freight planning explains why planning each lane on its own tends to create part-full trucks and late spot trips.
The second is data volume. Rate cards, fuel notices, demand and trip logs arrive as separate files every month, and one bad row can stall the whole workbook. The third is ownership. A sheet shows a gap, but it rarely says whose gap it is. FreightPlan splits the rate gap into five causes (empty space, rate card, unplanned trips, detention and service) and gives each one an owner. If the sheet already answers your questions, keep it. If the meeting keeps ending without decisions, that is the signal.
What to do this week
- Rebuild last month's freight sheet as one row per trip, with the sixteen columns above.
- Add the minimum billable tonnage for every truck type from your rate cards.
- Replace any text in the rate, fuel, toll and handling columns with numbers.
- Run the three pivots and sum the empty tonnes for each lane.
- Compare the new total with the freight bills and note how much of the old gap disappears.
Most of the month-end mystery is arithmetic, not misconduct. Once the sheet prices every trip the way the carrier bills it, the conversation moves from blame to the two or three lanes worth fixing. The rest of our freight planning guides take it from there.