Daily cash flow: track receipts, payments and balances

Daily cash flow shows the receipts, payments and closing cash balance for each day. It exposes a temporary shortage that a month-end balance can hide, provided the report uses settlement dates and reconciles to the same bank accounts throughout.

The calculation is a daily roll-forward. A broader cash flow forecast explains future funding needs; this view identifies the particular day a payment or delayed receipt puts pressure on cash.

Start with the bank movements and a defined cash floor

Export settled transactions for the accounts treasury can use to make payments. Keep the bank reference, settlement date, receipt amount and payment amount. Remove duplicate exports and exclude transfers between accounts included in the same report: those move cash within the company without changing its total.

Use the previous day's reconciled closing balance as opening cash. Document excluded restricted accounts separately. A book balance and a bank available balance can differ, so label the balance basis rather than switching between them.

Set a minimum cash balance approved by the person responsible for treasury. That is a management buffer, not a universal benchmark. For this example, a US distributor starts with $120,000 and requires an $80,000 minimum.

Create a sheet named Raw with these columns and rows. Enter column A as actual spreadsheet dates, without time values; columns C and D are numeric amounts with receipts and payments both positive.

These transactions occupy rows 2 through 11, with headers in row 1. Real exports can contain many payments on the same day; the aggregation below adds them rather than assuming one row per date.

Roll cash forward with spreadsheet formulas

Daily net movement = Cash receipts minus Cash payments

Closing cash = Opening cash plus Daily net movement

Cash headroom = Closing cash minus Required minimum cash

On an Inputs sheet, put opening cash of 120000 in B1, minimum cash of 80000 in B2 and the independently obtained October 23 bank closing balance of 100000 in B3.

Create a Daily sheet with the columns in the table below. The formula uses SUMIFS to add amounts that match a date, following the documented syntax in Microsoft Excel and Google Sheets.

Enter these formulas in row 2:

A2 = DATE(2026,10,19)
B2 = Inputs!$B$1
C2 = SUMIFS(Raw!$C$2:$C$11,Raw!$A$2:$A$11,A2)
D2 = SUMIFS(Raw!$D$2:$D$11,Raw!$A$2:$A$11,A2)
E2 = C2-D2
F2 = B2+E2
G2 = F2-Inputs!$B$2

In row 3, enter =A2+1 in A3 and =F2 in B3. Copy A3:B3 through row 6, then copy C2:G2 through row 6. Each new day's opening cash now equals the previous day's closing cash.

All amounts are exact whole dollars; no rounding is needed. Total receipts are $290,000 and total payments are $310,000. Opening cash of $120,000 plus those receipts minus those payments gives closing cash of $100,000.

Prove the balance before interpreting the gap

The following checks belong outside the report table on Daily:

=F6-B2-SUM(E2:E6)
=F6-Inputs!$B$3
=MIN(G2:G6)

The first two return zero: the roll-forward adds up, and its final balance matches the independent bank balance. The last returns -25000, showing the deepest closing-balance shortfall against the company's floor.

Also reconcile each day's balance, not just the final day. The IRS recordkeeping guidance describes reconciling checking accounts monthly and identifying bank charges and errors. A daily treasury report needs a more frequent comparison using the same principle: explain differences instead of inserting an unexplained adjustment.

The distributor finishes above its minimum, but October 20 closes $25,000 below it. Treasury should identify which receipts or payment dates could reasonably change before the equivalent future payment run. A closing-balance check does not prove the business stayed above its floor during the day; payment sequencing may require a separate intraday review.

Keep actual cash separate from scheduled cash

Maintain expected customer receipts and approved payment runs alongside the bank actuals, with a clear cutoff between them. Once a transaction settles, replace its scheduled entry with the bank record. Preserve the expected date separately if the team wants to explain a timing variance.

Use the daily cash flow worksheet guide for the dedicated layout, and the 13-week cash flow approach when the planning horizon extends beyond the daily view. If a gap requires evaluating repayment capacity, cash flow lending covers that separate analysis.

Where daily cash flow goes wrong

• Mixing dates. Invoice dates, payment initiation dates and settlement dates answer different questions. Use one date basis for actual movements.
• Counting transfers as receipts. Include both sides or remove both sides when the accounts are inside the reporting boundary.
• Adding forecasts to actuals. A collected invoice must leave the expected-receipts schedule when its bank receipt enters actual cash.
• Calling all cash available. Restricted balances and amounts unavailable for payment should not inflate treasury headroom.
• Checking only the final balance. A matched ending balance can conceal offsetting errors or a shortage earlier in the period.

Covirage is not affiliated with Microsoft or Google.

Check daily cash flow on your own file in Covirage

Upload a bank export or Google Sheet with your receipt and payment schedule so Covirage can map the columns and its tools can compute and check the daily roll-forward. The external AI model explains the figures, with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for finance teams for the supporting business context.