Petrol Pump Shift Management System in Excel: Meter Readings, Litres Sold, Cash and UPI Collection, Shortage and Tank Dip

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: petrol-pump-shift-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Meter readings, Collections, Tank stock, Report, Shift sheet, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Raja Fuels, Nashik, 15 and 16 October 2026 (made-up data). Petrol ₹104.20, diesel ₹92.10 a litre (example rates); shortage alert ₹200.

Every shift closes with one number per attendant: collected minus expected.

2. Build it sheet by sheet

Step 1. Setup: rates and nozzles

1. Rates in A7:B8, nozzles with product in D7:E10, attendants in G7:G10, B4 shortage alert (₹200).

Step 2. Meter readings

1. One row per nozzle per shift: Date, Shift, Nozzle, Attendant, Opening, Closing, Test (L).
2. H5 litres =F5-E5-G5: test litres go back into the tank, so they are not sales.
3. I5 product =XLOOKUP(C5,Setup!$D$7:$D$10,Setup!$E$7:$E$10,"Check nozzle"), J5 rate and K5 amount =ROUND(H5*J5,2).

Step 3. Collections

1. One row per attendant per shift: Cash, UPI, Card. G5 collected =SUM(D5:F5).
2. H5 expected: =ROUND(SUMIFS('Meter readings'!$K$5:$K$3000,'Meter readings'!$A$5:$A$3000,A5,'Meter readings'!$B$5:$B$3000,B5,'Meter readings'!$D$5:$D$3000,C5),2) (SUMIFS).
3. I5 short or excess =ROUND(G5-H5,2) and J5 =IF(I5<-Setup!$B$4,"Short","OK").

Step 4. Tank stock and Report

1. Tank stock: D5 sold from meters, E5 book stock =B5+C5-D5, G5 variance =F5-E5, H5 variance % of sales.
2. Report per attendant: shifts, litres, sales, net short or excess and short shifts.

Step 5. Shift sheet and Dashboard

1. Shift sheet: pick a date and shift in B4:B5; nozzle litres and amounts, collected and difference. Set the print area.
2. KPI cards: litres sold, sales, collected, net short or excess, short shifts and tank variance. Charts: litres and short or excess by attendant.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Counting test litres as sales. Test fuel goes back to the tank. Subtract it, or the attendant looks short.
2. Opening reading typed fresh. The opening of a shift must equal the closing of the last one on that nozzle.
3. Old rate after a revision. Change the rate on Setup the moment it changes, and start a new day's rows.
4. UPI left out of the count. Collected = cash + UPI + card. Check the UPI app total for the shift.
5. Ignoring small tank variance. Small daily losses add up. Watch variance % every day; temperature and evaporation explain only a little.

5. More real examples

Example 1: CNG station. Kg instead of litres, with dispenser readings and the same collection match.

Example 2: Lubricant counter. Add a stock sheet for oil cans with sales by attendant, like the hardware store system.

Example 3: Fleet credit customers. Add a credit column to Collections and track transporter dues like the receivables system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate litres sold from meter readings?

Closing − opening − test litres, as in Meter readings H5.

How do I find shortage per attendant?

SUMIFS the meter amount by date, shift and attendant; collected − expected gives short (−) or excess.

How do I reconcile tank stock?

Book stock = opening + received − sold; compare with the dip stock.

Which rate should I use?

The rate in force for that day. Update Setup when it changes.

How do I print one shift?

Use the Shift sheet: choose the date and shift; it adds that shift's rows only.

7. Quick quiz

Ten questions on this page. Sign in to save your score.