Petrol Pump Daily Sales Report (DSR) Format, Column by Column, with Excel Formulas
पेट्रोल पंप की डेली सेल्स रिपोर्ट का फ़ॉर्मेट, मुफ़्त Excel शीट के साथ
6 min read ·
A petrol pump daily sales report (DSR) is the one page that records a shift: for every nozzle, the opening and closing totalizer readings, test litres, litres sold, the rate, the expected amount, and the cash, online and credit collected against it, ending in a Difference per nozzle. Below is the format column by column, a filled example, and the Excel formula for each calculated column, so you can rule it in a register or build it in a spreadsheet.
The format, column by column
| Column | What goes in it | Excel |
|---|---|---|
| Nozzle | Dispenser and fuel, e.g. D-#398-MS-1 | typed |
| Opening reading | Last shift's closing reading for this nozzle | copied, not re-read |
| Closing reading | Totalizer at handover, all decimals | typed |
| Test litres | Total litres drawn for 5 L tests on this nozzle | typed (from the Tests sheet) |
| Fuel Sold (L) | Closing − Opening − Test | =C7-B7-D7 |
| Rate | Rate per litre for this fuel this shift | typed |
| Expected Amount | Fuel Sold × Rate | =E7*F7 |
| Cash | Cash handed over for this nozzle | typed |
| Online | UPI, card, wallet total for this nozzle | typed |
| Pending Dues | Credit (udhar) sales for this nozzle | typed |
| Collected | Cash + Online + Pending Dues | =H7+I7+J7 |
| Difference | Expected − Collected | =G7-K7 |
| Operator | Who ran this dispenser | typed |
Above the table: pump name, date, shift (for example 10:00 AM to 10:00 AM), and the names of who closed it. Below it: a line for refills received, a line for expenses, and signatures. The handover checklist gives the order to fill it in.
A filled example
| Nozzle | Opening | Closing | Test | Sold | Rate | Expected | Cash | Online | Dues | Collected | Diff. |
|---|---|---|---|---|---|---|---|---|---|---|---|
| D-#398-MS-1 | 45210.500 | 46180.750 | 5 | 965.250 | 108.28 | 1,04,517.27 | 70,000 | 30,517 | 4,000 | 1,04,517 | 0.27 |
| D-#398-HSD-1 | 78340.000 | 79560.400 | 5 | 1215.400 | 94.80 | 1,15,219.92 | 80,000 | 22,230 | 12,990 | 1,15,220 | −0.08 |
| D-#404-MS-1 | 62334.700 | 63100.200 | 0 | 765.500 | 108.28 | 82,888.34 | 60,000 | 22,888 | 0 | 82,888 | 0.34 |
| D-#404-HSD-1 | 70117.900 | 71311.300 | 5 | 1188.400 | 94.80 | 1,12,660.32 | 75,000 | 26,000 | 11,638 | 1,12,638 | 22.32 |
Three nozzles are settled (a few paise from rounding). D-#404-HSD-1 is short by Rs.22.32, and the first thing to check is whether the rate changed at 6 AM, because that is exactly what a one-rate shift looks like. Why.
The Excel formulas, explained
Row 7 is the first nozzle; columns B..L as in the table above. E7 Fuel Sold =C7-B7-D7 G7 Expected =ROUND(E7*F7,2) K7 Collected =H7+I7+J7 L7 Difference =G7-K7 M7 Status =IF(ABS(L7)<1,"Settled",IF(L7>0,"Short","Extra")) Totals row: =SUM(E7:E12) etc. Guard: =IF(C7<B7,"Closing < Opening!","") (next to the reading)
The guard formula catches the commonest reading mistake, a closing reading lower than the opening. Keep the status rule at under one rupee counted as Settled; paise differences come from rounding, not from people.
The three things Excel cannot do well
- Entries during the shift. UPI and credit sales happen at the dispenser, by the operator, at 1:30 PM. A spreadsheet on the owner's laptop is filled at 10 AM the next day from slips and memory.
- A rate change at 6 AM. You need a second reading per nozzle and two rates in the same row. Doable, but it is the row people get wrong.
- Carrying readings over. Yesterday's closing has to be copied into today's opening by hand (or by a formula that breaks when a row is inserted). Copying is where digits get swapped.
The same report from Fuel Pump Book
Every closed shift in Fuel Pump Book produces this report as a one-page PDF: nozzle-wise readings, tests, litres, expected, cash, online, pending dues, difference and operator, plus refills and expenses for the shift. The opening readings carry over by themselves, staff entries are already in, and a rate change is split inside the row. The Excel backup from Settings has every shift in one sheet for your accountant.
Common questions
What is a DSR at a petrol pump?
DSR stands for Daily Sales Report. It is the one-page record of a day's (or a shift's) fuel sales: opening and closing readings per nozzle, test litres, litres sold, rate, expected amount, cash, online and credit collected, and the difference. Many pumps add tank dips, receipts and expenses.
How do I make a petrol pump daily sales report in Excel?
One row per nozzle with columns for opening reading, closing reading, test litres, fuel sold, rate, expected amount, cash, online, pending dues, collected and difference. The formulas for each column are on this page; fuel sold, expected, collected and difference are calculated, the rest is typed.
What is the formula for fuel sold in Excel?
Fuel Sold = Closing − Opening − Test litres, so in Excel for row 7 it is =C7-B7-D7. Expected Amount is =E7*F7 (Fuel Sold × Rate). Difference is Expected minus (Cash + Online + Pending Dues).
Is Excel enough for petrol pump hisab?
For one or two dispensers and an owner who closes every shift personally, yes. It gets hard when staff need to add entries during the shift, when the rate changes mid-shift, and when you want the difference per operator. That is the point where an app pays for itself.