TL;DR:
A cash flow forecast tracks when money actually moves, not when you earned or owe it.
Each week: closing balance = opening balance + inflows − outflows, and next week opens where this one closed.
The number to watch is the lowest closing balance, because that is the week you could run short.
A business can be profitable on paper and still be unable to pay rent on Friday. The reason is timing: a customer pays you in six weeks, but your supplier wants money in two. A cash flow forecast makes those gaps visible weeks ahead, while you can still do something about them.
When a forecast is worth building
Build one when you are starting out, before a big purchase, when you hire, or when your income is lumpy. Twelve weeks is a good horizon: long enough to see monthly bills twice or three times, short enough that you can still guess customer payments with some honesty.
What you need first
Your actual bank balance today. This is the opening balance for week 1.
A list of money you expect to receive, with the week you realistically expect each payment, not the week the invoice was sent.
A list of money you will pay out, with its date: rent, wages, subscriptions, supplier bills, taxes and loan payments.
A spreadsheet with one row per week.
Building the weekly view
Make the columns: Use A Week, B Opening balance, C Inflows, D Outflows, E Net change and F Closing balance. Put the numbers 1 to 12 in column A, rows 2 to 13.
Enter the starting cash: Type your real bank balance in B2. This is the only opening balance you type by hand.
Add the formulas: In E2 enter
=C2-D2and in F2 enter=B2+E2. In B3 enter=F2, so each week opens where the last one closed. Fill rows 3 to 13 down.Place each inflow and outflow in its week: Put every expected payment in the week you expect cash to move. Monthly rent goes in the week it is due, every fourth week. Wages go in every pay week.
Find the low point: Below the table enter
=MIN(F2:F13)to show the lowest closing balance. Conditional formatting that colours negative balances red helps you spot trouble at a glance.
Worked example: a 12-week forecast with labeled example numbers
These are example figures, not anyone's real business. Say you start with 2,000 in the bank. Rent of 1,200 is due in weeks 1, 5 and 9. Payroll of 1,600 goes out in weeks 2, 4, 6, 8, 10 and 12. Software and small costs of 150 go out every week. Customer payments vary from week to week.
Week | Opening | Inflows | Outflows | Net | Closing |
|---|---|---|---|---|---|
1 | 2,000 | 2,000 | 1,350 | +650 | 2,650 |
2 | 2,650 | 1,500 | 1,750 | −250 | 2,400 |
3 | 2,400 | 2,200 | 150 | +2,050 | 4,450 |
4 | 4,450 | 1,800 | 1,750 | +50 | 4,500 |
5 | 4,500 | 400 | 1,350 | −950 | 3,550 |
6 | 3,550 | 2,400 | 1,750 | +650 | 4,200 |
7 | 4,200 | 1,200 | 150 | +1,050 | 5,250 |
8 | 5,250 | 1,500 | 1,750 | −250 | 5,000 |
9 | 5,000 | 300 | 1,350 | −1,050 | 3,950 |
10 | 3,950 | 2,000 | 1,750 | +250 | 4,200 |
11 | 4,200 | 1,600 | 150 | +1,450 | 5,650 |
12 | 5,650 | 1,300 | 1,750 | −450 | 5,200 |
Check how outflows are built. A rent week costs 1,200 + 150 = 1,350. A payroll week costs 1,600 + 150 = 1,750. A week with neither costs 150. Those are the three outflow amounts in the table.
Check the whole table in one move. Total inflows are 18,200 and total outflows are 15,000, so the net change over 12 weeks is 18,200 − 15,000 = 3,200. Starting from 2,000, the final balance should be 2,000 + 3,200 = 5,200, which matches the closing balance of week 12.
Now read the story. The lowest closing balance, =MIN(F2:F13), is 2,400 in week 2, right after the first payroll. The business is never negative, but week 2 is thin, and week 9 dips to 3,950 because customer payments were only 300 while rent was due. If a customer in week 1 or 2 paid late, you would feel it immediately. That is exactly the kind of risk the forecast is for.
Using the forecast to decide things
Change one input at a time. Move a 2,000 customer payment from week 3 to week 6 and watch what happens to the low point.
Ask whether a bill can move. A supplier payment two weeks later may remove the dip.
Decide your minimum cash cushion, for example enough for one payroll, and flag any week that falls under it.
Update the sheet weekly: replace the forecast row with what really happened, and push the forecast forward one week.
Mistakes to avoid
Counting a sale when you invoice it. Count it when the cash arrives.
Forgetting irregular costs: insurance, annual subscriptions, taxes, equipment repairs. Put them in the week they are due. For anything tax related, check with a professional about the amounts and dates that apply to you.
Being optimistic about customers. If a client usually pays 30 days late, forecast them 30 days late.
Typing over a formula with a number. Keep the Opening balance column formulas intact after week 1.
Building it once and never looking again. A forecast is only useful while it is current.
Doing it faster
Copy last quarter's sheet, clear the inflows and keep the recurring outflows already in place. That leaves only the new guesses to enter. If a formula refuses to behave, DuckyHelper can see your sheet on screen and explain what a cell is doing out loud while you keep working.
Frequently asked questions
Is this the same as a profit and loss statement?
No. A profit and loss statement counts income and costs when they are earned or incurred. A cash flow forecast counts only when money moves in or out of the bank.
How accurate does it need to be?
It will never be exact. Aim to be useful: right about the big payments and their weeks, and honest about the uncertain ones.
Get started
