Update to Daloopa

{
  "name": "build-model",
  "description": "Build a multi-tab Excel financial model",
  "included_files": [
    {
      "relative_path": "agents/openai.yaml",
      "size_in_bytes": 210
    }
  ],
  "skill_md_contents": "---\nname: build-model\ndescription: Build a multi-tab Excel financial model\n---\n\nBuild a comprehensive Excel financial model (.xlsx) for the company named in the user's request. If no ticker or company is provided, ask for one before proceeding.\n\n**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.\n\nThis skill gathers all available financial data and builds a multi-tab Excel model saved as `reports/{TICKER}_model.xlsx`.\n\n## Phase 1 — Company Setup\nLook up the company by ticker using `discover_companies`. Capture:\n- `company_id`\n- `latest_calendar_quarter` — anchor for all period calculations (see `../data-access.md` Section 1.5)\n- `latest_fiscal_quarter`\n- Firm name for report attribution (default: \"Daloopa\") — see `../data-access.md` Section 4.5\n\nGet current stock price, market cap, shares outstanding, beta, and trading multiples for {TICKER}. Use the 3-step resolution: (1) MCP market data tools if available, (2) web search, (3) sensible defaults (see `../data-access.md` Section 2).\n\n## Phase 2 — Comprehensive Data Pull\nCalculate periods backward from `latest_calendar_quarter`. Pull as much data as Daloopa has for this company. Target 8-16 quarters.\n\n**Income Statement — search and pull all available:**\n- Revenue / Net Sales\n- Cost of Revenue / COGS\n- Gross Profit\n- Research & Development\n- Selling, General & Administrative\n- Total Operating Expenses\n- Operating Income\n- Interest Expense / Income\n- Pre-tax Income\n- Tax Expense\n- Net Income\n- Diluted EPS\n- Diluted Shares Outstanding\n- EBITDA (or compute from Op Income + D&A)\n- D&A\n\n**Balance Sheet — search and pull all available:**\n- Cash and Equivalents\n- Short-term Investments\n- Accounts Receivable\n- Inventory\n- Total Current Assets\n- PP&E (net)\n- Goodwill\n- Total Assets\n- Accounts Payable\n- Short-term Debt\n- Long-term Debt\n- Total Liabilities\n- Total Equity\n\n**Cash Flow — search and pull all available:**\n- Operating Cash Flow\n- Capital Expenditures\n- Depreciation & Amortization\n- Acquisitions\n- Dividends Paid\n- Share Repurchases\n- Free Cash Flow (compute if not direct)\n\n**Segments:**\n- Revenue by segment\n- Operating income by segment (if available)\n\n**KPIs:**\n- All company-specific operating metrics\n\n**Guidance:**\n- All guidance series and corresponding actuals\n\n## Phase 3 — Market Data & Peers\n- Identify 5-8 peers and get their trading multiples using the same 3-step resolution: (1) MCP market data tools, (2) web search, (3) sensible defaults\n- Get risk-free rate using the same 3-step resolution\n- If consensus forward estimates are available (`../data-access.md` Section 3), include NTM estimates for peers\n\n## Phase 4 — Projections\nBuild forward estimates using the following methodology:\n- **Revenue:** Start with latest guidance (if available), then decay to long-term growth rate (industry average or historical trend). Apply quarterly seasonality patterns from trailing data.\n- **Gross Margin:** Mean-revert to trailing 8-quarter average, with adjustment for recent trends or guidance commentary.\n- **Operating Expenses:** Project as % of revenue, trending toward trailing averages. R&D and SG&A may have different trajectories.\n- **CapEx:** Project as % of revenue based on trailing 4-8 quarter average and guidance.\n- **D&A:** Project based on trailing average as % of revenue or PP&E.\n- **Tax Rate:** Use trailing effective tax rate or guidance.\n- **Share Count:** Project dilution/buyback based on trailing trends and guidance.\n- **Working Capital:** Project DSO, DIO, DPO based on trailing averages.\n\nCalculate all quarterly projections, then sum to annual. Project 4-8 quarters forward.\n\n## Phase 5 — DCF Inputs\nCalculate:\n- **WACC:** Use CAPM for cost of equity (Rf + Beta × ERP, where ERP = 6.0%). Cost of debt = Interest Expense / Total Debt. WACC = (E/V × Re) + (D/V × Rd × (1 - Tax Rate)).\n- **5-year FCF projections:** Annualize from quarterly projections (FCF = Op Cash Flow - CapEx).\n- **Terminal Value:** Use perpetuity growth at 2.5-3.0%.\n- **Sensitivity Matrix:** WACC (7 values: -3% to +3% from base) × Terminal Growth (6 values: 1.5% to 4.0%).\n\n## Phase 6 — Build Excel Model\nGenerate the `.xlsx` file directly using the best available spreadsheet-generation workflow. For Codex, prefer bundled spreadsheet tooling or Python/openpyxl when available. The workbook should:\n\n1. Create 8 tabs with the following structure:\n\n**Tab 1: Income Statement**\n- Rows: Revenue, COGS, Gross Profit, R&D, SG&A, Total OpEx, Op Income, Interest, Pre-Tax Income, Tax, Net Income, Diluted EPS, Shares\n- Columns: Historical periods (8-16Q) + Projected periods (4-8Q)\n- Sub-rows: YoY growth %, margin % where applicable\n- Header: Company name, ticker, report date\n- Formatting: Numbers with commas/decimals, percentages, bold headers, frozen panes\n\n**Tab 2: Balance Sheet**\n- Rows: Assets section (Cash, Investments, AR, Inventory, Current Assets, PP&E, Goodwill, Total Assets), Liabilities section (AP, ST Debt, LT Debt, Total Liabilities, Equity)\n- Columns: Historical + Projected periods\n- Sub-rows: % of Total Assets for key line items\n- Same formatting standards\n\n**Tab 3: Cash Flow**\n- Rows: Op Cash Flow, CapEx, Free Cash Flow, Acquisitions, Dividends, Buybacks, Net Change in Cash\n- Columns: Historical + Projected periods\n- Sub-rows: FCF yield %, CapEx as % Revenue\n- Same formatting standards\n\n**Tab 4: Segments**\n- Rows: Revenue by segment, Op Income by segment (if available)\n- Columns: Historical + Projected periods\n- Sub-rows: Segment as % of total, segment growth rates\n- Same formatting standards\n\n**Tab 5: KPIs**\n- Rows: All company-specific operating metrics discovered\n- Columns: Historical + Projected periods\n- Sub-rows: YoY growth or relevant unit economics\n- Same formatting standards\n\n**Tab 6: Projections**\n- Editable assumption inputs (yellow highlighting): Revenue growth %, Gross margin %, Op margin %, CapEx % revenue, Tax rate %, Buyback rate QoQ\n- Calculated outputs: Projected P&L, BS, CF driven by assumptions\n- Commentary box explaining methodology\n- Same formatting standards\n\n**Tab 7: DCF**\n- Inputs: WACC, Terminal Growth, Risk-Free Rate, ERP, Beta, Cost of Debt\n- FCF Projection (5 years annualized)\n- Terminal Value calculation\n- PV calculations\n- Enterprise Value → Equity Value → Implied Share Price\n- Sensitivity table: WACC (rows) × Terminal Growth (cols) showing implied price\n- Color scale: green (upside) to red (downside) vs current price\n- Same formatting standards\n\n**Tab 8: Summary**\n- Company overview (name, ticker, sector, description)\n- Current market data (price, market cap, shares, beta)\n- Valuation summary: DCF implied price, peer-implied range, current price, upside/downside %\n- Peer trading multiples table\n- Key model outputs: Trailing revenue, Projected revenue growth, Trailing/Projected margins\n- Same formatting standards\n\n2. Apply `../design-system.md` formatting conventions:\n- Number format: $X.Xbn for large numbers, X.X% for percentages, X.Xx for multiples\n- Color palette: Navy #1B2A4A (headers), Steel Blue #4A6FA5 (sub-headers), Gold #C5A55A (highlights), Green #27AE60 (positive), Red #C0392B (negative)\n- Bold headers, frozen top row and left column\n- Yellow fill (#FFEB3B) for editable input cells\n\n3. Save the workbook as `reports/{TICKER}_model.xlsx`\n\n## Output\nSave the generated workbook to `reports/{TICKER}_model.xlsx` and tell the user:\n- Summary of what tabs were built\n- Key model outputs: trailing revenue, projected revenue growth, implied DCF value, peer-implied range\n- Note that yellow cells in the Projections tab are editable inputs\n- Instruction to open the saved `.xlsx` file\n\nAll financial figures gathered must use Daloopa citation format: [$X.XX million](https://daloopa.com/src/{fundamental_id})\n"
}