DCF Builder Skill

Purpose

Build discounted cash flow (DCF) models from SEC filings with full source attribution. Outputs Excel workbook with formulas and Markdown summary.

Usage

# Build DCF for Omnicom Group
dcf = build_dcf(
    ticker_or_cik="OMC",
    currency="USD",
    scenario="base",
    forecast_years=5
)

Workflow

1. Fetch Historical Financials

def fetch_historical_financials(cik):
    """Fetch historical financials from SEC EDGAR"""
    # Get company facts (XBRL aggregated data)
    url = f"https://data.sec.gov/api/xbrl/companyfacts/CIK{cik.zfill(10)}.json"
    response = requests.get(url, headers=EDGAR_HEADERS, timeout=30)
    response.raise_for_status()

    facts = response.json()

    # Extract key metrics
    financials = {
        'revenue': extract_fact(facts, 'Revenues'),
        'cogs': extract_fact(facts, 'CostOfRevenue'),
        'operating_expenses': extract_fact(facts, 'OperatingExpenses'),
        'depreciation': extract_fact(facts, 'DepreciationAndAmortization'),
        'capex': extract_fact(facts, 'PaymentsToAcquirePropertyPlantAndEquipment'),
        'tax_rate': extract_fact(facts, 'EffectiveIncomeTaxRateContinuingOperations'),
        'cash': extract_fact(facts, 'Cash'),
        'debt': extract_fact(facts, 'LongTermDebt'),
        'shares_outstanding': extract_fact(facts, 'CommonStockSharesOutstanding'),
    }

    return financials

def extract_fact(facts, concept_name):
    """Extract XBRL fact by concept name"""
    try:
        # Navigate nested structure: facts -> concept -> units -> values
        concept = facts['facts']['us-gaap'][concept_name]
        # Get USD annual values
        usd_values = concept['units']['USD']
        # Filter for 10-K filings (FY = full year)
        annual_values = [v for v in usd_values if v['form'] == '10-K']
        # Sort by date
        annual_values.sort(key=lambda x: x['end'], reverse=True)
        # Return most recent 5 years
        return [{
            'date': v['end'],
            'value': v['val'],
            'accession': v['accn'],
            'source': f"https://www.sec.gov/cgi-bin/viewer?action=view&cik={cik}&accession_number={v['accn']}"
        } for v in annual_values[:5]]
    except KeyError:
        return []

2. Compute WACC

def compute_wacc(cik, financials):
    """Compute Weighted Average Cost of Capital"""

    # Cost of Equity (CAPM): Rf + Beta * (Rm - Rf)
    risk_free_rate = 0.045  # 10-year Treasury yield
    market_risk_premium = 0.08  # Historical equity risk premium
    beta = fetch_beta(cik)  # From financial data providers or regression

    cost_of_equity = risk_free_rate + beta * market_risk_premium

    # Cost of Debt: Interest Expense / Total Debt
    interest_expense = extract_latest_value(financials, 'InterestExpense')
    total_debt = extract_latest_value(financials, 'LongTermDebt')
    cost_of_debt = interest_expense / total_debt if total_debt > 0 else 0.05

    # After-tax cost of debt
    tax_rate = extract_latest_value(financials, 'EffectiveIncomeTaxRateContinuingOperations') / 100
    cost_of_debt_after_tax = cost_of_debt * (1 - tax_rate)

    # Market values
    equity_value = extract_latest_value(financials, 'CommonStockSharesOutstanding') * fetch_stock_price(cik)
    debt_value = total_debt

    # WACC = (E/V) * Re + (D/V) * Rd * (1 - Tc)
    total_value = equity_value + debt_value
    wacc = (equity_value / total_value) * cost_of_equity + (debt_value / total_value) * cost_of_debt_after_tax

    return {
        'wacc': wacc,
        'cost_of_equity': cost_of_equity,
        'cost_of_debt': cost_of_debt_after_tax,
        'weights': {
            'equity': equity_value / total_value,
            'debt': debt_value / total_value,
        },
        'sources': {
            'risk_free_rate': '10-Year Treasury Yield',
            'beta': 'Computed from historical returns',
            'debt': financials['debt'][0]['source'],
        }
    }

3. Forecast Free Cash Flow

def forecast_fcf(financials, scenario='base', forecast_years=5):
    """Forecast Free Cash Flow"""

    # Historical revenue growth
    revenues = [f['value'] for f in financials['revenue']]
    historical_growth = [(revenues[i] / revenues[i+1]) - 1 for i in range(len(revenues)-1)]
    avg_growth = sum(historical_growth) / len(historical_growth)

    # Scenario assumptions
    growth_assumptions = {
        'base': avg_growth,
        'bull': avg_growth * 1.2,  # 20% higher
        'bear': avg_growth * 0.8,  # 20% lower
    }

    revenue_growth = growth_assumptions[scenario]

    # Forecast revenues
    latest_revenue = revenues[0]
    forecasted_revenues = []
    for year in range(1, forecast_years + 1):
        forecasted_revenue = latest_revenue * ((1 + revenue_growth) ** year)
        forecasted_revenues.append(forecasted_revenue)

    # Forecast FCF components
    fcf_projections = []
    for year, revenue in enumerate(forecasted_revenues, start=1):
        # Operating margin assumption (use historical average)
        ebitda_margin = compute_avg_margin(financials, 'EBITDA')
        ebitda = revenue * ebitda_margin

        # D&A as % of revenue
        da_pct = compute_avg_pct(financials, 'depreciation')
        depreciation = revenue * da_pct

        # EBIT = EBITDA - D&A
        ebit = ebitda - depreciation

        # Taxes
        tax_rate = extract_latest_value(financials, 'EffectiveIncomeTaxRateContinuingOperations') / 100
        taxes = ebit * tax_rate

        # NOPAT (Net Operating Profit After Tax)
        nopat = ebit - taxes

        # Add back D&A
        # Subtract Capex
        capex_pct = compute_avg_pct(financials, 'capex')
        capex = revenue * capex_pct

        # Change in NWC (working capital)
        nwc_change = revenue * 0.02  # Assume 2% of revenue growth

        # FCF = NOPAT + D&A - Capex - ΔNW C
        fcf = nopat + depreciation - capex - nwc_change

        fcf_projections.append({
            'year': year,
            'revenue': revenue,
            'ebitda': ebitda,
            'depreciation': depreciation,
            'ebit': ebit,
            'taxes': taxes,
            'nopat': nopat,
            'capex': capex,
            'nwc_change': nwc_change,
            'fcf': fcf,
        })

    return fcf_projections

4. Calculate Terminal Value and Valuation

def calculate_valuation(fcf_projections, wacc, terminal_growth=0.025):
    """Calculate enterprise and equity value"""

    # Discount FCF to present value
    pv_fcf = []
    for projection in fcf_projections:
        year = projection['year']
        fcf = projection['fcf']
        discount_factor = (1 + wacc) ** year
        pv = fcf / discount_factor
        pv_fcf.append(pv)

    sum_pv_fcf = sum(pv_fcf)

    # Terminal value (perpetuity growth method)
    final_fcf = fcf_projections[-1]['fcf']
    terminal_value = (final_fcf * (1 + terminal_growth)) / (wacc - terminal_growth)

    # Discount terminal value to present
    terminal_year = len(fcf_projections)
    pv_terminal_value = terminal_value / ((1 + wacc) ** terminal_year)

    # Enterprise value
    enterprise_value = sum_pv_fcf + pv_terminal_value

    # Equity value = EV - Net Debt
    net_debt = extract_latest_value(financials, 'LongTermDebt') - extract_latest_value(financials, 'Cash')
    equity_value = enterprise_value - net_debt

    # Price per share
    shares_outstanding = extract_latest_value(financials, 'CommonStockSharesOutstanding')
    price_target = equity_value / shares_outstanding

    return {
        'sum_pv_fcf': sum_pv_fcf,
        'terminal_value': terminal_value,
        'pv_terminal_value': pv_terminal_value,
        'enterprise_value': enterprise_value,
        'net_debt': net_debt,
        'equity_value': equity_value,
        'shares_outstanding': shares_outstanding,
        'price_target': price_target,
    }

5. Sensitivity Analysis

def sensitivity_analysis(fcf_projections, wacc, terminal_growth, financials):
    """Generate sensitivity table"""

    wacc_range = [wacc - 0.02, wacc - 0.01, wacc, wacc + 0.01, wacc + 0.02]  # ±200 bps
    tg_range = [terminal_growth - 0.01, terminal_growth - 0.005, terminal_growth,
                terminal_growth + 0.005, terminal_growth + 0.01]  # ±100 bps

    sensitivity_table = []
    for w in wacc_range:
        row = []
        for tg in tg_range:
            valuation = calculate_valuation(fcf_projections, w, tg, financials)
            row.append(valuation['price_target'])
        sensitivity_table.append(row)

    return {
        'wacc_range': wacc_range,
        'terminal_growth_range': tg_range,
        'price_targets': sensitivity_table,
    }

6. Export to Excel

import openpyxl
from openpyxl.styles import Font, Alignment

def export_to_excel(dcf_model, filename='/exports/dcf_model.xlsx'):
    """Export DCF model to Excel with formulas"""

    wb = openpyxl.Workbook()

    # Historical sheet
    ws_hist = wb.active
    ws_hist.title = 'Historical'
    # ... populate historical data ...

    # Projections sheet
    ws_proj = wb.create_sheet('Projections')
    # ... populate projections with formulas ...

    # Valuation sheet
    ws_val = wb.create_sheet('Valuation')
    # ... populate valuation ...

    # Sensitivity sheet
    ws_sens = wb.create_sheet('Sensitivity')
    # ... populate sensitivity table ...

    # Sources sheet
    ws_sources = wb.create_sheet('Sources')
    ws_sources['A1'] = 'Data Sources'
    ws_sources['A1'].font = Font(bold=True, size=14)

    row = 3
    for metric, source in dcf_model['sources'].items():
        ws_sources[f'A{row}'] = metric
        ws_sources[f'B{row}'] = source
        row += 1

    wb.save(filename)
    return filename

7. Generate Markdown Summary

def generate_markdown_summary(dcf_model):
    """Generate Markdown summary with citations"""

    md = f"""
# DCF Valuation: {dcf_model['ticker']}

**Scenario**: {dcf_model['scenario']}
**Date**: {datetime.now().strftime('%Y-%m-%d')}
**Currency**: {dcf_model['currency']}

## Summary

- **Enterprise Value**: {dcf_model['valuation']['enterprise_value']:,.0f}
- **Equity Value**: {dcf_model['valuation']['equity_value']:,.0f}
- **Price Target**: {dcf_model['valuation']['price_target']:.2f}

## Assumptions

- **WACC**: {dcf_model['wacc']['wacc']:.2%}
- **Terminal Growth**: {dcf_model['terminal_growth']:.2%}
- **Forecast Period**: {dcf_model['forecast_years']} years

## Free Cash Flow Projections

| Year | Revenue | EBITDA | FCF |
|------|---------|--------|-----|
"""

    for proj in dcf_model['fcf_projections']:
        md += f"| {proj['year']} | {proj['revenue']:,.0f} | {proj['ebitda']:,.0f} | {proj['fcf']:,.0f} |\n"

    md += f"""
## Sensitivity Analysis

Price target range: **{min(min(dcf_model['sensitivity']['price_targets']))} - {max(max(dcf_model['sensitivity']['price_targets'])):.2f}**

## Sources

"""

    for metric, source in dcf_model['sources'].items():
        md += f"- **{metric}**: {source}\n"

    md += """

---

**Disclaimer**: This is an educational analysis and not investment advice.
"""

    return md

Evaluation

Test DCF output quality:

# tests/finance/dcf-builder.yaml
suite: dcf-builder
thresholds:
  has_excel_export: true
  has_sensitivities: true
  cites_sources: true

cases:
  - id: ev-dcf-omnicom
    prompt: "Build a base-case DCF for Omnicom (OMC) with 5y forecast"
    expects:
      - has_excel_export: true
      - has_formulas: true
      - has_sensitivity_table: true
      - cites_sources: true
      - price_target_reasonable: true  # Within 20% of current price

References

• DCF Methodology: https://www.investopedia.com/terms/d/dcf.asp
• XBRL Facts API: https://www.sec.gov/edgar/sec-api-documentation
• WACC Calculation: https://corporatefinanceinstitute.com/resources/valuation/wacc-formula/