Integrated Three-Statement Operating Model - 10 Years, Revolver, Covenant Headroom, Cross-Checked Valuation

Description
Three-statement templates are the most crowded shelf in financial modelling, and almost all of them have the same hole in the same place. The revolver draws when cash runs short. The interest charge depends on how much is drawn. The cash position depends on the interest charge. That is a loop, and there are only two honest ways out of it: switch on iterative calculation, which makes the file fragile and gives different answers in LibreOffice and Google Sheets, or leave the revolver out altogether and let cash go negative in a bad year.

This model takes the third way. Interest is charged on the balance at the start of the year, so the interest for a year is known before that year's cash flow is computed, the revolver draw is a function of a cash position that is already settled, and the entire financing block resolves left to right in one pass. There is not a single circular reference anywhere in the file. It computes the same numbers in every spreadsheet application, first time, with iterative calculation switched off - and Check 15 proves it by recomputing every interest charge in the workbook from the opening balances and their rates.

What is in the workbook. Fourteen tabs and 2,055 live formulas across ten forecast years and an opening actual column. Revenue from three segments, each with its own volume, growth, price and price growth. Cost split into what moves with volume and what does not. Working capital on five day counts. Capital expenditure separated into maintenance, as a share of revenue, and growth, as a share of the increase in revenue, each with its own depreciation schedule. An amortising term loan whose instalment is capped at the balance outstanding, a revolving credit facility with a commitment fee on the undrawn balance, a minimum cash balance the revolver is drawn to hold, and a cash sweep. Tax through a loss carry-forward pool. A dividend that is a policy rather than an input: a share of earnings, switched off entirely in any year following a close above the leverage limit you set.

The funding gap is reported, not hidden. When the revolver is exhausted and cash would still fall below the minimum balance, this model does not let cash go negative and does not borrow more than the facility allows. It reports the shortfall, names the first year it opens, carries it on the balance sheet as an unfunded requirement so the statements still balance, and fails a check. Switch the scenario to Downside and watch it happen: the revolver reaches its limit in year eight and the gap opens.

Covenants reported as headroom. Four tests - net leverage, interest cover, debt service cover and fixed charge cover - each reported three ways: the ratio, the headroom in the units the covenant is written in, and the headroom in dollars of EBITDA. The binding test is named in every year, so you can see at a glance which covenant you are actually running the company against.

A valuation that cross-checks itself. Unlevered free cash flow discounted at the weighted average cost of capital with an optional mid-year convention, and the terminal value computed twice - once as a growing perpetuity and once at an exit multiple of final-year EBITDA. The two are then turned against each other: the perpetuity terminal value is divided by final-year EBITDA to give the exit multiple it implies, and the exit-multiple terminal value is solved back for the growth rate that would make a perpetuity worth the same, tested against a sanity band you set. That second calculation is the one almost nobody does, and it is the one that catches a terminal value nobody would defend out loud. A football field then sets all four methods side by side.

Sensitivities that do not go stale. Two twenty-five cell grids - cost of capital against terminal growth, and cost of capital against the exit multiple - and a table showing how far EBITDA can fall in its tightest year before each covenant bites. None of it is an Excel data table: every cell is an ordinary formula computed from the same cash flows the Valuation tab uses.

Twenty-four checks. Each measures a number that must be nil, or a condition that must hold, in every one of the ten years: the balance sheet balances, the cash flow statement ties to it, retained earnings roll forward by net income less dividends, the asset schedules roll forward, the term loan closes at exactly nil, the revolver never exceeds its commitment and never goes negative, cash never falls below the minimum, interest is a function of opening balances only, tax is never a credit, dividends never exceed the policy, the sensitivity grid agrees with the valuation, and the implied growth rate stays inside its band. All twenty-four pass in the worked example.