Excel guide · 5 min read
How to make a cash flow statement in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Balance sheet | Dec 31, 2025 | Dec 31, 2026 | 2026 figures | Amount | Cash flow statement 2026 | Amount | ||
| 2 | Cash | 12400 | 18900 | Net profit (P&L) | 30400 | Net profit | 30400 | ||
| 3 | Accounts receivable | 21000 | 26500 | Depreciation (P&L) | 6500 | Add back depreciation | 6500 | ||
| 4 | Inventory | 15500 | 13200 | Owner drawings | 12000 | (Increase) / decrease in receivables | -5500 | ||
| 5 | Equipment at cost | 40000 | 52000 | (Increase) / decrease in inventory | 2300 | ||||
| 6 | Accumulated depreciation | -8000 | -14500 | Increase / (decrease) in payables | 1800 | ||||
| 7 | Accounts payable | 9800 | 11600 | Cash from operating activities | 35500 | ||||
| 8 | Bank loan | 25000 | 20000 | Purchase of equipment | -12000 | ||||
| 9 | Owner capital | 30000 | 30000 | Cash from investing activities | -12000 | ||||
| 10 | Retained earnings | 16100 | 34500 | Loan repayments | -5000 | ||||
| 11 | Owner drawings | -12000 | |||||||
| 12 | Cash from financing activities | -17000 | |||||||
| 13 | Net change in cash | 6500 | |||||||
| 14 | Opening cash | 12400 | |||||||
| 15 | Closing cash | 18900 | |||||||
| 16 | Check (should be 0) | 0 |
Download this sheet as an Excel practice file (.xlsx)
What you need: two balance sheets and the P&L
The indirect method explains why the cash balance moved when the business made a profit. Profit and cash differ because customers haven't paid yet, stock was bought and not sold, bills are unpaid, equipment was bought, and loans and owner money came in or went out.
Columns A:C hold the balance sheet at the start and end of the year, from your balance sheet. E:F hold the figures that only show up elsewhere: net profit and depreciation from the profit and loss statement, and owner drawings from the equity account. The statement is built in H:I with nothing typed: every line is a formula.
The sample balances: total assets of 96,100 at the end of 2026 equal liabilities of 31,600 plus equity of 64,500. Check yours balances before you start, or the cash flow statement can't.
Operating activities: profit, depreciation and working capital
Start with net profit (I2) and add back depreciation (I3). Depreciation reduced profit, but no cash left the business for it; the cash went when the equipment was bought.
Then the working capital lines. The rule that trips people up: when an asset goes up, cash goes down. Receivables rising from 21,000 to 26,500 means 5,500 of sales are still unpaid, so the formula is opening minus closing. Inventory fell by 2,300, which turned stock back into cash, so the same formula gives a positive number. For liabilities it's the other way round: payables rising by 1,800 means bills you haven't paid yet, which keeps cash in the bank, so use closing minus opening.
Result: -5500
-5,500: the increase in receivables. Inventory gives +2,300 (=B4-C4) and payables +1,800 (=C7-B7).
What's the total from operations?
Add the five lines in I2:I6.
Result: 35500
35,500 of cash from operating activities, against 30,400 of profit.
Investing and financing
Investing activities are cash spent on, or received from, long-term assets. Equipment at cost went from 40,000 to 52,000, so 12,000 was spent; it's an outflow, so the formula is negative. This assumes nothing was sold during the year. If equipment was sold, show the purchase and the sale proceeds as separate lines, from the asset register.
Financing activities are money from or to lenders and owners. The loan fell from 25,000 to 20,000: closing minus opening gives -5,000 of repayments. Owner drawings (or dividends) are an outflow of 12,000. New capital or a new loan would show as a positive line here.
Result: -12000
-12,000 spent on equipment. Financing (I12) is -17,000: -5,000 of loan repayments and -12,000 of drawings.
Should depreciation match the balance sheet?
With no disposals, the change in accumulated depreciation equals the year's depreciation charge. Put that check next to the statement; if it isn't 0, an asset was sold or written off and needs its own line.
Result: 0
0: accumulated depreciation grew by 6,500, the same as the P&L charge.
The check: opening cash + net change = closing cash
Add the three sections for the net change in cash (I13), add it to the opening cash from the first balance sheet (I14) and compare with the cash on the closing balance sheet. A cash flow statement that doesn't reconcile is wrong somewhere, so keep the check cell on the face of the statement.
If it isn't 0, the usual suspects are a sign flipped on one working capital line, a balance sheet line left out of the statement (every non-cash line must appear somewhere, or be explained), or drawings already netted off in net profit.
Result: 0
0: opening cash of 12,400 plus a net change of 6,500 gives 18,900, the cash on the 2026 balance sheet.
Do retained earnings appear in the statement?
Not as a line of their own. Retained earnings moved by profit minus drawings, and both are already in it. A quick check: closing minus opening retained earnings should equal net profit minus drawings.
Result: 0
0: retained earnings rose 18,400, which is 30,400 of profit less 12,000 of drawings.
Statement vs forecast: which do you need?
This statement looks back: it explains last year's cash movement from the accounts. To plan the next few weeks (when customers will pay, when the payroll and rent go out, the lowest balance coming up), you want a 13-week cash flow forecast instead. Many small businesses keep both: the forecast to run the business and the statement as part of the year-end accounts.
For a monthly cash flow statement, use month-end balance sheets in B and C and that month's profit and depreciation; the formulas don't change. How you classify interest, tax and other items is a question for your accountant.
- Format the statement with a custom number format #,##0;(#,##0) so outflows show in brackets: select I2:I16, press Ctrl+1, choose Custom and type the format.
- Make the section totals (I7, I9, I12, I13) bold with a top border so the three sections read clearly.
Common mistakes
Signs reversed on working capital
Assets: opening - closing. Liabilities: closing - opening. An increase in receivables is a negative number.
Forgetting to add back depreciation
It reduced profit without any cash leaving. Add it back on the second line, and check it against the change in accumulated depreciation.
Leaving a balance sheet line out
Every line except cash must be explained by the statement. Tick each one off against a statement line.
Typing the closing cash
Closing cash must be calculated (opening + net change) and compared with the balance sheet. That comparison is the whole check.
FAQ
What is the indirect method for a cash flow statement?
It starts with net profit and adjusts it for non-cash items (depreciation) and changes in working capital to get cash from operations, then adds investing and financing cash flows.
Why is an increase in receivables negative on a cash flow statement?
The sale was counted in profit, but the customer hasn't paid, so the cash isn't in the bank. Subtracting the increase takes it back out.
What's the difference between a cash flow statement and a cash flow forecast?
A statement explains past cash movement from the accounts. A forecast predicts future receipts and payments week by week so you can plan ahead.
Is there a cash flow statement template in Excel?
File > New has templates to search for, but they expect you to type each line. Linking every line to two balance sheets, as above, means the check cell catches mistakes.
Functions used
Related guides
- How to calculate free cash flow in Excel
- How to make a balance sheet in Excel
- How to make a profit and loss statement in Excel
- How to build a 13-week cash flow forecast in Excel
- How to calculate retained earnings in Excel
- How to abbreviate large numbers in Excel: thousands (K), millions (M), billions (B)
Updated