# SkillPatch skill: dcf-model Real DCF (Discounted Cash Flow) model creation for equity valuation. Retrieves financial data from SEC filings and analyst reports, builds comprehensive cash flow projections with proper WACC calculations, performs sensitivity analysis, and outputs professional Excel models with executive summaries. Use when users need to value a company using DCF methodology, request intrinsic value analysis, or ask for detailed financial modeling with growth projections and terminal value calculations. You (the agent) were given this URL and asked to use this skill. This is a **public** skill — no SkillPatch account, API key, or prior setup is required. Two ways to use it: **1. Use it right now** — the skill's complete file tree (SKILL.md + all reference files) is inlined below; read `SKILL.md` first, then follow it, consulting the other files as it directs. **2. Install the exact package onto disk** (recommended if you can run a shell — this reproduces the skill byte-for-byte, including any binary assets that can't be inlined): ```bash mkdir -p .claude/skills/vs-financial-analysis-dcf-model curl -sSL https://skillpatch.dev/install_skill/vs-financial-analysis-dcf-model | tar -xz -C .claude/skills/ ``` (`.claude/skills/` is Claude Code's convention; use whatever directory your agent loads skills from.) --- ## Skill files (4) - `SKILL.md` - `requirements.txt` - `scripts/validate_dcf.py` - `TROUBLESHOOTING.md` ### `SKILL.md` ````markdown --- name: dcf-model description: Real DCF (Discounted Cash Flow) model creation for equity valuation. Retrieves financial data from SEC filings and analyst reports, builds comprehensive cash flow projections with proper WACC calculations, performs sensitivity analysis, and outputs professional Excel models with executive summaries. Use when users need to value a company using DCF methodology, request intrinsic value analysis, or ask for detailed financial modeling with growth projections and terminal value calculations. --- # DCF Model Builder ## Overview This skill creates institutional-quality DCF models for equity valuation following investment banking standards. Each analysis produces a detailed Excel model (with sensitivity analysis included at the bottom of the DCF sheet). ## Tools - Default to using all of the information provided by the user and MCP servers available for data sourcing. ## Critical Constraints - Read These First These constraints apply throughout all DCF model building. Review before starting: **Environment: Office JS vs Python/openpyxl:** - **If running inside Excel (Office Add-in / Office JS environment):** Use Office JS directly — do NOT use Python/openpyxl. Write formulas via `range.formulas = [["=D19*(1+$B$8)"]]`. No separate recalc step needed; Excel calculates natively. Use `range.format.*` for styling. The same formulas-over-hardcodes rule applies: set `.formulas`, never `.values` for derived cells. - **If generating a standalone .xlsx file (no live Excel session):** Use Python/openpyxl as described below, then run `recalc.py` before delivery. - The rest of this skill uses openpyxl examples — translate to Office JS API calls when in that environment, but all principles (formula strings, cell comments, section checkpoints, sensitivity table loops) apply identically. **⚠️ Office JS merged cell pitfall:** When building section headers with merged cells, do NOT call `.merge()` then set `.values` on the merged range — Office JS still reports the range's original dimensions and will throw `InvalidArgument: The number of rows or columns in the input array doesn't match the size or dimensions of the range`. Instead, write the value to the top-left cell alone, then merge and format the full range: ```js // WRONG — throws InvalidArgument: const hdr = ws.getRange("A7:H7"); hdr.merge(); hdr.values = [["MARKET DATA & KEY INPUTS"]]; // 1×1 array vs 1×8 range → fails // CORRECT — value first on single cell, then merge + format the range: ws.getRange("A7").values = [["MARKET DATA & KEY INPUTS"]]; const hdr = ws.getRange("A7:H7"); hdr.merge(); hdr.format.fill.color = "#1F4E79"; hdr.format.font.bold = true; hdr.format.font.color = "#FFFFFF"; ``` This applies to every merged section header in the DCF (market data, scenario blocks, cash flow projection, terminal value, valuation summary, sensitivity tables). **Formulas Over Hardcodes (NON-NEGOTIABLE):** - Every projection, margin, discount factor, PV, and sensitivity cell MUST be a live Excel formula — never a value computed in Python and written as a number - When using openpyxl: `ws["D20"] = "=D19*(1+$B$8)"` is correct; `ws["D20"] = calculated_revenue` is WRONG - The only hardcoded numbers permitted are: (1) raw historical inputs, (2) assumption drivers (growth rates, WACC inputs, terminal g), (3) current market data (share price, debt balance) - If you catch yourself computing something in Python and writing the result — STOP. The model must flex when the user changes an assumption. **Verify Step-by-Step With the User (DO NOT build end-to-end):** - After data retrieval → show the user the raw inputs block (revenue, margins, shares, net debt) and confirm before projecting - After revenue projections → show the projected top line and growth rates, confirm before building margin build - After FCF build → show the full FCF schedule, confirm logic before computing WACC - After WACC → show the calculation and inputs, confirm before discounting - After terminal value + PV → show the equity bridge (EV → equity value → per share), confirm before sensitivity tables - Catch errors at each stage — a wrong margin assumption discovered after sensitivity tables are built means rebuilding everything downstream **Sensitivity Tables:** - **Use an ODD number of rows and columns** (standard: 5×5, sometimes 7×7) — this guarantees a true center cell - **Center cell = base case.** Build the axis values so the middle row header and middle column header exactly equal the model's actual assumptions (e.g., if base WACC = 9.0%, the middle row is 9.0%; if terminal g = 3.0%, the middle column is 3.0%). The center cell's output must therefore equal the model's actual implied share price — this is the sanity check that the table is built correctly. - **Highlight the center cell** with the medium-blue fill (`#BDD7EE`) + bold font so it's immediately visible which cell is the base case. - Populate ALL cells (typically 3 tables × 25 cells = 75) with full DCF recalculation formulas - Use openpyxl loops (or Office JS loops) to write formulas programmatically - NO placeholder text, NO linear approximations, NO manual steps required - Each cell must recalculate full DCF for that assumption combination **Cell Comments:** - Add cell comments AS each hardcoded value is created - Format: "Source: [System/Document], [Date], [Reference], [URL if applicable]" - Every blue input must have a comment before moving to next section - Do not defer to end or write "TODO: add source" **Model Layout Planning:** - Define ALL section row positions BEFORE writing any formulas - Write ALL headers and labels first - Write ALL section dividers and blank rows second - THEN write formulas using the locked row positions - Test formulas immediately after creation **Formula Recalculation:** - Run `python recalc.py model.xlsx 30` before delivery - Fix ALL errors until status is "success" - Zero formula errors required (#REF!, #DIV/0!, #VALUE!, etc.) **Scenario Blocks:** - Create separate blocks for Bear/Base/Bull cases - Show assumptions horizontally across projection years within each block - Use IF formulas: `=IF($B$6=1,[Bear cell],IF($B$6=2,[Base cell],[Bull cell]))` - Verify formulas reference correct scenario block cells ## DCF Process Workflow ### Step 1: Data Retrieval and Validation Fetch data from MCP servers, user provided data, and the web. **Data Sources Priority:** 1. **MCP Servers** (if configured) - Structured financial data from providers like Daloopa 2. **User-Provided Data** - Historical financials from their research 3. **Web Search/Fetch** - Current prices, beta, debt and cash when needed **Validation Checklist:** - Verify net debt vs net cash (critical for valuation) - Confirm diluted shares outstanding (check for recent buybacks/issuances) - Validate historical margins are consistent with business model - Cross-check revenue growth rates with industry benchmarks - Verify tax rate is reasonable (typically 21-28%) ### Step 2: Historical Analysis (3-5 years) Analyze and document: - **Revenue growth trends**: Calculate CAGR, identify drivers - **Margin progression**: Track gross margin, EBIT margin, FCF margin - **Capital intensity**: D&A and CapEx as % of revenue - **Working capital efficiency**: NWC changes as % of revenue growth - **Return metrics**: ROIC, ROE trends Create summary tables showing: ``` Historical Metrics (LTM): Revenue: $X million Revenue growth: X% CAGR Gross margin: X% EBIT margin: X% D&A % of revenue: X% CapEx % of revenue: X% FCF margin: X% ``` ### Step 3: Build Revenue Projections **Methodology:** 1. Start with latest actual revenue (LTM or most recent fiscal year) 2. Apply growth rates for each projection year 3. Show both dollar amounts AND calculated growth % **Growth Rate Framework:** - Year 1-2: Higher growth reflecting near-term visibility - Year 3-4: Gradual moderation toward industry average - Year 5+: Approaching terminal growth rate **Formula structure:** - Revenue(Year N) = Revenue(Year N-1) × (1 + Growth Rate) - Growth %(Year N) = Revenue(Year N) / Revenue(Year N-1) - 1 **Three-scenario approach:** ``` Bear Case: Conservative growth (e.g., 8-12%) Base Case: Most likely scenario (e.g., 12-16%) Bull Case: Optimistic growth (e.g., 16-20%) ``` ### Step 4: Operating Expense Modeling **Fixed/Variable Cost Analysis:** Operating expenses should model realistic operating leverage: - **Sales & Marketing**: Typically 15-40% of revenue depending on business model - **Research & Development**: Typically 10-30% for technology companies - **General & Administrative**: Typically 8-15% of revenue, shows leverage as company scales **Key principles:** - ALL percentages based on REVENUE, not gross profit - Model operating leverage: % should decline as revenue scales - Maintain separate line items for S&M, R&D, G&A - Calculate EBIT = Gross Profit - Total OpEx **Margin expansion framework:** ``` Current State → Target State (Year 5) Gross Margin: X% → Y% (justify based on scale, efficiency) EBIT Margin: X% → Y% (result of revenue growth + opex leverage) ``` ### Step 5: Free Cash Flow Calculation **Build FCF in proper sequence:** ``` EBIT (-) Taxes (EBIT × Tax Rate) = NOPAT (Net Operating Profit After Tax) (+) D&A (non-cash expense, % of revenue) (-) CapEx (% of revenue, typically 4-8%) (-) Δ NWC (change in working capital) = Unlevered Free Cash Flow ``` **Working Capital Modeling:** - Calculate as % of revenue change (delta revenue) - Typical range: -2% to +2% of revenue change - Negative number = source of cash (working capital release) - Positive number = use of cash (working capital build) **Maintenance vs Growth CapEx:** - Maintenance CapEx: Sustains current operations (~2-3% revenue) - Growth CapEx: Supports expansion (additional 2-5% revenue) - Total CapEx should align with company's growth strategy ### Step 6: Cost of Capital (WACC) Research **CAPM Methodology for Cost of Equity:** ``` Cost of Equity = Risk-Free Rate + Beta × Equity Risk Premium Where: - Risk-Free Rate = Current 10-Year Treasury Yield - Beta = 5-year monthly stock beta vs market index - Equity Risk Premium = 5.0-6.0% (market standard) ``` **Cost of Debt Calculation:** ``` After-Tax Cost of Debt = Pre-Tax Cost of Debt × (1 - Tax Rate) Determine Pre-Tax Cost of Debt from: - Credit rating (if available) - Current yield on company bonds - Interest expense / Total Debt from financials ``` **Capital Structure Weights:** ``` Market Value Equity = Current Stock Price × Shares Outstanding Net Debt = Total Debt - Cash & Equivalents Enterprise Value = Market Cap + Net Debt Equity Weight = Market Cap / Enterprise Value Debt Weight = Net Debt / Enterprise Value WACC = (Cost of Equity × Equity Weight) + (After-Tax Cost of Debt × Debt Weight) ``` **Special Cases:** - **Net Cash Position**: If Cash > Debt, Net Debt is NEGATIVE - Debt Weight may be negative - WACC calculation adjusts accordingly - **No Debt**: WACC = Cost of Equity **Typical WACC Ranges:** - Large Cap, Stable: 7-9% - Growth Companies: 9-12% - High Growth/Risk: 12-15% ### Step 7: Discount Rate Application (5-10 Year Forecast) **Mid-Year Convention:** - Cash flows assumed to occur mid-year - Discount Period: 0.5, 1.5, 2.5, 3.5, 4.5, etc. - Discount Factor = 1 / (1 + WACC)^Period **Present Value Calculation:** ``` For each projection year: PV of FCF = Unlevered FCF × Discount Factor Example (Year 1): FCF = $1,000 WACC = 10% Period = 0.5 Discount Factor = 1 / (1.10)^0.5 = 0.9535 PV = $1,000 × 0.9535 = $954 ``` **Projection Period Selection:** - **5 years**: Standard for most analyses - **7-10 years**: High growth companies with longer runway - **3 years**: Mature, stable businesses ### Step 8: Terminal Value Calculation **Perpetuity Growth Method (Preferred):** ``` Terminal FCF = Final Year FCF × (1 + Terminal Growth Rate) Terminal Value = Terminal FCF / (WACC - Terminal Growth Rate) Critical Constraint: Terminal Growth < WACC (otherwise infinite value) ``` **Terminal Growth Rate Selection:** - Conservative: 2.0-2.5% (GDP growth rate) - Moderate: 2.5-3.5% - Aggressive: 3.5-5.0% (only for market leaders) **Do not exceed**: Risk-free rate or long-term GDP growth **Exit Multiple Method (Alternative):** ``` Terminal Value = Final Year EBITDA × Exit Multiple Where Exit Multiple comes from: - Industry comparable trading multiples - Precedent transaction multiples - Typical range: 8-15x EBITDA ``` **Present Value of Terminal Value:** ``` PV of Terminal Value = Terminal Value / (1 + WACC)^Final Period Where Final Period accounts for timing: 5-year model with mid-year convention: Period = 4.5 ``` **Terminal Value Sanity Check:** - Should represent 50-70% of Enterprise Value - If >75%, model may be over-reliant on terminal assumptions - If <40%, check if terminal assumptions are too conservative ### Step 9: Enterprise to Equity Value Bridge **Valuation Summary Structure:** ``` (+) Sum of PV of Projected FCFs = $X million (+) PV of Terminal Value = $Y million = Enterprise Value = $Z million (-) Net Debt [or + Net Cash if negative] = $A million = Equity Value = $B million ÷ Diluted Shares Outstanding = C million shares = Implied Price per Share = $XX.XX Current Stock Price = $YY.YY Implied Return = (Implied Price / Current Price) - 1 = XX% ``` **Critical Adjustments:** - **Net Debt = Total Debt - Cash & Equivalents** - If positive: Subtract from EV (reduces equity value) - If negative (Net Cash): Add to EV (increases equity value) - **Use Diluted Shares**: Includes options, RSUs, convertible securities - **Other adjustments** (if applicable): - Minority interests - Pension liabilities - Operating lease obligations **Valuation Output Format:** ```csv Valuation Component,Amount ($M) PV Explicit FCFs,X.X PV Terminal Value,Y.Y Enterprise Value,Z.Z (-) Net Debt,A.A Equity Value,B.B ,, Shares Outstanding (M),C.C Implied Price per Share,$XX.XX Current Share Price,$YY.YY Implied Upside/(Downside),+XX% ``` ### Step 10: Sensitivity Analysis Build **three sensitivity tables** at the bottom of the DCF sheet showing how valuation changes with different assumptions: 1. **WACC vs Terminal Growth** - Shows enterprise value sensitivity to discount rate and perpetuity growth 2. **Revenue Growth vs EBIT Margin** - Shows impact of top-line growth and operating leverage 3. **Beta vs Risk-Free Rate** - Shows sensitivity to cost of equity components **Implementation**: These are simple 2D grids (NOT Excel's "Data Table" feature) with formulas in each cell. Each cell must contain a full DCF recalculation for that specific assumption combination. See Critical Constraints section for detailed requirements on populating all 75 cells programmatically using openpyxl. This section contains all the CORRECT patterns to follow when building DCF models. ### Scenario Block Selection Pattern - Follow This Approach **Assumptions are organized in separate blocks for each scenario:** **CRITICAL STRUCTURE - Three rows per section header:** ```csv BEAR CASE ASSUMPTIONS (section header, merge cells across) Assumption,FY1,FY2,FY3,FY4,FY5 Revenue Growth (%),12%,10%,9%,8%,7% EBIT Margin (%),45%,44%,43%,42%,41% BASE CASE ASSUMPTIONS (section header, merge cells across) Assumption,FY1,FY2,FY3,FY4,FY5 Revenue Growth (%),16%,14%,12%,10%,9% EBIT Margin (%),48%,49%,50%,51%,52% BULL CASE ASSUMPTIONS (section header, merge cells across) Assumption,FY1,FY2,FY3,FY4,FY5 Revenue Growth (%),20%,18%,15%,13%,11% EBIT Margin (%),50%,51%,52%,53%,54% ``` **Each scenario block MUST have a column header row** showing the projection years (FY2025E, FY2026E, etc.) immediately below the section title. Without this, users cannot tell which assumption value corresponds to which year. **How to reference assumptions - Create a consolidation column:** 1. Case selector cell (e.g., B6) contains 1=Bear, 2=Base, or 3=Bull 2. Create a consolidation column with INDEX or OFFSET formulas to pull from the correct scenario block 3. Projection formulas reference the consolidation column (clean cell references) 4. Each scenario block contains full set of DCF assumptions across projection years **Recommended consolidation column pattern (using INDEX):** `=INDEX(B10:D10, 1, $B$6)` **NOT this - scattered IF statements throughout:** `=IF($B$6=1,[Bear block cell],IF($B$6=2,[Base block cell],[Bull block cell]))` The consolidation column approach centralizes logic and makes the model easier to audit. ### Correct Revenue Projection Pattern **Create a consolidation column with INDEX formulas, then reference it in projections:** **Step 1 - Consolidation column for FY1 growth:** `=INDEX([Bear FY1 growth]:[Bull FY1 growth], 1, $B$6)` **Step 2 - Revenue projection references the consolidation column:** `Revenue Year 1: =D29*(1+$E$10)` Where: - D29 = Prior year revenue - $E$10 = Consolidation column cell for FY1 growth (contains INDEX formula) - $B$6 = Case selector (1=Bear, 2=Base, 3=Bull) **This approach is cleaner than embedding IF statements in every projection formula** and makes it much easier to audit which scenario assumptions are being used. ### Correct FCF Formula Pattern **Use consolidation columns with INDEX formulas, then reference them in FCF calculations:** **Consolidation column approach:** ```csv Item,Formula,Reference D&A,=E29*$E$21,$E$21 = consolidation column for D&A % CapEx,=E29*$E$22,$E$22 = consolidation column for CapEx % Δ NWC,=(E29-D29)*$E$23,$E$23 = consolidation column for NWC % Unlevered FCF,=E57+E58-E60-E62,E57=NOPAT E58=D&A E60=CapEx E62=Δ NWC ``` **Each consolidation column cell contains an INDEX formula** that pulls from the appropriate scenario block based on case selector. This keeps projection formulas clean and auditable. Before writing formulas, confirm scenario block row locations and set up consolidation columns. ### Correct Cell Comment Format **Every hardcoded value needs this format:** "Source: [System/Document], [Date], [Reference], [URL if applicable]" **Examples:** ```csv Item,Source Comment Stock price,Source: Market data script 2025-10-12 Close price Shares outstanding,Source: 10-K FY2024 Page 45 Note 12 Historical revenue,Source: 10-K FY2024 Page 32 Consolidated Statements Beta,Source: Market data script 2025-10-12 5-year monthly beta Consensus estimates,Source: Management guidance Q3 2024 earnings call ``` ### Correct Assumption Table Structure **CRITICAL: Each scenario block requires THREE structural elements:** 1. **Section header row** (merged cells): e.g., "BEAR CASE ASSUMPTIONS" 2. **Column header row** showing years - THIS IS REQUIRED, DO NOT SKIP 3. **Data rows** with assumption values **Structure:** ```csv BEAR CASE ASSUMPTIONS (section header - merge across columns A:G) Assumption,FY1,FY2,FY3,FY4,FY5 Revenue Growth (%),X%,X%,X%,X%,X% EBIT Margin (%),X%,X%,X%,X%,X% Terminal Growth,X%,,,, WACC,X%,,,, BASE CASE ASSUMPTIONS (section header - merge across columns A:G) Assumption,FY1,FY2,FY3,FY4,FY5 Revenue Growth (%),X%,X%,X%,X%,X% EBIT Margin (%),X%,X%,X%,X%,X% Terminal Growth,X%,,,, WACC,X%,,,, BULL CASE ASSUMPTIONS (section header - merge across columns A:G) Assumption,FY1,FY2,FY3,FY4,FY5 Revenue Growth (%),X%,X%,X%,X%,X% EBIT Margin (%),X%,X%,X%,X%,X% Terminal Growth,X%,,,, WACC,X%,,,, ``` **WITHOUT the column header row showing projection years (FY2025E, FY2026E, etc.), users cannot tell which assumption value corresponds to which year. This row is MANDATORY.** **Then create a consolidation column** (typically the next column to the right) that uses INDEX formulas to pull from the selected scenario block based on the case selector. This consolidation column is what your projection formulas reference. ### Correct Row Planning Process **1. Write ALL headers and labels FIRST:** ```csv Row,Content 1,[Company Name] DCF Model 2,Ticker | Date | Year End 4,Case Selector 7,KEY ASSUMPTIONS 26,Assumption hea ... ```` ### `requirements.txt` ``` # DCF Model Builder - Python Dependencies # Excel file handling openpyxl>=3.0.0 # HTTP requests requests>=2.28.0 ``` ### `scripts/validate_dcf.py` ``` #!/usr/bin/env python3 """ DCF Model Validation Script Validates Excel DCF models for formula errors and common DCF mistakes """ import sys import json from pathlib import Path from typing import Optional class DCFModelValidator: """Validates DCF models for errors and quality issues""" def __init__(self, excel_path: str): try: import openpyxl except ImportError: raise ImportError("openpyxl not installed. Run: pip install openpyxl") self.excel_path = excel_path self.openpyxl = openpyxl if not Path(excel_path).exists(): raise FileNotFoundError(f"File not found: {excel_path}") self.workbook_formulas = openpyxl.load_workbook(excel_path, data_only=False) self.workbook_values = openpyxl.load_workbook(excel_path, data_only=True) self.errors = [] self.warnings = [] self.info = [] def validate_all(self) -> dict: """ Run all validation checks Returns: Dict with validation results """ from datetime import datetime self.check_sheet_structure() self.check_formula_errors() self.check_dcf_logic() results = { 'file': self.excel_path, 'validation_date': datetime.now().isoformat(), 'status': 'PASS' if len(self.errors) == 0 else 'FAIL', 'error_count': len(self.errors), 'warning_count': len(self.warnings), 'errors': self.errors, 'warnings': self.warnings, 'info': self.info } return results def check_sheet_structure(self): """Verify required sheets exist""" required_sheets = ['DCF', 'WACC', 'Sensitivity'] sheet_names = self.workbook_values.sheetnames for sheet in required_sheets: if sheet not in sheet_names: self.warnings.append(f"Recommended sheet missing: {sheet}") else: self.info.append(f"Found sheet: {sheet}") def check_formula_errors(self): """Check for Excel formula errors in all sheets""" excel_errors = ['#VALUE!', '#DIV/0!', '#REF!', '#NAME?', '#NULL!', '#NUM!', '#N/A'] error_details = {err: [] for err in excel_errors} total_errors = 0 total_formulas = 0 for sheet_name in self.workbook_values.sheetnames: ws_values = self.workbook_values[sheet_name] ws_formulas = self.workbook_formulas[sheet_name] for row in ws_values.iter_rows(): for cell in row: formula_cell = ws_formulas[cell.coordinate] # Count formulas if formula_cell.value and isinstance(formula_cell.value, str) and formula_cell.value.startswith('='): total_formulas += 1 # Check for errors if cell.value is not None and isinstance(cell.value, str): for err in excel_errors: if err in cell.value: location = f"{sheet_name}!{cell.coordinate}" error_details[err].append(location) total_errors += 1 self.errors.append(f"{err} at {location}") break # Add summary info self.info.append(f"Total formulas: {total_formulas}") if total_errors == 0: self.info.append("✓ No formula errors found") else: self.errors.append(f"Total formula errors: {total_errors}") return error_details, total_errors def check_dcf_logic(self): """Validate DCF-specific logic and calculations""" self._check_terminal_growth_vs_wacc() self._check_wacc_range() self._check_terminal_value_proportion() def _check_terminal_growth_vs_wacc(self): """Critical check: Terminal growth must be less than WACC""" try: dcf_sheet = self.workbook_values['DCF'] terminal_growth = None wacc = None # Search for terminal growth and WACC values for row in dcf_sheet.iter_rows(max_row=100, max_col=20): for cell in row: if cell.value and isinstance(cell.value, str): cell_str = cell.value.lower() if 'terminal' in cell_str and 'growth' in cell_str: # Look for value in adjacent cells for offset in range(1, 5): adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value if isinstance(adjacent, (int, float)) and 0 < adjacent < 1: terminal_growth = adjacent break if 'wacc' in cell_str and wacc is None: for offset in range(1, 5): adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value if isinstance(adjacent, (int, float)) and 0 < adjacent < 1: wacc = adjacent break if terminal_growth is not None and wacc is not None: if terminal_growth >= wacc: self.errors.append( f"CRITICAL: Terminal growth ({terminal_growth:.2%}) >= WACC ({wacc:.2%}). " "This creates infinite value and is mathematically invalid." ) else: self.info.append( f"✓ Terminal growth ({terminal_growth:.2%}) < WACC ({wacc:.2%})" ) else: self.warnings.append("Could not locate terminal growth and WACC values") except KeyError: self.warnings.append("DCF sheet not found") except Exception as e: self.warnings.append(f"Could not validate terminal growth vs WACC: {str(e)}") def _check_wacc_range(self): """Check if WACC is in reasonable range""" try: wacc_sheet = self.workbook_values.get('WACC') or self.workbook_values['DCF'] wacc = None for row in wacc_sheet.iter_rows(max_row=100, max_col=20): for cell in row: if cell.value and isinstance(cell.value, str): if 'wacc' in cell.value.lower(): for offset in range(1, 5): adjacent = wacc_sheet.cell(cell.row, cell.column + offset).value if isinstance(adjacent, (int, float)) and 0 < adjacent < 1: wacc = adjacent break if wacc is not None: if wacc < 0.05 or wacc > 0.20: self.warnings.append( f"WACC ({wacc:.2%}) is outside typical range (5%-20%). Verify calculation." ) else: self.info.append(f"✓ WACC ({wacc:.2%}) in reasonable range") else: self.warnings.append("Could not locate WACC value") except Exception as e: self.warnings.append(f"Could not validate WACC range: {str(e)}") def _check_terminal_value_proportion(self): """Check if terminal value is reasonable proportion of enterprise value""" try: dcf_sheet = self.workbook_values['DCF'] terminal_value = None enterprise_value = None for row in dcf_sheet.iter_rows(max_row=200, max_col=20): for cell in row: if cell.value and isinstance(cell.value, str): cell_str = cell.value.lower() if 'terminal' in cell_str and 'value' in cell_str and 'pv' in cell_str: for offset in range(1, 5): adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value if isinstance(adjacent, (int, float)) and adjacent > 0: terminal_value = adjacent break if 'enterprise' in cell_str and 'value' in cell_str: for offset in range(1, 5): adjacent = dcf_sheet.cell(cell.row, cell.column + offset).value if isinstance(adjacent, (int, float)) and adjacent > 0: enterprise_value = adjacent break if terminal_value is not None and enterprise_value is not None and enterprise_value > 0: proportion = terminal_value / enterprise_value if proportion > 0.80: self.warnings.append( f"Terminal value is {proportion:.1%} of EV (typically should be 50-70%). " "Model may be over-reliant on terminal assumptions." ) elif proportion < 0.40: self.warnings.append( f"Terminal value is {proportion:.1%} of EV (typically should be 50-70%). " "Check if terminal assumptions are too conservative." ) else: self.info.append(f"✓ Terminal value is {proportion:.1%} of EV") else: self.warnings.append("Could not locate terminal value and enterprise value") except Exception as e: self.warnings.append(f"Could not validate terminal value proportion: {str(e)}") def validate_dcf_model(excel_path: str) -> dict: """ Validate a DCF model Excel file Args: excel_path: Path to Excel DCF model Returns: Dict with validation results """ validator = DCFModelValidator(excel_path) return validator.validate_all() def main(): """Command-line interface""" if len(sys.argv) < 2: print("Usage: python validate_dcf.py [output.json]") print("\nValidates DCF model for:") print(" - Formula errors (#REF!, #DIV/0!, etc.)") print(" - Terminal growth < WACC (critical)") print(" - WACC in reasonable range (5-20%)") print(" - Terminal value proportion of EV (40-80%)") print("\nReturns JSON with errors, warnings, and info") print("\nExample: python validate_dcf.py model.xlsx") print("Example: python validate_dcf.py model.xlsx results.json") sys.exit(1) excel_file = sys.argv[1] output_file = sys.argv[2] if len(sys.argv) > 2 else None try: results = validate_dcf_model(excel_file) # Print results print(json.dumps(results, indent=2)) # Save to file if requested if output_file: with open(output_file, 'w') as f: json.dump(results, f, indent=2) # Exit with error code if validation failed sys.exit(0 if results['status'] == 'PASS' else 1) except Exception as e: error_result = { 'file': excel_file, 'status': 'ERROR', 'error': str(e) } print(json.dumps(error_result, indent=2)) sys.exit(1) if __name__ == "__main__": main() ``` ### `TROUBLESHOOTING.md` ```markdown # DCF Model Troubleshooting Guide **When to read this file:** If recalc.py shows errors OR valuation results seem unreasonable OR case selector not working properly. ## Model Returns Error Values ### #REF! Errors - Usually caused by formulas referencing wrong rows after headers were inserted - Solution: Rebuild with correct row references, or start over following layout planning - Prevention: Define all row positions BEFORE writing formulas ### #DIV/0! Errors - Division by zero or empty cells - Solution: Add IF statements to handle zeros: `=IF([Divisor]=0,0,[Numerator]/[Divisor])` ### #VALUE! Errors - Wrong data type in calculation (text instead of number) - Solution: Verify all inputs are formatted as numbers ## Valuation Seems Unreasonable ### Implied price far too high - Check terminal value isn't >80% of EV - Verify terminal growth < WACC - Review if growth assumptions are realistic - Consider if margins are too optimistic ### Implied price far too low - Verify net debt vs net cash is correct - Check if WACC is too high - Review if projections are too conservative - Consider if terminal growth is too low ## Case Selector Not Working ### Consolidation column not updating when switching scenarios - Verify case selector cell contains 1, 2, or 3 - Check INDEX/OFFSET formulas reference correct row range and selector cell - Ensure absolute references ($B$6) are used for selector - Test by manually changing the selector cell and verifying projection values update ```