- If S&P Kensho MCP, FactSet MCP, or Daloopa MCP are available, use them exclusively for financial and trading information
DO NOT use web search
if the above MCP data sources are available
ONLY if MCPs are unavailable:
Then use Bloomberg Terminal, SEC EDGAR filings, or other institutional sources
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:
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:
User's explicit formatting preferences
Formatting from any uploaded template files
Company/team style guides
These defaults (only if no other guidance provided)
Suggested Font & Typography:
Font family
Times New Roman (professional, readable, industry standard)
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.
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
Format cells (blue for inputs, black for formulas)
Lock in units and date references
Gather data
(60-90 minutes)
Pull from primary sources (S&P Kensho MCP, FactSet MCP, Daloopa MCP if available; otherwise Bloomberg, SEC)
Input all raw numbers in blue
Document sources in notes section
Build formulas
(30 minutes)
Start with simple ratios (margins)
Progress to multiples (EV/Revenue)
Add cross-checks (do margins make sense?)
Add statistics
(15 minutes)
Copy formula structure for all columns
Verify ranges are correct (B7:B9, not B7:B10)
Check quartile logic
Quality control
(30 minutes)
Run sanity checks
Verify formula references
Check for #DIV/0! or #REF! errors
Compare against known benchmarks
Documentation
(15 minutes)
Complete notes section
Add data sources
Define methodologies
Date-stamp the analysis
Pro Tips
Save templates
Build once, reuse forever
Color-code outliers
Conditional formatting for values >2 standard deviations
Link to source files
Hyperlink to Bloomberg screenshots or SEC filings
Version control
Save as "Comps_v1_2024-12-15" with clear dating
Collaborative reviews
Have someone else check your formulas
Excel Formatting Checklist (Optional - adapt to user preferences)
Font set to user's preferred style (default: Times New Roman, 11pt data, 12pt headers)
Section headers formatted per user's template (default: dark blue #17365D with white bold text)
Column headers formatted per user's template (default: light blue/gray #D9E2F3 with black bold text)
Statistics rows formatted per user's template (default: light gray #F2F2F2)
No borders applied (clean, minimal appearance)
Column widths set to uniform/even width
(creates clean, professional appearance)
Row heights set to consistent height
(typically 20-25pt for data rows)
Numbers formatted with proper decimal precision and thousands separators
All metrics center-aligned
for clean, uniform appearance
One blank row for separation between company data and statistics rows
No separate "SECTOR STATISTICS" or "VALUATION STATISTICS" header rows
Every hard-coded input cell has a comment with either: (1) exact data source, OR (2) assumption explanation
Hyperlinks added to cells where applicable
(SEC filings, data provider pages, reports)
Section 8: Example Template Layout
Simple Version (Start here):
┌─────────────────────────────────────────────────────────────┐
│ TECHNOLOGY - COMPARABLE COMPANY ANALYSIS │
│ Microsoft • Alphabet • Amazon │
│ As of Q4 2024 | All figures in USD Millions │
├─────────────────────────────────────────────────────────────┤
│ OPERATING METRICS │
├──────────┬─────────┬─────────┬──────────┬──────────────────┤
│ Company │ Revenue │ Growth │ Gross │ EBITDA │ EBITDA │
│ │ (LTM) │ (YoY) │ Margin │ (LTM) │ Margin │
├──────────┼─────────┼─────────┼──────────┼─────────┼────────┤
│ MSFT │ 261,400 │ 12.3% │ 68.7% │ 205,100 │ 78.4% │
│ GOOGL │ 349,800 │ 11.8% │ 57.9% │ 239,300 │ 68.4% │
│ AMZN │ 638,100 │ 10.5% │ 47.3% │ 152,600 │ 23.9% │
│ │ │ │ │ │ │ [blank row]
│ Median │ =MEDIAN │ =MEDIAN │ =MEDIAN │ =MEDIAN │=MEDIAN │
│ 75th % │ =QUART │ =QUART │ =QUART │ =QUART │=QUART │
│ 25th % │ =QUART │ =QUART │ =QUART │ =QUART │=QUART │
├─────────────────────────────────────────────────────────────┤
│ VALUATION MULTIPLES │
├──────────┬──────────┬──────────┬──────────┬────────────────┤
│ Company │ Mkt Cap │ EV │ EV/Rev │ EV/EBITDA │ P/E│
├──────────┼──────────┼──────────┼──────────┼───────────┼────┤
│ MSFT │3,550,000 │3,530,000 │ 13.5x │ 17.2x │36.0│
│ GOOGL │2,030,000 │1,960,000 │ 5.6x │ 8.2x │24.5│
│ AMZN │2,226,000 │2,320,000 │ 3.6x │ 15.2x │58.3│
│ │ │ │ │ │ │ [blank row]
│ Median │ =MEDIAN │ =MEDIAN │ =MEDIAN │ =MEDIAN │=MED│
│ 75th % │ =QUART │ =QUART │ =QUART │ =QUART │=QRT│
│ 25th % │ =QUART │ =QUART │ =QUART │ =QUART │=QRT│
└──────────┴──────────┴──────────┴──────────┴───────────┴────┘
Add complexity only when needed:
Include quarterly AND LTM if seasonality matters
Add FCF metrics if cash generation is key story
Include industry-specific metrics (Rule of 40 for SaaS, etc.)
Add more statistics rows if you have >5 companies
Section 9: Industry-Specific Additions (Optional)
Only add these if they're critical to your analysis. Most comps work fine with just core metrics.
Software/SaaS:
Add if relevant: ARR, Net Dollar Retention, Rule of 40
Financial Services:
Add if relevant: ROE, Net Interest Margin, Efficiency Ratio
E-commerce:
Add if relevant: GMV, Take Rate, Active Buyers
Healthcare:
Add if relevant: R&D/Revenue, Pipeline Value, Patent Timeline
Manufacturing:
Add if relevant: Asset Turnover, Inventory Turns, Backlog
Section 10: Red Flags & Warning Signs
Data Quality Issues
🚩 Inconsistent time periods (mixing quarterly and annual)
🚩 Missing data without explanation
🚩 Significant differences between data sources (>10% variance)
Valuation Red Flags
🚩 Negative EBITDA companies being valued on EBITDA multiples (use revenue multiples instead)
🚩 P/E ratios >100x without hypergrowth story
🚩 Margins that don't make sense for the industry
Comparability Issues
🚩 Different fiscal year ends (causes timing problems)
🚩ixing pure-play and conglomerates
🚩 Materially different business models labeled as "comps"
When in doubt, exclude the company.
Better to have 3 perfect comps than 6 questionable ones.
Section 11: Formulas Reference Guide
Essential Excel Formulas
// Statistical Functions
=AVERAGE(range) // Simple mean
=MEDIAN(range) // Middle value
=QUARTILE(range, 1) // 25th percentile
=QUARTILE(range, 3) // 75th percentile
=MAX(range) // Maximum value
=MIN(range) // Minimum value
=STDEV.P(range) // Standard deviation
// Financial Calculations
=B7/C7 // Simple ratio (Margin)
=SUM(B7:B9)/3 // Average of multiple companies
=IF(B7>0, C7/B7, "N/A") // Conditional calculation
=IFERROR(C7/D7, 0) // Handle divide by zero
// Cross-Sheet References
='Sheet1'!B7 // Reference another sheet
=VLOOKUP(A7, Table1, 2) // Lookup from data table
=INDEX(MATCH()) // Advanced lookup
// Formatting
=TEXT(B7, "0.0%") // Format as percentage
=TEXT(C7, "#,##0") // Thousands separator
Common Ratio Formulas
Gross Margin = Gross Profit / Revenue
EBITDA Margin = EBITDA / Revenue
FCF Margin = Free Cash Flow / Revenue
FCF Conversion = FCF / Operating Cash Flow
ROE = Net Income / Shareholders' Equity
ROA = Net Income / Total Assets
Asset Turnover = Revenue / Total Assets
Debt/Equity = Total Debt / Shareholders' Equity
Key Principles Summary
Structure drives insight
- Right headers force right thinking
Less is more
- 5-10 metrics that matter beat 20 that don't
Choose metrics for your question
- Valuation analysis ≠ efficiency analysis
Statistics show patterns
- Median/quartiles reveal more than average
Transparency beats complexity
- Simple formulas everyone understands
Comparability is king
- Better to exclude than force a bad comp
Document your choices
- Explain which metrics and why in notes section
Output Checklist
Before delivering a comp analysis, verify:
All companies are truly comparable
Data is from consistent time periods
Units are clearly labeled (millions/billions)
Formulas reference cells, not hardcoded values
All hard-coded input cells have comments with either: (1) exact data source with citation, OR (2) clear assumption with explanation
Hyperlinks added where relevant
(SEC EDGAR filings, Bloomberg pages, research reports)
Statistics include at least 5 metrics (Max, 75th, Med, 25th, Min)
Notes section documents sources and methodology
Visual formatting follows conventions (blue = input, black = formula)
Sanity checks pass (margins logical, multiples reasonable)
Date stamp is current ("As of [Date]")
Formula auditing shows no errors (#DIV/0!, #REF!, #N/A)
Continuous Improvement
After completing a comp analysis, ask:
Did the statistics reveal unexpected insights?
Were there any data gaps that limited analysis?
Did stakeholders ask for metrics you didn't include?
How long did it take vs. how long should it take?
What would make this more useful next time?
The best comp analyses evolve with each iteration. Save templates, learn from feedback, and refine the structure based on what decision-makers actually use.