Why reconciliation matters

  • Leakage sources: card sharing, partial-fill abuse, siphoning, station miscalibration.
  • Impact: hidden cost creeps into TCO; trust drops between finance and operations.
  • Fix: link transactions to sensor deltas and geofences, then act on anomalies.

The 7 steps (simple version)

  • Normalize sources. Use standard fields: vehicle_id, driver_id, card_id, datetime, lat, lon, volume_l, amount_aed, odometer_km.
  • Time-window match. Pair each transaction with sensor readings ±10 minutes (before/after) and within 150 m.
  • Volume delta check. Post‑level minus pre‑level should roughly equal the receipt volume (allow ±5 L tolerance).
  • Sanity checks. Compare price-per-liter vs station rate; detect back‑to‑back fills > 70 L in < 30 min.
  • Flag & classify. Label anomalies: policy breach, geo mismatch, volume mismatch, night fueling.
  • Close the loop. Lock the card, coach driver, or correct station mapping. Update policy.
  • Track KPIs. Leakage % (= flagged volume ÷ total), AED recovered/month, time‑to‑close days.

Data model (what your sheet needs)

Table Fields Notes
Fuel transactions vehicle_id, driver_id, card_id, datetime, station, lat, lon, volume_l, amount_aed, odometer_km Import weekly from card provider
Sensor readings datetime, vehicle_id, lat, lon, tank_level_l Use pre/post window around tx time
Rules rule_id, name, logic, threshold Editable tolerances (time, distance, volume)
Anomaly log case_id, tx_id, category, status, owner, eta_close Feeds finance & HR workflows

💬 FAQs

Start with high‑spend or high‑risk routes; expand as savings prove out.
Begin with ±10 minutes and ±5 L; tune by vehicle and station.
Build fueling geofences for each station; use those perimeters instead of raw coordinates.
Policy: require supervisor PIN 00:00–04:00; auto‑flag exceptions.
Report recovered AED/month and trend of leakage % over 90 days.

KPIs to show Finance

  • Leakage % and AED recovery
  • Flag rate by driver/card/station
  • Time‑to‑close anomaly cases
  • Sensor uptime and data quality