Modeling II: DCF, Comps, LBO & M&A

Learning objectives

By the end of the week you can:

1. Build a DCF tab on top of a linked three-statement model: an FCFF build pulled row-by-row from the model (FCFF = NOPAT + D&A − capex − ΔNWC), a WACC block, discount factors, and PV of the explicit window: every cell a formula, no hardcodes.
2. Compute terminal value both ways, a Gordon perpetuity with a reinvestment-consistent numerator (FCFF_T+1 = NOPAT_T+1 × (1 − g/ROIC_T)) and an exit multiple, then reconcile them: back the implied exit multiple out of the perpetuity and the implied perpetual growth out of the multiple.
3. Run the EV → equity bridge with every bridge item (debt, leases, cash and non-operating assets, minority interest, preferred, options via the treasury-stock method), land on per-share value, and compare it to price without flinching.
4. Build a live two-way sensitivity table (WACC × terminal growth) with Excel/Sheets data tables: correct input-cell wiring, same-sheet rule, recalculation settings. Read it as a range, not 25 point estimates.
5. Construct a comps tab: peer selection, clean EV assembly, forward and trailing multiples, medians. Then apply peer multiples to your company with the M3.06 normalization discipline intact.
6. Assemble a football field from the 52-week range, trading comps, precedent transactions, the DCF band, and a reverse-DCF marker, then describe its construction (helper columns, stacked bars, invisible base series, price line) well enough to build it in any spreadsheet.
7. Explain and compute how leverage torques equity returns: run a complete 5-year mini-LBO: sources & uses, a debt waterfall (term loan with mandatory amortization + full cash sweep, revolver for shortfalls), exit value, equity IRR and MOIC. Then decompose the return into EBITDA growth, multiple change, and debt paydown.
8. Run M&A accretion/dilution for all-stock, all-cash, and mixed consideration, including foregone interest and new intangible amortization. State the two quick rules and their limits, and demonstrate numerically why EPS accretion is not value creation (conservation of value, M3.07).

Prerequisites & connections

Builds on. M3.08 is the chassis: everything here bolts onto the three-statement model you built there (its Assumptions / Model / checks architecture, its revolver, its no-hardcodes discipline). M3.04 supplies the FCFF definition and forecast drivers, and M3.05 the terminal-value law (g capped, numerator rebuilt for reinvestment) plus the reverse-DCF habit. M3.03 supplies the WACC you will now wire into cells; this phase's illustrative hurdles are ~12% INR for India, ~8–9% USD for the US, as of mid-2026, verify against your own M3.03 builds. M3.06 supplies the multiples grammar (EV construction, driver-justified multiples, normalization before comparison), and M3.07 the conservation-of-value lens that will let you see through EPS accretion, plus the M&A decision framework (value created = PV of synergies − premium). M3.01's IRR machinery returns as the LBO scorecard, including its trap (IRR ignores scale and duration; that is why MOIC rides alongside).

Feeds forward. M3.10 uses the finished suite on special situations, and the phase capstone requires it: a three-way valuation of one Indian and one US company, presented on a football field, with a memo. Phase 8's 2–3 day deep dive carries a compressed version (the "3-statement-lite → base/bull/bear → reverse DCF" of Day 2). The valuation workflow artifact finalized alongside this module becomes a permanent pre-flight for every valuation after this week. Sector models (Phase 5) are these tabs re-skinned with sector drivers; the M&A lens returns in M8.04 when you grade acquisitive managements (a serial acquirer touting "EPS accretion" is a M3.07 exam question wearing a suit).

One engine, four gauges. The same forecast can be read as intrinsic value (DCF), as the market's opinion (comps), as a leveraged buyer's bid (LBO), and as a corporate buyer's EPS story (accretion/dilution). An analyst who can compute all four knows which one is lying.

4.1 The finished machine: workbook architecture

Open the M3.08 workbook. You have (at minimum) three layers: Assumptions (blue inputs, the only cells a user touches), Model (the linked IS/BS/CFS with supporting schedules), and Checks (balance check, cash-flow tie-out, revolver logic). Five tabs go on top of that: