Managing a fleet with Excel — knowing profit and loss per trip, no need for expensive software

9 min read

In this article

1. High fleet revenue doesn't mean profit — when costs are scattered across 5 notebooks
2. Three common mistakes that prevent vehicle owners from ever knowing their true profit
3. 3 layers of data in one Excel file — from vehicle registration to profit dashboard
4. Analyzing profit by route — detecting the route that is eroding the margin
5. Depreciation and maintenance — the most silently profit-killing hidden expense
6. Frequently Asked Questions

High fleet revenue doesn't mean profit — when costs are scattered across 5 notebooks

Most small transportation businesses evaluate their performance based on total monthly revenue. That figure provides a sense of security — 150 million, 200 million, the fleet is running smoothly. However, revenue in transportation hides many layers of costs: fuel is in the gas station bill, tolls are in the receipt, driver salaries are in the payroll book, and maintenance is in the manager's memory. To know the true profit, you need to pull data from at least 5 different sources — and in reality, almost no one does that in enough detail.

The most serious consequence is not losing money without knowing it, but having no basis for making decisions. Should you buy more vehicles or not? Which route should you expand? Which driver is operating efficiently? All of these become guesses when there's no cost data per trip.

Three common mistakes that prevent vehicle owners from ever knowing their true profit

Lumping all costs together. Fuel, tolls, driver meals, loading fees — all recorded in a single line as “trip cost”. This recording method makes it impossible for managers to distinguish between fixed costs (depreciation, insurance, parking fees) and variable costs (fuel, tolls, loading). If you can't tell them apart, you can't optimize — because each type requires a completely different control approach.

A vehicle runs 300,000 km without replacing the chain sprocket — every trip “profits” on paper. Then the engine breaks down, 80 million VND to repair, and a whole year's profit is gone. Depreciation isn't a future cost — it's a cost happening every kilometer you drive.

— The most common mistake in managing costs for small fleets

Not factoring depreciation and maintenance into trip costs. A truck worth 800 million VND, used over 8 years, is “consuming” about 275,000 VND/day in depreciation — whether it runs or not. Add insurance, inspection fees, parking fees, and loan interest if financed. All these costs must be allocated across every trip to know the real profit. Skip them, and every profit calculation is wrong from the start.

Completely trusting driver-reported costs. It's not a matter of dishonesty — everyone forgets details, everyone rounds numbers. An 85 km trip easily gets recorded as 90 km. A 45-liter fuel fill-up gets remembered as 50 liters. Small discrepancies per trip, but multiplied by 100 trips/month, the error becomes significant. You need a cross-check system: compare actual km with average fuel consumption by vehicle type — a variance above 15% is a red flag worth investigating.

3 layers of data in one Excel file — from vehicle registration to profit dashboard

Designing a fleet tracking system isn’t complicated if you organize data into 3 clear layers. Each layer answers a different question, and the three combined create a comprehensive financial picture of the fleet.

The most important point when designing these 3 layers is linking data between them. The vehicle table provides fixed costs to the trip table. The trip table provides transaction data to the dashboard. When a new trip is added, the entire system updates — no manual calculations, no copy-pasting between sheets. This is why structured Excel is superior to a notebook: not because data entry is faster, but because data self-links and self-calculates.

Analyzing profit by route — detecting the route that is eroding the margin

When trip data is fully recorded, the first analysis step should be ranking profit by route. The table below illustrates how a fleet of 8 vehicles running urban and intercity routes discovered which routes were truly creating value.

The most notable figure lies in the Đồng Nai route: a profit of 900,000đ per trip — 2.5 times higher than the Bình Dương route — but few people notice because the absolute revenue per trip is not as outstanding as Vũng Tàu. Meanwhile, the Bình Dương route accounts for 35% of the total number of trips but only contributes around 30% of the profit — with the lowest profit margin per trip in the entire team. If we reduce 10 Bình Dương trips and switch to Đồng Nai, the total profit increases by 5.5 million/month — equivalent to an 18% increase in margin without adding more vehicles or drivers.

Route-level analysis is just the first step. On the same route, two different drivers can produce profit differences of 20–30% — due to different fuel consumption rates, different loading/unloading times, different on-time delivery rates (affecting repeat customers). A profit dashboard by driver is the second layer of analysis, and often the one that delivers the most surprising insights.

Depreciation and maintenance — the most silently profit-killing hidden expense

Fuel and road tolls are expenses everyone sees — because they're paid immediately. But the largest transportation cost belongs to things that don't require daily payment: vehicle depreciation, tire wear, and periodic maintenance. A 800-million truck depreciated linearly over 8 years 'spends' 275,000đ daily. Tires replaced every 50,000 km at 12–18 million per set, about 60–80,000đ per 1,000 km. Installment loan payments: 500–800,000đ/month depending on remaining debt. These hidden costs easily comprise 15–25% of total operating expenses, but almost never appear in daily ledgers.

Preventive maintenance doesn’t just reduce repair costs — it reduces downtime. Every day a vehicle sits idle waiting for repairs is a day of lost revenue. With a vehicle averaging 3 trips/day, each trip profiting 500,000 VND, one unexpected breakdown taking 3 days to fix means losing 4.5 million in profit — not counting the inflated emergency repair costs. This is why km-based maintenance scheduling isn’t “nice to have” but the centerpiece of fleet cost management.

Frequently Asked Questions

Can Excel manage a fleet larger than 20 vehicles?

With 20–50 vehicles, Excel still works well if you use Structured Tables and Named Ranges properly — data auto-expands when new rows are added. Above 50 vehicles, if multiple people need to enter data simultaneously, consider switching to Google Sheets or combining with Apps Script to automate data entry from phones.

How to detect drivers inflating fuel costs?

Cross-check 3 data sources: actual km vs driver-reported km, average fuel consumption by vehicle type vs reported consumption, and costs for the same route across different drivers. The Fleet Trip Cost Manager system automatically flags fuel consumption anomalies when discrepancies exceed the configured threshold — typically 15% above average.

Should data be entered daily or weekly?

Minimum at the end of each day. The most effective way: drivers send photos of fuel and toll receipts via the messaging app right after the trip, and managers enter them at the end of the day. If delayed until the end of the week, small details are forgotten and errors increase rapidly — especially with loading and meal expenses.

Should depreciation costs be calculated by time or by km?

For trucking fleets, calculating by km is more accurate — because vehicles that run more wear out faster, regardless of time. Formula: (Purchase price − Estimated salvage value) ÷ Total expected km of use = Depreciation cost per km. Example: truck costing 800 million VND, salvage value 150 million, running 500,000 km → depreciation of 1,300 VND/km. The Fleet Trip Cost Manager template supports both depreciation methods.

Our fleet uses GPS management software, do we still need Excel?

GPS software tracks location and routes — but doesn’t calculate profit. GPS shows where the vehicle is and how many km it traveled, but doesn’t know if the trip was profitable or not. Excel adds the financial layer that GPS lacks: attaching revenue, costs, and profit to each trip. The two tools complement each other, not replace each other.