MarCat

Daily sales report format in Excel for a shop: counter, shift, cash, UPI and returns

A day-end sales report format for a supermarket or grocery shop in Excel: counter and shift totals, cash, UPI and card, returns, cash tally and the formulas.

By Swarbhanu Gupta, who has run Banjara supermarket in Ahmedabad since January 2025 (billing on MarCat since April 2026) · 15 November 2026

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:

  1. Net sales (I2): =F2-G2-H2
  2. 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.
  3. 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:

  1. The original bill number.
  2. What came back and why (wrong item, damaged, expired, customer changed mind).
  3. 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.

Questions

What should a daily sales report for a shop show?
For each counter and shift: number of bills, gross sales, returns, discounts, net sales, and the split by cash, UPI and card. Then the cash tally: opening cash, cash sales, cash paid out, expected cash, counted cash and the difference.
How do I check if the cash in the drawer is right?
Expected cash = opening float + cash sales - cash refunds - cash paid out from the drawer. Count the drawer, then write counted minus expected. A small difference is change; the same difference on the same shift every week needs a closer look.
Should returns be shown separately or just subtracted?
Show them separately. Net sales hide returns. A return column lets you see how many returns came, on which counter and shift, and whether the money went back in cash or to UPI.
Why does my UPI total not match the payment app?
Usually a payment taken on the wrong counter's QR, a bill marked cash that was paid by UPI, or a payment received after the day closed. Check the app's settlement for that date against the report, counter by counter.
Does MarCat give a day-end report?
Yes. MarCat lets you open and close shifts, with a day-end report for each shift. Bills can be edited after saving, and every change is kept in an audit trail.