13-week cash flow forecast guide, with a worked example

A 13-week cash flow forecast is a weekly projection of the cash a business will actually receive and pay over the next quarter, starting from the bank balance. It uses the direct method: it lists receipts and disbursements as they happen instead of adjusting net income. Its job is to show the lowest cash balance in the period, the week it occurs, and how much funding is needed to stay above a minimum. You build it by listing receipts by source and disbursements by type, adding a revolver line that draws and repays automatically, and then updating it every week with actuals. For the short definition, see what is a 13-week cash flow forecast.

Why use 13 weeks and weekly columns in a cash flow forecast?

Thirteen weeks is one quarter (52 weeks divided by four). It is far enough ahead to plan around quarterly taxes, insurance renewals and seasonal swings, and near enough that individual customer payments and payroll dates can be predicted rather than guessed.

Weekly columns matter because monthly totals hide the low points. A month can end with plenty of cash and still contain a week in which payroll, rent and a tax payment all land before the customers' payments do. The direct method also ties to the bank statement line by line, which makes each week's actuals easy to compare.

The forecast is rolling. Each week you replace the first week with actuals, drop it, and add a new week 13, so you always look 13 weeks ahead.

Who uses a 13-week cash flow forecast?

Treasury and finance teams in small and mid-size companies, to manage liquidity and decide when to draw on a credit line.
Lenders, who often ask for one, along with weekly variance reports, when a borrower is under covenant pressure or negotiating a waiver or forbearance.
Turnaround and restructuring advisors, for whom it is often the main reporting document, because it shows how long existing cash and funding will last.

How should a 13-week cash flow forecast be laid out?

Put the inputs in one block at the top: opening cash (from the bank, not the books), minimum cash, revolver limit and the opening revolver balance. Put weeks across the columns and the lines down the rows, in this order:

Receipts by source: collections from receivables, cash sales, other receipts.
Disbursements by type: payroll and payroll taxes, vendors, rent and utilities, taxes and insurance, debt service, capital expenditure.
Net cash flow: receipts minus disbursements.
Opening cash, cash before revolver, revolver draw or repayment, ending cash.
Revolver balance and availability.

Freeze the label column and the header row so they stay visible as you scroll (see how to freeze a header row).

What does a worked 13-week cash flow forecast look like?

The example is a small distributor. Amounts are in $ thousands.

Opening cash is 120.0, minimum cash is 100.0, the revolver limit is 100.0 and the opening revolver balance is 40.0.
Payroll and payroll taxes are 64.0 every second week, starting in week 2.
Rent is 18.0 in weeks 1, 5 and 9. Sales tax is 11.0 in weeks 4, 8 and 13. Debt service is 6.5 in weeks 4, 8 and 12. The insurance premium is 14.0 in week 7. All four sit in the "Other" column.
Receipts and vendor payments come from the collections schedule and the payment runs.

Week	Receipts	Payroll	Vendors	Other	Net cash flow	Revolver draw/(repay)	Ending cash	Revolver balance
1	86.0	0.0	46.0	18.0	22.0	-40.0	102.0	0.0
2	78.0	64.0	41.0	0.0	-27.0	25.0	100.0	25.0
3	84.0	0.0	44.0	0.0	40.0	-25.0	115.0	0.0
4	80.0	64.0	52.0	17.5	-53.5	38.5	100.0	38.5
5	92.0	0.0	47.0	18.0	27.0	-27.0	100.0	11.5
6	76.0	64.0	42.0	0.0	-30.0	30.0	100.0	41.5
7	82.0	0.0	45.0	14.0	23.0	-23.0	100.0	18.5
8	79.0	64.0	51.0	17.5	-53.5	53.5	100.0	72.0
9	94.0	0.0	46.0	18.0	30.0	-30.0	100.0	42.0
10	80.0	64.0	43.0	0.0	-27.0	27.0	100.0	69.0
11	83.0	0.0	45.0	0.0	38.0	-38.0	100.0	31.0
12	81.0	64.0	50.0	6.5	-39.5	39.5	100.0	70.5
13	95.0	0.0	47.0	11.0	37.0	-37.0	100.0	33.5
Total	1,090.0	384.0	599.0	120.5	-13.5	-6.5	100.0	33.5

The totals reconcile: opening cash of 120.0, plus net cash flow of -13.5, plus a net revolver repayment of 6.5, equals ending cash of 100.0.

Read the table for the pattern, not the total. Over 13 weeks the business burns only 13.5, but the revolver swings between 0.0 and 72.0 because payroll weeks and tax weeks fall together. Cash before the week 8 draw would be 46.5, which is 53.5 below the minimum. The lowest headroom is 28.0 (the limit of 100.0 minus the peak balance of 72.0), and that is the number a lender will look at.

Which formulas drive the revolver in a 13-week cash flow forecast?

With the inputs in B2 (opening cash), B3 (minimum cash), B4 (revolver limit) and B5 (opening revolver balance), and weeks in columns C to O, the week 2 column D uses these formulas, with receipts in row 8, payroll, vendors and other in rows 9 to 11, and the lines below in the order listed:

Total disbursements: =SUM(D9:D11)
Net cash flow: =D8-D12
Opening cash: =C17
Cash before revolver: =D14+D13
Draw or (repay): =MAX(-C18, MIN($B$4-C18, $B$3-D15))
Ending cash: =D15+D16
Revolver balance: =C18+D16
Availability: =$B$4-D18

The draw formula does three things. If cash before the revolver is below the minimum, it draws the shortfall, but no more than the unused limit. If cash is above the minimum, it repays the excess, but no more than the balance. Week 1 uses $B$2 for opening cash and $B$5 in place of C18. Add a flag, =D17<$B$3, for any week in which the limit is exhausted and cash still ends below the minimum. A real facility may also be capped by a borrowing base, so use availability from the lender's report, not just the limit minus the balance.

How do you build receipts and disbursements in a 13-week forecast?

Receipts. Forecast collections from payment behavior, not due dates. For each open invoice, expected payment date is due date plus the customer's average days late, and the week number is =INT((pay_date-week1_start)/7)+1. Weekly collections are then =SUMIFS(Amount, WeekNo, C$7). Invoices for large customers are best forecast one by one; for the long tail, apply a collection curve to the aging report. Put new sales through the same curve. Add other receipts, such as tax refunds or asset sales, only when they are committed.

Disbursements. Use pay dates and payment-run dates, not accrual dates. Payroll is the largest and most predictable line, followed by payroll tax deposits and benefits. Vendor payments follow the payment-run calendar, with critical vendors flagged as cash on delivery. Then add the lumpy items: rent, sales tax, quarterly estimated tax, insurance, annual license fees, debt service and capital expenditure that is already committed.

How do you track weekly variance and roll the forecast forward?

Each week, enter actuals from the bank, compare them with the forecast line by line, and then roll the forecast forward. Define variance as the effect on cash, so that positive is always favorable: actual minus forecast for receipts, forecast minus actual for disbursements.

Line	Week 1 forecast	Week 1 actual	Week 1 variance	Week 2 variance	Cumulative variance
Receipts	86.0	79.0	-7.0	+5.0	-2.0
Vendors	46.0	44.0	+2.0	-3.0	-1.0
Net cash flow	22.0	17.0	-5.0	+2.0	-3.0

In week 1 receipts were 7.0 below forecast (-8.1%, =(Actual-Forecast)/ABS(Forecast)). Week 2 recovered 5.0 of it, which suggests a timing slip rather than a lost receipt. See how to calculate percentage change for the formula. Classify each variance as timing, which reverses in a later week, or permanent, which does not. Permanent variances need a change to the assumptions. Payroll and the other lines matched the forecast in both weeks, so they are left out of the table.

To roll forward, set the next week's opening cash to the actual bank balance, delete the completed week, and add week 14. Keep each week's prior forecast so that you can measure accuracy by line over time.

What pitfalls make a 13-week cash flow forecast wrong?

Payroll timing. A 13-week window contains six or seven bi-weekly paydays. In the example, moving paydays to weeks 1, 3, 5 and so on gives seven paydays (448.0 instead of 384.0). The revolver then reaches its 100.0 limit in week 9 and cash ends the week at 94.0, which is 6.0 below the minimum. In a year with 26 pay dates, two months have three. Check payroll tax deposit dates separately.
Collections assumptions. If receipts are 5% below forecast in every week (54.5 in total), the revolver is exhausted in week 8 and cash ends that week at 95.1. A small, systematic bias in collections matters more than a large one-time item.
Starting from book cash. The opening balance must be the bank balance, net of outstanding checks and deposits in transit.
Due dates instead of pay dates. This applies to payables as well as receivables. Forecasting vendors on due dates makes the forecast too smooth.
Forgotten annual items. Insurance renewals, annual taxes, audit fees and bonuses.
No owner. One person should own the forecast, collect inputs from sales, purchasing and payroll, and sign it off weekly.

13-week cash flow forecast checklist

Start with the bank balance, a minimum cash level and the facility terms.
Build receipts from payment behavior, and disbursements from pay and payment-run dates.
Let one formula handle revolver draws and repayments, and flag any week below minimum cash.
Enter actuals weekly, classify variances as timing or permanent, and keep prior versions.
Roll forward every week so the horizon stays at 13 weeks.
Run two stress cases: a payroll week shift and a 5% collections shortfall.

The 13-week cash flow forecast template from Sheet Reserve provides the weekly layout with example data, so you can replace the figures with your own.