A 13-Week Cash Flow Forecast in Excel: Profitable and Out of Money
A business can be profitable every month and hit zero cash in week eleven, because profit is recognised when earned and cash arrives when collected. Here is the rolling forecast that shows it, and the two formulas that turn payment terms into dates.
Profit Is an Opinion, Cash Is a Fact
An income statement records a sale when it is earned. A bank account records it when the money arrives. Between those two events sit payment terms, and a growing business finances that gap out of its own cash.
That is why the most common failure mode for a profitable small business is running out of money. A thirteen-week direct cash forecast is the standard tool for seeing it coming, and it takes an afternoon to build.
Why thirteen weeks
It is one quarter, which is long enough to include a full billing and collection cycle and short enough that the estimates are still grounded in known invoices and known bills. Beyond about thirteen weeks the forecast becomes a budget, which is a different document with a different purpose.
The layout
Swipe sideways to compare columns.
| Section | Rows |
|---|---|
| Opening balance | One row, linking to the previous week's closing |
| Receipts | Collections from existing invoices, new sales, other income |
| Payments | Payroll, suppliers, rent, tax, loan payments, capital spend |
| Net movement | Receipts minus payments |
| Closing balance | Opening plus net movement |
| Facility headroom | Any credit line still available |
| Total liquidity | Closing balance plus headroom |
Turning terms into weeks
The whole value of the forecast is in the timing, and the timing comes from the invoice date plus the payment terms plus how late the customer actually pays.
The scenario worth modelling
A business with $85,000 of cash, invoicing $120,000 a month, collecting on average 47 days after invoice, and paying suppliers on 30 days. Monthly operating profit is $14,000. It is winning work and growing 8% a month.
Swipe sideways to compare columns.
| Week | Receipts | Payments | Closing balance |
|---|---|---|---|
| 1 | $28,000 | $31,500 | $81,500 |
| 3 | $27,000 | $34,000 | $70,200 |
| 5 | $29,500 | $36,800 | $56,100 |
| 7 | $31,000 | $39,200 | $40,300 |
| 9 | $32,500 | $42,000 | $21,600 |
| 11 | $34,000 | $45,100 | −$2,400 |
| 13 | $36,000 | $48,300 | −$29,800 |
Every month in that period is profitable. The business runs out of money in week eleven, because growth means paying for more work before being paid for the last lot. Faster growth makes it worse, which is the counterintuitive part.
Runway and burn
Report the zero-cash date rather than a runway in months. "We are out of cash in the week of 18 November" prompts action; "we have 2.6 months of runway" prompts a nod.
The levers, in order of speed
Swipe sideways to compare columns.
| Lever | Speed | Effect on the example |
|---|---|---|
| Collect overdue invoices | Days | Immediate, and usually the largest single win |
| Deposit or milestone billing | One cycle | Moves the whole receipt curve left |
| Extend supplier terms | One cycle | Delays payments without reducing cost |
| Delay non-essential capital spend | Immediate | Direct and reversible |
| Draw on a credit facility | Days | Buys time, adds cost |
| Raise prices | Months | Right answer, wrong timescale for a week-eleven problem |
| Cut headcount | Weeks, with severance cost first | Often makes cash worse before better |
Keeping it alive
- Update it weekly, on the same day. A forecast updated monthly is a report, not a tool.
- Add an actuals column beside week one and compare against what you forecast. Systematic bias in your own estimates shows up within a month.
- Roll the window forward rather than rebuilding: delete the completed week, add a new week thirteen.
- Keep a variance note per week explaining anything more than 10% out. It is how the forecast gets better.
- Separate committed from expected. An invoice already issued is a different quality of number from a sale you hope to win.
What the forecast cannot see
- A customer who fails. One large receivable going bad can move the zero-cash week forward by a month, and no average collection assumption captures a single default.
- Anything beyond the horizon. Thirteen weeks is deliberately short, and a business with a seasonal cycle needs an annual view alongside it.
- Whether the underlying business works. A cash forecast can be perfectly accurate about a business that should not exist.
- Tax timing, which is lumpy and easy to omit. Sales tax collected is not your money and is usually the largest single payment in the quarter it falls due.
- Covenant tests on a credit facility, which can remove the headroom row at exactly the moment you plan to use it.
- The difference between a cash problem and a profit problem. If the forecast improves every time you collect faster, it is a working capital issue. If it does not, the business is losing money and the forecast is only telling you when.
Why can a profitable business run out of cash?
Because profit is recognised when a sale is earned and cash arrives when it is collected. If you pay for materials and labour on 30 days and collect on 47, every new job consumes cash for seventeen days before it returns any. Growth multiplies that gap.
Why thirteen weeks rather than twelve or six months?
Thirteen weeks is one quarter and covers a full billing and collection cycle, while staying short enough that the numbers come from known invoices and known bills. Longer horizons become budgets built on assumptions rather than forecasts built on facts.
Should I use contractual terms or actual payment behaviour?
Actual behaviour, computed per customer from your own invoice history. A customer on 30-day terms who reliably pays in 44 days is a 44-day customer, and forecasting them at 30 makes every week of the model optimistic in the same direction.
What is the difference between gross and net burn?
Gross burn is total cash out. Net burn is cash out minus cash in. For a business with revenue, net burn is the meaningful figure, since gross burn makes a healthy business with high turnover look like it is about to fail.
How do I present this to a lender or a board?
Lead with the zero-cash date and the assumption behind it, then the levers with their modelled effect. A single line saying "without action we are out of cash in week eleven; collecting the overdue book moves it past week thirteen" does more than a thirteen-column grid.
How accurate should the forecast be?
Within about 5% for week one and 15% by week thirteen is a reasonable working standard. Track your own variance weekly. Accuracy matters far less than direction and timing: knowing that a gap arrives in week eleven rather than week fifteen is what changes decisions.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team