dcf-model

# 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

- **fundamentals MCP**: `get_financial_statements`, `get_financial_ratios`, `get_growth_metrics`, `get_historical_valuation`
- **macro MCP**: `get_treasury_rates`, `get_market_risk_premium`
- **`get_company_overview` tool**: analyst consensus, growth estimates, company profile
- User-provided data and web search/fetch as supplements

## Critical Constraints - Read These First

These constraints apply throughout all DCF model building. Review before starting:

**Sensitivity Tables:**
- Populate ALL 75 cells (3 tables × 25 cells) with full DCF recalculation formulas
- Use openpyxl 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 skills/xlsx/scripts/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

**Execution pattern**: build the DCF as a saved Python script (e.g., `work/<task_name>/build_dcf.py`) rather than inline `ExecuteCode`. Model building is iterative — you will debug formulas, tweak assumptions, and rerun. Writing to a file + running via `Bash` lets you `Edit` specific sections and rerun cheaply; resubmitting the whole openpyxl block inline on every iteration is wasteful.

### Step 1: Data Retrieval and Validation

Fetch data from MCP servers, user provided data, and the web.

**Data Sources:**
- **Financial statements** (income/balance/cash flow): Use fundamentals MCP: `get_financial_statements(symbol, 'all', 'annual', 5)`
- **Ratios and metrics**: Use fundamentals MCP: `get_financial_ratios(symbol)`
- **Growth rates**: Use fundamentals MCP: `get_growth_metrics(symbol)`
- **Risk-free rate / treasury yields**: Use macro MCP: `get_treasury_rates()` -- use the 10Y rate
- **Market risk premium**: Use macro MCP: `get_market_risk_premium()`
- **Analyst growth estimates / consensus**: Use `get_company_overview` tool -- includes analyst consensus and growth estimates
- **Historical valuation data**: Use fundamentals MCP: `get_historical_valuation(symbol)`
- **User-provided data**: Historical financials from their research
- **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 (Ne