At closing time every store asks the same three questions. How much did we sell? Is the cash in the drawer right? And if it is not, which counter and which shift?
If your counter runs on supermarket billing software, the same figures usually come from its day-end report.
A good daily sales report answers all three on one page. This guide gives you a format for a supermarket or grocery shop in Excel, the formulas, a made-up example, and the checks that catch most counter mistakes before they become a habit.
What the report has to answer
| Question | Where it comes from |
|---|---|
| How much did we sell today? | Net sales, all counters |
| How did customers pay? | Cash, UPI, card split |
| Is the drawer right? | Cash tally: expected vs counted |
| Who handled the money? | Counter and shift columns |
| What came back? | Returns and refunds |
| What went out of the drawer that was not a refund? | Cash paid out (tea, a delivery boy, a small supplier) |
If your report only has a sales total, you can see that something went wrong but never where.
The format: one row per counter per shift
Keep one row for each counter and each shift, not one row for the day. A store with two counters and two shifts has four rows a day.
Made-up numbers:
| A: Date | B: Counter | C: Shift | D: Cashier | E: Bills | F: Gross sales | G: Returns | H: Discounts | I: Net sales | J: Cash | K: UPI | L: Card |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 16-11 | 1 | Morning | R | 120 | 24,000 | 300 | 200 | 23,500 | 9,500 | 12,000 | 2,000 |
| 16-11 | 1 | Evening | S | 210 | 46,000 | 500 | 500 | 45,000 | 15,000 | 26,000 | 4,000 |
| 16-11 | 2 | Evening | T | 150 | 30,500 | 0 | 500 | 30,000 | 8,000 | 20,000 | 2,000 |
Formulas, row 2:
- Net sales (I2):
=F2-G2-H2 - Payment check: add a column M with
=J2+K2+L2-I2. It should be 0. Anything else means a bill was marked with the wrong payment, or a split payment was entered only once. - Average bill (N2):
=I2/E2. Watch this across days; a sudden drop on one counter is worth a look.
Use Data > Data Validation > List for the Shift column (Morning, Evening) and the Counter column, so "eve", "Evening" and "evening " do not become three different shifts in your totals.
The cash tally
The sales row tells you what the cash should be. The tally tells you what it is. One tally per counter per shift:
| Line | Example (₹) | Formula |
|---|---|---|
| Opening float | 2,000 | typed in at shift start |
| + Cash sales | 15,000 | from column J |
| − Cash refunds | 300 | returns paid in cash |
| − Cash paid out | 200 | with a slip for each one |
| = Expected cash | 16,500 | =B2+B3-B4-B5 |
| Counted cash | 16,450 | counted by note value |
| Difference | −50 | =B7-B6 |
Rules that make the tally work:
- Count by note value. Write how many ₹500, ₹200, ₹100 and so on. A note count is easy to recheck. A single total is not.
- Every paid-out needs a slip. Tea, auto fare, a ₹150 loose vegetable purchase. No slip, no paid-out.
- The float is fixed. Start every shift with the same float, and take it out first at the end.
- Count with two people when the shift changes, and both sign.
Reading the differences
A difference of a few rupees is change. What matters is the pattern. Keep the difference column for the month, then use a pivot table (Insert > PivotTable) with Cashier and Shift as rows and the sum of Difference as values.
| Pattern | Most likely cause |
|---|---|
| Small, random, plus and minus | Change given, rounding. Normal |
| Short on one shift, most days | Refunds not recorded, paid-outs without slips, or a training problem |
| Over by the price of one item | A cash sale billed as UPI, or a bill saved twice and cancelled late |
| UPI short, cash over by the same amount | Payment mode chosen wrong on the bill |
| Big difference on one day only | A missed bill or a missed paid-out. Check that day's bills first |
Returns: give them their own column
Many shops only record net sales, and returns disappear inside it. Keep returns separate, with:
- The original bill number.
- What came back and why (wrong item, damaged, expired, customer changed mind).
- How the money went back: cash, UPI, or a credit on the customer's khata.
A returned pack that is fine goes back into stock. A damaged one goes to the damaged column of your stock register. If returns are not split this way, both your cash and your stock will be wrong for the same reason.
The month view
At month end, a summary sheet with SUMIFS gives you totals by counter, shift and payment mode:
- Net sales for Counter 1, Evening:
=SUMIFS(Daily!I:I,Daily!B:B,1,Daily!C:C,"Evening") - UPI for the month:
=SUM(Daily!K:K)
Two things to look at every month: which shift carries most of the sales (that is where your best cashier should be), and whether the share of UPI is growing (that is how much small change you need for the float).
Where Excel stops working
The daily report in Excel is only as good as the numbers typed into it. Someone has to copy totals from the counter every evening, and copied numbers drift. If a bill is changed after the day closes, the sheet does not know. And with more than one counter, waiting for everyone's numbers before the report is ready makes closing late.
From our counter
We have run Banjara, our own supermarket in Ahmedabad, since January 2025. A few opinions from that counter:
- Close by shift, not by day. A day total tells you that something is off. A shift total tells you where.
- A difference is information, not an accusation. Check the payment modes and the paid-out slips before you suspect anyone. Treat a difference as a process to fix first.
- The report is for tomorrow morning. Read it before opening, while the cashier still remembers the day.
Where software helps
Billing software already knows every bill, its counter, its payment mode and any return. A day-end report from the software means nobody types totals by hand, and an edited bill shows up in the report instead of hiding from it. The cash count still has to be done by a person, with a pen.
How MarCat handles it
We have run Banjara since January 2025, and it has billed on MarCat since April 2026. Closing the day is a daily job in any store. This is what MarCat does:
- Shifts. Open and close shifts, with a day-end report for each shift.
- Several counters, one view. Multiple counters bill at the same time and share one view of stock.
- Changes are tracked. Bills can be edited after saving, and every change is kept in an audit trail.
- Reports. Sales comparison across periods and product-wise sales, plus GST-ready reports for your accountant.
If you want to see a shift close on a real counter, message us on WhatsApp at +91 96787 65518.
