Transport and Fleet Management System in Excel: Trips, Km per Litre, Cost per Km, Document Expiry and Profit

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: transport-fleet-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Vehicles, Trips, Maintenance, Vehicle report, Trip sheet, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Amir Logistics, Sambhaji Nagar, 1 to 17 October 2026 (made-up data). Diesel at an example ₹90 a litre and a driver allowance of ₹500 per trip.

Trucks give about 4.2 km a litre and pickups about 9.5. For a single car or bike, the fuel mileage tracker is enough; on-time delivery by route is in the logistics project.

2. Build it sheet by sheet

Step 1. Setup

1. B3 as-of date, B4 diesel price (an example; update it when the price changes), B5 document warning days (30), B6 driver allowance per trip (500).
2. Maintenance types in D3:D6 for a drop-down.

Step 2. Vehicles: document expiry

1. Row 4: Vehicle, Type, Driver, Insurance expiry, PUC expiry, Fitness expiry, Permit expiry, Next expiry, Days left, Document flag.
2. H5 =MIN(D5:G5) finds the earliest date, I5 =H5-Setup!$B$3.
3. J5 =IF(I5<0,"Expired",IF(I5<=Setup!$B$5,"Renew soon","OK")).
4. Colour each date cell with formula rules: red when D5<Setup!$B$3, amber when within 30 days. The same idea for one car is in the service log.

Step 3. Trips: one row per round trip

1. Row 4: Date, Vehicle, From, To, Start km, End km, Km, Diesel (L), Km per litre, Freight, Tolls, Trip ID.
2. Type odometer readings. G5 =F5-E5, I5 =IFERROR(G5/H5,0) with a colour scale so a strange km per litre stands out.
3. L5 trip ID ="T-"&TEXT(ROW()-4,"000").

Step 4. Maintenance and vehicle report

1. Maintenance: Date, Vehicle, Type, Amount, one row per bill.
2. On Vehicle report, B5 trips =COUNTIF(Trips!$B$5:$B$1000,A5), C5 km and D5 litres with SUMIFS.
3. F5 diesel cost =D5*Setup!$B$4, G5 tolls, H5 maintenance, I5 driver =B5*Setup!$B$6, J5 total.
4. K5 cost per km =IFERROR(J5/C5,0), L5 freight, M5 profit =L5-J5. K9 is the fleet cost per km, total cost ÷ total km.

Step 5. Trip sheet

1. Type a trip ID in B4 (T-005). Rows 6 to 15 pull date, vehicle, route, odometer, km, diesel, freight and tolls with XLOOKUP.
2. B16 finds the driver from the vehicle. Print A1:D18 for the driver to sign.

Step 6. Dashboard

1. Cards: trips, km run, cost per km, freight, profit and vehicles with a document alert =COUNTIF(Vehicles!J5:J8,"<>OK").
2. Chart 1 at A8: cost per km by vehicle. Chart 2 at A25: profit by vehicle.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Typing km instead of odometer readings. Typed km hide mistakes. From odometer readings a typo shows up as 40 km a litre.
2. Averaging km per litre of trips. A short trip at 12 km/l and a long one at 4 km/l do not average to 8. Use total km ÷ total litres.
3. Leaving maintenance out of cost per km. Diesel alone makes the truck with new tyres look cheapest. Add every cost.
4. Tracking one expiry date. Insurance, PUC, fitness and permit each expire on their own date. MIN shows the next one to act on.
5. Old diesel price. Update Setup B4 when the price changes, or keep a price column on Trips for exact cost.

5. More real examples

Example 1: School or staff bus. Freight becomes fee or contract amount per month; cost per km stays the same and helps price the next route.

Example 2: Two-wheeler delivery fleet. Replace diesel with petrol, drop tolls and add Orders delivered to get cost per order.

Example 3: Tanker with load in litres. Add Load (kL) on Trips and freight per kL-km to compare routes fairly.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate cost per km in Excel?

Add every cost of the vehicle (diesel, tolls, maintenance, driver) and divide by km run: =IFERROR(J5/C5,0).

How do I calculate km per litre?

Km ÷ litres. For a vehicle, use total km ÷ total litres from SUMIFS, not the average of trips.

How do I track vehicle document expiry?

Keep the four dates in columns, take =MIN(D5:G5) as the next expiry and flag it within 30 days.

Should I log each fill-up or each trip?

Each trip with litres used is simplest. If you fill up irregularly, log fill-ups on a separate sheet and use full-tank to full-tank km.

Can I add a GPS km column?

Yes. Add it next to odometer km and a difference column; large gaps point to meter or entry errors.

7. Quick quiz

Ten questions on this page. Sign in to save your score.