Fuel is usually the largest variable cost in a fleet and the easiest one to leak. Reconciling the fuel card statement against your trips each week answers two questions: was every litre used on a real trip? and is it charged to the right driver?
Decide what you'll match on
Fuel card statements typically include transaction date and time, card number, vehicle registration, station, litres and amount. Pick the strongest link you have to your trip register, in this order:
- Trip or load reference entered at the pump — the most reliable when drivers are disciplined about it.
- Vehicle + date window — the vehicle's trip that was running at the time of the fill.
- Card + driver assignment — only if each card stays with one driver.
Whatever you choose, normalise it first: trip references arrive as T-1001, t1001 or T 1001, and vehicle numbers with or without spaces (MH12 AB 1234 vs MH12AB1234).
A weekly routine
- Combine all vendor statements for the week into one list with the same columns. Watch for amounts stored as text and dates in dd/MM format being read as MM/dd.
- Remove exact duplicates — the same card, time and amount appearing twice is usually a statement export issue, not two fills.
- Match each fill to a trip. Fills with no match go on an exceptions list.
- Total fuel per trip and per driver, and deduct advances in the driver settlement.
- Review the red flags below before approving payments.
Red flags worth checking every week
- Fills with no trip. Typos are common, but a pattern on one card deserves a conversation.
- Two fills close together whose litres add up to more than the tank holds.
- Fills on days the vehicle had no trip, or at stations far from the route.
- Fuel efficiency outliers. Litres per trip compared with the route's usual consumption — compute km per litre per vehicle and look at the bottom few each week.
- Price-per-litre outliers versus other fills on the same day, which can indicate non-fuel purchases on the card.
Doing the matching in Excel
With a cleaned trip reference in both sheets (see the driver settlement guide for the formula), look up the driver and flag unmatched fills:
Driver =XLOOKUP([@CleanRef], Trips[CleanRef], Trips[Driver], "NO TRIP")
Fills =COUNTIFS([CleanRef], [@CleanRef])
Litres =SUMIFS([Litres], [CleanRef], [@CleanRef])Filter Driver = "NO TRIP" for the exceptions list, and sort trips by litres to eyeball the heaviest consumers.
What Reconza automates — and what it doesn't
Reconza handles the matching part of this routine: it normalises references, removes exact duplicates, handles several fills on one trip, attributes fuel to drivers through their trips, lists fills with no matching trip, and deducts fuel in each driver's settlement and statement. Route- and mileage-based checks (tank capacity, km per litre, station location) still need your judgement and vehicle data — use the exceptions list as the starting point for those conversations.