Building Dcf Models

DCF Model Structure:
Revenue Growth: Historical 3yr avg → Forward projections
EBITDA Margin: Normalize for cyclicality
CapEx: % of revenue (maintenance vs growth)
Working Capital: Days sales outstanding analysis
Tax Rate: Normalized effective rate
WACC: Risk-free + Beta × Market premium + Debt cost
Terminal: 2-3% growth OR 10-12x EV/EBITDA exit

Phase 1: Historical Analysis

• Gather 5+ years financials (10-K, 10-Q)
• Normalize one-time items and accounting changes
• Calculate key ratios: Revenue growth, margins, ROIC, working capital turns
• Identify cyclical patterns and business drivers

Phase 2: Projections (5-10 years)

• Revenue: Build bottoms-up by segment/geography
• EBITDA: Apply normalized margins with improvement trajectory
• D&A: Straight-line or accelerated based on asset base
• CapEx: Maintenance (3-4% revenue) + Growth investments
• Working Capital: Model AR, inventory, AP separately
• Tax: Use normalized rate (21-25% US corporate)

Phase 3: WACC Calculation

• Risk-free rate: 10-year Treasury
• Beta: 2-year regression vs S&P 500, unlevered/relevered
• Market risk premium: 6-7% historical
• Cost of debt: Current borrowing rates + credit spread
• Tax shield: Marginal tax rate

Phase 4: Terminal Value

• Growth method: 2-3% perpetual growth
• Exit multiple: 8-15x EBITDA based on comps
• Cross-check both methods for reasonableness

Phase 5: Sensitivity & Scenarios

• Two-way sensitivity: WACC vs Terminal growth/multiple
• Key driver analysis: Revenue growth, EBITDA margin
• Scenario analysis: Bull/Base/Bear cases

Example 1: SaaS Company Input: $100M ARR, 25% growth, 20% EBITDA margin Output:

• Terminal growth: 3%
• WACC: 12% (high beta)
• 5yr FCF CAGR: 35%
• Valuation range: $800M-1.2B

Example 2: Manufacturing Input: $500M revenue, cyclical margins 8-15% Output:

• Normalized EBITDA: 12%
• High CapEx years 1-3 for modernization
• Terminal multiple: 10x (industry standard)
• LBO feasibility: 3x debt/EBITDA max

Model Architecture:

• Separate assumptions, calculations, and output tabs
• Color code: Blue (inputs), Black (formulas), Green (links)
• Build annual then quarterly detail if needed
• Include data validation and error checks

Key Assumptions:

• Revenue growth: Triangulate top-down market vs bottoms-up capacity
• Margins: Consider operating leverage and competitive dynamics
• CapEx: Distinguish maintenance vs growth, model major projects separately
• Working capital: Model underlying business drivers, not just % of sales

Benchmarking:

• Compare ROIC, FCF conversion, and multiples to peers
• Stress test assumptions against historical ranges
• Validate terminal assumptions against long-term industry outlook

• Don't model to a number - Let assumptions drive valuation, not vice versa
• Avoid hockey stick projections - Growth should be sustainable and defensible
• Don't ignore cyclicality - Use mid-cycle metrics for terminal value
• Terminal value sanity check - Shouldn't be >70% of enterprise value
• WACC precision false comfort - Focus on reasonable range, not exact number
• Working capital timing - Model the cash flow impact in correct periods
• Circular references - Structure debt/interest calculations properly
• Currency consistency - Match all inputs to same reporting currency

Football Field Components:

• DCF: Base, bull, bear scenarios
• Trading comps: Current multiples adjusted for differences
• Transaction comps: Include control premiums and synergies
• LBO analysis: Test feasibility at 15-25% IRR thresholds