Best Practices for Building Comps and Precedent Transaction Models in Excel

Introduction

Comparable company analysis and precedent transaction analysis are the most frequently built models in investment banking. They are also the first models most analysts learn to build, making them a critical skill for career success. While the analytical framework is covered in the earlier sections of this guide, this article focuses on the practical execution: how to build these models in Excel so they are accurate, maintainable, and presentation-ready.

Tab Structure

A clean comps model has three main sections (which can be on one tab for a simple analysis or separated across tabs for a larger peer group):

Market data section: Share price, diluted shares, equity value, net debt components (debt, preferred, minority interests, cash), and enterprise value. Each EV bridge item should be a separate cell so the bridge can be audited.

Financial data section: Revenue, EBITDA, EBIT, net income for LTM and NTM periods. Operating metrics (growth rates, margins, ROIC) provide context. If companies have different fiscal year-ends, calendarization is needed.

Multiples section: Calculated multiples (EV/EBITDA, EV/Revenue, P/E, etc.) for each time period, with summary statistics (mean, median, 25th percentile, 75th percentile, high, low) at the bottom.

Data Sources and Updates

Market data (share prices) should be refreshed before any presentation. Most banks use Bloomberg or Capital IQ links that update automatically when the file is opened. For models without live links, the analyst manually updates share prices from the most recent close.

Consensus estimates (NTM, CY+1) are sourced from Bloomberg, FactSet, or Capital IQ. These update as sell-side analysts revise their models, so NTM multiples can shift even if the share price is unchanged.

Handling Special Situations

NM (Not Meaningful) in Comps Tables

A designation used when a calculated multiple is not analytically useful, most commonly when the denominator is negative (producing a negative multiple) or very close to zero (producing an extremely large multiple). In comps tables, NM replaces the calculated value for the affected company, and that data point is excluded from the summary statistics (mean, median, percentiles). The most common NM situations are negative EBITDA (for pre-profit companies), negative net income (for money-losing companies), and extremely high multiples that distort the average (e.g., a 200x P/E from near-zero earnings). Without proper NM treatment, a single outlier can make the median and mean meaningless for the entire peer group.

The precedent transaction model shares many structural elements with the comps model but has several unique requirements:

Reconstructing Transaction Values

For each deal, the analyst must reconstruct the implied enterprise value from the offer terms: offer price x diluted shares = equity value, then add net debt to get EV. This is more labor-intensive than comps because each deal requires researching the specific terms from press releases, merger proxies, and data providers.

Time-Stamping Financial Data

The financial data must be as of the announcement date, not the current date. If a deal was announced in March 2024, the LTM EBITDA should be the twelve months ending closest to March 2024, not the twelve months ending today. This requires looking up the historical financials for each deal, which can be time-consuming for older transactions. Data providers like Capital IQ and Bloomberg maintain historical financials, but the analyst should cross-reference against the original SEC filings (10-K, 10-Q, or merger proxy) to verify accuracy, particularly for deals where the target was subsequently integrated and its historical filings are no longer easily accessible.

Premium Analysis

In addition to multiples, include a premium paid analysis showing the offer price premium to the target's undisturbed stock price at 1-day, 1-week, and 4-week lookback windows. This data provides context for the control premium that the current deal might command.

Master Comps File

A comprehensive, continuously maintained Excel workbook that contains the trading comps for a bank's entire coverage universe within a sector. Investment banking groups maintain master comps files that are updated regularly (ideally after each earnings season) so that any deal team can pull the latest comps for their specific engagement without building from scratch. The master file is typically maintained by the most junior analyst in the group and serves as a shared resource for pitchbooks, client updates, and live deal work. Keeping the master file current and accurate is one of the most important and most thankless analyst responsibilities.

The final comps or precedent transaction table should be formatted for direct inclusion in a pitchbook: