How to build a monthly cash flow spreadsheet from bank statements
Turn statement rows into a historical cash-movement report, separating operating activity, financing and internal transfers with a free Excel workbook.
To build a monthly cash flow spreadsheet from bank statements, gather every included bank account, standardise the signed amounts, classify movements and summarise them by month. Reconcile the result to opening and closing cash. This produces a historical cash-movement report; it is not automatically a profit-and-loss statement or a formal statutory cash flow statement.
Download the cash flow workbook (.xlsx). It contains synthetic transactions and monthly SUMIFS summaries for 2026. Inputs include a reporting year and opening cash, with separate Operating, Investing, Financing, Internal transfer and Review classifications.
Define which accounts count as cash
Write down the bank accounts and currency included in the report. A transfer between two included accounts should net to zero. If the destination account is outside your scope, the payment is still a movement out of the cash you are reporting; do not remove it just because the description says Transfer.
Keep credit cards outside the bank-cash balance. If you import card purchases into this cash report and also include the bank repayment, you may count cash out twice. A separate card reconciliation supports the bookkeeping without redefining the bank balance.
Prepare the statement rows
Use native bank CSVs where available. For PDFs, convert the statements, inspect the results and save an untouched copy. Combine months with the merge workflow, retaining Account and Statement references.
Use actual date values, a consistent currency and signed amounts. The workbook uses positive money in and negative money out. Importing expenses as positive numbers will reverse the meaning of the cash report.
Classify the cash movements
For a simple internal report:
- Operating: customer receipts and routine business payments.
- Investing: equipment and other investment-related cash movements, subject to your reporting policy.
- Financing: loans received, repayments of principal and owner funding or withdrawals as appropriate.
- Internal transfer: movements between bank accounts included in the report, with both sides present.
- Review: anything awaiting evidence or a classification decision.
These are working labels. Interest, tax, leases and other items may need specific treatment in formal reporting, so agree the policy with your accountant.
Read the worked example
The fictional July data has £5,000 customer receipts, £2,000 operating payments, a £900 equipment payment and a £3,000 loan receipt. A £500 transfer out of one included account and into another nets to zero.
| July cash movement | Amount |
|---|---|
| Operating | 3,000.00 |
| Investing | -900.00 |
| Financing | 3,000.00 |
| Internal transfer, net | 0.00 |
| Total movement | 5,100.00 |
| Opening cash | 2,000.00 |
| Closing cash | 7,100.00 |
The business increased cash by £5,100. It did not necessarily earn £5,100 profit: £3,000 was borrowed, and accounting treatment of the equipment differs from simply recognising the bank payment as an expense.
Reconcile every month
The monthly table rolls forward the opening balance using all signed rows, including Review and any unmatched transfer. This keeps unexplained movements visible instead of silently dropping them from closing cash.
Compare calculated closing cash with the sum of the included bank statements' closing balances. Investigate missing accounts, duplicate imports, incomplete transfers and dates parsed in the wrong locale. A nonzero Internal transfer total is a prompt to investigate scope or a timing difference, not a number to force to zero.
The workbook includes a full-year movement control against the monthly totals. Its working range is 500 transactions; extend the formulas and summary ranges together for larger datasets. Filter out-of-year rows or change the reporting year intentionally rather than assuming every imported row belongs in the report.
Use the report for the next decision
Review recurring payment timing, customer collection patterns and the dependence on financing. If you want a forecast, create a separate forward-looking model using expected future receipts and payments; copying historical cash does not account for new commitments.
Keep the statement exports and classification notes alongside the workbook. Use the categorisation guide to maintain consistent decisions and the quarter-end checklist to review outstanding issues.
Convert the source statements, then build a cash report whose totals you can explain account by account.
Statements in, clean books out.
NoRekey converts bank statement PDFs to CSV, Excel, OFX and QFX — every conversion balance-checked. Free to try.
Convert a statement free →How to categorise bank transactions in Excel
Use a reviewed payee lookup, an exceptions queue and a category summary to organise converted bank transactions without changing the source evidence.
How to combine multiple bank statements into one Excel file
Merge monthly statement PDFs into one spreadsheet, retain the source of every row, and check accounts, currencies and overlaps before importing.
Quarter-end bank reconciliation checklist for small businesses
Close July–September with an account inventory, statement checks, outstanding-item list and a clear handover to your bookkeeper.