Cash flow chart in Excel: receipts, payments and balances

A cash flow chart in Excel combines receipts, payments and closing balances so a finance team can see when movements put cash under pressure. Build the chart from a reconciled period table rather than selecting unrelated columns from a bank export. The closing cash line must equal opening cash plus cumulative receipts minus cumulative payments.

Start with dated cash movements

Use one currency and a clearly defined set of bank accounts. Export settled receipts and payments with transaction date, account, reference and amount. For a forward view, use expected payment dates and label those rows as planned; the cash flow forecast guide covers how those dates are estimated.

For this example, a US distributor has opening cash of $100,000 on January 1, 2026. Its simplified movement export contains the following rows. Store these in a worksheet named Movements, with headers in row 1 and data in rows 2 through 7. Dates must be real spreadsheet dates, displayed here in US format.

Receipt and payment amounts are positive in the export. Internal transfers between included accounts must cancel or be excluded from both sides under a documented consolidation rule. Otherwise, the columns can imply collection and spending activity that only moved money within the company.

Summarize the rows by month

Create a Summary worksheet with these columns: A month start, B receipts, C payments, D net movement, E opening cash, F closing cash and G buffer headroom. Enter the first day of each month in A2:A4, and put the minimum cash buffer of $60,000 in J2.

In B2 and C2, use inclusive month starts and exclusive next-month starts. That also captures transactions that include a time of day:

B2 =SUMIFS(Movements!$C$2:$C$7,Movements!$A$2:$A$7,">="&A2,Movements!$A$2:$A$7,"<"&EDATE(A2,1))
C2 =SUMIFS(Movements!$D$2:$D$7,Movements!$A$2:$A$7,">="&A2,Movements!$A$2:$A$7,"<"&EDATE(A2,1))
D2 =B2-C2
E2 =100000
F2 =E2+D2
G2 =F2-$J$2
E3 =F2

Fill B:D and F:G down through row 4; fill E3 down to E4. Microsoft documents SUMIFS as summing cells that meet multiple criteria, which is the date-window test used here. Extend the source ranges when adding movements.

All amounts are whole dollars; no rounding is needed. The final row sums movements but carries the starting and final balances. Adding the three closing cash amounts would have no useful cash interpretation.

Build the chart from the summary

Add H, labeled Payments shown below zero, and I, labeled Cash buffer:

H2 =-C2
I2 =$J$2

Fill down. Select the month labels and the receipt, negative-payment, closing-cash and buffer series. Insert a combo chart, then set receipts and payments to clustered columns and the two balance series to lines. Microsoft's Excel chart-type reference includes clustered columns, lines and their combination.

Use one USD axis so the reader can compare cash with the buffer directly. Keep the zero line visible. Label the chart “Monthly cash movements and closing cash, USD” and identify whether the rows are actual or planned. Do not stack a closing balance on top of receipts: a balance is a position at a date, while receipts are movements during the period.

The columns show two months with negative net movement. The line shows that March closes at $55,000, below the $60,000 buffer by $5,000. That is the decision the chart supports: investigate the March receipt and payment rows, then assess timing or funding options using the company's approved treasury process.

Prove the chart before sharing it

The overall roll-forward must return zero:

=F4-(E2+SUM(B2:B4)-SUM(C2:C4))

Here, 55,000 - (100,000 + 440,000 - 485,000) = 0. Also compare each month's actual closing balance with the reconciled balance for the same included accounts. A matching arithmetic roll-forward does not prove that an export is complete.

Filter the movement table to March and confirm that its receipt and payment rows total $140,000 and $170,000. This makes the reported shortfall traceable to transactions. A pivot chart can help explore movement categories, but preserve the same cash reconciliation.

For a future-period presentation, keep assumptions beside the cash flow forecast chart. For exposure between month ends, use a daily cash flow worksheet. A monthly chart cannot establish the lowest cash balance reached inside the month.

Where it goes wrong

• Posting date replaces cash date. An invoice date measures billing activity. Use the date cash settles for actual movements, and an explicit expected cash date for planned rows.
• Payment signs change halfway through. Positive stored payments are subtracted here. A signed bank export needs a different treatment; reconcile that convention before aggregation.
• Transfers inflate activity. Counting both sides of transfers as business receipts and payments distorts the columns even when final cash still agrees.
• Balances are added across periods. Sum receipts and payments across time; carry balances forward. Only add account balances that refer to the same date and scope.
• A monthly view becomes a daily assurance. A period-end line can miss payroll or supplier payments that temporarily exhaust headroom.

Covirage is not affiliated with Microsoft.

Check the movements in your own file

Upload your cash movement workbook or Google Sheet with its dates, receipts, payments and opening balance. Covirage maps the columns, and its tools compute and check the movements and balance comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See Excel analysis with Covirage for the supporting workflow.