Excel DCF Modeler
Overview
Creates professional DCF valuation models following investment banking standards with WACC calculations and sensitivity analysis.
Prerequisites
- Excel or compatible spreadsheet software
- Historical financial data for target company
- Industry comparables for WACC estimation
Instructions
- Create assumptions sheet with revenue growth, margins, WACC, and terminal growth rate
- Build free cash flow projections (5-year forecast)
- Calculate terminal value using Gordon Growth Model
- Discount cash flows and terminal value to present value
- Sum to get enterprise value, subtract net debt for equity value
- Add sensitivity tables for key assumptions
Output
- Complete 4-sheet DCF model with assumptions, projections, valuation, and sensitivity
- Enterprise value and equity value per share
- Sensitivity analysis on WACC and terminal growth rate
Error Handling
| Error |
Cause |
Solution |
| #DIV/0! in terminal value |
WACC equals terminal growth |
Terminal growth must be less than WACC |
| Negative FCF |
High CapEx or WC needs |
Review assumptions, may need different model |
| Unrealistic EV |
Extreme growth assumptions |
Benchmark against industry comparables |
Examples
Example: Value a SaaS Company
Request: "Create a DCF model for a $50M ARR SaaS company growing 30%"
Result: 4-sheet model with 5-year projections, 12% WACC, 3% terminal growth, sensitivity tables
Example: M&A Valuation
Request: "DCF analysis for acquisition target"
Result: Model with synergy adjustments, scenario analysis, and per-share valuation
Resources
