Update to Daloopa

{
  "name": "dcf",
  "description": "Discounted cash flow valuation with sensitivity analysis",
  "included_files": [
    {
      "relative_path": "agents/openai.yaml",
      "size_in_bytes": 202
    }
  ],
  "skill_md_contents": "---\nname: dcf\ndescription: Discounted cash flow valuation with sensitivity analysis\n---\n\nBuild a discounted cash flow (DCF) valuation 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\nFollow these steps:\n\n## 1. Company Lookup\nLook up the company by ticker using `discover_companies`. Capture:\n- `company_id`\n- `latest_calendar_quarter` — anchor for all period calculations below (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\n## 2. Market Data\nGet market-side inputs for {TICKER} (see `../data-access.md` Section 2 for how to source market data in your environment):\n- Current price, market cap, shares outstanding, beta\n- 10Y Treasury yield (risk-free rate for WACC)\n\nIf market data is unavailable, use reasonable defaults: beta=1.0, risk-free rate=4.5%, and note the assumptions.\n\n## 3. Historical Financials from Daloopa\nCalculate 8 quarters backward from `latest_calendar_quarter`. Pull:\n- Revenue\n- Operating Income\n- Net Income\n- Diluted EPS\n- Operating Cash Flow\n- Capital Expenditures\n- Free Cash Flow (compute as OCF - CapEx, label \"(calc.)\")\n- Depreciation & Amortization\n- Tax expense and pre-tax income (for effective tax rate)\n- Interest expense (for cost of debt)\n- Total debt (short + long term)\n- Cash and equivalents\n- Shares outstanding\n\nAlso pull segment revenue and any available guidance series.\n\n## 4. Calculate WACC\n\n**Cost of Equity (CAPM):**\n- Risk-free rate (Rf) = 10Y Treasury from market data\n- Equity risk premium (ERP) = 5.5% (standard assumption)\n- Beta from market data (or 1.0 default)\n- Cost of equity = Rf + Beta × ERP\n\n**Cost of Debt:**\n- If interest expense and total debt available: Cost of debt = Interest Expense / Average Total Debt\n- After-tax cost of debt = Cost of debt × (1 - effective tax rate)\n- If not available, use 5.0% pre-tax as default\n\n**Capital Structure:**\n- Market cap for equity weight\n- Total debt for debt weight\n- WACC = (E/V) × Re + (D/V) × Rd × (1-t)\n\nShow all inputs and the resulting WACC clearly.\n\n## 5a. KPI-Driven Revenue Build (Preferred)\n\nBefore projecting top-down, attempt a bottoms-up revenue build using operational KPIs. This produces a significantly more defensible DCF — a top-down trend decay is a guess; a bottoms-up KPI build is analysis.\n\n**Discover segment and KPI data:**\nPull segment revenue breakdown + segment-specific KPIs for the target company. Use the sector taxonomy to know what to search for:\n\n- **SaaS/Cloud**: ARR, net revenue retention, RPO/cRPO, customers >$100K, cloud gross margin\n- **Consumer Tech**: DAU/MAU, ARPU, engagement metrics, installed base, paid subscribers\n- **E-commerce/Marketplace**: GMV, take rate, active buyers/sellers, order frequency\n- **Retail**: same-store sales, store count, average ticket, transactions\n- **Telecom/Media**: subscribers, churn, ARPU, content spend\n- **Hardware**: units shipped, ASP, attach rate, installed base\n- **Financial Services**: AUM, NIM, loan growth, credit quality metrics, fee income ratio\n- **Pharma/Biotech**: pipeline stage, patient starts, scripts, market share\n- **Industrials/Energy**: backlog, book-to-bill, utilization, production volumes, reserves\n\n**Build bottoms-up projections per segment:**\nFor each segment with KPI data, project revenue using unit economics:\n- Hardware segments: projected units × projected ASP\n- Subscription segments: projected subscribers × projected ARPU (net of churn)\n- Marketplace segments: projected GMV × projected take rate\n- Services/recurring: apply growth rate informed by retention metrics and customer adds\n\nSum segment projections to get total revenue for each of 5 years. Show the build clearly so the reader can challenge individual segment assumptions.\n\n**Fall back to top-down if KPIs aren't available.** If segment KPIs are sparse or unavailable, use the top-down approach in Section 5b instead, but note explicitly that the model is less reliable without bottoms-up drivers.\n\n## 5b. Top-Down FCF Projections (Fallback)\n\nBuild 5-year FCF projections. If a projection engine is available (see `../data-access.md` Section 5), use it. Otherwise, project manually:\n- **Revenue:** Use management guidance for near-term, then decay toward 3% long-term growth\n- **FCF Margin:** Use trailing average, adjust for any clear trends\n- **FCF = Projected Revenue × Projected FCF Margin**\n\nShow all assumptions clearly — this is the most judgment-intensive part. If using this fallback instead of the KPI-driven build (Section 5a), note the limitation.\n\n## 6. Terminal Value\nCalculate terminal value using perpetuity growth method:\n- Terminal FCF = Year 5 FCF × (1 + terminal growth rate)\n- Terminal Value = Terminal FCF / (WACC - terminal growth rate)\n- Default terminal growth rate: 2.5-3.0% (should not exceed long-term GDP growth)\n- Discount terminal value to present\n\n## 7. Compute Implied Valuation\n- Sum of PV of projected FCFs + PV of terminal value = Enterprise Value\n- Equity Value = Enterprise Value - Net Debt\n- Implied Share Price = Equity Value / Shares Outstanding\n- Compare to current market price: upside/downside %\n\nAlso compute:\n- Implied EV/EBITDA (Enterprise Value / Trailing EBITDA)\n- Implied P/E (Equity Value / Trailing Net Income)\n- Terminal value as % of total value (flag if > 80% — this means the DCF is very sensitive to terminal assumptions)\n\n## 8. Sensitivity Analysis\nBuild a sensitivity table varying two key inputs:\n\n**WACC (rows):** Base WACC ± 2% in 0.5% increments (7 rows)\n**Terminal Growth Rate (columns):** 1.5% to 4.0% in 0.5% increments (6 columns)\n\nEach cell = implied share price at that WACC/growth combination.\nHighlight the base case cell and the current market price for reference.\n\nAlso show a secondary sensitivity: Revenue Growth vs FCF Margin if data supports it.\n\n## 9. Consensus Sanity Check (if available)\nIf consensus estimates are available (see `../data-access.md` Section 3):\n- Compare your projected revenue/EPS path to consensus for the next 1-2 years\n- Note where your DCF assumptions diverge from Street expectations\n- If your implied price is significantly above/below consensus target, explain why\n\nIf consensus data is not available, skip this check.\n\n## 10. Sanity Checks & Self-Challenge\nFlag any issues:\n- If implied price is >2x or <0.5x current price, note that the DCF produces an extreme result and examine assumptions\n- If terminal value is >85% of total value, the model is highly sensitive to terminal assumptions\n- If WACC < risk-free rate or > 15%, the capital structure inputs may be off\n- Compare implied multiples to historical trading range\n\n**Challenge your own assumptions — don't anchor to the current price:**\n- Build the DCF from fundamentals first, THEN compare to market price. Don't work backwards from the current price to justify assumptions.\n- If your base case revenue growth assumes continuation of recent trends, stress-test: what if growth mean-reverts to the industry average? What if the current cycle peaks?\n- Explicitly state what has to go right for the bull case implied price and what has to go wrong for the bear case.\n- If the DCF only \"works\" with aggressive terminal growth or unrealistically low WACC, say so — the stock may simply be expensive on fundamentals.\n\n## 11. Save Report\nSave to `reports/{TICKER}_dcf.html` using the HTML report template from `../design-system.md`. Write the full analysis as styled HTML with the design system CSS inlined. This is the final deliverable — no intermediate markdown step needed.\n\nStructure the report with these sections:\n\n```\n<h1>{Company Name} ({TICKER}) — DCF Valuation</h1>\n<p>Generated: {date}</p>\n\n<h2>Summary</h2>\n<table>\n| Metric | Value |\n| Current Price | $XXX |\n| Implied Share Price | $XXX |\n| Upside / Downside | +X.X% / -X.X% |\n| WACC | X.X% |\n| Terminal Growth | X.X% |\n| Terminal Value % of Total | XX% |\n</table>\n\n<h2>WACC Calculation</h2>\n<table>\n| Component | Value | Source |\n| Risk-Free Rate | X.X% | FRED 10Y Treasury |\n| Equity Risk Premium | 5.5% | Standard assumption |\n| Beta | X.XX | Market data |\n| Cost of Equity | X.X% | CAPM |\n| Cost of Debt (after-tax) | X.X% | Interest/Debt × (1-t) |\n| Equity Weight | XX% | Market cap |\n| Debt Weight | XX% | Total debt |\n| **WACC** | **X.X%** | |\n</table>\n\n<h2>Historical Free Cash Flow (8 Quarters)</h2>\n<table>\n| Metric | Q1 | Q2 | ... | Q8 |\n{OCF, CapEx, FCF, FCF Margin — with Daloopa citations}\n</table>\n\n<h2>FCF Projections (5 Years)</h2>\n<table>\n| Metric | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 |\n{Revenue, FCF Margin, FCF — with assumptions noted}\n</table>\n\n<h2>Valuation Bridge</h2>\n<table>\n| Component | Value |\n| PV of Projected FCFs | $XXX |\n| PV of Terminal Value | $XXX |\n| Enterprise Value | $XXX |\n| Less: Net Debt | ($XXX) |\n| Equity Value | $XXX |\n| Shares Outstanding | XXX |\n| **Implied Share Price** | **$XXX** |\n</table>\n\n<h2>Sensitivity Table: WACC vs Terminal Growth</h2>\n<table>\n| WACC \\ Growth | 1.5% | 2.0% | 2.5% | 3.0% | 3.5% | 4.0% |\n{matrix of implied share prices, base case bolded}\n</table>\n<p>Current market price: $XXX for reference.</p>\n\n<h2>Key Assumptions & Risks</h2>\n<ul>{List all key assumptions and what could invalidate them}</ul>\n\n<h2>Sanity Checks</h2>\n<ul>{Implied multiples vs historical, terminal value concentration, etc.}</ul>\n```\n\nAll financial figures must use Daloopa citation format: `<a href=\"https://daloopa.com/src/{fundamental_id}\">$X.XX million</a>`\n\nTell the user where the HTML report was saved.\n\nSummarize: implied price vs current price, key upside/downside drivers, and the biggest sensitivity.\n"
}