Comparable Company Analysis (Comps) Template in Excel (.xlsx)
An institutional relative valuation model benchmarking public peer trading multiples across Enterprise Value (EV) and Equity Value metrics. Features dynamic capital structure bridges, Trailing Twelve Month (TTM/LTM) calendarization schedules, non-GAAP footnote normalizations, and median-implied valuation ranges engineered to investment banking standards.
Download Institutional Trading Comps Model (.xlsx)
Pre-formatted matrix with formulas for EV calculation, peer benchmarking, outlier trimming, and implied share pricing
1. The Complete Enterprise Value (EV) Bridge
To compare peer enterprises with heterogeneous capital structures, relative valuation cannot rely on raw Equity Value (Market Capitalization). A highly levered company will appear deceptively inexpensive on simple price multiples while carrying substantial senior debt obligations. Institutional analysts neutralize capital structure variations by evaluating Enterprise Value (EV)—the total theoretical takeover cost representing claims from all capital providers:
Fully Diluted Shares: The Treasury Stock Method (TSM)
Using basic common shares outstanding severely understates share count and distorts equity value. Wall Street standards require the Treasury Stock Method for in-the-money options and warrants:
Diluted Shares = Basic Shares + Total In-The-Money Options - [ (Options × Strike Price) / Current Share Price ] + Unvested RSUs
The proceeds received from assumed option exercises are presumed to be used by the corporation to repurchase common shares in the open market at prevailing spot prices, thereby partially offsetting dilution.
2. Valuation Multiples & Denominator Matching Discipline
A cardinal rule of institutional financial analysis is the matching principle: the numerator and denominator of every valuation multiple must address the exact same stakeholder claims. Enterprise Value must strictly be divided by operational, pre-interest metrics, while Equity Value must strictly be divided by post-debt, post-tax metrics:
| Multiple | Denominator | Capital Structure Neutral? | Primary Sector Application |
|---|---|---|---|
| EV / EBITDA | Operating Cash Flow Proxy | Yes | M&A, LBOs, Industrials, Telecom, Consumer |
| EV / EBIT | True Pre-Tax Operational Profit | Yes | Capital-intensive manufacturing, automotive, energy |
| EV / Revenue | Top-Line Gross Sales | Yes | High-growth SaaS, early-stage cloud software, biotech |
| EV / Unlevered FCF | NOPAT + D&A - CapEx - ΔNWC | Yes | Mature cash cows, semiconductors, infrastructure |
| P / E (Price to Earnings) | Diluted Net Income | No (Distorted by Debt) | Commercial banking, insurance, asset management |
| P / B (Price to Book) | Shareholders' Common Equity | No (Asset-Sensitive) | Financial institutions, REITs, holding companies |
| PEG Ratio | P/E / Expected EPS Growth Rate | No | Growth-at-a-reasonable-price (GARP) equity screening |
3. LTM / TTM Calendarization Mechanics
In public comparable sets, peer companies frequently operate on asynchronous fiscal calendars. For example, in enterprise technology:
- Apple (AAPL) concludes its fiscal year on the last Saturday of September.
- Microsoft (MSFT) concludes its fiscal year on June 30th.
- NVIDIA (NVDA) concludes its fiscal year on the final Sunday of January.
- Alphabet (GOOGL) & Meta (META) conclude their fiscal years on December 31st.
Directly comparing unadjusted full-year filings introduces severe temporal distortions. Institutional analysts normalize all operating results to a uniform Last Twelve Months (LTM or TTM) baseline using the SEC 10-Q bridging formula:
4. Statistical Normalization, Outlier Detection & Footnote Adjustments
Raw GAAP statements include transitory gains, litigation settlements, restructuring charges, and non-cash equity compensation that obscure ongoing earning power. Investment banks apply rigorous footnote adjustments prior to computing multiples:
Stock-Based Compensation (SBC)
While management teams routinely add back SBC to present higher "Adjusted EBITDA", SBC is a permanent dilutive operating cost. CALIO models present both GAAP EBITDA and Management Adjusted EBITDA side-by-side to expose genuine shareholder dilution.
Non-Recurring & Litigation Items
One-off legal settlements, severance payments, facility closure costs, and advisory fees from M&A transactions are removed from operating income to reflect true normalized ongoing run-rate profitability.
Central Tendency: Why Medians Outperform Arithmetic Means
In small peer sets (5 to 10 companies), an extreme multiple from a distressed firm or high-momentum outlier can skew an arithmetic average by 30% to 50%. Institutional valuation desks rely on the Median and Harmonic Mean:
- Median: The exact 50th percentile of the sorted peer multiples. Robust against extreme skewness and negative earnings outliers.
- Harmonic Mean: Mathematically proven for price ratios ($P/E$, $EV/EBITDA$):
Harmonic Mean = n / Σ(1 / x_i). Prevents upward bias caused by taking the arithmetic average of inverted performance numbers. - Interquartile Range (IQR): Benchmarking across the 25th percentile (low end of fair value) and 75th percentile (premium valuation tier).
5. Institutional Excel Modeling Mechanics & Dynamic Formulas
The downloadable template enforces rigorous Wall Street spreadsheet architecture:
- Dynamic Median Multiples: Uses
=MEDIAN(Range)and=PERCENTILE.INC(Range, 0.25)to establish defensible valuation brackets. - Implied Share Price Derivation: Applies benchmark peer multiples back to target company LTM metrics:
Implied EV = Target EBITDA × Peer Median EV/EBITDA, followed by the reverse EV-to-Equity Value bridge:Implied Share Price = (Implied EV - Net Debt) / Diluted Shares. - Financial Modeling Color Coding: Blue font indicates hardcoded inputs (stock prices, shares), Black font indicates formula calculations, and Green font highlights dynamic internal cross-sheet links.
6. Institutional Multi-Peer Benchmark Matrix Preview
Below is a preview of the pre-formatted comparative matrix included in the downloadable .xlsx workbook, illustrating trading metrics across leading enterprise software infrastructure companies:
| Company (Ticker) | Share Price | Market Cap ($M) | Enterprise Value ($M) | LTM Rev ($M) | LTM EBITDA ($M) | EV / Rev | EV / EBITDA | P / E (x) |
|---|---|---|---|---|---|---|---|---|
| CrowdStrike (CRWD) | $320.50 | $78,200 | $74,800 | $3,950 | $1,050 | 18.9x | 71.2x | 84.5x |
| Datadog (DDOG) | $124.80 | $41,600 | $38,900 | $2,680 | $680 | 14.5x | 57.2x | 68.1x |
| Palo Alto Networks (PANW) | $365.00 | $118,500 | $115,200 | $8,030 | $2,450 | 14.3x | 47.0x | 52.4x |
| Snowflake (SNOW) | $132.40 | $44,200 | $40,800 | $3,480 | $310 | 11.7x | 131.6x | N/M |
| Cloudflare (NET) | $88.50 | $30,100 | $28,600 | $1,660 | $290 | 17.2x | 98.6x | 112.0x |
| Zscaler (ZS) | $195.00 | $29,600 | $27,200 | $2,170 | $480 | 12.5x | 56.7x | 64.2x |
| 25th Percentile | — | $30,225 | $29,175 | $2,298 | $353 | 12.9x | 56.8x | 64.2x |
| Peer Median | — | $42,900 | $39,850 | $3,080 | $580 | 14.4x | 64.2x | 68.1x |
| 75th Percentile | — | $69,700 | $66,300 | $3,833 | $958 | 16.5x | 91.8x | 84.5x |
Frequently Asked Questions (Relative Valuation & Comps)
Why is EV/EBITDA preferred over Price-to-Earnings (P/E) in institutional valuation?
EV/EBITDA is capital-structure neutral because Enterprise Value reflects the claims of all capital providers (both debt and equity), while EBITDA measures core operational profitability before debt interest, depreciation accounting conventions, and jurisdictional tax rates. In contrast, P/E uses Net Income, which is heavily distorted by varying leverage ratios, interest expense, and legacy non-cash depreciation schedules, making peer comparisons less reliable across diverse capital structures.
How do you calendarize financial results when peer companies report on different fiscal year ends?
When peers have asynchronous fiscal years (e.g., Apple ending in September, Microsoft in June, Nvidia in January), analysts calendarize financials to a uniform Trailing Twelve Months (TTM/LTM) baseline. The formula is: LTM = Latest Available Cumulative Period + Prior Full Fiscal Year - Prior Comparable Period. For forward calendarization, analysts linearly interpolate consensus estimates across overlapping quarters to match the target firm's fiscal calendar.
How does the ASC 842 lease accounting standard affect Enterprise Value and EBITDA multiples?
Under US-GAAP ASC 842 and IFRS 16, operating leases are brought onto the balance sheet as Right-of-Use (ROU) assets and operating lease liabilities. If an analyst treats operating lease liabilities as funded debt when bridging to Enterprise Value, they must also ensure lease interest and amortization are added back to operating income to prevent denominator mismatch. CALIO's institutional models explicitly break out operating lease liabilities so analysts can evaluate multiples on both pre-lease and post-lease bases.
Why do institutional analysts rely on the median or harmonic mean rather than the arithmetic mean for peer multiples?
Arithmetic means are heavily skewed by positive outliers (e.g., a high-flying turnaround company trading at 150x EBITDA will severely distort a 6-company peer group mean). The median provides a robust measure of central tendency that is resistant to extreme outliers. For price-scaled valuation ratios, the harmonic mean is mathematically superior because it prevents upward bias when averaging inverted valuation multiples.
Instant Peer Benchmarks Across 25 Sectors
CALIO continuously computes US-GAAP XBRL multiples across semiconductors, enterprise software, banking, retail, and energy with zero manual data entry.