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.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
The_Liquidity_Test.xlsx (32 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview
The book behind this model
This file is one worked case from Treasury Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
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. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
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.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.