Neura Market DCF Equity Valuation Model Reference

Overview

This document describes the institutional-quality Discounted Cash Flow (DCF) equity valuation model built using headless openpyxl and the excel-author skill conventions. The model produces a live, formula-driven Excel workbook with sensitivity analysis, suitable for public company equity valuation. All projections, margins, discount factors, present values, and sensitivity cells are live Excel formulas — never values computed in Python and written as numbers.

Purpose

Build an institutional-quality DCF equity-valuation Excel model with sensitivity analysis, using headless openpyxl and the excel-author skill conventions.

When to Use

Equity valuation of a public company requiring a live, formula-driven Excel model.
Scenario analysis (Bear/Base/Bull) with full sensitivity tables.
Deliverable that must be immediately usable and auditable without manual steps.

Capabilities

Creates a two-sheet .xlsx file: DCF (main model + sensitivity) and WACC (cost of capital).
Uses live Excel formulas for all projections, margins, discount factors, PVs, and sensitivity cells — no Python-computed hardcodes.
Implements three-case scenario blocks (Bear/Base/Bull) with a consolidation column using INDEX formulas.
Builds three 5×5 sensitivity tables (WACC vs Terminal Growth, Revenue Growth vs EBIT Margin, Beta vs Risk-Free Rate) with full DCF recalculation formulas in every cell.
Applies professional formatting: blue font for inputs, black for formulas, green for sheet links; blue/grey fill palette; thick/medium/thin borders.
Adds cell comments to every hardcoded input as it is created.
Runs recalc.py before delivery to ensure zero formula errors.

Prerequisites

Headless openpyxl environment (excel-author skill conventions).
Access to MCP servers for structured financial data (preferred), or web_search/web_extract capabilities, or user-provided data.
recalc.py script from excel-author skill for formula recalculation.
Company identifier (ticker or name).
Growth assumptions or instruction to use consensus.
Optional: projection period, scenario cases, terminal growth rate, specific WACC inputs.

Parameters

Parameter	Meaning	Required
Company identifier	Ticker symbol or company name for data retrieval	true
Growth assumptions	Revenue growth rates for projection period (or 'use consensus')	true
Projection period	Number of forecast years (default: 5)	false
Scenario cases	Bear/Base/Bull growth and margin assumptions	false
Terminal growth rate	Perpetuity growth rate (default: 2.5-3.0%)	false
Specific WACC inputs	If not using CAPM, provide direct WACC or its components	false

DCF Model Build Procedure

Step 1: Data Retrieval and Validation

Fetch data from MCP servers (preferred), user-provided data, or web_search/web_extract. Validate net debt vs net cash, diluted shares, historical margins, tax rate (21-28%). Use native-mcp for structured financial data. If data is unavailable, flag as [UNSOURCED] and surface to the user.

Step 2: Historical Analysis (3-5 years)

Analyze revenue growth CAGR, margin progression, capital intensity (D&A, CapEx as % of revenue), working capital efficiency, return metrics. Create summary tables. Present historical metrics in this format:

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

Start with latest actual revenue, apply growth rates for each projection year. Use three-scenario approach (Bear/Base/Bull). Show dollar amounts and calculated growth %. Scenario definitions:

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%)

The scenario definitions table uses the following format for growth rates:

Assumption,Bear,Base,Bull
Revenue Growth FY1,10%,13%,16%
Revenue Growth FY2,9%,12%,15%

Step 4: Operating Expense Modeling

Model S&M, R&D, G&A as % of revenue (NOT gross profit). Show operating leverage (declining % as revenue scales). Calculate EBIT = Gross Profit - Total OpEx. Track margin progression:

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 in 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

Model working capital as % of revenue change.

Step 6: Cost of Capital (WACC) Research

Use CAPM 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)

Calculate after-tax cost of debt:

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

Determine capital structure weights (market values):

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)

Handle net cash / no-debt special cases. 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)

Use mid-year convention (periods 0.5, 1.5, 2.5...). PV of FCF = Unlevered FCF × Discount Factor.

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

Step 8: Terminal Value Calculation

Prefer perpetuity growth method:

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)

Alternative exit multiple method:

Terminal Value = Final Year EBITDA × Exit Multiple

Where Exit Multiple comes from:
- Industry comparable trading multiples
- Precedent transaction multiples
- Typical range: 8-15x EBITDA

Sanity check: TV should be 50-70% of EV. If TV > 75% of EV, model may be over-reliant on terminal assumptions.

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 growth rate ranges: conservative 2.0-2.5%, moderate 2.5-3.5%, aggressive 3.5-5.0% (do not exceed risk-free rate or long-term GDP growth).

Step 9: Enterprise to Equity Value Bridge

Sum PV of FCFs + PV of TV = EV. Subtract net debt (or add net cash). Divide by diluted shares for implied price per share. Show current price and implied return.

(+) 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%

Present the valuation bridge in a table:

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 5×5 tables at bottom of DCF sheet. Each cell contains a full DCF recalculation formula. Center cell = base case, highlighted with #BDD7EE fill and bold font. Use openpyxl loops to write all 75 formulas programmatically.

Scenario Block Structure

Use three separate blocks (BEAR/BASE/BULL), each with section header (merged cells), column header row showing projection years (FY2025E, FY2026E, etc.), and data rows for Revenue Growth %, EBIT Margin %, Terminal Growth, WACC. Consolidation column uses INDEX formula to pull from selected scenario based on case selector cell.

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%

Extended scenario block with terminal growth and WACC:

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%,,,,

Consolidation Column Pattern

Use INDEX formulas for scenario selection — NOT scattered IF statements. The correct pattern:

=INDEX([Bear FY1 growth]:[Bull FY1 growth], 1, $B$6)

Revenue projection formula:

=E29*(1+$E$10)

Where $E$10 is the consolidation column cell. NOT:

=E29*(1+IF($B$6=1,$B$10,IF($B$6=2,$C$10,$D$10)))

The B10:D10 range contains the three scenario growth rates. The case selector cell $B$6 determines which column to pull from (1=Bear, 2=Base, 3=Bull).

Free Cash Flow Formula Pattern

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

Cell Comments

Add cell comments AS each hardcoded value is created — do not defer to end. Comment format:

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 cell comment format: 'Source: [System/Document], [Date], [Reference], [URL if applicable]'. Example: 'Source: 10-K FY2024 Page 32 Consolidated Statements'.

Sensitivity Table Structure

Three 5×5 tables: WACC vs Terminal Growth, Revenue Growth vs EBIT Margin, Beta vs Risk-Free Rate. Each cell contains a full DCF recalculation formula. Center cell = base case, highlighted with #BDD7EE fill and bold font.

WACC vs Terminal Growth table structure:

WACC vs Terminal Growth,  2.0%,  2.5%,  3.0%,  3.5%,  4.0%
              8.0%,       [fml], [fml], [fml], [fml], [fml]
              8.5%,       [fml], [fml], [fml], [fml], [fml]
              9.0%,       [fml], [fml], [★  ], [fml], [fml]   ← middle row = base WACC
              9.5%,       [fml], [fml], [fml], [fml], [fml]
             10.0%,       [fml], [fml], [fml], [fml], [fml]
                                   ↑
                          middle col = base terminal g

Populate ALL sensitivity cells (3 tables × 25 cells = 75) with full DCF recalculation formulas — NO placeholder text, NO linear approximations, NO manual steps.

Pseudocode for populating sensitivity table:

# 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"=<DCF recalc using {wacc_value} and {term_growth_value}>"
        ws.cell(row=start_row+row_idx, column=start_col+col_idx).value = formula

Sensitivity tables use simple 2D grids with regular formulas — NOT Excel's Data Table feature. Sensitivity tables are placed at the bottom of the DCF sheet, not on a separate sheet.

Incorrect Sensitivity Table Approaches

These approaches are WRONG and must never be used:

// 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

// 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"]

Axis Values for Sensitivity Tables

Use the pattern axis_values = [base - 2*step, base - step, base, base + step, base + 2*step] to generate the 5 values for each axis. For WACC vs Terminal Growth: WACC values (8.0%, 8.5%, 9.0%, 9.5%, 10.0%) as row headers and terminal growth values (2.0%, 2.5%, 3.0%, 3.5%, 4.0%) as column headers. Center cell (WACC=9.0%, g=3.0%) equals base case implied share price.

Row Layout Planning

Define ALL section row positions BEFORE writing any formulas. Write ALL headers and labels first, then section dividers and blank rows, THEN write formulas using locked row positions.

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
...,...

Top rows layout:

Row,Content
1,[Company Name] DCF Model
2,Ticker: [XXX] | Date: [Date] | Year End: [FYE]
3,Blank
4,Case Selector Cell (1=Bear 2=Base 3=Bull)
5,Case Name Display (formula: =IF([Selector]=1"Bear"IF([Selector]=2"Base""Bull")))

Market data block:

Item,Value
Current Stock Price,$XX.XX
Shares Outstanding (M),XX.X
Market Cap ($M),[Formula]
Net Debt ($M),XXX [or Net Cash if negative]

Income Statement Block

Income Statement ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
Revenue,XXX,XXX,XXX,XXX,[=E29*(1+$E$10)],[=F29*(1+$E$11)],[=G29*(1+$E$12)]
  % growth,XX%,XX%,XX%,XX%,[=E29/D29-1],[=F29/E29-1],[=G29/F29-1]
,,,,,,
Gross Profit,XXX,XXX,XXX,XXX,[=E29*E33],[=F29*F33],[=G29*G33]
  % margin,XX%,XX%,XX%,XX%,[=E33/E29],[=F33/F29],[=G33/G29]
,,,,,,
Operating Expenses:,,,,,,,
  S&M,XXX,XXX,XXX,XXX,[=E29*0.15],[=F29*0.14],[=G29*0.13]
  R&D,XXX,XXX,XXX,XXX,[=E29*0.12],[=F29*0.11],[=G29*0.10]
  G&A,XXX,XXX,XXX,XXX,[=E29*0.08],[=F29*0.07],[=G29*0.07]
  Total OpEx,XXX,XXX,XXX,XXX,[=E36+E37+E38],[=F36+F37+F38],[=G36+G37+G38]
,,,,,,
EBIT,XXX,XXX,XXX,XXX,[=E33-E39],[=F33-F39],[=G33-G39]
  % margin,XX%,XX%,XX%,XX%,[=E41/E29],[=F41/F29],[=G41/G29]
,,,,,,
Taxes,(XX),(XX),(XX),(XX),[=E41*$E$24],[=F41*$E$24],[=G41*$E$24]
  Tax rate,XX%,XX%,XX%,XX%,[=E43/E41],[=F43/E41],[=G43/G41]
,,,,,,
NOPAT,XXX,XXX,XXX,XXX,[=E45],[=F45],[=G45]

Cash Flow Block

Cash Flow ($M),2020A,2021A,2022A,2023A,2024E,2025E,2026E
NOPAT,XXX,XXX,XXX,XXX,[=E45],[=F45],[=G45]
(+) D&A,XXX,XXX,XXX,XXX,[=E29*$E$21],[=F29*$E$21],[=G29*$E$21]
    % of Rev,XX%,XX%,XX%,XX%,[=E58/E29],[=F58/F29],[=G58/G29]
(-) CapEx,(XX),(XX),(XX),(XX),[=E29*$E$22],[=F29*$E$22],[=G29*$E$22]
    % of Rev,XX%,XX%,XX%,XX%,[=E60/E29],[=F60/F29],[=G60/G29]
(-) Δ NWC,(XX),(XX),(XX),(XX),[=(E29-D29)*$E$23],[=(F29-E29)*$E$23],[=(G29-F29)*$E$23]
    % of Δ Rev,XX%,XX%,XX%,XX%,[=E62/(E29-D29)],[=F62/(F29-E29)],[=G62/(G29-F29)]
,,,,,,
Unlevered FCF,XXX,XXX,XXX,XXX,[=E57+E58-E60-E62],[=F57+F58-F60-F62],[=G57+G58-G60-G62]

DCF Valuation Block

DCF Valuation,2024E,2025E,2026E,2027E,2028E,Terminal
Unlevered FCF ($M),XXX,XXX,XXX,XXX,XXX,
Period,0.5,1.5,2.5,3.5,4.5,
Discount Factor,0.XX,0.XX,0.XX,0.XX,0.XX,
PV of FCF ($M),XXX,XXX,XXX,XXX,XXX,
,,,,,,
Terminal FCF ($M),,,,,,,XXX
Terminal Value ($M),,,,,,,XXX
PV Terminal Value ($M),,,,,,,XXX
,,,,,,
Valuation Summary ($M),,,,,,
Sum of PV FCFs,XXX,,,,,
PV Terminal Value,XXX,,,,,
Enterprise Value,XXX,,,,,
(-) Net Debt,(XX),,,,,
Equity Value,XXX,,,,,
,,,,,,
Shares Outstanding (M),XX.X,,,,,
IMPLIED PRICE PER SHARE,$XX.XX,,,,,
Current Stock Price,$XX.XX,,,,,
Implied Upside/(Downside),XX%,,,,,

WACC Sheet Structure

COST OF EQUITY CALCULATION,,
Risk-Free Rate (10Y Treasury),X.XX%,[Yellow input]
Beta (5Y monthly),X.XX,[Yellow input]
Equity Risk Premium,X.XX%,[Yellow input]
Cost of Equity,X.XX%,[Calculated blue]
,,
COST OF DEBT CALCULATION,,
Credit Rating,AA-,[Yellow input]
Pre-Tax Cost of Debt,X.XX%,[Yellow input]
Tax Rate,XX.X%,[Link to DCF sheet]
After-Tax Cost of Debt,X.XX%,[Calculated blue]
,,
CAPITAL STRUCTURE,,
Current Stock Price,$XX.XX,[Link to DCF]
Shares Outstanding (M),XX.X,[Link to DCF]
Market Capitalization ($M),"X,XXX",[Calculated]
,,
Total Debt ($M),XXX,[Yellow input]
Cash & Equivalents ($M),XXX,[Yellow input]
Net Debt ($M),XXX,[Calculated]
,,
Enterprise Value ($M),"X,XXX",[Calculated]
,,
WACC CALCULATION,Weight,Cost,Contribution
Equity,XX.X%,X.X%,X.XX%
Debt,XX.X%,X.X%,X.XX%
,,
WEIGHTED AVERAGE COST OF CAPITAL,X.XX%,[Green output]

Formula Writing Rules

The only hardcoded numbers permitted are: raw historical inputs, assumption drivers (growth rates, WACC inputs, terminal g), and current market data (share price, debt balance). 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.

Correct formula assignment:

ws["D20"] = "=D19*(1+$B$8)"

Incorrect formula assignment (hardcoding a calculated value):

ws["D20"] = calculated_revenue

Formatting Specifications

Font Colors

Blue for ALL hardcoded inputs.
Black for ALL formulas.
Green for sheet links.

Fill Palette

Dark blue section headers: #1F4E79
Light blue sub-headers: #D9E1F2
Light grey input cells: #F2F2F2
White calculated cells.
Medium blue output/summary rows.

Borders

Thick (1.5pt) around major sections.
Medium (1pt) between sub-sections.
Thin (0.5pt) around data tables.

Number Formats

Percentages: 0.0%
Currency for millions: $#,##0
Per-share values: $#,##0.00
Zeros shown as '-': $#,##0;($#,##0);-
Plain numbers: #,##0
Negative numbers in parentheses: (#,##0)

File Naming Convention

[Ticker]_DCF_Model_[Date].xlsx

Example: AAPL_DCF_Model_2025-10-12.xlsx

Recalculation and Verification

Run recalc.py before delivery and fix ALL errors until status is 'success'. Command syntax:

python recalc.py [path_to_excel_file] [timeout_seconds]

Example:

python recalc.py AAPL_DCF_Model_2025-10-12.xlsx 30

Alternative command using full path:

python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx

Successful response:

{
  "status": "success",           // or "errors_found"
  "total_errors": 0,              // Total error count
  "total_formulas": 42,           // Number of formulas in file
  "error_summary": {}             // Only present if errors found
}

Error response:

{
  "status": "errors_found",
  "total_errors": 2,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {
      "count": 2,
      "locations": ["DCF!B25", "DCF!C25"]
    }
  }
}

The status field must be "success" before delivery. If "errors_found", fix all errors and re-run until clean.

Constraints and Caveats

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.
The only hardcoded numbers permitted are: raw historical inputs, assumption drivers (growth rates, WACC inputs, terminal g), and current market data (share price, debt balance).
Verify step-by-step with the user after data retrieval, revenue projections, FCF build, WACC, and terminal value + PV — do NOT build end-to-end without confirmation.
Sensitivity tables must use an ODD number of rows and columns (standard: 5×5). Center cell = base case, highlighted with #BDD7EE fill + bold font.
Populate ALL sensitivity cells (3 tables × 25 cells = 75) with full DCF recalculation formulas — NO placeholder text, NO linear approximations, NO manual steps.
Add cell comments AS each hardcoded value is created — do not defer to end.
Define ALL section row positions BEFORE writing any formulas. Write ALL headers and labels first, then section dividers and blank rows, THEN write formulas using locked row positions.
Run recalc.py before delivery and fix ALL errors until status is 'success'.
Operating expenses must be based on REVENUE, not gross profit.
Terminal growth must be less than WACC (otherwise infinite value).
Use consolidation column with INDEX formulas for scenario selection — NOT scattered IF statements.
Sensitivity tables are simple 2D grids with regular formulas — NOT Excel's Data Table feature.
Font colors: blue for ALL hardcoded inputs, black for ALL formulas, green for sheet links.
Fill palette: dark blue section headers, light blue sub-headers, light grey input cells, white calculated cells, medium blue output/summary rows.
Borders: thick (1.5pt) around major sections, medium (1pt) between sub-sections, thin (0.5pt) around data tables.
Number formats: percentages 0.0%, currency $#,##0 for millions, $#,##0.00 for per-share, zeros shown as '-', negative numbers in parentheses.
File naming: [Ticker]_DCF_Model_[Date].xlsx.
Terminal value should represent 50-70% of Enterprise Value — if >75%, model may be over-reliant on terminal assumptions.
Tax rate should be 21-28%.
WACC ranges: Large Cap Stable 7-9%, Growth Companies 9-12%, High Growth/Risk 12-15%.
Terminal growth rate: conservative 2.0-2.5%, moderate 2.5-3.5%, aggressive 3.5-5.0% (do not exceed risk-free rate or long-term GDP growth).

Failure Modes

Formula row references off because headers were inserted after formulas were written — leads to #REF! errors.
Missing cell comments on hardcoded inputs — model not audit-ready.
Simplified sensitivity tables using linear approximations or placeholder text instead of full DCF recalculation formulas.
Scenario block references wrong because IF formulas pull from incorrect Bear/Base/Bull blocks.
No borders — model looks unprofessional and is hard to navigate.
Operating expenses based on gross profit instead of revenue — produces unrealistic margin progression.
WACC calculation errors: mixing book and market values, using equity beta incorrectly, wrong tax rate, incorrect risk-free rate, failure to adjust for net debt vs net cash.
Growth assumption flaws: terminal growth > WACC, projection growth inconsistent with historical performance, ignoring industry constraints.
Terminal value mistakes: using wrong growth method, TV >80% of EV, inconsistent terminal margins, wrong discount period.
Cash flow projection errors: D&A/CapEx percentages misaligned, working capital changes not properly calculated, tax rate inconsistency, NOPAT calculation errors.
Single row for each assumption across scenarios (vertical layout) instead of separate blocks per scenario (horizontal across years).
Wrong font colors or no font color distinction — can't distinguish inputs from formulas.
Sensitivity tables placed on separate sheet instead of bottom of DCF sheet.
Using Excel's Data Table feature instead of writing regular formulas in each cell.
Not recalculating formulas before delivery — model may contain errors.
Fabricating data when sources are unavailable — must flag as [UNSOURCED] and surface to user.

Common Formula Errors and Corrections

Wrong row references:

D&A: =E29*$E$34 // Should be $E$21, but referencing wrong row
CapEx: =E29*$E$41 // Should be $E$22, but row shifted

Operating expenses based on gross profit (WRONG):

S&M: =E33*0.15 // E33 = Gross Profit (WRONG)

Operating expenses based on revenue (CORRECT):

S&M: =E29*0.15 // E29 = Revenue (CORRECT)

Scenario Block Structure Rules

Correct consolidation column pattern:

=INDEX(B10:D10, 1, $B$6)

Incorrect pattern using nested IF:

=IF($B$6=1,[Bear cell],IF($B$6=2,[Base cell],[Bull cell]))

Incorrect pattern for block references:

=IF($B$6=1,[Bear block cell],IF($B$6=2,[Base block cell],[Bull block cell]))

Correct revenue projection formula:

Revenue Year 1: =D29*(1+$E$10)

Sensitivity Table Formula Construction

Each sensitivity cell contains a full DCF recalculation formula. The formula references row and column headers using mixed references:

Row header reference: $A88 (absolute column, relative row)
Column header reference: B$87 (relative column, absolute row)

The formula pattern: = row offset, indicating the formula uses $A88 for the row axis value and B$87 for the column axis value, then recalculates the full DCF using those inputs.

Data Sources

Use native-mcp for structured financial data. If unavailable, use web_search and web_extract to find data from sources like https://www.sec.gov/cgi-bin/browse-edgar. Use browser_navigate for web scraping. If data cannot be sourced, flag as [UNSOURCED] and surface to the user.

Verification Steps

Verify step-by-step with the user after:

Data retrieval and validation.
Revenue projections.
Free cash flow build.
WACC calculation.
Terminal value and present value calculations.

Do NOT build end-to-end without confirmation at each step.

WACC Calculation Summary

Market Cap = Price × Shares
Net Debt = Total Debt - Cash
Enterprise Value = Market Cap + Net Debt
Equity Weight = Market Cap / EV
Debt Weight = Net Debt / EV
WACC = (Cost of Equity × Equity Weight) + (After-tax Cost of Debt × Debt Weight)

Number Format Examples

0.0% for percentages like 9.0%, 3.0%, 28%, 12%, 16%, 20%, 40%, 30%, 15%, 8%, 2%, 3%, 5%, 6.0%, 9%, 10%, 2.5%, 3.5%, 5.0%, 70%, 75%, 7%, 45%, 44%, 43%, 42%, 41%, 14%, 48%, 49%, 50%, 51%, 52%, 18%, 13%, 11%, 53%, 54%, 2.0%, 4.0%, 8.0%, 8.5%, 9.5%, 10.0%, 80%, 0.0%.
$#,##0 for currency amounts like $8, $6, $1000, $954, $10, $21, $22, $23, $87, $34, $41, $11, $12, $24.
$#,##0.00 for per-share values.
#,##0 for plain numbers like 1000.
(#,##0) for negative numbers.

Color Code References

#BDD7EE medium blue for sensitivity table center cell.
#1F4E79 dark blue for section headers.
#D9E1F2 light blue for sub-headers.
#F2F2F2 light grey for input cells.
0255, 0128, 78121, 217225242, 242242242, 189215238 are additional color references for formatting.

Year References

2024 and 2025 are used as projection year labels.
2024A through 2026E in historical/projection blocks.

Delivery Checklist

Before delivery, ensure:

All 75 sensitivity cells contain full DCF recalculation formulas.
All hardcoded inputs have cell comments.
Font colors are correct (blue inputs, black formulas, green links).
Fill colors follow the palette (dark blue, light blue, light grey, white, medium blue).
Borders are applied (thick, medium, thin).
Number formats are correct.
File name follows [Ticker]_DCF_Model_[Date].xlsx.
recalc.py returns status of "success" with total_errors of 0.
No error_summary present.
No [UNSOURCED] data without user notification.