Comps Analysis

Comparable Company Analysis

A comps table is arithmetic anyone can do and data discipline almost nobody does. The multiples are the easy half: the work is in which companies belong in the set, whether each number measures the same thing over the same period, and how old it is. The steps below build the table in the order those questions have to be answered.

Evidence labels, source tiers, staleness, the readiness posture and the intake limits: .agents/skills/research-conventions/SKILL.md, read before the first figure enters the table.

Period labels, per-company LTM windows, NTM as four quarterly estimates, and one common base date for every comparative return are binding here: .agents/skills/research-conventions/references/market-data-rules.md, read before the first pull. Step 3 adds no rule of its own and says only which of those period cases a comps table selects.

Step 1: Frame the table

Four questions decide what gets built. How many to ask, in what shape, and what to do when no answer comes back: .agents/skills/research-conventions/references/intake.md.

1. Format: their template, or ours
2. Audience: investment committee, board, quick reference, detailed memo
3. The key question: valuation, growth, competitive position, efficiency
4. The context: M&A, an investment decision, sector benchmarking, performance review

The answers change the table, not just its wrapper. A relative-value question needs multiples and quartiles; an efficiency question needs margins and turns and can drop the multiples entirely. Big-cap incumbents and emerging names in the same sector do not take the same metrics. User-provided examples and stated preferences outrank every default in this file.

Done when the question the table answers is written down in one sentence and the metric list follows from it.

Step 2: Build the peer set, with roles

Every peer carries a role, and the roles are tiered so the headline statistic is not diluted by a name that was only ever context.

Headline statistics come from tier 1 alone. Tier 2 appears in the table for context, visibly separated, and never inside the median that drives the selected range. The excluded list is part of the deliverable: a reader who cannot see why the obvious comparable is missing assumes it was missed.

Five to ten names in tier 1 is the working range. Below three, the median is one company's opinion: the selected-range hierarchy in Step 8 falls through to a single named analogue and the output is screen-grade under Step 10. Above ten, the set has stopped being a peer group.

Exclude rather than stretch. The comparability tells: materially different business models labelled as comps, a pure-play sitting beside a conglomerate, a peer whose fiscal year end does not line up and has not been calendarised, negative EBITDA valued on an EBITDA multiple, and a peer whose revenue is recognised on a different basis (gross billings against net revenue).

Done when every name carries a role and a tier, every exclusion carries a reason, and tier 1 holds three or more names or the screen-grade posture in Step 10 applies.

Step 3: Fix the basis before the first pull

Every rule here is cheap now and expensive to retrofit once the table is written.

Reported or adjusted, decided once. One basis for the whole table, applied to every peer, with the adjustments made to each name listed. Reported and adjusted are different metrics, and a table that mixes them compares two things that were never the same. Where a peer only discloses one of the two, either bridge it or mark the row not comparable and say why.

Comparability, per peer and per line. Three states: directly comparable, comparable after a stated adjustment, or not comparable with the reason. The state travels with the cell, not with the company: a peer can be directly comparable on revenue and not comparable on EBITDA.

Denominator pairing. The numerator and the denominator have to belong to the same claim on the business, and to the same period:

The period matches on both sides: an LTM numerator over an NTM denominator is a number with no meaning, however carefully it was computed.

Sign, scale and currency at ingestion. Normalise when the data enters the sheet, not when it is used: one reporting currency for the table, one scale (millions or billions, stated in the header), and one sign convention. Record what was applied per peer, because the alternative is a silent factor of 1,000 in a single row.

FX. Balance-sheet items convert at spot, flow items at the period average, and the table states which currency and which convention. A peer reporting in another currency and translated at spot for revenue overstates or understates growth by the currency move alone.

Calendarisation. The period rule has three cases, and a comps table takes the first for every LTM column:

• an LTM multiple takes each company's own latest four reported quarters, per peer, with the four quarters recorded beside the name. A peer that has reported one quarter further than the subject carries that quarter,
• a calendarised comparison is a separate column of its own, built only for the forward or fiscal-year estimates that need one calendar year: sum the quarters falling inside it and head the column calendarised to CY20XX. An LTM column relabelled as a calendar year is a different metric wearing the label,
• a historical analysis takes the window each figure was true for, carrying its own as-of.

State the method and the window on the table. A peer whose quarterly detail does not support a calendarised column is marked as reporting on its own year end and kept out of the tier 1 statistic for that column.

Done when the basis, the currency, the scale, the FX convention and the calendarisation window are written in the table's header block or its notes, and every peer has been normalised to them.

Step 4: Pull the data, with an as-of on every row

• fundamentals MCP: get_financial_statements(symbol, 'all', 'annual', 5) for the statements, get_financial_ratios(symbol) for ratios, get_growth_metrics(symbol) for growth, get_historical_valuation(symbol) for the enterprise-value history behind the EV multiples (the multiples themselves come from get_financial_ratios)
• get_company_overview for market cap, consensus, price targets and rating distribution
• get_sec_filing where the tool figure and the filing disagree, and the filing settles it

Every row carries an as-of column: the date of the reporting period behind its fundamentals, not the date you pulled them. Prices, market caps and every multiple built on them carry the retrieval date as well, in the header block, since they move daily.

The freshness threshold for each data type, the six staleness states and the conflict register are in .agents/skills/research-conventions/references/evidence.md. Reported financials there stay fresh until the company's next scheduled report; a peer that is past that date and has not filed is past its threshold rather than exempt, and the table below says what a comps row does about it rather than lifting the threshold:

When two sources disagree on the same figure, name both, select by the source tiers in evidence.md, and disclose the discrepancy. Two figures averaged into a third that nobody reports is the failure this rule exists to prevent.

Done when every fundamentals cell has an as-of, every price-derived figure has a retrieval date, and no tier 1 row is beyond four quarters old without a bridge.

Step 5: Share count and the enterprise value bridge

The dilution the market prices is not the basic share count, and the EV a table ships is often nobody's EV.

Dilution protocol, applied per peer and named on the table:

• Options and warrants in the money: treasury stock method. Shares issued on exercise, less the shares repurchasable with the proceeds at the current price. Out-of-the-money grants are excluded, and the strike distribution comes from the equity footnote
• Convertibles in the money: if-converted. Add the conversion shares, and for an earnings-based multiple add back the after-tax interest to the numerator. Out of the money, the instrument stays in debt
• Unvested RSUs: included at their gross count; there are no proceeds to net against
• Show the share count build rather than a single number, and name the method beside it

The EV bridge, reconciled. Compute both sides independently and compare:

Market cap  = price x diluted shares
Enterprise value = market cap + total debt + preferred + minority interest - cash and equivalents

Compare the computed market cap and enterprise value against the reference figures from get_company_overview. A gap beyond 2 percent is a finding, not a rounding difference: it usually means a different share count, a stale price, or a claim (leases, pensions, non-controlling interests) that one side counts and the other does not. Resolve it, or disclose the difference and its cause on the table. Both comparisons are rows on the Checks sheet.

Done when every peer's share count names its method, and the market cap and EV reconciliations are within 2 percent or carry a stated cause.

Step 6: Choose the metrics

Start from the question, not from the list of everything computable.

Core operating columns: company, revenue, revenue growth, gross profit, gross margin, EBITDA, EBITDA margin. Core valuation columns: company, market cap, enterprise value, EV/Revenue, EV/EBITDA, P/E.

Add by sector, and only what changes a conclusion:

The 5-10 rule: five operating metrics, five valuation metrics, ten columns. Past fifteen you are including noise. Include three to five multiples that matter for the sector rather than every multiple that computes.

Done when every column either appears in the metric list written down in Step 1 or is identification or provenance metadata (name, ticker, role and tier, as-of, source), and the count is at or under ten.

Step 7: Build the workbook

Formulas, not hardcoded values. Every derived value (margin, multiple, statistic, implied value) is a live Excel formula referencing the input cells. A number computed in Python and pasted in is a defect even when the value is right today. With openpyxl, cell.value = "=E7/C7" is correct and cell.value = 0.687 is not. The only typed numbers are the raw inputs (revenue, EBITDA, share price, share count, net debt), and every one carries a cell comment naming its source, its as-of, and any adjustment applied. For an assumption rather than a source, the comment carries the reasoning and what would replace it.

Build, present, recalc, audit. Build from a saved Python script using openpyxl, following .agents/skills/xlsx/SKILL.md. Then run python .agents/skills/xlsx/scripts/recalc.py <file> 30 until the status is "success", and python .agents/skills/xlsx/scripts/audit.py <file> --strict and fix every fail.

Three sheets: Cover first, then the comps table, then Checks. The cover's tiles are formulas linking to the selected statistic, the implied enterprise value, the implied equity value and the per-share value, and it is written once those cells are locked.

Present each stage as you finish it rather than delivering the sheet complete. A PTC turn is not chat-interactive at every step, so this is not a blocking question: present the block, say what you are building next, and carry on unless the user objects. A bad peer or a mismatched period surfaces before the statistics and the implied value are built on top of it.

1. Peer set and structure - the names, their roles and tiers, why each is comparable, and the column layout
2. Basis - currency, scale, reported or adjusted, FX and calendarisation conventions
3. Raw inputs - the input block, with the period, as-of and source behind each number
4. Share count and EV bridge - the dilution build and both reconciliations
5. Operating metrics - the calculated margins and growth rates
6. Valuation multiples - the multiples, against the sanity ranges in Step 9
7. Statistics and selected range - the quartile block, the outlier decisions, the selected statistic
8. Checks sheet - the roll-up cell reading OK

Header block (rows 1 to 3):

Row 1: [ANALYSIS TITLE] - COMPARABLE COMPANY ANALYSIS
Row 2: [Company 1 (TICK1)] | [Company 2 (TICK2)] | [Company 3 (TICK3)]
Row 3: Prices as of [date] | Financials as of [period] | [USD millions] | [reported or adjusted] basis

Ratios: every one is [something] / [revenue] or [something] / [something on this sheet]. Gross Margin (F7): =E7/C7, EBITDA Margin (H7): =G7/C7, Rule of 40 on FCF margin: =[growth %]+[FCF margin %], named that way on the sheet so it is never confused with the EBITDA-margin variant.

Cross-reference rule: valuation multiples reference the operating cells. Never input the same raw number twice. If revenue is in C7, EV/Revenue divides by C7.

Done when the workbook rebuilds from the saved script, audit.py --strict reports no fail, and no derived cell holds a typed number.