build-model

name	build-model
description	Build a multi-tab Excel financial model
argument-hint	TICKER

Build a comprehensive Excel financial model (.xlsx) for the company specified by the user: $ARGUMENTS

Before starting, read ../data-access.md for data access methods and ../design-system.md for formatting conventions. Follow the data access detection logic and design system throughout this skill.

This skill gathers all available financial data and builds a multi-tab Excel model from scratch using openpyxl.

Phase 1 — Company Setup

Look up the company by ticker using discover_companies. Capture:

• company_id
• latest_calendar_quarter — anchor for all period calculations (see ../data-access.md Section 1.5)
• latest_fiscal_quarter
• Firm name for report attribution (default: "Daloopa") — see ../data-access.md Section 4.5

Get current stock price, market cap, shares outstanding, beta, and trading multiples for {TICKER} (see ../data-access.md Section 2 for how to source market data).

Phase 2 — Comprehensive Data Pull

Calculate periods backward from latest_calendar_quarter. Pull as much data as Daloopa has for this company. Target 8-16 quarters.

Income Statement — search and pull all available:

• Revenue / Net Sales
• Cost of Revenue / COGS
• Gross Profit
• Research & Development
• Selling, General & Administrative
• Total Operating Expenses
• Operating Income
• Interest Expense / Income
• Pre-tax Income
• Tax Expense
• Net Income
• Diluted EPS
• Diluted Shares Outstanding
• EBITDA (or compute from Op Income + D&A)
• D&A

Balance Sheet — search and pull all available:

• Cash and Equivalents
• Short-term Investments
• Accounts Receivable
• Inventory
• Total Current Assets
• PP&E (net)
• Goodwill
• Total Assets
• Accounts Payable
• Short-term Debt
• Long-term Debt
• Total Liabilities
• Total Equity

Cash Flow — search and pull all available:

• Operating Cash Flow
• Capital Expenditures
• Depreciation & Amortization
• Acquisitions
• Dividends Paid
• Share Repurchases
• Free Cash Flow (compute if not direct)

Segments:

• Revenue by segment
• Operating income by segment (if available)

KPIs:

• All company-specific operating metrics

Guidance:

• All guidance series and corresponding actuals

Phase 3 — Market Data & Peers

• Identify 5-8 peers and get their trading multiples (see ../data-access.md Section 2)
• Get risk-free rate (see ../data-access.md Section 2)
• If consensus forward estimates are available (../data-access.md Section 3), include NTM estimates for peers

Phase 4 — Projections

Build forward estimates. If a projection engine is available (see ../data-access.md Section 5), use it. Otherwise, project manually:

• Revenue: guidance + decay to long-term growth
• Margins: mean-revert to trailing averages
• CapEx, D&A, tax rate, share count: trailing trends

Project 4-8 quarters forward.

Phase 5 — DCF Inputs

Calculate:

• WACC (CAPM: Rf + Beta × ERP; cost of debt from interest/debt)
• 5-year FCF projections (annualized from quarterly)
• Terminal value (perpetuity growth at 2.5-3%)
• Sensitivity matrix: WACC (7 values) × terminal growth (6 values)

Phase 6 — Build Excel Model

Write the complete context to reports/.tmp/{TICKER}_model_context.json, then run: python infra/excel_builder.py --context reports/.tmp/{TICKER}_model_context.json --output reports/{TICKER}_model.xlsx

The context JSON should include ALL of these sections (each optional — the builder handles missing data):

{
  "company": {name, ticker, exchange, currency},
  "market_data": {price, market_cap, shares_outstanding, beta, trailing_pe, forward_pe, ev_ebitda, ...},
  "periods": ["2023Q1", ...],
  "projected_periods": ["2026Q1", ...],
  "income_statement": {"Revenue": {"2023Q1": value, ...}, ...},
  "balance_sheet": {"Total Assets": {...}, ...},
  "cash_flow": {"Operating Cash Flow": {...}, ...},
  "segments": {"Revenue by Segment": {"iPhone": {...}, ...}},
  "kpis": {"Metric Name": {...}, ...},
  "guidance": {"series": {...}, "actuals": {...}},
  "projections": {"Revenue": {...}, ...},
  "projection_assumptions": {revenue_growth, gross_margin, op_margin, capex_pct_revenue, tax_rate, buyback_rate_qoq},
  "dcf": {wacc, terminal_growth, risk_free_rate, equity_risk_premium, projected_fcf, terminal_value, enterprise_value, implied_share_price, sensitivity},
  "comps": {"peers": [{ticker, name, trailing_pe, ev_ebitda, ...}, ...]}
}

If the Excel builder fails, report the error. The context JSON is still saved for debugging.

Output

Tell the user:

• Where the .xlsx was saved: reports/{TICKER}_model.xlsx
• Where the context JSON was saved: reports/.tmp/{TICKER}_model_context.json
• Summary of what tabs were built
• Key model outputs: trailing revenue, projected revenue growth, implied DCF value, peer-implied range
• Remind user that yellow cells in the Projections tab are editable inputs

All financial figures gathered must use Daloopa citation format: $X.XX million