w95/awesome-claude-corporate-skills/comps-analysis

Comparable Company Analysis

⚠️ CRITICAL: Data Source Priority (READ FIRST)

ALWAYS follow this data source hierarchy:

1. FIRST: Check for MCP data sources - If S&P Kensho MCP, FactSet MCP, or Daloopa MCP are available, use them exclusively for financial and trading information
2. DO NOT use web search if the above MCP data sources are available
3. ONLY if MCPs are unavailable: Then use Bloomberg Terminal, SEC EDGAR filings, or other institutional sources
4. NEVER use web search as a primary data source - it lacks the accuracy, audit trails, and reliability required for institutional-grade analysis

Why this matters: MCP sources provide verified, institutional-grade data with proper citations. Web search results can be outdated, inaccurate, or unreliable for financial analysis.

Overview

This skill teaches Claude to build institutional-grade comparable company analyses that combine operating metrics, valuation multiples, and statistical benchmarking. The output is a structured Excel/spreadsheet that enables informed investment decisions through peer comparison.

Reference Material & Contextualization:

An example comparable company analysis is provided in examples/comps_example.xlsx. When using this or other example files in this skill directory, use them intelligently:

DO use examples for:

• Understanding structural hierarchy (how sections flow)
• Grasping the level of rigor expected (statistical depth, documentation standards)
• Learning principles (clear headers, transparent formulas, audit trails)

DO NOT use examples for:

• Exact reproduction of format or metrics
• Copying layout without considering context
• Applying the same visual style regardless of audience

ALWAYS ask yourself first:

1. "Do you have a preferred format or should I adapt the template style?"
2. "Who is the audience?" (Investment committee, board presentation, quick reference, detailed memo)
3. "What's the key question?" (Valuation, growth analysis, competitive positioning, efficiency)
4. "What's the context?" (M&A evaluation, investment decision, sector benchmarking, performance review)

Adapt based on specifics:

• Industry context: Big tech mega-caps need different metrics than emerging SaaS startups
• Sector-specific needs: Add relevant metrics early (e.g., cloud ARR, enterprise customers, developer ecosystem for tech)
• Company familiarity: Well-known companies may need less background, more focus on delta analysis
• Decision type: M&A requires different emphasis than ongoing portfolio monitoring

Core principle: Use template principles (clear structure, statistical rigor, transparent formulas) but vary execution based on context. The goal is institutional-quality analysis, not institutional-looking templates.

User-provided examples and explicit preferences always take precedence over defaults.

Core Philosophy

"Build the right structure first, then let the data tell the story."

Start with headers that force strategic thinking about what matters, input clean data, build transparent formulas, and let statistics emerge automatically. A good comp should be immediately readable by someone who didn't build it.

Section 1: Document Structure & Setup

Header Block (Rows 1-3)

Row 1: [ANALYSIS TITLE] - COMPARABLE COMPANY ANALYSIS
Row 2: [List of Companies with Tickers] • [Company 1 (TICK1)] • [Company 2 (TICK2)] • [Company 3 (TICK3)]
Row 3: As of [Period] | All figures in [USD Millions/Billions] except per-share amounts and ratios

Why this matters: Establishes context immediately. Anyone opening this file knows what they're looking at, when it was created, and how to interpret the numbers.

Visual Convention Standards (OPTIONAL - User preferences and uploaded templates always override)

IMPORTANT: These are suggested defaults only. Always prioritize:

1. User's explicit formatting preferences
2. Formatting from any uploaded template files
3. Company/team style guides
4. These defaults (only if no other guidance provided)

Suggested Font & Typography:

• Font family: Times New Roman (professional, readable, industry standard)
• Font size: 11pt for data cells, 12pt for headers
• Bold text: Section headers, company names, statistic labels

Suggested Color & Shading:

• Section headers (e.g., "OPERATING STATISTICS & FINANCIAL METRICS"):
• Dark blue background (#17365D or similar navy)
• White bold text
• Full row shading across all columns
• Column headers (e.g., "Company", "Revenue", "Margin"):
• Light blue/gray background (#D9E2F3 or similar pale blue)
• Black bold text
• Centered alignment
• Data rows:
• White background for company data
• Black text for inputs and formulas
• Statistics rows (Maximum, 75th Percentile, etc.):
• Light gray background (#F2F2F2)
• Black text, left-aligned labels

Suggested Formatting Conventions:

• Decimal precision:
• Percentages: 1 decimal (12.3%)
• Multiples: 1 decimal (13.5x)
• Dollar amounts: No decimals, thousands separator (69,632)
• Margins shown as percentages: 1 decimal (68.7%)
• Borders: No borders (clean, minimal appearance)
• Alignment: All metrics center-aligned for clean, uniform appearance
• Cell dimensions: All column widths should be uniform/even, all row heights should be consistent (creates clean, professional grid)

Note: If the user provides a template file or specifies different formatting, use that instead.

Section 2: Operating Statistics & Financial Metrics

Core Columns (Start with these)

1. Company - Names with consistent formatting
2. Revenue - Size metric (can be LTM, quarterly, or annual depending on context)
3. Revenue Growth - Year-over-year percentage change
4. Gross Profit - Revenue minus cost of goods sold
5. Gross Margin - GP/Revenue (fundamental profitability)
6. EBITDA - Earnings before interest, tax, depreciation, amortization
7. EBITDA Margin - EBITDA/Revenue (operating efficiency)

Optional Additions (Choose based on industry/purpose)

• Quarterly vs LTM - Include both if seasonality matters
• Free Cash Flow - For capital-intensive or SaaS businesses
• FCF Margin - FCF/Revenue (cash generation efficiency)
• Net Income - For mature, profitable companies
• Operating Income - For businesses with varying D&A
• CapEx metrics - For asset-heavy industries
• Rule of 40 - Specifically for SaaS (Growth % + Margin %)
• FCF Conversion - For quality of earnings analysis (advanced)

Formula Examples (Using Row 7 as example)

// Core ratios - these are always calculated
Gross Margin (F7): =E7/C7
EBITDA Margin (H7): =G7/C7
 
// Optional ratios - include if relevant
FCF Margin: =[FCF]/[Revenue]
Net Margin: =[Net Income]/[Revenue]
Rule of 40: =[Growth %]+[FCF Margin %]

Golden Rule: Every ratio should be [Something] / [Revenue] or [Something] / [Something from this sheet]. Keep it simple.

Statistics Block (After company data)

CRITICAL: Add statistics formulas for all comparable metrics (ratios, margins, growth rates, multiples).

[Leave one blank row for visual separation]
- Maximum: =MAX(B7:B9)
- 75th Percentile: =QUARTILE(B7:B9,3)
- Median: =MEDIAN(B7:B9)
- 25th Percentile: =QUARTILE(B7:B9,1)
- Minimum: =MIN(B7:B9)

Columns that NEED statistics (comparable metrics):

• Revenue Growth %, Gross Margin %, EBITDA Margin %, EPS
• EV/Revenue, EV/EBITDA, P/E, Dividend Yield %, Beta

Columns that DON'T need statistics (size metrics):

• Revenue, EBITDA, Net Income (absolute size varies by company scale)
• Market Cap, Enterprise Value (not comparable across different-sized companies)

Note: Add one blank row between company data and statistics rows for visual separation. Do NOT add a "SECTOR STATISTICS" or "VALUATION STATISTICS" header row.

Why quartiles matter: They show distribution, not just average. A 75th percentile multiple tells you what "premium" companies trade at.

Section 3: Valuation Multiples & Investment Metrics

Core Valuation Columns (Start with these)

1. Company - Same order as operating section
2. Market Cap - Current market valuation
3. Enterprise Value - Market Cap ± Net Debt/Cash
4. EV/Revenue - How much market pays per dollar of sales
5. EV/EBITDA - How much market pays per dollar of earnings
6. P/E Ratio - Price relative to net earnings

Optional Valuation Metrics (Choose based on context)

• FCF Yield - FCF/Market Cap (for cash-focused analysis)
• PEG Ratio - P/E/Growth Rate (for growth companies)
• Price/Book - Market value vs. book value (for asset-heavy businesses)
• ROE/ROA - Return metrics (for profitability comparison)
• Revenue/EBITDA CAGR - Historical growth rates (for trend analysis)
• Asset Turnover - Revenue/Assets (for operational efficiency)
• Debt/Equity - Leverage (for capital structure analysis)

Key Principle: Include 3-5 core multiples that matter for your industry. Don't include every possible metric just because you can.

Formula Examples

// Core multiples - always include these
EV/Revenue: =[Enterprise Value]/[LTM Revenue]
EV/EBITDA: =[Enterprise Value]/[LTM EBITDA]
P/E Ratio: =[Market Cap]/[Net Income]
 
// Optional multiples - include if data available
FCF Yield: =[LTM FCF]/[Market Cap]
PEG Ratio: =[P/E]/[Growth Rate %]

Cross-Reference Rule

CRITICAL: Valuation multiples MUST reference the operating metrics section. Never input the same raw data twice. If revenue is in C7, then EV/Revenue formula should reference C7.

Statistics Block

Same structure as operating section: Max, 75th, Median, 25th, Min for every metric. Add one blank row for visual separation between company data and statistics. Do NOT add a "VALUATION STATISTICS" header row.

Section 4: Notes & Methodology Documentation

Required Components

Data Sources & Quality:

• Where did the data come from? (S&P Kensho MCP, FactSet MCP, Daloopa MCP, Bloomberg, SEC filings)
• What period does it cover? (Q4 2024, audited figures)
• How was it verified? (Cross-checked against 10-K/10-Q)
• Note: Prioritize MCP data sources (S&P Kensho, FactSet, Daloopa) if available for better accuracy and traceability

Key Definitions:

• EBITDA calculation method (Gross Profit + D&A, or Operating Income + D&A)
• Free Cash Flow formula (Operating CF - CapEx)
• Special metrics explained (Rule of 40, FCF Conversion)
• Time period definitions (LTM, CAGR calculation periods)

Valuation Methodology:

• How was Enterprise Value calculated? (Market Cap + Net Debt)
• What growth rates were used? (Historical CAGR, forward estimates)
• Any adjustments made? (One-time items excluded, normalized margins)

Analysis Framework:

• What's the investment thesis? (Cloud/SaaS efficiency)
• What metrics matter most? (Cash generation, capital efficiency)
• How should readers interpret the statistics? (Quartiles provide context)

Section 5: Choosing the Right Metrics (Decision Framework)

Start with "What question am I answering?"

"Which company is undervalued?" → Focus on: EV/Revenue, EV/EBITDA, P/E, Market Cap → Skip: Operational details, growth metrics

"Which company is most efficient?" → Focus on: Gross Margin, EBITDA Margin, FCF Margin, Asset Turnover → Skip: Size metrics, absolute dollar amounts

"Which company is growing fastest?" → Focus on: Revenue Growth %, EBITDA CAGR, User/Customer Growth → Skip: Margin metrics, leverage ratios

"Which is the best cash generator?" → Focus on: FCF, FCF Margin, FCF Conversion, CapEx intensity → Skip: EBITDA, P/E ratios

Industry-Specific Metric Selection

Software/SaaS: Must have: Revenue Growth, Gross Margin, Rule of 40 Optional: ARR, Net Dollar Retention, CAC Payback Skip: Asset Turnover, Inventory metrics