Inventory reconciliation and leak detection

A tank that is leaking slowly gives very few signs. There is no puddle, no smell at first and no alarm if the leak is below what the sensors can see. But fuel that leaks out of a tank is fuel that was paid for and never sold, and careful records will show it as a shortfall that grows day after day. Inventory reconciliation is the oldest leak detection method there is: comparing how much fuel the records say should be in the tank with how much is actually there.

This lesson explains how daily inventory records are kept, how they are reconciled, what the numbers mean and when a shortfall must be investigated. It also explains how inventory records fit with the other leak detection methods from Lesson 7.

Book inventory and measured inventory

Every day, for each tank, the station works out two numbers:

• Book inventory: what the records say should be in the tank. It starts with yesterday's measured volume, adds any fuel delivered today and subtracts the fuel sold through the dispensers, as counted by the dispenser meters or the point-of-sale system.
• Measured inventory: what is actually in the tank, measured by the tank gauge (or, if the gauge is out of order, with a gauge stick and the tank's chart).

The difference between them is the daily variance, often called over or short. A tank that is tight and well measured will show small variances that go both ways: a little over one day, a little short the next. They come from rounding, meter accuracy, temperature changes and measuring error, and over a month they mostly cancel out.

A leak looks different. It is a shortfall that keeps going the same way, day after day. On any one day it may be small enough to look like ordinary noise, but added up over weeks it grows steadily. That is why the daily numbers are added into a running, or cumulative, variance, and why the trend matters more than any single day.

Doing the daily reconciliation

At Northfield, Leah Morin or Raj Patel does the reconciliation every morning from the tank gauge's inventory report and the point-of-sale fuel sales report. The Alberta Fire Code requires inventory records to be kept for tanks at retail stations and reconciled, and sets when unexplained losses must be investigated; the station procedure turns that into a daily routine and an investigation trigger. Northfield's procedure says to investigate if the month's cumulative shortfall for any tank goes over 0.5% of the fuel sold from it, or if the variance shows a steady loss for more than a week, whichever comes first. These triggers are Northfield's own, chosen to be on the cautious side; the code and the regulator's guidance set the legal minimum.

Good reconciliation depends on good data. The common sources of error are:

• Deliveries recorded with the wrong volume or to the wrong tank, or not at all.
• Readings taken at different times of day from the sales report, so some sales are counted on the wrong day.
• Dispenser meters out of calibration, which make every sale look slightly bigger or smaller than it was.
• Temperature: fuel delivered warm in summer shrinks in the cool ground; many gauges report temperature-corrected volumes to allow for this.
• Water in the tank counted as fuel. The gauge measures water separately; if it is not working, a stick with water-finding paste shows the water level.
• Fuel used for testing the dispensers, or pumped back into the tank after a meter check, that is not recorded.

When a variance looks wrong, the first step is to check the data: the delivery tickets, the sales report and the gauge readings for the days in question. Many apparent losses turn out to be a delivery recorded on the wrong tank. If the data are correct and the loss is still there, the station treats it as a possible leak.

Reconciliation in code

The program below reconciles a month of illustrative daily readings for one tank. It works out the daily and cumulative variance, compares the month with the limit and checks for a steady one-way trend by counting the days the tank was short.

import random
random.seed(8)
# Illustrative month for the 50,000 L regular tank; all figures made up.
days = 30
sold = [round(random.uniform(5200, 6800)) for _ in range(days)]
delivered = [32000 if d in (6, 16, 26) else 0 for d in range(days)]
leak_per_day = 9.0                         # hidden loss we are trying to find
measured = 24000.0
cumulative, short_days, rows = 0.0, 0, []
for d in range(days):
    book = measured + delivered[d] - sold[d]
    measured = book - leak_per_day + random.gauss(0, 6)    # gauge noise
    variance = measured - book
    cumulative += variance
    short_days += variance < 0
    rows.append((d + 1, round(variance), round(cumulative)))

total_sold = sum(sold)
limit = 0.005 * total_sold                  # Northfield trigger: 0.5% of sales
print("Day  variance  cumulative (L)")
for r in rows[::5]:
    print(f"{r[0]:>3} {r[1]:>9} {r[2]:>11}")
print(f"Month: sold {total_sold:,} L, cumulative {cumulative:,.0f} L, trigger -{limit:,.0f} L")
print(f"Short on {short_days} of {days} days")
trend = short_days >= 22
if cumulative < -limit:
    print("OVER TRIGGER: investigate as a possible leak")
elif trend:
    print("Within the monthly trigger, but a steady one-way loss: investigate")
else:
    print("Within normal variation")

Day  variance  cumulative (L)
  1       -18         -18
  6        -7         -58
 11       -18         -88
 16         1        -136
 21       -13        -158
 26        -6        -214
Month: sold 176,878 L, cumulative -241 L, trigger -884 L
Short on 27 of 30 days
Within the monthly trigger, but a steady one-way loss: investigate

In this example the leak is only about 9 L a day, a tiny fraction of the 6,000 L sold each day, so no single day looks alarming, and the month's loss is still inside Northfield's 0.5% trigger. But the tank was short on almost every day of the month, which is the signature of a leak. A leak of 9 L a day is more than 3,000 L a year going into the ground, which is why Northfield investigates trends and does not just wait for the monthly trigger.

Other leak detection methods

Inventory reconciliation is one of several methods, and each catches different things:

What to do when the numbers say leak

A reconciliation that shows an unexplained loss over the trigger is a possible leak and must be followed up promptly. At Northfield, the manager checks the data first, then has the tank gauge run an extra leak test, checks the sumps and alarms and calls the tank contractor to test the tank and lines if the loss is still unexplained. Under Alberta's rules, a confirmed release must be reported (Lesson 9), and the fire code sets out what must be done with a leaking tank system, which can include taking it out of service. Not reporting a suspected leak because "it's probably the meters" is the kind of decision that turns a small repair into a large cleanup.

For supervisors: the reconciliation habit

Do the reconciliation every day, at the same time, from the same reports, and sign it. Review the cumulative variance for each tank every week, not just at the end of the month, and look at the trend. Check every delivery ticket against the gauge's delivery report the same day. Have the dispenser meters calibrated on schedule. Keep the records for as long as the rules require, and send them to a SIR company if the station uses one. Most importantly, if the numbers keep saying short, believe them until the cause is found.

When the gauge is out of order

If the tank gauge fails, the station still has to keep inventory records, so the tanks are measured by hand with a gauge stick: a long wooden or fibreglass rod marked in centimetres, lowered through the fill pipe or a gauging port to the bottom of the tank. The wet mark on the stick shows the fuel depth, and the tank's calibration chart turns that depth into litres. A stick reading is much less precise than the gauge, so small variances will be bigger and noisier until the gauge is repaired. Stick readings are taken at the same time every day, with no deliveries or sales in progress, and only by a worker trained to do it: the fill area is a vapour zone, and the stick must be lowered gently so it does not splash or make sparks.

A gauge that is out of order also means its leak tests and alarms are not working. That is a gap in leak detection, and the manager must arrange the repair quickly and record how the tanks were monitored in the meantime.

Water in the tank

A little water at the bottom of a tank is common: it comes from condensation and from deliveries. A water level that rises when it has not rained, or rises after every rain or snowmelt, can mean that water is getting in through a loose fill cap, a damaged spill bucket or a hole in the tank. Anywhere water can get in, fuel can get out. The gauge reports water in each tank; the daily record should include it, and a rise should be reported to the manager just like a fuel loss.