How to Build a Cash Flow Report From Bank Transactions

A cash flow report answers one question: how much cash came in, how much went out, and what's left. This guide builds one from bank transactions or cash-account ledger entries — a direct record of the movements in the exported account(s), because every line actually happened. It's a backward-looking report of money that has already moved. If what you actually want is to project future cash — a cash flow forecast or projection — that's a different job with different inputs, and it isn't covered here.

Two things trip people up before they start. The first is the word "report": this guide uses "cash flow report" for the management view most teams need, which is not the statutory statement of cash flows. The formal statement is prepared under an accounting framework — it classifies cash flows into operating, investing, and financing activities and reconciles cash and cash equivalents to the equivalent items in the statement of financial position (under the indirect method, profit or loss is only the starting point, adjusted for non-cash and accrual effects). The second is the opening balance — a transaction list alone can't produce a balance without a starting point you supply. Get those two right and the rest is arithmetic.

01What a cash flow report shows

At its most useful, a cash flow report has a handful of lines per period:

• Opening balance — the cash you held at the start. It comes from you, not from the transaction file.
• Inflows — money received: customer payments, refunds, financing drawn.
• Outflows — money paid: payroll, suppliers, rent, software, tax.
• Net change — inflows minus outflows; the single number people came for.
• Closing balance — opening plus net, which should reconcile to the closing balance of the account(s) you exported when the account set, opening balance, complete posted activity, cutoff, and date basis all match.

Stack those side by side across months and you can read the rhythm of the business — which months generated cash and which leaned on the balance. A cash flow report is not the same as a profit-and-loss: profit can be positive while the bank runs dry, because an accrual-basis P&L counts invoices you've raised but not yet been paid. Cash flow strips that away and shows what actually landed. The formal statement of cash flows layers the movement into operating, investing, and financing activities; for most operators, the first valuable step is simply inflows, outflows, net, and running balance, by month — which is exactly what the sample below produces.

02Build one from bank transactions in Excel

If your export is clean and small, Excel handles this directly. Get each transaction onto one row with a date, a description, a category, and a signed amount (positive = in, negative = out), then:

• Add a month column so you can group by period. EOMONTH turns any date into its month-end, which makes a clean series to total against.
• Total by month with a PivotTable (months down the side, inflow/outflow across), or with SUMIFS against the month series. Inflows minus outflows gives net change.
• Carry the running balance: each month's closing = the previous month's closing + this month's net, starting from your opening balance.
• Reconcile: the final closing balance should equal the statement closing balance for the same account(s), cutoff, and date basis. Put opening, net, computed closing, and the statement closing in four cells with a difference cell beside them.

A couple of things can trip up the reconciliation. Transfers between your own accounts inflate both inflows and outflows in a consolidated all-cash view, so there you tag and eliminate both matched legs — but in a single-account report each transfer leg is a real movement in that account, so leaving it out breaks that account's reconciliation. And a zero reconciliation difference proves your totals, not your categories: you can tie to the statement perfectly and still have payroll sitting in the wrong bucket. Excel is fine for a clean export; it gets fragile when the export is large, spans several accounts, or needs categories fixed by hand every month.

03Build it traceably from a transaction export

When the export is messy, spans months, or the report has to be defensible — every figure explained to a lender, a board, or your own future self — the useful question becomes "just give me the monthly cash flow, traceable to the rows, from this file." That's what Anomaly does directly. You import the transaction export as a table and ask in plain language, supplying the opening balance:

"Positive amount = inflow, negative = outflow. The opening balance before the first month is 1,000. For each month give inflows, outflows, net change, and a running closing balance (opening + cumulative net), plus a breakdown by category. Make every total trace to the source rows, and confirm the final closing balance equals the opening balance plus the sum of all net changes."

It computes the report and returns a monthly table — inflows, outflows, net change, running closing balance, transaction count — plus a category breakdown, and it keeps View calculation and View data controls on the results so you can inspect the report's logic and the supporting rows behind any figure. It reconciles too: the closing balance it reports is your opening balance plus the sum of all net changes.

Two things to state plainly, because they're where cash reports go wrong. The opening balance is your input — the product doesn't invent a starting balance from a transaction file. And it reads the amount's sign as the inflow/outflow indicator; if your export uses separate debit and credit columns or a transaction-type field instead, say so in one line and it maps them. Within those bounds you get a reconciled, inspectable, monthly cash flow report from a plain request — the same result the Excel route makes you assemble and re-check by hand. For the full step-by-step version, see how to create a traceable finance report from transaction data. If you are still choosing the reporting route, compare financial reporting software when your numbers live in Excel.

04Daily cash flow report

A daily cash flow report is the same method at a finer grain: group by day instead of by month. It's what you want when timing matters — a payroll run and a large receivable landing in the same week decide whether Thursday is tight. The one caution at daily grain is the date basis: a card charge, the processor's settlement, and the day the bank actually credits your account can be three different dates, so pick the posting or value date that matches the statement you reconcile to, state it on the report, and use it for every row.

05From QuickBooks or FreshBooks

If your transactions already live in QuickBooks or FreshBooks, each has a built-in cash-flow report. QuickBooks Online produces a Statement of Cash Flows that you can export; FreshBooks offers a Cash Flow report on its Plus, Premium, and Select plans, and its Export for Excel action downloads a CSV rather than an Excel workbook. If the built-in report answers your question, use it — it isn't a universal transaction-level export. Where an export helps is a recut the built-in report can't give you: a custom category, period, or account grouping, with every figure tied to a row. Export the underlying transactions and build the exact report you want, the same way as any other transaction export. The point isn't to replace your bookkeeping; it's to get a cash view you can slice and defend.

06Cash flow analysis in Excel: templates and where they break

Search for "cash flow analysis Excel" and you'll find dozens of downloadable templates. A good template saves setup — the columns, formulas, and formatting are done — and for a stable, single-account business it can be all you need. Where a template can break is the gap between its expected shape and your data: it may assume a single account and one date basis, a particular sign convention, or a fixed set of columns, and it may leave transfer handling and reconciliation to you. Where your real export doesn't match that shape — extra columns, a different sign convention, multiple accounts — you adjust it by hand, and repeat that adjustment whenever the shape changes. The sample above deliberately isn't a template you fill in; it's your actual export, turned into the report, with the checks built into the result rather than left as an exercise.

FAQFrequently asked questions

—The bottom line

A cash flow report is one of the most useful things you can build from a transaction export, and it's mostly discipline: tag inflows and outflows, handle transfers by the view you're reconciling (eliminate both legs in a consolidated all-cash view, keep them in a single-account report), total by period, carry a running balance from an opening figure you supply, and reconcile the close to the matching statement. Excel does it for a small, clean export. When the file is large, spans accounts, or the numbers have to be defensible, describing the report in plain language and getting back a monthly, category-split, source-traceable result is a more direct workflow — and it stays a report of what happened, not a guess about what's next.

Turn your transaction export into a cash flow report

Related finance guides:

• How to build an accounts receivable aging report from an invoice export
• Budget vs actual variance analysis in Excel
• How to build an inventory aging report from receipts
• How to choose financial reporting software when your numbers live in Excel