Comps analysis

Comps Analysis

Overview

Create a public comps analysis that is structurally clean, numerically defensible, and presentation-ready. Treat the workbook as a database-first model: build a full DATABASE section first, pull the displayed comps from that database with references or lookup formulas, calculate growth, margins, and trading multiples from the referenced data, and then drive the implied share price bridge from the displayed trading multiples plus target inputs.

Read [references/workbook_format.md]when building or reformatting the sheet layout, fonts, fills, borders, and conditional formatting. Read [references/model_construction.md] when deciding which cells are database pulls, hardcodes, or formulas. Read [references/calculation_guide.md] when mapping formulas, deciding whether a multiple is meaningful, or checking valuation outputs.

Workflow

1. Define the frame. Capture the target company, audience, task, valuation date, reporting basis (FY, CY, LTM, NTM), currency, units, and the fiscal years to show. Do not mix bases inside one table unless the user explicitly asks for it, and label any mix clearly. Before building anything, lock one explicit Year Set for the entire model, for example 2025A / 2026E / 2027E or CY2026E / CY2027E. Treat this Year Set as a hard constraint for the selected comps table, analytics, valuation bridge, and database headers. If the user asks for a specific year, such as 2026, FY26, CY26, 26E, or a 2026 ticker, that year request overrides any generic default. Do not silently fall back to more familiar years such as 2023 and 2024.

2. Build the database first. Start with a broader universe than the final peer set. Include ticker, company name, market data, capital structure fields, and historical or forward operating metrics needed for the multiples. This block must sit below the main output and act as the source of truth for the displayed comps table above.

3. Build the displayed comps table by reference. Choose the peer subset that best matches business model, end market, geography, margin profile, scale, and growth profile. A typical top table contains about 6 to 10 names. Pull the base fields for each selected company from the database by direct references, XLOOKUP, INDEX/MATCH, or VLOOKUP. Do not manually retype values in the top table if the database exists.

4. Calculate the analytical blocks from the pulled data. Compute the analytical blocks that fit the audience, industry, and task from the pulled data. Revenue Growth, EBITDA Margin, Profit Margin, and Trading Multiples are the default public-equity set, but do not force the same metrics across all sectors. Keep formula logic in the analysis area instead of hardcoding computed outputs. Show NM or -- when a metric is not meaningful, such as negative EBITDA for EV / EBITDA or negative earnings for P / E.

5. Derive valuation outputs. Use min, mean, median, and max selected-peer multiples to value the target. The valuation bridge should combine references from the selected comps summary rows, target hardcodes or assumption cells, and formulas. Show the target's financial metric, implied enterprise value, implied equity value, and implied share price. If a multiple values equity directly, such as P / E, skip enterprise value and go straight to implied price or implied equity value.

6. Review before delivery. Check that units, signs, dates, and currencies are consistent; statistics exclude NM; the top table traces back to the database; hardcodes are visibly distinct from formulas; and the implied valuation range is directionally reasonable relative to the current share price.

Output Rules

• Default to one clean comps sheet rather than splitting across many tabs unless the user asks otherwise.
• Preserve the order and visual hierarchy in [references/workbook_format.md].
• Always include a DATABASE section when the sheet contains a comps table. Do not omit it unless the user explicitly requests a presentation-only output.
• Treat the database as the source block and the top table as a selected view of that source block.
• Prefer explicit labels such as Revenue, EBITDA, Net Income, EV / Revenue, EV / EBITDA, and P / E.
• Use the same year labels across the whole sheet. If the table uses 2024 and 2025 multiples, the target valuation bridge should use those same years.
• When the user explicitly names a year window, preserve that exact year window across the whole sheet. For example, if the user wants 2026 trading metrics, the workbook should continue to show 2026-based headers and calculations.
• If data for a requested year is unavailable for some peers, keep the requested year labels and show --, NM, or an explicit coverage note. Never replace the requested year with an older year without clearly saying so.
• Use Mean and Median summary rows in the selected comps table.
• Highlight Mean and Median in the selected comps table, but do not carry that green highlight into the implied share price bridge.
• Extend the selected-comps Mean and Median highlight across the full table width and wrap the two-row highlighted block with one thin outer border.
• Exclude blanks and NM entries from summary statistics rather than coercing them to zero.
• Use formatting to distinguish cell types: hardcodes, formula outputs, and section headers should not all look the same.
• Keep header content centered and merge cells where the visual grouping requires it.
• Surface data limitations directly in the output if the peer set contains thin coverage, outlier capital structures, or accounting inconsistencies.

Decision Rules

• Prefer operating comparability over brand recognition when the two conflict.
• Exclude companies with clearly broken comparability from the selected set even if they are in the database.
• Keep loss-making or negative-EBITDA names only when they are economically relevant; otherwise, explain the exclusion.
• Use enterprise-value multiples for capital-structure-neutral comparison and P / E only when earnings are positive and not distorted by one-off items.
• If the user does not specify a basis or year set, default to the most recently completed full-year basis for broad comps tables.
• If the user specifies a year set, anchor year, or forward year, that instruction takes priority over the generic default-to-latest rule.
• Do not freeze one universal metric set across industries. Choose metrics based on audience, industry, and task.
• For Big Tech comps, default to scale, growth, margin, cash generation, and large-cap trading metrics that matter for mature platform businesses.
• For SaaS comps, shift emphasis toward recurring-revenue growth, profitability progression, and revenue-based valuation metrics; many SaaS tables will care more about EV / Revenue than P / E.
• Apply the same logic to other sectors: banks, insurers, REITs, energy, semis, consumer, industrials, and internet names may each need different operating and valuation metrics.

Modeling Rules

• Build the DATABASE first and only then build the top comps table.
• Define the exact Year Set before laying out headers or writing formulas. Every year-bearing section in the workbook must reuse that same locked set.
• Use lookup or direct-link formulas for top-table raw data fields such as Current Price, Share Count, Enterprise Value, Market Cap, Revenue, EBITDA, and Net Income.
• Calculate Revenue Growth, EBITDA Margin, Profit Margin, and Trading Multiples from the linked raw data instead of linking precomputed outputs from elsewhere when the formulas are straightforward.
• If a column is labeled 2026, FY26, CY26, or 26E, the underlying numerator, denominator, and bridge references must point to that same period. Do not label a column 2026 while feeding it 2023 or 2024 data.
• Adjust which analytical columns appear based on audience, industry, and task. Keep the layout discipline fixed, but let the metric set change when the sector demands it.
• Use blue font rgb(79, 113, 190) for manual hardcodes and target inputs, black font for formulas and calculated outputs, and keep ticker labels visually distinct from numeric inputs.
• Use explicit grouped headers so every year column is clearly tied to its metric. Do not leave trading-multiple columns or second-year columns visually unlabeled.
• Keep the title input box minimal by default: Date and Currency are standard; do not add Basis unless the user requests it.
• Use thin black header borders by default rather than gray borders.
• Use a deep navy-blue DATABASE divider bar with white text.
• Turn off worksheet gridlines in the delivered Excel output.
• Format title-box dates in slash style yyyy/m/d, for example 2022/7/23, rather than long English month names.
• Format trading-multiple cells with an explicit Excel custom number format that appends a literal x, for example 0.0\"x\".
• Use conditional formatting only in the analytical blocks where relative performance matters; do not heatmap the entire database.
• Use -- or NM for non-meaningful cells rather than leaving broken formulas or Excel errors visible.

Deliverable Shape

When returning the result in Excel or spreadsheet form, produce:

1. A title block with the sheet title, date, and currency.
2. A selected comps table whose raw data fields are pulled from the database.
3. Analytical blocks for growth, margins, and trading multiples that calculate from the displayed data.
4. A valuation bridge showing min, mean, median, and max implied values for the target.
5. A larger DATABASE table beneath the main output.

When returning the result in text form, summarize:

1. Selected peers and why they fit.
2. Mean and median trading multiples.
3. Implied valuation range and share-price range.
4. The locked Year Set used in the workbook.
5. Any caveats that materially affect interpretation, especially if requested-year coverage is incomplete.