# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-cash-flow-forecast-and-runway
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

> **Direct, not indirect** — An indirect cash flow statement starts from net income and adjusts. A direct forecast lists actual expected receipts and payments. For a short-horizon operating forecast the direct method is the right one, because it maps onto what will actually appear on the bank statement.

## The layout

**Rows down the side, weeks across the top**
| 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 |

**The two formulas that make it a forecast**

```
Closing: =OpeningBalance + SUM(Receipts) − SUM(Payments)    ·    Next opening: =PreviousClosing
```
- Every week is those two links plus the detail rows.
- Never type a balance into a week after the first, or the chain breaks silently.
- Add a check row: =IF(ThisOpening=PreviousClosing, "", "BREAK") across the whole forecast.

## 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.

**Expected collection week**

```
=WEEKNUM(InvoiceDate + TermsDays + AverageDaysLate) − WEEKNUM(ForecastStart) + 1
```
- Average days late comes from your own history, not from the terms.
- A customer on 30-day terms who consistently pays in 44 days is a 44-day customer.
- Then collect into the grid with =SUMIFS(Amounts, ExpectedWeek, WeekNumber).

> **Use actual behaviour, not stated terms** — The single biggest improvement to a cash forecast is replacing contractual terms with observed payment behaviour per customer. Compute days sales outstanding by customer from last year's invoices and use that. Most forecasts are wrong because they assume everyone pays on time and nobody does.

## 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.

**Alternate weeks from the 13-week grid, opening balance $85,000**
| 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

**The three runway figures**

```
Net burn = Payments − Receipts, per period  ·  Runway = Cash ÷ Average net burn  ·  Zero-cash week = MATCH(TRUE, ClosingRow<0, 0)
```
- Use net burn, not gross spend. Gross burn overstates the problem for a business with revenue.
- Average over three periods rather than one, since a single week is noisy.
- The MATCH formula names the week the balance first goes negative, which is the number to act on.

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

**What actually moves cash within thirteen weeks**
| 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 |

> **Model the lever, do not assume it** — Add a scenario column set for each lever and see what it does to the zero-cash week. Collecting $40,000 of overdue invoices in week two moves the crossing point from week eleven to beyond the forecast horizon, and that is a specific, checkable claim rather than a hope.

## 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-cash-flow-forecast-and-runway). Quote freely with attribution and a link to this page._