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": {...}, ...}
    ... ...
     ... ...
    ... ...
    ...  ...
    ... ...
   revenue_growth gross_margin op_margin capex_pct_revenue tax_rate buyback_rate_qoq
   wacc terminal_growth risk_free_rate equity_risk_premium projected_fcf terminal_value enterprise_value implied_share_price sensitivity
    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