Daily cash flow template in Excel

A daily cash flow template in Excel rolls scheduled receipts and payments into a closing cash balance for each date. Add a minimum buffer and actual columns so finance can see which days need attention and which timing assumptions proved wrong. The worksheet layout below can be copied into a workbook and filled from your company's receipt and payment schedules.

Set the scope before filling the worksheet

Choose the bank accounts and legal entity covered by the plan. Use opening cash available for those operations, with restricted amounts excluded or shown separately. Keep USD as the reporting currency in this example; a workbook with other currencies needs a documented conversion basis.

The planning question is whether scheduled movements leave enough cash on each future date. The separate daily cash flow guide covers the broader monitoring process. This worksheet focuses on maintaining the plan and comparing it with the movements that subsequently occur.

Use the expected cash settlement date, rather than the invoice or accounting date. An approved supplier invoice may already be an expense while its payment is still scheduled for a later day. The cash flow forecast guide explains the wider receipt and payment assumptions.

Build the input table

Create an Excel table named Movements. Preserve separate planned and actual rows so importing actuals does not erase the schedule you are evaluating.

Use one row per expected cash movement. Split a payment across dates when it is scheduled in installments. For actuals, use the settlement date in the company's cash records and retain the corresponding bank reference.

Microsoft's structured-reference documentation explains how named table references adjust when rows are added. That helps prevent an appended receipt from falling outside a fixed formula range. Covirage is not affiliated with Microsoft.

The daily planning layout

A US distributor starts November 2, 2026 with $50,000 available cash. Finance selects an illustrative $25,000 minimum buffer. The five dates below are consecutive weekdays; this example has no weekend movements.

All amounts are whole USD, with no rounding. The receipt and payment columns are the daily sums of the planned movement rows.

Closing cash = Opening cash + Receipts − Payments

Headroom = Closing cash − Minimum buffer

November 3 is below the buffer by $5,000 even though the period ends at $37,000. A closing balance above the buffer at the end of the week does not prove that every earlier date was adequately funded.

Enter the Excel formulas

On a summary sheet, use columns A through F for the six columns in the planning table. Put opening cash in N1 and the minimum buffer in N2. Enter the first date in A2 and use =A2+1 in A3 when showing every calendar day.

For the first daily row:

B2 = $N$1
C2 = SUMIFS(Movements[Amount],Movements[CashDate],A2,Movements[Basis],"Plan",Movements[Direction],"Receipt")
D2 = SUMIFS(Movements[Amount],Movements[CashDate],A2,Movements[Basis],"Plan",Movements[Direction],"Payment")
E2 = B2+C2-D2
F2 = E2-$N$2

The cell labels show where each expression belongs. Enter the expression after the label. In B3 enter =E2, then fill down; fill C2:F2 down through the horizon. Microsoft documents SUMIFS as adding amounts that meet multiple criteria. Here it separates dates, planned movements, and cash directions.

Highlight negative headroom with conditional formatting. For this example, =MIN(F2:F6) returns -5,000. The positive amount needed to reach the buffer is:

=MAX(0,-MIN(F2:F6))

This is a gap to the chosen buffer, not automatically a borrowing requirement. Finance still needs to decide how to address it and confirm the timing of any action.

Add actuals without replacing the plan

Use G for ActualReceipts, H for ActualPayments, I for ActualClosing, and J for ClosingVariance. Set N3 to the last date with complete actual movements. For the first row:

G2 = IF(A2<=$N$3,SUMIFS(Movements[Amount],Movements[CashDate],A2,Movements[Basis],"Actual",Movements[Direction],"Receipt"),"")
H2 = IF(A2<=$N$3,SUMIFS(Movements[Amount],Movements[CashDate],A2,Movements[Basis],"Actual",Movements[Direction],"Payment"),"")
I2 = IF(A2<=$N$3,$N$1+G2-H2,"")
J2 = IF(I2="","",I2-E2)

In I3 use =IF(A3<=$N$3,I2+G3-H3,"") and fill down. Copy the G, H, and J formulas down too. Future actual cells stay blank rather than implying that all expected activity failed to occur.

Once the example period is complete, actuals are:

The minimum actual closing cash is $23,000, leaving a $2,000 buffer shortfall. The closing variance is cumulative: November 3's positive $3,000 does not mean that day's receipts alone exceeded plan by that amount.

Check totals and test the timing

The plan reconciles as $50,000 + $75,000 − $88,000 = $37,000. Actuals reconcile as $50,000 + $75,000 − $89,000 = $36,000. The $1,000 additional payments explain the final $1,000 cash shortfall against plan. Tie actual closing cash to the same in-scope cash records after investigating reconciling items.

Change one planned input: move November 5's $10,000 receipt to November 3, assuming the customer confirms earlier settlement. The revised closing balances are $55,000, $30,000, $48,000, $40,000, and $37,000. The minimum rises to $30,000 while final cash remains unchanged. This separates a timing issue from an overall lack of receipts.

For a longer horizon, use the 13-week cash flow template. To visualize the daily movements and balance, follow the separate cash flow chart in Excel guide.

Where it goes wrong

• Using invoice dates. Planning dates should reflect when cash is expected to settle.
• Including internal transfers as new cash. Eliminate transfers between accounts inside the worksheet's scope or they inflate receipts and payments.
• Treating missing actuals as zero. Advance the completeness cutoff only after all in-scope movements have been captured.
• Reading closing cash as the intraday minimum. A daily closing worksheet can miss a payment that settles before a same-day receipt. Review settlement order on tight dates.

Compare your cash schedule in Covirage

Bring your planned receipt and payment schedule and actual movement file to Covirage. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the comparison with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See FP&A reporting for the reporting context.