Cash Flow Statement From Bank Statements (Excel Guide)

TL;DR Bank statements are a record of cash, which makes them a natural source for a cash flow report. Convert every account's statements to one table, tag transfers between your own accounts, group the rest into operating, investing and financing (or simpler groups like income, costs, owner and loans), and summarize by month. The report must tie out: opening cash plus net cash flow for the period should equal closing cash across all accounts. This gives you a direct-method view of cash, which is useful for managing a small business, though it isn't a substitute for accountant-prepared financial statements.

Profit and cash aren't the same thing. A business can be profitable on paper and still run out of money, because customers pay late, inventory ties up cash, or a loan repayment lands in the same month as a tax bill. A cash flow report shows where cash actually came from and where it went, month by month.

If you have accounting software, it can produce a cash flow statement for you. But many small businesses, freelancers and side projects don't have clean books, or the books are months behind. Bank statements, on the other hand, always exist. This guide shows how to build a practical cash flow report from them in Excel.

This is general information to help you understand and manage your cash. For formal financial statements, tax filings, or anything a lender or investor will rely on, work with a qualified accountant.

What a cash flow statement shows

A formal cash flow statement is usually divided into three sections:

• Operating activities: cash from running the business. Customer receipts, payments to suppliers, wages, rent, utilities, taxes.
• Investing activities: cash spent on or received from long-term assets. Buying equipment or a vehicle, selling an asset.
• Financing activities: cash from or to owners and lenders. Loan proceeds and repayments, owner contributions and withdrawals, dividends.

The total of the three sections is the net change in cash, which should equal closing cash minus opening cash.

There are two ways to prepare the operating section. The indirect method, common in formal statements, starts from profit and adjusts for non-cash items and changes in working capital. The direct method lists actual cash receipts and payments. Bank statements give you the direct method naturally, because every line is an actual receipt or payment.

Why bank statements work for this

A bank statement is a list of cash movements with a verified opening and closing balance. That gives you two things a cash flow report needs:

1. Every cash movement for the period, in one place.
2. A built-in check. If the opening balance plus all movements equals the closing balance for each account, nothing is missing.

The limitation is that a bank statement only shows cash. It won't show what customers owe you, bills you haven't paid yet, or the value of inventory. A cash report from bank statements tells you what happened to cash; it doesn't tell you whether you made a profit.

Step 1: Gather every account

List every account that holds business cash: checking, savings, money market, payment processor balances held separately, and credit cards. For a complete picture, you need all of them for the same period.

Credit cards deserve a decision. A card purchase isn't a cash outflow until you pay the card. Two common approaches:

• Treat the card payment as the cash outflow. Simpler: the card account stays out of the cash report, and the payment from checking is categorized as a payment to the card. You lose the detail of what was bought.
• Treat card purchases as outflows and the card as short-term financing. More detail: each purchase is categorized, and the card balance is treated like a short-term loan. Payments to the card are then transfers between the card and checking.

For a small business that wants to know what it's spending money on, the second approach is more useful. Either way, be consistent.

Step 2: Convert statements to one table

Convert each account's statements into one table with these columns: Date, Description, Amount (signed: money in positive, money out negative), Account, Statement month.

For PDF statements, SheetStatement's bank statement to Excel converter produces this layout and checks each statement's transactions against its balances. For card statements, use the credit card statement converter and make sure purchases come out as negative amounts in your table.

Check each statement as you go: opening balance plus the sum of the month's amounts must equal the closing balance. The balance checker does this quickly. A cash flow report built on incomplete data will tie out to nothing.

Step 3: Tag transfers

Moves between your own accounts aren't cash flow; they just move cash from one pocket to another. Tag them first:

• Checking to savings and back.
• Payments to a credit card (if the card is included in the report).
• Moves between a payment processor balance and the bank account (if both are included).

Each transfer appears twice, once in each account, with opposite signs. Tag both sides. When you're done, the sum of all transfer rows should be zero, or very close, with any difference explained by timing at the period boundary. That's a useful check that you've tagged both sides.

Step 4: Categorize into cash flow groups

Add two columns: Category (detailed, such as Rent or Customer receipts) and Group (the cash flow section).

A simple mapping for a small business:

Loan repayments include both principal and interest. Strictly, interest is often treated as operating and principal as financing. If your loan statements split them, you can split the payment into two rows. If you're not sure, ask your accountant how they'd like it treated; for internal cash management, keeping the whole payment in financing is a common simplification, as long as you're consistent.

Use a rules table with XLOOKUP to fill categories for recurring payees, and a second lookup from category to group. Our guide to categorizing business expenses from bank statements has ideas for a category list.

Step 5: Build the monthly report

With the table categorized, build the report. A pivot table is the fastest way: Group and Category in rows, Date grouped by month in columns, Amount in values, Transfers filtered out. Our pivot table guide covers the setup in detail.

If you'd rather have a fixed layout you can print, use SUMIFS on a report sheet:

=SUMIFS(Tx[Amount], Tx[Category], $B5, Tx[Month], C$3)

where Tx is your transaction table, Month is a helper column with =TEXT([@Date],"yyyy-mm"), column B holds the category names and row 3 holds the months.

A layout that works:

The figures here are an illustration, not a benchmark. Opening cash for each month is the previous month's closing cash. Closing cash is opening cash plus net cash flow.

Categorization traps that distort cash flow

A few kinds of transaction regularly end up in the wrong group and make the report misleading.

Payment processor payouts. If customers pay through a card processor or an online marketplace, the deposit you see is usually the net amount after fees and refunds, sometimes combining many sales. Treat the payout as customer receipts. If you want fees shown separately, use the processor's payout reports to split them out; otherwise accept that receipts are shown net of fees and note it on the report.

Refunds to customers. Money returned to customers reduces receipts. Categorize it against customer receipts (as a negative) rather than as an expense, so the receipts line reflects what you actually kept.

Owner spending on the business card. If personal purchases go on the business card, they're drawings, not operating costs. Leaving them in operating makes the business look more expensive to run than it is.

Loan proceeds. A loan deposit is a big inflow, but it's financing, not income. Putting it in operating can make a weak month look strong.

Sales tax collected. If you collect sales tax and pay it later, receipts include tax that isn't yours to keep. For cash management, showing the tax payment as an operating outflow is fine, but remember that part of each month's receipts is earmarked for it. Your accountant can advise on how to present it.

Security deposits and one-off refunds received. A returned deposit or an insurance payout is real cash, but not recurring. Give it its own category so it doesn't flatter the trend.

A quick review of the largest twenty inflows and outflows each month catches most of these. They're the transactions that move the report, so they deserve a second look.

Step 6: Tie it out

This is the step that makes the report trustworthy. For each month:

Closing cash in the report = sum of closing balances on all included bank statements

If you included credit cards as accounts, subtract the card balances owed (they're negative cash). If the report and the statements don't agree:

• An account is missing from the table for that month.
• A transfer was tagged on one side only, so it's included as an inflow or outflow somewhere.
• A statement is incomplete, which the per-statement balance check should have caught.
• A category was filtered out of the report by mistake, such as an "Uncategorized" group.

Fix the source and refresh. Don't add a plug figure to make it balance.

Reading the report

A few things to look for once the numbers are right:

Operating cash flow over several months. Is the business generating cash from its normal activity, or burning it? One negative month can be timing; three in a row is a pattern worth understanding.

Timing of large payments. Quarterly tax payments, annual insurance premiums and yearly software renewals create lumpy months. Seeing them laid out helps you plan the cash buffer you need.

The gap between sales and receipts. If you invoice customers, compare your sales with customer receipts in the same month. A growing gap means customers are paying slower, which shows up in cash before it shows up anywhere else.

Owner drawings versus operating cash. If drawings regularly exceed operating cash flow, the business is shrinking its cash pile to pay you. That may be fine for a while, but it's worth knowing.

A simple forecast

Once you have six or twelve months of actual cash flow, a basic forecast is a natural next step. Copy the report layout for the next few months and fill in:

• Recurring outflows you know about: rent, wages, loan repayments, subscriptions.
• Expected receipts, based on invoices outstanding and typical monthly sales.
• Known one-offs: tax payments, planned equipment purchases.

Then compare each month's forecast with the actual figures from the bank once the month ends. Over time, the forecast gets better and you get earlier warning of tight months.

A worked example

A two-person web agency has a checking account, a savings account and a business credit card. Its books are six months behind, and the owners want to understand why cash felt tight in the spring.

1. Convert. They convert six months of statements for all three accounts. Each statement balances.
2. Combine. Everything goes into one table with an Account column; card purchases are negative.
3. Tag transfers. Card payments from checking and moves to and from savings are tagged on both sides. The transfer rows sum to zero.
4. Categorize. Client payments, contractor payments, software, rent, card purchases by category, a quarterly estimated tax payment, and owner drawings.
5. Report. A SUMIFS report by month, grouped into operating, investing and financing.
6. Tie out. Each month's closing cash equals checking plus savings minus the card balance owed, from the statements.
7. Read it. In April, two large clients paid late, a quarterly tax payment went out and the owners took their usual drawings. Operating cash was negative for the month, and drawings made it worse. Nothing was wrong with the business's profitability; the timing simply lined up badly.

They decided to keep a cash buffer equal to one month of fixed costs plus the next tax payment, and to move drawings to after client payment dates. The report took an afternoon to build and now updates in minutes each month.

When to use accounting software instead

A bank-statement cash report is a practical tool, but it has limits. It doesn't show profit, receivables, payables, inventory or accruals. If you need formal financial statements, if you're applying for significant financing, or if your accountant needs proper books, the cash report doesn't replace them. Importing the same converted statements into QuickBooks or Xero is often the next step; see how to import bank statements into QuickBooks Online. Our month-end close checklist covers the wider monthly routine.

Start with your statements

Upload your bank and card statements to SheetStatement, confirm each one balances, and download Excel. From there, the table, categories and report above take an afternoon. The free plan covers 3 pages a month, and paid plans are $9, $19 and $39 a month for larger batches.

This article is general information, not accounting, tax or financial advice. Treatment of specific items can vary; consult a qualified accountant for your situation.

FAQ

Can I make a cash flow statement from bank statements?

Yes, using the direct method. Convert all your accounts' statements, tag transfers, categorize receipts and payments into operating, investing and financing, and summarize by month. Check that closing cash equals the statements' closing balances.

What's the difference between a cash flow report and a profit and loss statement?

A profit and loss statement shows income and expenses when they're earned or incurred. A cash flow report shows when cash actually moved. They can differ a lot when customers pay late or when you buy equipment.

Should I include credit cards in a cash flow report?

You can either treat card payments as the outflow, or treat card purchases as outflows and the card as short-term financing. The second gives more detail. Pick one approach and use it consistently.

How do I handle transfers between my accounts?

Tag both sides as transfers and exclude them. The total of all transfer rows should be zero, apart from timing at the start or end of the period.

Is a bank-statement cash report good enough for a lender?

It can help you prepare and understand your numbers, but lenders often want accountant-prepared statements or specific documents. Check what they require.

How often should I update it?

Monthly works well for most small businesses. Add the new statements, categorize, refresh, and check that closing cash ties out.