dcf-model – skill Tài liệu & Office cho agent AI

Environment

This skill assumes headless openpyxl — you are producing an .xlsx file on disk.

Follow the excel-author skill's conventions for cell coloring, formulas, named ranges, and sensitivity tables.

Recalculate before delivery: python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx.

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:

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

… (nội dung đầy đủ có trong gói tải về)