22.12 Logistics

By Ravindra Bagale Last updated: 6 October 2026

Dispatch interviews ask on-time delivery, damage, and cost per shipment. Kilometres driven, and shipment count, do not tell you the lane that failed. Depots here are fictional.

KPI	What the number means	Who uses it	Action it drives
On-time %	Shipments delivered on time divided by shipments.	Lane owner	A lane under the promise gets a different cutoff or a different carrier in the practice file.
Damage %	Damaged shipments divided by shipments.	Warehouse and dispatch	Damage is a packing or handling problem. Do not hide it inside "delivered".
Cost per shipment	Cost divided by shipments.	Dispatch manager	A cheap lane that is late is not cheap. Read it beside on-time.

Fictional sample rows. One week. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblLanes (Table Design › Table Name).

Lane	Shipments	On time	Damaged	Cost
Pune → Nashik	40	36	1	₹20,000
Pune → Kolhapur	20	10	2	₹16,000
Nashik → Nagpur	30	27	0	₹18,000
Nashik → Pune	25	22	1	₹12,500
Kolhapur → Solapur	16	12	2	₹9,600
Nagpur → Sambhaji Nagar	24	18	3	₹14,400

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI	Excel formula	Result	Number format
On-time %	`=SUM(tblLanes[On time])/SUM(tblLanes[Shipments])`	80.6%	`0.0%`
Damage %	`=SUM(tblLanes[Damaged])/SUM(tblLanes[Shipments])`	5.8%	`0.0%`
Cost per shipment	`=SUM(tblLanes[Cost])/SUM(tblLanes[Shipments])`	₹584	`₹#,##0`

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column	Formula in the first row
On-time %	`=[@[On time]]/[@Shipments]`
Damage %	`=[@Damaged]/[@Shipments]`
Cost per shipment	`=[@Cost]/[@Shipments]`

Expected result per row:

Lane	On-time %	Damage %	Cost per shipment
Pune → Nashik	90.0%	2.5%	₹500
Pune → Kolhapur	50.0%	10.0%	₹800
Nashik → Nagpur	90.0%	0.0%	₹600
Nashik → Pune	88.0%	4.0%	₹500
Kolhapur → Solapur	75.0%	12.5%	₹600
Nagpur → Sambhaji Nagar	75.0%	12.5%	₹600

Always calculate the file-level KPI as a ratio of totals (SUM ÷ SUM). Do not take the AVERAGE of the per-row percentages; a small row would count as much as a big one.

	Good KPI	Vanity metric
From the same rows	Pune → Kolhapur on time 50.0%	Pune → Nashik 40 shipments
Why	Half the Kolhapur lane was late, at ₹800 each.	The Nashik lane has the most trips and looks like the "main work". It is actually the healthier cheap-enough lane (36 ÷ 40 = 90.0% on time).

What the manager does. The Pune dispatch manager opens the 10 late Kolhapur shipments (cutoff time, or a truck that left after the dock closed) before adding a second truck on the Nashik lane. The Nashik manager keeps the Nagpur lane as the pattern and checks the one damaged box on Nashik → Pune, not the shipment count. Nagpur → Sambhaji Nagar damage is 3 ÷ 24 = 12.5%. That is a packing conversation, not a volume conversation.

Practice workbook and guide: Logistics dashboard workbook (Excel) · How to build the Logistics dashboard in Excel. The workbook data is also fictional.

Ravindra Bagale's Tip

A box that arrived early but broken is not a success. Keep Damaged as its own column and its own KPI next to on-time %.