A profitable company runs out of cash on a Tuesday because payroll landed before the big invoice did. A 13-week cash flow forecast exists to see that Tuesday coming. It is the most useful spreadsheet most small companies do not have, and it is not hard to build. This guide covers the layout, where each line’s numbers come from, how to roll it every week without losing the history, and the four checks that keep it honest.
Why thirteen weeks, and why weekly
Thirteen weeks is a quarter. It is long enough to contain every payroll run, the quarterly tax payment, the rent and the annual insurance premium that always surprises someone, and short enough that a weekly column is realistic rather than a guess. A monthly forecast is for planning the year. A weekly one is for making sure you are still here in April. They run off the same inputs and should agree with each other at the month ends.
The layout
Weeks across, lines down, and the structure never changes:
| Line | Where it comes from | |
|---|---|---|
| 1 | Opening cash | Last week’s closing cash. Week one is the bank balance, typed in and dated. |
| 2 | Receipts from existing receivables | The receivables aging, spread over future weeks by a collection curve |
| 3 | Receipts from new sales | The sales forecast, delayed by your average days to collect |
| 4 | Other receipts | Refunds, financing, asset sales, each on its own row with a date |
| 5 | Total cash in | Sum of lines 2 to 4 |
| 6 | Payroll | The payroll calendar: exact dates, gross plus employer taxes and benefits |
| 7 | Rent and fixed costs | The contracts: amount and due date, not the monthly accrual |
| 8 | Vendors | The payables aging, paid on terms, plus forecast purchases on the same terms |
| 9 | Debt service, taxes, one-offs | The loan schedule, the tax calendar, and anything unusual you already know about |
| 10 | Total cash out | Sum of lines 6 to 9 |
| 11 | Closing cash | Opening plus cash in less cash out |
| 12 | Minimum cash | An input: the balance below which you are not comfortable |
| 13 | Headroom | Closing cash less minimum cash. The number the whole sheet exists to show. |
Where the receipts come from
Receipts are where a cash forecast is won or lost, because they are the line you control least. Start from the receivables aging report. For each customer, or each aging bucket if the list is long, apply a collection curve: what share of a current invoice gets paid within one week, two weeks, four, six. The curve comes from your own history if you have it, and from your payment terms plus a realistic delay if you do not. Put the curve on the inputs sheet so it can be tightened or loosened in one place.
New sales get the same treatment: forecast the invoicing week, then push the cash out by the collection curve. If one customer is a large share of receipts, give them their own row and their own expected date. A forecast that averages a customer who pays in 30 days with one who pays in 90 is wrong for both of them.
Where the payments come from
Payments are easier because you decide most of them. Payroll goes in on its actual dates, including the weeks where a month has three pay runs. Rent, insurance, subscriptions and loan repayments go in from the contract, on the due date. Vendor payments come from the payables aging paid on terms, plus forecast purchases that follow the sales forecast on the same terms. Taxes go in from the calendar, and the annual items get their own row so nobody is surprised by the premium in week nine.
Rolling it every week
On the same day each week, replace the oldest forecast column with the actual bank movements for that week, add a new week thirteen at the far end, and refresh the agings. Keep the forecast you made for the week that just closed, in a row underneath the actual. The difference between them, week after week, is your forecast accuracy, and it tells you which line to work on. Most companies find their payments forecast is within a few percent inside a month and their receipts forecast is the one that needs the collection curve adjusted.
Roll the forecast; do not rebuild it. A sheet that gets rebuilt from scratch each week loses the history that makes it better, and it gets rebuilt worse each time.
The four checks
- Continuity. Closing cash in week n equals opening cash in week n plus one, every week. A broken link here is the most common error in a rolled forecast.
- Bank tie. For every week that has actuals, closing cash equals the bank statement balance. A difference is a payment or a receipt the forecast never knew about.
- Receivables tie. Total forecast receipts from existing receivables, across all weeks, equals the receivables balance you started from, less whatever you have written off as uncollectible. If it is higher, the collection curve sums to more than 100 percent.
- Totals. Total cash in and total cash out equal the sum of their lines, checked with a second formula written a different way. A row that was inserted outside the SUM range is invisible until this check catches it.
Each check is a rounded difference that must be zero. Then break one on purpose, overwrite a receipts formula with a number, and confirm the check goes red before you trust it. The rest of that routine is in how to check a financial model you didn’t build.
Monthly, for the year
The monthly version has the same lines with months across, drives receipts from the revenue forecast and days sales outstanding rather than the aging report, and adds the capex and financing lines that a quarter rarely contains. The model on our home page is a monthly one: opening cash, collections as a percentage of revenue, payroll, opex, closing cash, and a tie-out at the bottom that the balance sheet has to agree with. Build the weekly and the monthly from the same inputs and check that they match at each month end.
Having one built
A cash flow forecast is a Models-plan request. Send your receivables and payables agings, the payroll calendar, the loan schedule and the last three months of bank statements, and the forecast comes back rolled to the current week, with the collection curve on the inputs sheet where you can adjust it, the four checks in place, and a walkthrough. The plans are on the home page.