Thirteen-week cash forecast

You forecast cash week by week for the next quarter, the horizon where the owner can still act: chase a customer, move a supplier payment, draw on a facility, delay a purchase. The forecast uses the direct method (actual money in and out by week, not profit), starts from a reconciled bank balance, ties out to the cent, and is rolled forward and checked against actuals every week. Its job is to show the low point early enough to do something about it.

When to use

• "Can we make payroll next month?"
• "How long will our cash last?"
• "The bank wants a 13-week cash flow."
• "If Cedar pays late, are we in trouble?"
• Every week, once set up: use cronjob to roll it forward on the same day each week.

What you need

• Access, best first: connected apps (Xero, QuickBooks, Stripe) on the Connections page; your own browser signed in by the owner to the accounting software and the bank's website (read only); or exports from the owner: bank statements, aged receivables and payables detail, payroll calendar and amounts, loan schedules, tax deadlines. If none is there, ask with clarify.
• The opening balance of every bank account the business uses, reconciled to the statement on the as-of date. If you cannot reconcile it, say so at the top of the forecast.
• 6 to 12 months of customer payment history (invoice due date vs date paid) to measure how late each customer pays.
• Fixed commitments: payroll dates and amounts, payroll tax and pension dates, rent, loan repayments, leases, subscriptions, insurance.
• Tax payment dates for the next quarter (VAT, GST or sales tax, payroll taxes, corporate tax instalments). These vary by country and business: check each on the tax authority's site with web_search and cite it.
• The owner's minimum cash buffer and any overdraft or credit facility limit. Save in memory.

Method

1. Set the frame. Week 1 starts on the as-of date (use the owner's week start, usually Monday). 13 columns. All amounts in the business's currency; foreign-currency accounts converted at today's rate, stated in the header.
2. Opening cash = reconciled balances of all operating bank accounts on the as-of date. Exclude money that is not the owner's to use (client money, deposits held for others) and say so.
3. Receipts from open invoices. For each open invoice: expected date = due date plus that customer's average days late, from the last 6 to 12 payments. If a customer has fewer than 3 paid invoices, use the average across all customers. Already overdue with a promise to pay: use the promise date. More than 60 days overdue with no promise: leave it out of the base case and show it on a separate "possible upside" line.
4. Receipts from future sales. Forecast sales by week from the pipeline, recurring billing or recent run rate, then apply the same collection lag. Processor sales (Stripe, PayPal) arrive on the processor's payout schedule net of fees: use the actual payout pattern from the last few weeks.
5. Other receipts. Tax refunds, grants, loan drawdowns, owner injections: only when confirmed, with a date.
6. Payments. Payroll on pay dates (net pay), payroll taxes and pension on their due dates, rent and leases, loan repayments (principal plus interest), suppliers from the payables detail by due date (or by the owner's payment-run day), forecast purchases, tax payments, planned capital spending, owner drawings or dividends. Fixed items first, then variable.
7. Build the workbook with execute_code and openpyxl, live formulas for totals, net flow, opening and closing balances, and headroom over the buffer. Layout and tested code: references/forecast-workbook.md.
8. Tie it out before showing anyone. Compute every week in pandas, evaluate the workbook's formulas (the free formulas package, or LibreOffice if installed) and compare. Opening week 1 = bank balance; opening week n+1 = closing week n; sum of weekly net flows = closing week 13 minus opening week 1. Any difference means a formula is wrong. Fix it.
9. Read it like a lender would. Find the lowest closing balance and its week. Flag every week below the buffer and every week below zero (or below the facility limit). Show what drives the low point: usually payroll and tax falling in the same week as slow collections.
10. Recommend levers, ranked by cash and by cost: chase specific overdue customers (amounts and names), move discretionary payments, ask named suppliers for terms, time capital spending, draw on the facility, owner injection. The owner decides. You never move money, call suppliers or draw a facility.
11. Weekly roll-forward. Replace week 1 with actuals from the bank, compute variance by line, drop it, add a new week 13, and update assumptions that were wrong. Explain any line where actual differs from forecast by more than 10% and by more than a set amount. Track forecast accuracy over time (actual net flow vs the week-1 and week-4 forecast for that week). A forecast that is always optimistic needs its collection lags lengthened.

Worked example: the first four weeks

As of Monday 5 Oct, opening cash 42,300 (reconciled). Buffer 15,000. Acme's 2,400 invoice was due 30 Sep and Acme averages 12 days late, so it lands in the week of 12 Oct; Birch's 850 (due 7 Oct, 3 days late on average) lands in week 1; new sales start collecting from week 3.

	wc 5 Oct	wc 12 Oct	wc 19 Oct	wc 26 Oct
Opening	42,300	35,650	33,550	39,050
Collections, open invoices	850	2,400	0	0
Collections, new sales	0	0	10,000	10,000
Net payroll	0	0	0	(15,200)
Rent	(3,000)	0	0	0
Suppliers	(4,500)	(4,500)	(4,500)	(4,500)
Loan repayment	0	0	0	(1,250)
Net flow	(6,650)	(2,100)	5,500	(10,950)
Closing	35,650	33,550	39,050	28,100
Headroom over buffer	20,650	18,550	24,050	13,100
Cedar's 9,600, due 30 Oct with a 25-day average lag, lands in the week of 23 Nov (week 8). If the week-7 payroll depends on it, say so now, not in week 7. The same layout runs to week 13 in references/forecast-workbook.md.

Collection lag in practice

Customer	Paid invoices on record	Average days late	Spread (std dev)	Lag used
Acme	9	12	6	12 days
Birch	2	3	n/a	overall average (14 days): fewer than 3 payments
Cedar	7	25	22	25 days, flagged unreliable: the spread is wide
A wide spread means the date is a guess. Show that customer's receipts as a risk line in the summary, not as a certainty in the base case.

Weekly roll-forward routine

1. Pull last week's bank lines; replace the week-1 forecast with actuals.
2. Variance by line; explain anything over 10% and over the agreed amount ("Cedar paid 9 days later than its average").
3. Change the assumption that was wrong (Cedar's lag from 25 to 34 days), not just the number in the cell.
4. Drop week 1, add a new week 13, re-run the tie-out, re-read the low point and the levers.
5. Record accuracy for the net flow: 1 minus |actual minus forecast| / |actual|, for the forecasts made one week and four weeks earlier. Report the 8-week average on the card.

Output

• 13-week-cash-forecast-<date>.xlsx with sheets: Forecast, Receipts detail (invoice-level), Payments detail, Assumptions (each with source and date), Variance (last week forecast vs actual), Checks.
• A show_card: opening cash, lowest balance and its week, weeks below buffer, total receipts and payments for 13 weeks, the top 3 risks, the top 3 levers with their cash value.
• One paragraph for the owner: the answer to their question with a number, the low point, and what to do this week.

Checks before you finish

• Opening cash equals the reconciled bank balance on the as-of date.
• Each week's opening equals the prior week's closing; formulas evaluated and matched with Python.
• Receipts detail total equals the receipts line total; payments detail likewise.
• Every open invoice and bill is either in a week, on the upside line, or listed as excluded with a reason.
• Every payroll date in the quarter appears; every tax payment due in the quarter appears with its cited date.
• Nothing is in the forecast twice (an invoice in both open receivables and new sales).

Pitfalls

• Using profit instead of cash. Depreciation is not a payment; VAT collected is not yours; loan principal is a payment even though it is not an expense.
• Assuming customers pay on terms. Use what they actually do.
• Monthly lumps in a weekly forecast. Rent on the 1st and payroll on the 25th fall in specific weeks. Put them there.
• Forgetting quarterly and annual items: tax payments, insurance, annual software, bonuses.
• Optimism on new sales. Base case uses signed or recurring business; the pipeline goes in upside.
• Never checking against actuals. A forecast that is not reconciled weekly drifts within a month.
• Hiding the bad week. Lead with the low point.

See also

• receivables-chasing, supplier-payment-run, financial-scenario-model, budget-build.