Build defensible DCF models with cited sources, Excel export, and sensitivity analysis
Build discounted cash flow (DCF) models from SEC filings with full source attribution. Outputs Excel workbook with formulas and Markdown summary.
# Build DCF for Omnicom Group
dcf = build_dcf(
ticker_or_cik="OMC",
currency="USD",
scenario="base",
forecast_years=5
)
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 []
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'],
}
}
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
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,
}
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,
}
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
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
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
