Fuel station wet-stock reconciliation: dip, sales and variance

The dealer's question, and the oil company's

A retail outlet dealer wants to know whether fuel is going missing: through a leak, a miscalibrated dispenser, short deliveries or theft. The oil marketing company's territory staff want the same answer for environmental and safety reasons as well as commercial ones. Both are poorly served by the usual register, which records a daily "gain/loss" figure that nobody reads until it is large.

Records you reconcile

The dip rod gives a height; the calibration chart for that specific tank converts it to litres. A wrong or outdated chart is a systematic error, so confirm which chart version your data used.

The stock equation for each tank

• Net sales = Σ nozzle (closing totaliser − opening totaliser) − test returns
• Receipt quantity (dip) = dip-derived volume after decantation − volume before, adjusted for sales during decantation if the pump kept selling
• Book closing = opening dip + receipt (dip) − net sales
• Daily variance = closing dip − book closing
• Variance % of sales = daily variance ÷ totaliser sales × 100
• Cumulative variance % = Σ daily variance ÷ Σ totaliser sales × 100

Using the invoice quantity instead of the dip-measured receipt is the most common mistake, covered below.

One tank, six days, synthetic data

CREATE TABLE tank_day (tank_id TEXT, business_date TEXT, opening_dip_l REAL, receipt_invoice_l REAL,
                       receipt_dip_gain_l REAL, totaliser_sales_l REAL, test_return_l REAL,
                       closing_dip_l REAL);
INSERT INTO tank_day VALUES
('MS-1','2026-09-01',8200,0,0,2150,10,6068),
('MS-1','2026-09-02',6068,12000,11930,2310,10,15690),
('MS-1','2026-09-03',15690,0,0,2080,10,13600),
('MS-1','2026-09-04',13600,0,0,2240,10,11345),
('MS-1','2026-09-05',11345,0,0,2400,10,8935),
('MS-1','2026-09-06',8935,0,0,2190,10,6730);

WITH d AS (
  SELECT tank_id, business_date, totaliser_sales_l, closing_dip_l,
         opening_dip_l + receipt_invoice_l  - (totaliser_sales_l - test_return_l) AS book_close_invoice,
         opening_dip_l + receipt_dip_gain_l - (totaliser_sales_l - test_return_l) AS book_close_dip
  FROM tank_day
)
SELECT business_date,
       ROUND(closing_dip_l - book_close_invoice, 0) AS var_invoice_l,
       ROUND(closing_dip_l - book_close_dip, 0) AS var_dip_l,
       ROUND(100.0 * (closing_dip_l - book_close_dip) / totaliser_sales_l, 2) AS var_pct_sales,
       ROUND(SUM(closing_dip_l - book_close_dip) OVER (ORDER BY business_date), 0) AS cum_var_l,
       ROUND(100.0 * SUM(closing_dip_l - book_close_dip) OVER (ORDER BY business_date)
             / SUM(totaliser_sales_l) OVER (ORDER BY business_date), 2) AS cum_var_pct
FROM d
ORDER BY business_date;

Two findings in six rows

The delivery shortfall is not a tank loss. On 2 September the invoice said 12,000 litres; the dips before and after decantation showed 11,930. Reconciling on the invoice makes the tank look 78 litres short that day. Reconciling on the dip puts 70 of those litres where they belong, as a receipt short of invoice, which is a conversation with the transporter and the supply location, and leaves an ordinary −8 litres of measurement noise.

From 3 September the pattern changes. The first two days are small and opposite in sign, which is what dip error looks like. After that, every day loses 20–25 litres, roughly 1% of sales, and cumulative variance falls steadily to −90 litres. No single day would trigger an alarm; the direction does. A steady one-directional loss that starts on a date is what you would expect from a new leak, a dispenser recalibration problem or a change in who takes the dip, and the dates narrow the search.

What distorts wet-stock numbers

• Temperature. Fuel expands as it warms. A tanker loaded cool and decanted into a warmer tank, or a hot afternoon dip compared with a cool morning one, moves volumes without any fuel being gained or lost. Where the data carries temperature and density, reconcile corrected volumes; if it does not, take dips at the same time every day.
• Dip technique and timing. Dipping while a dispenser is running, or too soon after a delivery before the fuel settles, adds error. Record who took the dip and when.
• Calibration charts. A chart made for a different tank or before a tank was re-laid produces a steady bias. It looks like a leak that began on the chart change date.
• Totaliser resets and nozzle swaps. A replaced totaliser starting from zero produces an enormous negative "sale". Validate that each nozzle's closing reading exceeds its opening reading.
• Missing test returns. Forgetting to record the few litres drawn for a test and poured back shows up as a small daily gain.
• Tolerance thresholds. Your oil company and any applicable rules may define an acceptable variance. Use the threshold that applies to your outlet as a parameter; do not borrow a figure from elsewhere.

Reporting to the dealer or territory manager

Show one chart per tank: bars for daily variance and a line for cumulative variance %, with delivery dates marked. Below it, one table of receipts with invoice quantity, dip-measured quantity and the difference. A short text note states the pattern and the next physical check, for example: "Consistent loss of about 1% of sales on MS-1 since 3 September. Recommend a dispenser calibration check on its nozzles and a tank tightness test if calibration is correct."

Do not accuse staff on the strength of variance data. It shows where and when; people, equipment and suppliers all remain possible explanations until the physical checks come back.

Where this skill comes from

Running totals with window functions, and deciding which source of truth feeds each term of an equation, are core SQL skills taught in the Data Analyst programme. The same discipline of reconciling a book figure against a physical count appears in pharmacy near-expiry stock. The free skill check shows how much of this your current resume already demonstrates.