13-week cash flow forecast: build it week by week in Excel

What a 13-week cash flow forecast is and who asks for it

Thirteen weeks is one quarter: long enough to see payroll cycles, rent, tax dates and a loan instalment, short enough to forecast each line from real invoices and contracts. Lenders, boards and turnaround advisers ask for a 13-week cash flow forecast (13WCF) as soon as a company is under pressure, because a monthly budget hides the week in which several large payments land together. It is built with the direct method: cash in and cash out by date, no accruals, no depreciation. Every week it is rolled forward by one week, so there are always 13 weeks ahead.

Start from available cash, not the bank balance

The opening position comes from the bank statement, adjusted to what you can actually spend: opening available cash = bank balance + deposits in transit − cheques issued but not cleared − restricted cash. Cash pledged as collateral for a letter of credit sits in the account but cannot pay a supplier. In the example, $412,600 on the statement becomes $362,100 of available cash: plus $12,900 in transit, minus $38,400 of uncleared cheques and $25,000 of restricted cash.

The week calendar

Excel dates are day numbers, so the calendar is arithmetic. With the first Monday in a cell, week n starts on =$F$8+7*(n-1) and ends on the Friday, start + 4. Monthly items need a flag: a Monday-to-Sunday week contains the 1st of a month when DAY(start+6)<=7. Bi-weekly payroll falls in the weeks where MOD(n,2)=1 if the first payroll is in week 1. Use real dates rather than typed labels, so the whole calendar shifts when you roll forward.

Forecast receipts from the receivables, not from sales

The biggest line is customer receipts, and the most common error is to copy budgeted sales into it. Start from the AR ageing: invoices that are current mostly pay in the next weeks, invoices 60 or 90 days past due much less, and some never. Spread each bucket with a collection curve based on how customers actually pay, then add new invoices from forecast sales after the collection lag, which you can read off your days sales outstanding. One-off receipts, such as an insurance claim or an asset sale, go into the week you expect the cash, weighted by how certain they are.

Disbursements: payroll, rent, suppliers and debt service

Payments are easier to forecast than receipts because the company controls them: payroll on its cycle, rent on the 1st, suppliers on their payment terms from the open payables plus new purchases, taxes on their due dates, interest and loan instalments on the loan schedule. In the example, payroll runs every second week (weeks 1, 3, 5 and so on), rent is paid at the start of each month in weeks 1, 5 and 9, and a quarterly loan instalment lands in week 9 as well, which is why that week pays out $527,000.

Worked example: 13 weeks of a wholesaler under pressure

Yellow cells are inputs: the bank position, the minimum cash balance, the first Monday and the weekly receipts and disbursements. Columns E and F and the summary rows are formulas. Amounts in USD.

Worked example: 13 weeks of a wholesaler under pressure

	A	B	C	D	E	F
1	Opening position and assumptions					Value
2	Bank balance per statement					412,600
3	+ Deposits in transit					12,900
4	− Cheques issued, not yet cleared					38,400
5	− Restricted cash (collateral)					25,000
6	Opening available cash					362,100
7	Minimum cash balance					150,000
8	Week 1 starts (Monday)					02 Nov 2026
9
10	Week	Starts	Receipts	Disbursements	Net flow	Closing cash
11	1	Mon 2 Nov	286,000	402,000	-116,000	246,100
12	2	Mon 9 Nov	318,000	241,000	77,000	323,100
13	3	Mon 16 Nov	274,000	436,000	-162,000	161,100
14	4	Mon 23 Nov	301,000	262,000	39,000	200,100
15	5	Mon 30 Nov	268,000	489,000	-221,000	-20,900
16	6	Mon 7 Dec	335,000	247,000	88,000	67,100
17	7	Mon 14 Dec	290,000	398,000	-108,000	-40,900
18	8	Mon 21 Dec	352,000	296,000	56,000	15,100
19	9	Mon 28 Dec	241,000	527,000	-286,000	-270,900
20	10	Mon 4 Jan	412,000	236,000	176,000	-94,900
21	11	Mon 11 Jan	398,000	392,000	6,000	-88,900
22	12	Mon 18 Jan	436,000	254,000	182,000	93,100
23	13	Mon 25 Jan	371,000	401,000	-30,000	63,100
24
25	Total, 13 weeks		4,282,000	4,581,000	-299,000	63,100
26	Lowest closing cash (the trough)					-270,900
27	Trough week					Mon 28 Dec
28	Weeks below the minimum cash balance					9
29	Funding need to keep the minimum (peak revolver draw)					420,900

The company and all figures are illustrative. Payroll runs in odd weeks, rent in weeks 1, 5 and 9, and week 9 also carries a quarterly loan instalment. Educational example, not financial or lending advice.

Closing cash, the trough and the revolver

Net flow is receipts minus disbursements, and closing cash is the previous closing cash plus the net flow. Three formulas then summarise the quarter: the trough is =MIN(F11:F23), the trough week is =INDEX(B11:B23,MATCH(F26,F11:F23,0)), and the funding need is =MAX(0,F7-F26), the amount needed to keep the minimum cash balance in the worst week. Here cash falls below the $150,000 minimum in 9 of the 13 weeks and the trough is $270,900 below zero, so the company needs $420,900 of revolver availability, or earlier collections and later payments, before week 9.

A full model lets the revolver draw and repay automatically: draw = MAX(minimum − cash before revolver, 0) and repay = MIN(MAX(cash before revolver − minimum, 0), revolver balance). The peak revolver balance over the 13 weeks then equals the funding need above, as long as the revolver starts undrawn.

Roll it forward and track the variance

The forecast earns its keep in the weekly review. Compare last week's forecast with the bank, split each variance into timing (a customer paid a week late, the cash will still come) and permanent (a customer will not pay), move the timing differences into later weeks, replace the opening cash with the actual balance and add a new week 13. A forecast whose receipts are always late is too optimistic, so adjust the collection curve rather than repeating the same variance. For the working capital view behind the weekly numbers, see the cash conversion cycle.

Common mistakes

• Starting from the bank balance. Uncleared cheques and restricted cash make the statement look richer than it is. Start from available cash.
• Forecasting receipts from sales. Sales become cash only after the collection lag, and overdue invoices may never pay. Build receipts from the AR ageing and a collection curve.
• Monthly thinking in a weekly model. Dividing monthly rent or payroll by four smooths away the very peaks the forecast is meant to find. Put each payment in its own week.
• Forgetting debt service and taxes. Interest, loan instalments, sales tax and payroll taxes are fixed dates. Missing one is a default, not a variance.
• Looking only at week 13. The example ends with positive cash, yet it is $270,900 below zero in week 9. The trough, not the ending balance, decides whether the company can pay.
• Never comparing with actuals. Without a weekly variance review and roll-forward, the forecast is stale after two weeks.