Daily Collection / Cash Position Sheet in Excel

Last updated: 27 June 2026 · Daily layout, formulas and a free template.

A daily cash position sheet in Excel records, for each day, your opening cash, the day's collections and payments, and the closing balance — split by cash, bank and UPI — so you always know exactly how much money you have and whether the till tallies. The closing balance of one day becomes the opening balance of the next, and a physical cash-count box catches any shortage on the spot.

Key takeaways

• Daily equation: Closing = Opening + Collections − Payments, carried into tomorrow's opening.
• Split by mode — Cash, Bank, UPI/card — because the physical till is counted, the rest is verified against statements.
• A cash-count box (notes × denomination) compared to the computed cash balance flags shortages or excess instantly.
• Collections by mode and party total with SUMIF; the day's net movement shows at a glance.
• Roll the daily closings up to feed your weekly cash-flow forecast.
• It's the shop-floor, end-of-day version of the bigger cash-flow statement — built for speed.

Fact box. A daily cash position (or "cash book" / day-end report) answers two questions every evening: how much money do we have, and does the physical cash match the books? Catching a mismatch the same day is far easier than reconstructing it a week later.

What is a daily cash position sheet?

It's a one-day snapshot of your money: what you started the day with, what came in, what went out, and what's left — kept by mode (cash in hand, bank, UPI). Retailers, distributors, clinics and any cash-handling SMB use it to close the day cleanly: tally the till, confirm bank/UPI receipts, and carry a verified closing balance into tomorrow.

It's deliberately simpler and faster than a full cash-flow statement — designed to be filled in five minutes at day-end.

What columns and layout does it need?

A clean daily layout, by payment mode:

Underneath, keep a transaction log for the day (Time, Party, Mode, In/Out, Amount) that the SUMIFs read from. The opening row each day is simply = the previous day's closing for each mode.

How do I add a physical cash-count box?

This is what makes a daily sheet trustworthy — comparing computed cash against counted cash:

1. Build a denomination table: rows for ₹500, ₹200, ₹100, ₹50, ₹20, ₹10, coins; a "count" column you fill at day-end.
2. Value each: =Denomination × Count; total with =SUM.
3. Compare: Difference = Counted cash − Closing cash (computed).
4. Flag it: conditional formatting turns the difference red if it isn't 0 — a shortage (negative) or excess (positive) to investigate before staff leave.

Fact box. Difference = Physical cash counted − Computed cash balance. A non-zero figure at day-end means a miscount, an unrecorded transaction, or a shortage — investigating the same evening, while memories are fresh, is far more effective than at month-end.

How do I see collections by mode and by person?

Use SUMIF/SUMIFS on the day's transaction log:

• Cash collected today: =SUMIFS(Amount, Mode, "Cash", Type, "In")
• UPI collected today: =SUMIFS(Amount, Mode, "UPI", Type, "In")
• Collected by one salesperson: =SUMIFS(Amount, CollectedBy, "Ramesh", Type, "In")
• Total payments out today: =SUMIF(Type, "Out", Amount)

This gives the day-end numbers a shop owner actually wants: total taken, how it split across cash and digital, and who collected what.

How to build it (step by step)

1. Set up the mode columns (Cash/Bank/UPI) and the four balance lines.
2. Enter today's opening as = yesterday's closing for each mode.
3. Log each transaction during the day in the transaction table.
4. Wire collections and payments with SUMIFS by mode.
5. Compute the closing balance =Opening + Collections − Payments.
6. Add the cash-count box and the difference flag.
7. Copy the sheet for each day (or one row per day on a monthly tab) so closings carry forward.

A free daily cash position template is available to download, no sign-up — opening carries forward, SUMIFs total the modes, and the cash-count box flags any gap.

How Ankeshan helps: Ankeshan builds your day-end cash position inside Excel — collections by mode and person, closing balances that carry forward, and a cash-count check that flags shortages the same evening. It's launching soon; join the waitlist.

Frequently asked questions

What is a daily cash position sheet? It's an end-of-day record of opening cash, the day's collections and payments, and the closing balance — split by cash, bank and UPI — plus a physical count to confirm the till matches the books.

How do I carry yesterday's balance into today? Set each mode's opening cell to = the previous day's closing cell for that mode. The closing balance (Opening + Collections − Payments) then flows forward automatically.

How do I catch a cash shortage? Add a denomination count box, value it (Denomination × Count), and compare the counted total to the computed cash closing. Any non-zero difference is a shortage or excess to investigate the same day.

How is this different from a cash-flow statement? The daily sheet is a fast day-end till report; the cash-flow statement aggregates over weeks or months and is used to plan and forecast. The daily closings feed the bigger statement.

Is the template free? Yes — free to download, no sign-up.

Sources

• Microsoft Excel function reference — SUMIF, SUMIFS, SUM: support.microsoft.com.
• General accounting practice for cash books and day-end cash reconciliation.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.

Related: Cash Flow Statement & Forecast in Excel » · Accounts Receivable Tracker in Excel » · Customer & Vendor Ledger in Excel » · Cashflow pillar »