A free corporate liquidity forecast model in Excel, with a full worked case
A forecast-error reserve built from twelve real quarters, minimum liquidity derived rather than assumed, headroom with and without the revolver, and the EBITDA and revenue fall at which a covenant utilisation certificate can no longer be signed: six linked sheets, one fictional company, no macros.
The file forecasts liquidity for a fictional company with $1.4 billion of revenue and $168 million of EBITDA. Minimum liquidity, the operating float plus a 99% forecast-error reserve plus a seasonal inventory build, comes to $124.6 million. Against $152 million of accessible cash and a $250 million undrawn revolver, headroom over that minimum is $277.4 million, but only $27.4 million of it survives without the revolver. And the revolver is gone once EBITDA falls 21.0%, an 8.4127% drop in annual revenue given the company's operating leverage. Every one of those numbers is a formula you can move.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
Six sheets plus a Checks sheet, in the order the calculation runs. Blue type is an input; black type is a formula; yellow fill marks an assumption that decides the answer.
Inputs
The company, its cash position, the debt and its pricing, the covenant levels, and the downside scenario, all in one place.
Forecast
The twelve quarterly forecast errors against disbursements, the standard deviation they imply, and the reserve at 95%, 99% and 99.9% confidence.
Minimum
Minimum liquidity built from the operating float, the reserve and the seasonal peak, and the headroom against it with and without the revolver.
Drawstop
The EBITDA and revenue fall at which a utilisation certificate can no longer be signed, the downside case run through it, and the same test on the board's own reading of cash.
PriceOfLiquidity
Three ways to hold the same amount of liquidity, undrawn revolver, term debt on deposit, or the revolver drawn and on deposit, each priced and each tested against interest cover.
WhichTestBinds
Leverage and interest cover put on the same footing, a fall in EBITDA, to show which covenant actually breaks first.
Checks
Every figure the book prints set against the cell that computes it, with a PASS or FAIL verdict on each line.
EBITDA at which the revolver's covenant is exactly met
$132.7m
Fall in annual revenue to that point
8.4127%
Revolver's share of total headroom
67.5%
Interest cover today
6.33x
Read the last four lines together. Two thirds of the company's headroom over its own minimum liquidity is the undrawn revolver, and the revolver is only there while the covenant holds. An 8.4127% fall in annual revenue, a 21.0% fall in EBITDA, is enough to close it, which leaves $27.4 million of margin against a minimum of $124.6 million, about five business days of disbursements. That gap between liquidity on paper and liquidity that survives a downside is a narrower question than a week-by-week forecast under stress; for that, see the 13-week cash flow model.
How to use it on your own company
Open Inputs and overwrite the blue cells: revenue, EBITDA, the cash position, the facility and its pricing, the covenant levels.
Read Minimum before Drawstop: minimum liquidity decides how much of the headroom is actually free to use.
Check Drawstop against your own downside: it prints the EBITDA and revenue fall at which the covenant, not the cash, becomes the binding constraint.
Compare the three ways of holding liquidity on PriceOfLiquidity before assuming an undrawn revolver is the cheap option.
Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this corporate liquidity model really free?
Yes. It is a companion file to a book. There is no sign-up, no email address and no paid version.
Does it use macros or circular references?
No macros and no circular reference: the reserve, the minimum and the drawstop test are each built from inputs and earlier cells, never from their own result. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why is headroom without the revolver so much smaller than total headroom?
Because $250 million of the $277.4 million of headroom over the minimum is the undrawn revolver itself. Strip it out and $27.4 million is left, about 5.2 business days of disbursements. The revolver supplies 67.5% of the company's headroom over its own minimum.
Can I use it for a real company?
You can reuse the structure: the forecast-error reserve, the minimum-liquidity build and the drawstop test. The company, the facility and every figure in it are fictional, and nothing here is investment, legal or accounting advice.
The rest of the files
This template is one of the companion files for Treasury Management: the full set adds
the five-balances reconciliation behind the accessible-cash figure, the working-capital and discount workbook, the interest-rate and currency hedging workbook, and the banks, payments and one-page report workbook. Free, like this one.