Business

A weekly sales summary: what to add up from seven daily sheets

A weekly sales summary from seven daily sheets needs five totals: sales by mode, udhaar given, udhaar collected, refunds and closing cash.

Editorial illustration for A weekly sales summary: what to add up from seven daily sheets

To summarise a week from seven daily sheets, take five totals: sales by payment mode, udhaar given, udhaar collected, refunds, and closing cash. Put them beside last week's five, and the week tells you whether sales grew, whether credit is piling up, and whether the cash in hand makes sense.

What are the five totals, and why these?

  • Sales by mode: cash and UPI (and card, if you take it) added over seven days. This is your real takings.
  • Udhaar given: sales made on credit this week. It counts as a sale but is not yet money.
  • Udhaar collected: old credit paid back this week. It is money, but not a sale, so it must not be counted again.
  • Refunds: money returned to customers, taken off sales.
  • Closing cash: the drawer count at the end of the last day, which you compare with what the sheets predict.

A made-up week, next to the one before

TotalThis weekLast weekChange
Cash sales₹62,400₹58,000+₹4,400
UPI sales₹48,900₹44,500+₹4,400
Udhaar given₹7,300₹6,200+₹1,100
Udhaar collected₹5,100₹6,900-₹1,800
Refunds₹1,450₹900+₹550
Closing cash₹21,600₹19,800+₹1,800

Net sales for the week are cash plus UPI plus udhaar given, less refunds. This week: 62,400 plus 48,900 plus 7,300 minus 1,450 is ₹1,17,150. Last week: 58,000 plus 44,500 plus 6,200 minus 900 is ₹1,07,800. The difference is ₹9,350, which is 9,350 divided by 1,07,800, or about 8.7% up.

What does the udhaar line say?

Look at given minus collected. This week, 7,300 minus 5,100 means customers owe ₹2,200 more than at the start of the week. Last week, 6,200 minus 6,900 meant they owed ₹700 less. So sales are up, but part of that growth is on credit. If this continues for a few weeks, you are lending more of your working money to customers, which is a decision to make on purpose, not by drift.

A practical habit is to list the three largest outstanding balances each week and note who will be reminded, and when. A gentle reminder at the time of the next purchase often works better than a special visit.

How do I add up seven sheets?

If you keep each day on its own tab in Excel, named Mon to Sun with the cash total in C10, then =SUM(Mon:Sun!C10) adds the same cell across the tabs. Google Sheets does not support that form, so there it is easier to keep all days in one long list: date in A, type in B (Cash, UPI, Udhaar given, Udhaar collected, Refund) and amount in C. Then, with the week's first date in F1:

=SUMIFS($C$2:$C$500,$B$2:$B$500,"UPI",$A$2:$A$500,">="&$F$1,$A$2:$A$500,"<="&$F$1+6)

Change "UPI" to each of the other types for the other totals. The two date conditions pick out seven days starting at F1.

Does closing cash make sense?

Check it with simple arithmetic: last week's closing cash, plus the week's cash sales, plus udhaar collected in cash, minus cash refunds, minus cash paid out for purchases and expenses, minus any cash you took out or banked, should equal this week's closing cash. If it does not, the gap is the thing to chase. Because the sheets already hold most of these numbers, it takes a few minutes.

Do the summary on the same day each week, ideally Sunday night or Monday morning, so every comparison is made on a steady footing and nobody remembers last week's figures from memory.

What should I write at the bottom?

  1. One thing that went up, and a guess why.
  2. One thing that went down, and a guess why.
  3. One action for next week, such as chasing the three biggest udhaar balances or checking why refunds rose.

Keep the summary to a single page and keep every week's page. After a quarter, the pages line up into a record of your shop that you can read in minutes, and the pattern in your own figures will tell you more than any average of other shops.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports