A kirana cash book in Excel has one row per day: opening cash, cash sales, UPI sales, udhaar recovered, payments for stock and expenses, then expected closing cash. You count the drawer, type the real figure beside it, and a formula flags any shortage. Tomorrow's opening cash is today's counted cash, so the book carries forward by itself.
What columns does the daily sheet need?
| Column | Heading | Note |
|---|---|---|
| A | Date | One row per day |
| B | Opening cash | Yesterday's counted cash |
| C | Cash sales | Money in the drawer from sales |
| D | UPI sales | Goes to the bank, not the drawer |
| E | Udhaar recovered (cash) | Old credit collected in cash |
| F | Stock bought (cash) | Paid to suppliers from the drawer |
| G | Wages | Helper, delivery |
| H | Rent, electricity, other | Anything else paid in cash |
| I | Expected closing | Formula |
| J | Drawer count | What you actually count at night |
| K | Difference | Formula |
| L | Flag | Formula |
What are the formulas?
- I2, expected closing cash: =B2+C2+E2-F2-G2-H2
- B3, tomorrow's opening: =J2
- K2, difference: =J2-I2
- L2, flag: =IF(J2="","",IF(K2<0,"CASH SHORT",IF(K2>0,"CASH EXTRA","OK")))
Notice that UPI sales (column D) are not in the closing cash formula. A UPI payment does not put a note in the drawer, so counting it there would show a false surplus. Keep D for a different check: at day end, match it against your payment app's total for the day.
Can you walk through one day?
Say the day starts with ₹4,000 in the drawer. Cash sales are ₹11,800 and UPI sales ₹6,200. You collect ₹1,500 of old udhaar in cash. You pay ₹5,000 to a supplier from the drawer, ₹600 to the helper and ₹400 for other costs.
Expected closing: 4,000 plus 11,800 plus 1,500 minus 5,000 minus 600 minus 400 is ₹11,300. You count the drawer and find ₹11,150. K2 is 11,150 minus 11,300, so minus ₹150, and L2 shows CASH SHORT. Total sales that day are 11,800 plus 6,200, or ₹18,000. Tomorrow's opening cash is 11,150, the amount you actually counted, not the expected one, so the shortage is recorded once and does not repeat.
How do I handle udhaar and bank deposits?
Credit sales are not cash, so they should not appear in cash sales. Keep a separate udhaar page: customer, date given, amount, date recovered. Only when a customer pays, enter it in column E (cash) or column D if they pay by UPI. When you take cash to the bank, enter the deposit in column H and note it; the money reappears in your bank statement, which is a good check. If you withdraw cash from the bank to top up the drawer, add a column called Cash from bank and include it in the column I formula.
How do I look at the month?
- Total cash sales: =SUM(C2:C32); total UPI: =SUM(D2:D32).
- Total sales: =SUM(C2:D32).
- Number of days with a shortage: =COUNTIF(L2:L32,"CASH SHORT").
Compare the month's UPI total with the credits in your bank statement. Settlements from a payment app may reach the bank a day or two later, so match week totals rather than single days, and check your own statement for the actual pattern.
Freeze the header row, protect the formula columns, and save a copy at month end. Your CA may want these totals, so ask what format they prefer.
Where does a personal tracker fit?
This sheet records the shop's cash. It does not do invoicing or GST filing. SpendixAI is a personal expense tracker, not a shop accounting tool, so keep the shop's money and your household spending separate: when you pay yourself from the shop, record the withdrawal here and the receipt as income in your own account.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

