Reference · Valuation

Valuation Models

Load when **Valuation**.

Part of the Financial Modeling skill · loaded on demand from SKILL.md

Six recipes: DCF, trading comps, precedent transactions, dividend discount model, sum-of-the-parts, football field. All share the build engine in references/build-engine.md (cell colours, named ranges, recalc-and-verify) — only the distinctive structure, formulas, checks, inputs, and example are given here.

DCF Model (dcf-model)

Intrinsic value: project unlevered free cash flow, discount at WACC, dual terminal value.

When: "DCF", "intrinsic value", "WACC", "terminal value", "free cash flow projection".

Tabs: Cover · Assumptions · Model (revenue → uFCF) · DCF (discounting, TV, bridge) · Sensitivity (WACC × terminal growth Data Table) · Checks.

Assumptions (per scenario, live column via CHOOSE): revenue y0, revenue growth %/yr, EBIT margin %, tax rate, D&A %rev, capex %rev, NWC %rev, risk-free, ERP, levered beta, pretax Kd, debt/equity weights, terminal growth g, exit EV/EBITDA, mid-year toggle (0/1), net debt, minority interest, investments, diluted shares.

Model (per forecast year):

  • Revenue =PriorRevenue*(1+GrowthPct); EBIT =Revenue*EBITMarginPct; Taxes =-EBIT*TaxRate; NOPAT =EBIT+Taxes.
  • D&A =Revenue*DAPct (add-back); Capex =-Revenue*CapexPct; NWC level =Revenue*NWCPct, ΔNWC =-(NWC_t-NWC_{t-1}).
  • uFCF =NOPAT+DA+Capex+ChangeNWC (capex and NWC already signed negative).

DCF:

  • Cost of equity (CAPM) =RiskFree+Beta*ERP; after-tax Kd =PretaxKd*(1-TaxRate); WACC =EquityWeight*CostEquity+DebtWeight*AfterTaxKd.
  • Period t =t-0.5*MidYearToggle; discount factor =1/(1+WACC)^PeriodT; PV =FCF_t*DF_t; sum =SUM(PVrow).
  • TV Gordon =FCF_N*(1+g)/(WACC-g); TV exit =EBITDA_N*ExitMultiple (EBITDA_N =EBIT_N+DA_N); PV of TV =TV*DF_N.
  • EV =SumPV_FCF+PV_TV; equity =EV-NetDebt-MinorityInterest+Investments; per share =Equity/DilutedShares.

Sensitivity: two-variable Data Table, row input = TermGrowth, column input = WACC, corner = per-share.

Checks: implied g from exit multiple vs assumed g; PV-of-TV / EV share (flag if dominant); WACC>g (Gordon breaks otherwise); equity bridge ties; the two TV methods give EVs within a sane band.

Inputs: valuation date, horizon, latest revenue, growth/margin paths, tax, D&A & capex %rev, NWC %rev, risk-free, ERP, beta, pretax Kd, weights, terminal g, exit multiple, net debt, minorities, investments, diluted shares, mid-year preference.

Example: revenue 1,000 growing 8%→3% over 5y, EBIT margin 20%, tax 25%, D&A 5% / capex 6% of revenue, NWC 10%, risk-free 4%, ERP 5%, beta 1.10, pretax Kd 6%, 70/30 weights, g 2.5%, exit 9.0x → WACC ≈ 9.4%; EV from ΣPV(FCF)+PV(TV), less net debt 200 over 100 shares → per-share. Illustrative.

Trading Comps (trading-comps)

Relative value off public peer multiples.

When: "comps", "comparable companies", "peer multiples", "EV/EBITDA", "trading multiples".

Tabs: Cover · Comps Input (one row/peer, blue) · Calcs (per peer) · Output (stats + implied range) · Checks. Uses openpyxl+pandas.

Calcs (per peer):

  • TSM dilution =MAX(0,(SharePrice-Strike)/SharePrice)*Options; diluted shares =Basic+TSM; market cap =SharePrice*Diluted.
  • Net debt =TotalDebt-Cash; EV =MarketCap+NetDebt+Minority+Preferred.
  • EV/Revenue =EV/LTMRevenue, EV/EBITDA =EV/LTMEBITDA, EV/EBIT =EV/LTMEBIT, P/E =MarketCap/LTMNetIncome; forward multiples on forward figures.
  • Clean flag =IF(OR(EV<=0,EVEBITDA>Upper,EVEBITDA<Lower),0,1) (bounds are inputs) to exclude outliers.

Output (over clean set, per multiple): median =MEDIAN(IF(CleanFlag=1,Multiple)) (array) or via a helper clean column; mean =AVERAGEIF(CleanFlag,1,Multiple); PERCENTILE.INC(...,0.25) / 0.75. Apply to subject: implied EV =ChosenMultiple*SubjectMetric; equity =EV-SubjNetDebt-Minority-Preferred; per share =Equity/SubjDiluted. Let the user pick which statistic/percentile anchors low and high.

Checks: EV>0 every peer; multiples within bounds (auto-exclude); market cap reconciles on a hand spot-check; subject low ≤ high; show count of clean peers. Watch P/E with zero/negative net income.

Inputs: pricing date; per peer share price, shares & options, debt, cash, minorities, preferred, revenue/EBITDA/EBIT/NI + forwards; subject same metrics + which multiple/percentile to anchor.

Example: five peers EV/EBITDA 7.5/8.2/9.0/9.8/12.5x; exclude 12.5x → clean median ≈ 8.6x; apply 8.0–9.5x to subject LTM EBITDA 250 → EV range, less net debt 300 over 80 shares → per-share range. Illustrative.

Precedent Transactions (precedent-transactions)

Relative value off prices paid in past deals — captures the control premium comps miss.

When: "precedent transactions", "deal comps", "transaction multiples", "control premium", "M&A comps".

Tabs: Cover · Deals Input (one row/deal, blue; generic acquirer/target labels only) · Calcs · Output · Checks. openpyxl+pandas.

Calcs (per deal): offer equity =OfferPrice*TargetDilutedShares; net debt =TargetDebt-TargetCash; transaction EV =OfferEquity+NetDebt+Minority+Preferred; EV/Revenue, EV/EBITDA; implied premium =OfferPrice/UnaffectedPrice-1; clean flag =IF(OR(EV<=0,EVEBITDA>Upper,EVEBITDA<Lower,Premium>PremiumCap),0,1).

Output: median/mean of each multiple and the premium over the clean set. Apply to subject two ways: multiple-based EV → equity → per share; and premium-based offer =SubjectUnaffectedPrice*(1+ChosenPremium) for low/high. Show both so the control-premium effect is visible.

Checks: premium not negative / not above cap; EV>0; EV/EBITDA in bounds; precedent median ≥ trading-comps median (control premium sanity); subject low ≤ high both methods. Watch EBITDA and unaffected-price divisors.

Inputs: analysis date; per deal announced date, generic labels, offer price, target shares/debt/cash/minorities/preferred, unaffected price, target LTM revenue & EBITDA; subject same + multiple and premium range.

Example: four deals EV/EBITDA 9.0/10.5/11.0/13.5x, premia 25/30/35/45% → median ≈ 10.8x, ≈ 32.5%; apply 10–12x to subject EBITDA 250 and 25–35% premium on a 40 unaffected price. Illustrative.

Dividend Discount Model (dividend-discount-model)

Value equity directly off the dividend stream — banks, insurers, utilities, stable payers.

When: "DDM", "dividend discount", "Gordon growth", "cost-of-equity valuation".

Tabs: Cover · Assumptions · DDM (explicit forecast + single-stage Gordon block) · Sensitivity (Ke × terminal growth) · Checks.

DDM:

  • Ke =RiskFree+Beta*ERP (CAPM).
  • Explicit DPS =PriorDPS*(1+DivGrowth) or =EPS_t*Payout (EPS grown).
  • DF =1/(1+Ke)^t; PV =DPS_t*DF_t; sum =SUM(PVrow).
  • TV (Gordon on year-N dividend) =DPS_N*(1+g)/(Ke-g); PV of TV =TV*DF_N.
  • Value per share (multi-stage) =SumPV_Dividends+PV_TV.
  • Single-stage Gordon cross-check =DPS_0*(1+g)/(Ke-g).

Sensitivity: Data Table, row input = TermGrowth, column input = Ke, corner = multi-stage per-share.

Checks: Ke>g; payout within 0–100% each year; dividend growth < Ke in the explicit window; single- vs multi-stage within a sane band; terminal share of value flagged if dominant. Watch the Ke-g denominator.

Inputs: valuation date, latest DPS or EPS+payout, dividend growth path, explicit horizon, risk-free, ERP, beta, terminal g, scenario values if running cases.

Example: DPS 2.00 growing 8% for 5y then 3% perpetual; risk-free 4%, ERP 5%, beta 0.90 → Ke ≈ 8.5%; explicit PVs + PV(Gordon TV) → per-share; single-stage Gordon as a cross-check. Illustrative.

Sum-of-the-Parts (sum-of-the-parts)

Value a diversified business segment-by-segment on each segment's own method, then bridge to equity.

When: "sum of the parts", "SOTP", "segment valuation", "conglomerate value", "break-up value".

Tabs: Cover · Segments (per-segment financials + chosen method, blue) · SOTP (per-segment EV + % of total) · Bridge (EV → equity → per share) · Checks.

Segments: per segment revenue, EBITDA, EBIT, method selector (1 = EV/EBITDA, 2 = EV/Revenue, 3 = mini-DCF), chosen multiple, and for mini-DCF the FCF path/WACC/g. Plus unallocated corporate cost, its cap multiple, net debt, minority interest, stakes/investments, diluted shares.

SOTP:

  • Segment EV =CHOOSE(Method, SegEBITDA*Multiple, SegRevenue*Multiple, MiniDCF_EV).
  • Mini-DCF EV = Σ FCF_t/(1+WACC)^t + FCF_N*(1+g)/(WACC-g)/(1+WACC)^N.
  • Corporate drag =-UnallocatedCost*CorporateCapMultiple (negative EV item).
  • Total EV =SUM(SegmentEVs)+CorporateDrag; % of total =SegmentEV/TotalEV.

Bridge: equity =EV-NetDebt-MinorityInterest+StakesAndInvestments; per share =Equity/DilutedShares; optional implied blended =EV/SUM(SegmentEBITDA).

Checks: segment EVs + drag tie to TotalEV exactly; % of total reconciles to 100%; each multiple in a sane industry band; blended EV/EBITDA reasonable vs standalone; bridge ties. Watch any mini-DCF WACC-g and the %-of-total divisions.

Inputs: valuation date, segment list, per-segment revenue/EBITDA/EBIT + method & multiple, mini-DCF assumptions, corporate cost + cap multiple, net debt, minorities, stakes, shares.

Example: Seg A EBITDA 300 @ 9.0x, Seg B EBITDA 150 @ 7.0x, Seg C mini-DCF, corporate drag 20 @ 8.0x; ΣEV − drag → total EV; less net debt 500 + stake 100 over 120 shares → per-share. Illustrative.

Football Field (football-field)

The summary exhibit: each valuation method's low–high range as a floating horizontal bar with a current-price reference.

When: "football field", "valuation summary chart", "valuation range chart".

Tabs: Cover · Methods (low/high per method, blue inputs or green links from the DCF/comps/precedent/LBO outputs) · Chart Data (floating-bar helper) · Chart · Checks.

Chart Data (per method): Base (invisible) =MethodLow; Range (visible) =MethodHigh-MethodLow; Midpoint =(Low+High)/2; a CurrentPrice column repeating the single input.

Chart: BarChart, type="bar" (horizontal), grouping="stacked", overlap=100. Add Base series first (no fill, no border → invisible), then Range. Categories = method labels. Add current price as a distinct thin marker/reference series. Value-axis format #,##0.00, title "Valuation Summary".

Checks: each low ≤ high; every method labelled (no blank bars); current price inside the union of ranges (or noted if outside); Base+Range=High every row (proves the float); flag any zero-width range.

Inputs: valuation date, current price (or EV), and per method the low/high implied value (or the link to the source model). Confirm ordering and per-share vs EV.

Example: per-share ranges DCF 38–52, Comps 36–48, Precedent 42–58, 52-Week 34–50, LBO 35–45, Analyst 40–55, current price 44 → six floating bars with the price marked. Illustrative.