KHIZHNYAKOVHow to Create a Cash Flow Statement in Excel
Cash Flow Tracker and Cash Flow Statement — Excel Template — Open the template →
A business can report a profit and still have an empty account. Customers pay late, a large supplier invoice lands in the same month as payroll, and suddenly the profit is irrelevant. A cash flow statement shows what actually moved through your accounts, month by month, which is the thing that decides whether you can pay your bills.
You do not need accounting software for a management-level version. One transaction list and a few formulas produce it.
Cash flow versus profit
Profit counts revenue when it is earned and costs when they are incurred. Cash flow counts money when it moves. Selling 20,000 USD of goods on credit is profit this month and cash next month, or the one after. Buying equipment for 15,000 USD is cash out today and a cost spread over years. Both views are useful, but only cash flow tells you whether the account will be empty.
Step 1: One list of transactions
Everything starts from a single table. One row per movement:
| Date | Account | Type | Category | Counterparty | Amount | Status |
|---|---|---|---|---|---|---|
| Jan 5 | Bank | Receipt | Sales | Client A | 12,000 | Actual |
| Jan 10 | Bank | Payment | Rent | Landlord | 2,500 | Actual |
| Feb 3 | Bank | Payment | Taxes | Tax office | 3,200 | Plan |
Keep amounts positive and use the Type column to decide the direction, or use signed numbers; just pick one approach. The Status column (Actual or Plan) lets the same list serve both history and forecast.
Transfers between your own accounts do not belong here. They change where the money is, not how much you have.
Step 2: Categories
Keep the list short: receipts (sales, other income, loans received) and payments (suppliers, payroll, rent, taxes, marketing, loan repayments, other). If you have more than about 15 categories, the report becomes hard to read.
Step 3: Build the monthly grid
Rows are categories, columns are months. Each cell is a SUMIFS on the transaction list:
=SUMIFS(Transactions!$F:$F,Transactions!$D:$D,$A5,Transactions!$A:$A,">="&B$3,Transactions!$A:$A,"<"&EDATE(B$3,1),Transactions!$G:$G,"Actual")
B3 holds the first day of the month. Total receipts minus total payments gives net cash flow. Opening balance for a month is the closing balance of the previous one:
=Opening+NetCashFlow
Worked example
| Jan | Feb | Mar | |
|---|---|---|---|
| Opening balance | 10,000 | 13,500 | 8,300 |
| Receipts | 22,000 | 14,000 | 25,000 |
| Payments | 18,500 | 19,200 | 21,000 |
| Net cash flow | 3,500 | -5,200 | 4,000 |
| Closing balance | 13,500 | 8,300 | 12,300 |
February is a negative month, but the balance stays positive thanks to January. If the opening balance had been zero, February would end at -1,700 USD, a cash gap. Highlight months with a negative closing balance in red with conditional formatting.
Step 4: Actual versus plan
Add a switch cell with three modes: actual only, plan only, or actual plus plan for the months to come. This lets you see the real year to date and a forecast to year end in one report. Update the plan as reality changes. A plan that is never touched becomes decoration.
Common mistakes
- Counting transfers between accounts as income and expense.
- Mixing accrual and cash. Record an invoice when money arrives, not when it is issued.
- Too many categories.
- Forgetting account balances. Reconcile each account with the bank statement at least monthly.
Ready-made template: cash flow statement
The template does the grid for you. It is paid, works in Excel 2007 and later and in LibreOffice, and has no macros. It comes blank and as a filled-in sample with fictitious data.
- Reference Data: accounts with opening balances for the year, receipt and payment categories, and the report year and mode.
- Transactions: up to 2500 transactions with date, account, kind, category, counterparty, amount and type Actual or Plan.
- Cash Flow: receipts and payments by category and month, net cash flow, opening and closing balances, and charts.
- Accounts: the balance of each account as of today.
In the settings you choose to show actual, plan, or actual together with plan. Months that end with a negative cash balance are highlighted in red. It is a management report: it does not replace formal accounting statements, and the calculation is approximate. It suits small businesses, sole traders, agencies and studios.
FAQ
How often should I update it?
Weekly is ideal. Monthly works if you reconcile with the bank statement.
Do I need to enter transfers between accounts?
No. They do not change the total balance.
How far ahead should the plan go?
Through the end of the year is a good start. Revise it every month.
Is this the same as the direct method in formal accounting?
It is a simplified version of the same idea, built from actual receipts and payments. Formal statements follow specific rules, and your accountant can tell you what applies to you.
