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

• 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:

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 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 75%, model may be over-reliant on terminal assumptions
- If 

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:

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:

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:

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:

Row,Content
1,[Company Name] DCF Model
2,Ticker | Date | Year End
4,Case Selector
7,KEY ASSUMPTIONS
26,Assumption headers
27-31,Growth assumptions
...,...

2. Write ALL section dividers and blank rows

3. THEN write formulas using the locked row positions

4. Test formulas immediately after creation

Think of it like construction:

• Good: Pour foundation, then build walls (stable structure)
• Bad: Build walls, then pour foundation (walls collapse)

Excel version:

• Good: Add headers, then write formulas (formulas stable)
• Bad: Write formulas, then add headers (formulas break)

Correct Sensitivity Table Implementation

IMPORTANT: These are NOT Excel's "Data Table" feature. These are simple grids where you write regular formulas using openpyxl. Yes, this means ~75 formulas total (3 tables × 25 cells each), but this is straightforward and required.

Programmatic Population with Formulas:

Each sensitivity table must be fully populated with formulas that recalculate the implied share price for each combination of assumptions. Do not use Excel's Data Table feature (it requires manual intervention and cannot be automated via openpyxl).

Implementation approach - CONCRETE EXAMPLE:

Table Structure (5x5 grid):

WACC vs Terminal Growth,2.0%,2.5%,3.0%,3.5%,4.0%
8.0%,[B88 formula],[C88 formula],[D88 formula],[E88 formula],[F88 formula]
9.0%,[B89 formula],[C89 formula],[D89 formula],[E89 formula],[F89 formula]
...,...,...,...,...,...

Formula Pattern - Cell B88 (WACC=8.0%, Terminal Growth=2.0%):

The formula in B88 should recalculate the implied price using:

• WACC from row header: $A88 (8.0%)
• Terminal Growth from column header: B$87 (2.0%)

Recommended approach: Reference the main DCF calculation but substitute these values.

Example formula structure: =([SUM of PV FCFs using $A88 as discount rate] + [Terminal Value using B$87 as growth rate and $A88 as WACC] - [Net Debt]) / [Shares]

CRITICAL - Write a formula for EVERY cell in the 5x5 grid (25 cells per table, 75 cells total). Use openpyxl to write these formulas programmatically in a loop. Do NOT skip this step or leave placeholder text.

Python implementation pattern:

# Pseudocode for populating sensitivity table
for row_idx, wacc_value in enumerate(wacc_range):
    for col_idx, term_growth_value in enumerate(term_growth_range):
        # Build formula that uses wacc_value and term_growth_value
        formula = f"="
        ws.cell(row=start_row+row_idx, column=start_col+col_idx).value = formula

The sensitivity tables must work immediately when the model is opened, with no manual steps required from the user.

This section contains all the WRONG patterns to avoid when building DCF models.

WRONG: Simplified Sensitivity Table Approximations or Placeholder Text

Don't use linear approximations:

// WRONG - Linear approximation
B97: =B88*(1+(0.096-0.116))    // Assumes linear relationship

// WRONG - Division shortcut
B105: =B88/(1+(E48-0.07))      // Doesn't recalculate full DCF

Don't leave placeholder text:

// WRONG - Placeholder note
"Note: Use Excel Data Table feature (Data → What-If Analysis → Data Table) to populate sensitivity tables."

// WRONG - Empty cells
[leaving cells blank because "this is complex"]

**Don't

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: w95
• Source: w95/awesome-claude-corporate-skills
• License: MIT

Install and usage instructions live in the source repository linked above.