Business Valuation Model – DCF with Cross-Checked Terminal Value, Trading Comparables, Precedent Transactions and Football Field (Excel)

This workbook values one operating company three ways and reconciles them on a football field. The discounted cash flow is driven off the company’s own three years of actuals rather than a set of drivers somebody invented; the trading comparables and the precedent transactions are applied to the same metrics; and the range the three produce is the answer, not a point estimate one of them happened to land on. Nine tabs, 434 live formulas, one worked example loaded and working the moment you open it.

Why this one is different

There are a lot of valuation templates, and almost all of them have the same two holes. The first is that the terminal value — seventy-three per cent of enterprise value in the worked example, and more in most models — is taken on trust. You type an exit multiple, the model multiplies, and nothing in the file ever asks whether that multiple is consistent with anything else you have assumed. The second is that the comparables are averaged: eight peers, one arithmetic mean, and the two companies with a story quietly set the valuation.

The terminal value is cross-checked in both directions

Set a 7.5 times exit and the model tells you that you have just assumed 2.69 per cent growth for ever, and whether that sits inside the band you set. Set 2.25 per cent perpetual growth and it tells you that implies a 7.04 times exit. When those two numbers are far apart, the two halves of your valuation are not describing the same company — and check 15 says so. Raise the exit multiple far enough and check 13 fails while every other number in the file still looks reasonable.

The cost of capital is assembled, not typed

A peer asset beta relevered at your target capital structure through the Hamada formula, a cost of equity built from the risk-free rate, the equity risk premium and an explicit specific premium, and a cost of debt that is taxed. Eight inputs, seven visible steps, one number at the bottom. It uses target weights rather than today’s, which is why nothing in this file is circular.

Comparables are a distribution, not an average

Every multiple gets six statistics: minimum, lower quartile, median, mean, upper quartile, maximum. The valuation runs at the median and the range at the quartiles, because the minimum and the maximum of eight peers are two companies with a story and neither of them is yours. Beside each peer’s multiples sit its revenue growth, EBITDA margin and leverage, so you can see why one trades at 6.5 times and another at 10.4.

The football field

Four methods, each with a low, a midpoint and a high, drawn as floating bars. The two discounted cash flow rows move the cost of capital one hundred basis points either way and the terminal assumption one step either way; the two market rows run from the lower quartile to the upper quartile. The central range is the lowest and highest of the four midpoints — the range to quote. The widest low and high are the range to be ready to defend. A dispersion line measures how far apart the four methods actually are: above about forty per cent they are not describing the same asset, and what you have is not a range but a disagreement.

Two sensitivity grids and twenty-two checks

Cost of capital against terminal growth, and cost of capital against the exit multiple, both as equity value per share. Nothing here is an Excel data table: all fifty cells are ordinary formulas recomputed from the same cash flows the DCF uses, so nothing goes stale. Checks 20 and 21 test that the centre cell of each grid equals the model beside it. The other checks cover the bridge from enterprise value to equity value, the relevering formula, unlevered free cash flow against its five components, the first forecast margin against the last actual, capital expenditure against depreciation, and the terminal value against the share of enterprise value you will accept.

Tabs: Read Me, Historicals, Assumptions, Forecast, DCF, Comparables, Precedents, Football Field, Checks.

Historicals come first

Three years of actuals go in at the top, and underneath them the model computes what those actuals imply: revenue growth, gross and EBITDA margin, depreciation and capital expenditure as a share of revenue, the effective tax rate, and receivable, inventory and payable days. Those are the numbers you should be arguing about before you forecast anything, and they are where your drivers should start.

Worked example

Northwind Components — $236.5m of revenue, a 12.4 per cent EBITDA margin, $78m of net debt and 24m shares. Cost of capital 9.91 per cent from a 0.95 asset beta relevered at a 30 per cent target debt share. Enterprise value $265.2m on the exit multiple and $253.2m on the perpetuity, against a trading median of 8.0 times and a deal median of 8.85 times. The four methods land between $6.52 and $7.80 a share, a midpoint of $7.16, and an implied 8.52 times last actual EBITDA.

How it is built

Microsoft Excel (.xlsx). No macros, no add-ins, no external links, no password protection, no locked cells, and iterative calculation off. Every figure was independently re-derived before publication: the whole valuation was rebuilt in a second implementation and compared to the workbook in 153 separate tests, including all fifty sensitivity cells and the quartile statistics, which were reimplemented from the definition rather than by calling Excel.

What this model is not

Not a leveraged buyout model — no debt schedule, no sponsor return, no exit waterfall. Not a merger model — one company, no accretion and dilution, no synergies. Not a sum of the parts. It does not adjust peer multiples for size, growth or margin differences; it puts those differences next to the multiples and leaves the judgement with you, because an automatic adjustment nobody can see is worse than none.

A 10-page guide and preview PDF is included with the download.

You must log in to submit a review.