How to Build a 13-Week Cash Flow Forecast: Find the Shortfall Before It Arrives

How a profitable business runs out of cash

The company in the workbook invoices about $4.2m a week, buys goods at 55% of sales and pays $0.35m of other operating costs every week, $2.0m of payroll every other Friday, $0.9m of rent on the first of each month and $0.7m of interest at each month end. Over the thirteen weeks that is $54.5m of sales against roughly $30.0m of purchases, $4.6m of other costs, $12.0m of payroll, $3.6m of rent and $2.1m of interest: an operating profit of around $2m before depreciation, which is what the monthly management accounts will show.

Cash before the revolver falls by $5.6m over the same thirteen weeks. The difference is everything the profit and loss account either ignores or spreads thinly: $3.25m of restructuring professional fees, a $1.2m tax payment in week nine, $1.3m of maintenance capex, $1.2m of one-off payments in weeks three and eleven, and a receivables book that grows by about $0.9m because customers pay later than the company invoices. None of those is a surprise to anyone who has read the contracts and the tax calendar. All of them are invisible in a monthly profit figure.

That is the shape of most liquidity crises. The business is not loss-making; it is paying for things that are not costs, in weeks when the receipts have not arrived. The forecast exists to put the two timetables side by side.

Opening cash, the opening book and what counts as collateral

Start where the company stands at the start of week one, on the Assumptions sheet: $5.0m of cash, $10.0m already drawn on the revolver, $9.5m of trade receivables and $12.0m of inventory. The receivables are the first thing to get right, because they are next week’s receipts. Of the $9.5m opening book, $6.3m is collected in weeks one to three ($2.8m, $2.1m and $1.4m) and the rest is assumed not to be collected within the forecast: disputed, overdue, or simply not worth counting on. A forecast that collects 100% of its opening book is the first thing a lender’s adviser marks down.

Inventory appears only in the borrowing base. It is not sold into cash within thirteen weeks at anything like book value, so it earns nothing in receipts, but it is collateral the asset-based lender advances against, which is the only reason it is on the sheet.

• Opening cash is the bank balance, not the ledger balance. Uncleared items belong in the week they clear.
• The opening receivables are listed by expected collection week, not aged by invoice date. The question is when the cash arrives.
• A revolver balance is a liability that moves every week, so it is an opening balance, not a fixed facility.

Receipts: a collections curve, not a sales forecast

Receipts are not sales. They are sales from this week and the weeks before, arriving according to how customers actually pay. The Receipts sheet applies a collections curve to the sales forecast: 10% of an invoice is collected in the week it is raised, 30% a week later, 40% two weeks later and 20% three weeks later. Week one therefore collects $0.4m of its own $4.2m of sales plus the $2.8m from the opening book, $3.2m in all. By week four the curve is fully loaded and receipts run close to sales, at $4.2m.

The check is the receivables roll-forward on the same sheet: opening receivables plus sales less receipts equals closing receivables, every week, and over the quarter the book should move the way the curve implies. Here it grows from $9.5m to $10.5m, because the curve collects a little less than the company invoices. If the roll-forward does not tie, a receipt has been counted that no invoice supports.

The downside is a separate input, Downside reduction in collections, left at 0% in the base case. It cuts every week’s collections by a percentage without changing the timing, which is the stress a lender asks for: customers who hear the company is in difficulty pay late and short, and dispute what they can. Keep it as one cell, because a reviewer wants to move it and see what happens.

Disbursements by calendar, never by average

A monthly model divides payroll by four. A weekly forecast pays it on the Fridays it is actually paid, and the difference is the whole point. On the Disbursements sheet payroll is $2.0m in even weeks and nothing in odd weeks; rent is $0.9m in weeks one, five, nine and thirteen; interest and fees fall in weeks four, eight and twelve; the $1.2m tax bill lands in week nine; maintenance capex and the restructuring advisers’ fees are the only lines that are the same every week. Supplier payments follow the purchase terms: 25% paid on delivery, the rest two weeks later, with the two weeks of purchases made before the forecast began paid in weeks one and two.

Total disbursements swing from $2.9m in week seven to $5.7m in week eight, and from $3.7m in week eleven to $5.7m in week twelve. Those are the weeks the forecast exists for. Averaged across the month, the company pays about $4.5m a week and the revolver covers it comfortably; paid by calendar, two Fridays a month need $1.5m more than the receipts bring in, and the question is whether the facility has that much headroom left on those Fridays.

Professional fees belong on the sheet whatever the company would prefer. In a restructuring they are among the most certain payments the company will make, they are paid weekly or fortnightly, and a forecast that leaves them out understates the funding need by exactly the amount the advisers will later ask for.

The revolver inside its borrowing base

A revolving credit facility has a commitment and a borrowing base, and the smaller of the two is what the company can draw. The commitment here is $20m. The borrowing base is 85% of eligible receivables (90% of the closing book, the share under 90 days old and not in dispute) plus 50% of inventory, and it comes to about $14m. So the facility that reads $20m on the term sheet lends $14m, and that figure moves every week as the receivables book moves.

The Liquidity sheet draws the revolver the way the facility works: the company draws only what holds cash at the $3.0m minimum operating balance, within the availability it has left, and repays from any cash above the minimum, because a drawn balance costs interest and undrawn availability costs almost nothing. If the borrowing base falls below the drawn balance, the excess is repaid whether or not the company can spare the cash. That mandatory repayment is why a shrinking receivables book is dangerous even when sales are steady: the lender takes back the facility exactly when the company most needs it.

In the base case the revolver climbs from $10.0m to $13.6m over the quarter and peaks at $13.9m in week twelve. The company does not fund the quarter from its own receipts. The bank does, and it is lending against collateral that has about $0.3m of room left.

Liquidity, headroom and the shortfall: reading the Summary

Three numbers are confused in every first forecast. Cash is the bank balance. Availability is the borrowing base less what is drawn. Liquidity is the two together, and liquidity is what the covenant tests, because it is what the company could pay with tomorrow. The Liquidity sheet shows all three each week and the covenant headroom beneath them: total liquidity less the $3.0m minimum liquidity covenant.

The Summary sheet reads the quarter off in nine rows. Base case: net cash flow before the revolver of minus $5.6m, minimum total liquidity of $3.3m, reached in week twelve, a peak revolver balance of $13.9m, minimum covenant headroom of $0.3m, no weeks in breach and no funding shortfall. The company survives the quarter, with $0.3m to spare on its tightest Friday, entirely on the bank’s money. That is what a lender reads as “fully drawn and one bad week from a breach”.

A funding shortfall is defined precisely: the amount by which cash would fall below the minimum once the revolver has nothing left to give. It is the figure the whole exercise is for, because it is the size of the new money, the waiver or the deferral that has to be found, and the week it appears is the date by which it has to be found.

Where the shortfall is hiding: the downside case

Set Downside reduction in collections to 10%, which is a mild version of what happens when customers learn a supplier is in trouble. Every week’s receipts fall by about $0.4m. The revolver now has to cover the gap, so it climbs faster, to $18.1m by week twelve, while the borrowing base, which is made of the receivables that are not being collected, grows more slowly than the drawings. Availability runs out in week ten. Total liquidity falls to $2.1m in week twelve, the covenant is breached in weeks ten, twelve and thirteen, and the funding shortfall reaches $0.9m: the cash that would have to come from somewhere other than the bank to keep the minimum balance on the Friday payroll and interest both fall due.

At 15% the picture is a quarter rather than a bad fortnight: the revolver reaches its $20m commitment in week thirteen, liquidity falls to $1.4m, and the shortfall is $1.6m. Change the supplier terms instead, so that 50% of purchases are paid on delivery rather than 25%, and the base case still clears, but with $0.2m of headroom rather than $0.3m. Suppliers tightening terms is the other thing that happens when a company is known to be stressed, and it happens at the same time as the collections slow.

The forecast has now done its job. It says the business survives the quarter on its own only if collections hold, that a 10% shortfall in collections needs about $1m of new money in place before week ten, and that a 15% shortfall needs about $1.6m. Those are the numbers a board takes to its lenders, and they are far easier to raise in week two than in week nine.

Updating it every week: actual against forecast

A 13-week forecast is not built once. Each Monday the first column is replaced with what actually happened, the window rolls forward a week, and the variance between the two is explained line by line before anything else is done. Collections first, because a miss there is either a customer who did not pay or a curve that was too optimistic, and the two call for different responses. Then disbursements, where most variances are timing (a supplier paid early, a payment that slipped) rather than amount. The receivables roll-forward catches the error that matters most: receipts recorded with no invoice behind them.

Two disciplines keep the update honest. Never adjust the forecast to meet the covenant; adjust the business or the facility, and let the forecast report the result. And keep the base and downside cases as the same workbook with one input changed, as this one does, so that the lenders who review the forecast every week are reading the same sheet with a different number in one cell rather than a different model.

The Checks sheet is the last thing to look at before the forecast leaves the building. The cash roll-forward, the revolver against the borrowing base, the collection curve summing to 100% and the receivables roll-forward all read zero, and the status reads OK. A forecast that fails its own checks fails in front of the lender.

What a reviewer looks for

A lender’s adviser reads a company’s 13-week forecast with a short list, and most of the items on it are already visible in the workbook.

• Receipts modelled as sales, with no collections lag and no opening book. The receivables roll-forward will not tie.
• Payroll and rent spread evenly across the weeks when they are paid on specific Fridays. The minimum-liquidity week moves and usually gets worse.
• A revolver assumed available to its commitment when the borrowing base is smaller, which overstates liquidity by the difference.
• Professional fees left out, although they are among the most certain payments in a restructuring.
• A covenant tested on month-end cash when the documents test total liquidity every week.
• No downside case, or a downside case that only changes the timing of collections and never their amount.