How to Make a Cash Flow Spreadsheet in Excel for Your Business
Profitable businesses still close their doors when they run out of cash. A cash flow spreadsheet shows when money comes in and goes out, so you can see a tight month coming weeks before it arrives.
Cash flow, cash book and profit: what’s the difference?
| What it shows | Time frame | |
|---|---|---|
| Cash book | Every cash in and out, with the running balance | Daily |
| Cash flow | Cash in and out grouped by category, with the balance per period | Monthly (or weekly) |
| Profit and loss (income statement) | Revenue minus costs and expenses, whether paid or not | Monthly or yearly |
A sale on 30-day terms counts as revenue in the income statement this month, but only enters the cash flow when the customer pays. That timing gap is why profit and cash differ.
The structure of a monthly cash flow
Set months in columns (January to December, plus a year total) and these blocks in rows:
- Starting balance: cash available at the beginning of the month
- Inflows (entries): sales in cash, card, invoices or bank slips; services; other revenue and refunds
- Direct discounts: card fees, discounts granted, direct taxes
- Operating expenses: suppliers, raw materials, wages, maintenance
- Administrative expenses: rent, utilities, accounting, legal, marketing, software
- Taxes
- Financing and investments: loans received and paid, equipment purchases
- Partner contributions and withdrawals
- Ending balance: starting balance + inflows − all outflows
The ending balance of each month becomes the starting balance of the next.
The key formulas
Total inflows = SUM(inflow lines)
Total outflows = SUM(all outflow lines)
Net cash flow = Total inflows − Total outflows
Ending balance = Starting balance + Net cash flow
Next start (Feb) = Ending balance (Jan)
In Excel, if January’s ending balance is in cell B40, February’s starting balance is simply =B40.
A quick example
| January | February | |
|---|---|---|
| Starting balance | 5,000 | 3,800 |
| Inflows | 12,000 | 9,500 |
| Outflows | 13,200 | 12,100 |
| Net cash flow | −1,200 | −2,600 |
| Ending balance | 3,800 | 1,200 |
The business is still positive in February, but at this pace March will close negative. Seeing it in January gives time to collect receivables earlier, renegotiate a supplier or delay a purchase.
Five habits that make cash flow useful
- Record actual values every week and compare them with the forecast.
- Forecast at least 3 months ahead.
- Keep partner withdrawals separate from company expenses.
- Watch receivables: sales you haven’t collected yet are not cash. An accounts receivable spreadsheet helps.
- Keep a minimum cash reserve (many small businesses aim for one to three months of fixed expenses).
Daily cash control
For shops and businesses that handle cash every day, a cash book complements the monthly view: opening balance, every entry and exit, and the closing balance of the day. See the Daily Cash Book Template or the Daily Cash Flow Sheet, which separates income by type and payments to suppliers.
Ready-made template
Our 12-Month Cash Flow Template already includes all the blocks above, with starting balance, revenue by channel, operating and administrative expenses, taxes, financing, investments, partner contributions and withdrawals, and monthly results for the whole year. To plan targets and compare them with reality, pair it with the Business Budget Worksheet, and read how to make a budget spreadsheet for the planning side.
FAQ
Should cash flow be monthly or weekly? Monthly for planning; weekly when cash is tight or the business is seasonal.
Do I include taxes in the cash flow? Yes, in the month they are actually paid.
What’s the difference between cash flow and an income statement? Cash flow follows money in and out; the income statement shows profit, regardless of when money moves. You need both. See the Income Statement Template.
