dcf-model
The dcf-model skill builds institutional-grade Discounted Cash Flow models for equity valuation by retrieving financial data from SEC filings, constructing multi-year cash flow projections with formula-driven WACC calculations, performing sensitivity analysis, and generating professional Excel workbooks with executive summaries. Use this when analyzing company intrinsic value, performing detailed financial modeling with growth projections and terminal value calculations, or conducting investment analysis requiring DCF methodology.
git clone --depth 1 https://github.com/fivetaku/claude-office-skills /tmp/dcf-model && cp -r /tmp/dcf-model/claude-in-excel/dcf-model ~/.claude/skills/dcf-modelSKILL.md
# DCF Model Builder ## Overview Institutional-quality DCF models for equity valuation following investment banking standards. Each analysis produces a detailed Excel model (with sensitivity analysis at bottom of DCF sheet). ## Preflight: Dependency Check Before starting, verify required libraries and tools are installed and install any that are missing. ```bash python3 -c "import openpyxl" 2>/dev/null || python3 -m pip install openpyxl command -v soffice >/dev/null 2>&1 || command -v libreoffice >/dev/null 2>&1 || ls /Applications/LibreOffice.app/Contents/MacOS/soffice >/dev/null 2>&1 || echo "WARNING: LibreOffice not found. Install: brew install --cask libreoffice (macOS) or apt install libreoffice (Linux). Required for scripts/recalc.py." ``` **Important**: Do not skip this step — `scripts/recalc.py` and `scripts/validate_dcf.py` need these tools to verify sensitivity tables and TV-as-%-of-EV sanity checks. ## Scripts - `scripts/recalc.py` — Force formula recalculation via headless LibreOffice. Run after building: `python scripts/recalc.py <model.xlsx>` - `scripts/validate_dcf.py` — DCF-specific validation (terminal growth < WACC, WACC in 5-20% range, TV as 40-80% of EV, formula errors). Run after recalc: `python scripts/validate_dcf.py <model.xlsx>` ## Tools - Default to using all information provided by the user and MCP servers available for data sourcing. ## Critical Constraints - Read These First ### Environment: Office JS vs Python/openpyxl - Office JS: Use range.formulas = [["=D19*(1+$B$8)"]] — never range.values for derived cells - Python/openpyxl: Write ws["D15"] = "=D14*(1+Assumptions!$B$5)", then run recalc.py - Office JS merged cell pitfall: Write value to top-left cell alone, then merge + format ### Formulas Over Hardcodes (NON-NEGOTIABLE) - Every projection/margin/discount factor/PV/sensitivity cell MUST be live Excel formula - Only permitted hardcodes: (1) raw historical inputs, (2) assumption drivers, (3) current market data - "If you catch yourself computing something in Python and writing the result — STOP" ### Verify Step-by-Step With the User (DO NOT build end-to-end) 1. After data retrieval → confirm raw inputs 2. After revenue projections → confirm top line + growth rates 3. After FCF build → confirm FCF schedule logic 4. After WACC → confirm calculation + inputs 5. After terminal value + PV → confirm equity bridge 6. After sensitivity tables → final review ### Sensitivity Tables - Use ODD number of rows/columns (5×5 or 7×7) - Center cell = base case (must equal model's actual implied share price) - Highlight center cell with #BDD7EE + bold - Populate ALL cells with full DCF recalculation formulas (75 total for 3 tables) - NO placeholder text, NO linear approximations ### Cell Comments - Add AS each hardcoded value is created - Format: "Source: [System/Document], [Date], [Reference], [URL]" - Never defer to end or write "TODO: add source" ### Model Layout Planning - Define ALL section row positions BEFORE writing formulas - Write ALL headers → section dividers → THEN formulas ## DCF Process Workflow ### Step 1: Data Retrieval and Validation - Priority: MCP Servers → User-Provided Data → Web Search/Fetch - Validation: net debt vs net cash, diluted shares, historical margins, growth rates, tax rate ### Step 2: Historical Analysis (3-5 years) - Revenue growth trends (CAGR), margin progression, capital intensity, WC efficiency, return metrics ### Step 3: Build Revenue Projections - Revenue(Year N) = Revenue(Year N-1) × (1 + Growth Rate) - Three-scenario approach: Bear/Base/Bull - Growth rates: Year 1-2 higher → Year 3-4 moderate → Year 5+ approaching terminal ### Step 4: Operating Expense Modeling - S&M, R&D, G&A — ALL percentages based on REVENUE, not gross profit - Model operating leverage: % should decline as revenue scales ### Step 5: Free Cash Flow Calculation - EBIT → (-)Taxes → NOPAT → (+)D&A → (-)CapEx → (-)ΔNWC → Unlevered FCF ### Step 6: Cost of Capital (WACC) Research - CAPM: Cost of Equity = Risk-Free Rate + Beta × ERP - After-Tax Cost of Debt = Pre-Tax × (1 - Tax Rate) - WACC = (Ke × We) + (Kd × Wd) ### Step 7: Discount Rate Application - Mid-year convention: periods 0.5, 1.5, 2.5... - Discount Factor = 1 / (1 + WACC)^Period ### Step 8: Terminal Value Calculation - Perpetuity Growth: TV = Terminal FCF / (WACC - g) - Exit Multiple: TV = Final Year EBITDA × Exit Multiple - Sanity check: TV should be 50-70% of EV ### Step 9: Enterprise to Equity Value Bridge - Sum PV FCFs + PV Terminal Value = EV - EV - Net Debt = Equity Value - Equity Value / Diluted Shares = Implied Price per Share ### Step 10: Sensitivity Analysis Three tables: (1) WACC vs Terminal Growth, (2) Revenue Growth vs EBIT Margin, (3) Beta vs Risk-Free Rate ## <correct_patterns> section - Scenario Block Selection: INDEX/consolidation column approach (not scattered IF statements) - Revenue Projection: Reference consolidation column - FCF Formula: Use consolidation columns with INDEX - Cell Comment Format: exact source with date/URL - Assumption Table Structure: 3 elements per block (header + column headers + data rows) - Row Planning Process: headers first → dividers → formulas - Sensitivity Table: 5×5 grid, symmetric axes, center = base case, formula in every cell ## <common_mistakes> section - WRONG: Linear approximations in sensitivity tables - WRONG: Placeholder text instead of formulas - WRONG: Missing cell comments - WRONG: Formula row references off (write formulas before headers) - WRONG: Single row per assumption across scenarios - WRONG: No borders - WRONG: Wrong font colors - WRONG: OpEx based on Gross Profit instead of Revenue - TOP 5 ERRORS: row references, comments, sensitivity, scenario references, borders ## Excel Model Structure ### Sheet Architecture: 2 sheets — DCF + WACC ### Formatting Standards - Font Colors: Blue=#0000FF (inputs), Black (formulas), Green=#008000 (cross-sheet) - Fill Colors: Dark blue #1F4E79 (headers), Light blue #D9E1F2 (sub-hea
Complete, populate and fill out 3-statement financial model templates (Income Statement, Balance Sheet, Cash Flow Statement). Use when asked to fill out model templates, complete existing model frameworks, populate financial models with data, complete a partially filled IS/BS/CF framework, or link integrated financial statements within an existing template structure. Triggers include requests to fill in, complete, or populate a 3-statement model template
Audit a spreadsheet for formula accuracy, errors, and common mistakes. Scopes to a selected range, a single sheet, or the entire model, including financial-model integrity checks like BS balance, cash tie-out, and logic sanity. Triggers on "audit this sheet", "check my formulas", "find formula errors", "QA this spreadsheet", "sanity check this", "debug model", "model check", "model won't balance", "something's off in my model", and "model review".
Clean up messy spreadsheet data — trim whitespace, fix inconsistent casing, convert numbers-stored-as-text, standardize dates, remove duplicates, and flag mixed-type columns. Use when data is messy, inconsistent, or needs prep before analysis. Triggers on "clean this data", "clean up this sheet", "normalize this data", "fix formatting", "dedupe", "standardize this column", and "this data is messy".
Build institutional-grade comparable company analyses with operating metrics, valuation multiples, and statistical benchmarking in Excel/spreadsheet format.
This skill should be used when completing LBO (Leveraged Buyout) model templates in Excel for private equity transactions, deal materials, or investment committee presentations. The skill fills in formulas, validates calculations, and ensures professional formatting standards that adapt to any template structure.
Framework for building competitive landscape decks — market positioning, competitor deep-dives, comparative analysis, strategic synthesis. Use when the user asks for a competitive landscape, competitor analysis, peer comparison, market positioning assessment, strategic review, or investment memo deck. Also triggers on "who are the competitors to X", "benchmark X against peers", "build a market map", or any request to systematically evaluate competitive dynamics across an industry.
Updates a presentation with new numbers — quarterly refreshes, earnings updates, comp rolls, rebased market data. Use whenever the user asks to "update the deck with Q4 numbers", "refresh the comps", "roll this forward", "swap in the new earnings", "change all the $485M to $512M", or any request to swap figures across an existing deck without rebuilding it.
Investment banking presentation quality checker. Reviews a pitch deck or client-ready presentation for (1) number consistency across slides, (2) data-narrative alignment, (3) language polish against IB standards, (4) visual and formatting QC. Use whenever the user asks to review, check, QC, proof, or do a final pass on a deck, pitch, or client materials — including requests like "check my numbers", "reconcile figures across slides", "is this client-ready", or "what am I missing before I send this out".