Precedent Transactions Analysis in Excel: Build the Comps Table and Multiples Step by Step

Precedent transactions analysis values a company based on what acquirers have actually paid for control of comparable businesses. You build a comps table of past deals, convert each deal price into enterprise value multiples (EV/Revenue, EV/EBITDA) using the target's financials at announcement, and apply the median and quartile range to your own target. Because these prices include a control premium, the method usually lands at the top of a valuation football field.

This guide walks through building a precedent transactions analysis in Excel step by step. It uses four real technology deals with public terms to show where analysts usually make mistakes. Those mistakes cluster in three places: unaffected share prices, stock consideration, and assumed debt.

What Precedent Transactions Analysis Measures and When to Use It

Trading comps tell you what a minority stake in a business is worth today. Precedent transactions tell you what a buyer paid to own the whole thing, so the multiple includes a control premium and usually some expected synergies. The CorpDev.ai valuation wiki puts transaction multiples at roughly 20% to 40% above trading multiples. Practitioner guides cite typical control premiums in a similar 20% to 40% band, depending on sector and deal dynamics.

Use the method when:

You are advising on a sale and need to frame what a buyer would pay for control.
You are writing a fairness opinion or board deck that needs a market-tested range.
A sponsor or strategic buyer is pressure-testing its bid against recent sector pricing.
You need a sanity check on DCF terminal value assumptions.

The weaknesses are known. Synergies are rarely disclosed, data on private targets is thin, and a deal from 2021 was priced under different interest rates than one from 2024. Each of these shows up in specific columns of the spreadsheet model, which is why the layout matters.

Takeaway: Before you open Excel, write down the question the analysis must answer (sale price range, fairness, bid check). That decides how tight your screening criteria need to be.

Step 1: Screen the Deal Set for Your Precedent Transactions Analysis

A comps table is only as good as its deal set. Ryan O'Connell's precedent transactions guide recommends screening on industry, business model and size, with target revenue roughly 0.25x to 3x of your company's. In practice, follow this sequence:

Define the sector tightly. "Software" is too broad. "Infrastructure and security software sold to enterprises" is usable.
Set a time window. Five years is standard. Go back further only if deal flow is sparse, and flag older deals in a separate column.
Apply a size band. Use the 0.25x to 3x revenue rule as a starting filter.
Separate buyer types. Strategic and financial buyers price differently. Tag each deal so you can run medians on each group.
Exclude distressed and minority deals. A 20% stake purchase or a bankruptcy sale will distort a control multiple.
Target 8 to 15 deals. Fewer than six and one outlier moves the median. More than 20 usually means your screen is too loose.

For a large-cap infrastructure software example, a reasonable shortlist from 2022 to 2024 includes these four announced deals:

Broadcom / VMware, announced 26 May 2022, about $61 billion including $8 billion of assumed VMware net debt (Help Net Security, Reuters).
Cisco / Splunk, announced 21 September 2023, $157 per share in cash, about $28 billion (TechCrunch).
HPE / Juniper Networks, announced 9 January 2024, $40.00 per share in cash, about $14 billion equity value (HPE 8-K filed with the SEC).
Synopsys / Ansys, announced 16 January 2024, about $35 billion enterprise value, $197.00 cash plus 0.3450 Synopsys shares per Ansys share (Goodwin press release).

Takeaway: Put your screening criteria in a header block above the table. When a managing director asks why a deal is missing, the answer is already on the page.

Step 2: Build the Precedent Transactions Comps Table in Excel

Lay out one row per deal and keep inputs, calculations and outputs in visually distinct columns. Use blue font for hard-coded inputs and black for formulas, the standard banking convention. A working column structure:

Deal identification (columns A to F)

Announcement date
Acquirer
Target
Buyer type (Strategic / Sponsor)
Consideration type (Cash / Stock / Mixed)
Source document (8-K, merger proxy, press release URL)

Price inputs (columns G to L)

Offer price per share
Unaffected share price and its date
Diluted shares outstanding (treasury stock method on options and RSUs)
Total debt, preferred stock and minority interest assumed
Cash and equivalents acquired

Target financials at announcement (columns M to P)

LTM revenue
LTM EBITDA (adjusted, with your adjustments documented)
NTM revenue and EBITDA consensus, if available

Outputs (columns Q onward)

Equity value, enterprise value
EV/LTM Revenue, EV/LTM EBITDA, EV/NTM EBITDA
Premium to unaffected price

The LTM financials must be as of the announcement date, not today. The CorpDev.ai wiki flags this as a common error. For Juniper, that means the trailing twelve months reported in filings before 9 January 2024, which you build from the latest 10-K plus subsequent 10-Qs: LTM = FY + YTD current − YTD prior.

Takeaway: Add a source column for every row and link to the exact filing. Reviewers check those first, and unsourced rows get cut from the final deck.

Step 3: Calculate the Multiples and Premiums Step by Step

This is where the formulas go. Build them once in the first row, then copy down.

Equity value. =Offer_Price * Diluted_Shares. For cash deals this is simple. For stock or mixed deals, value the stock component at the acquirer's share price on announcement, and record that price in its own input cell.
Enterprise value. =Equity_Value + Debt + Preferred + Minority_Interest - Cash. Broadcom/VMware shows why this matters: the $61 billion headline included $8 billion of VMware net debt that Broadcom assumed. If you put the headline number into the equity value column, you double count the debt.
Multiples. =IFERROR(EV/LTM_EBITDA,"NM"). Return "NM" (not meaningful) when EBITDA is negative or when the multiple is above a ceiling you set, such as 50x, and say so in a footnote.
Premium. =Offer_Price/Unaffected_Price-1.
Summary statistics. Below the table, compute =MAX(), =QUARTILE.INC(range,3), =MEDIAN(), =QUARTILE.INC(range,1) and =MIN(). MEDIAN and QUARTILE.INC skip text, so "NM" cells drop out automatically.

Getting the unaffected price right

The premium column causes the most review comments, because the same deal can show different premiums depending on the reference date. The unaffected price is the last close before the market learned about the deal, which is often several days before the announcement.

HPE / Juniper: HPE measured its roughly 32% premium against Juniper's 8 January 2024 close, the last full trading day before media reports. Bloomberg put that close at $30.22. In Excel, =40/30.22-1 returns 32.4%, which matches the figure Reuters cited.
Synopsys / Ansys: The 29% premium was measured against Ansys's 21 December 2023 close, the day before deal reports surfaced, almost four weeks before the 16 January announcement. Using the 12 January close would understate the premium because the leak had already moved the stock.
Broadcom / VMware: The companies cited a 44% premium on blended consideration of $138.23 per share against VMware's 20 May 2022 close, the last trading day before media speculation. Reuters reported about 49% using a different baseline. Your table should show one number, with the reference date in an adjacent column.

Handling stock and mixed consideration

VMware shareholders could elect $142.50 in cash or 0.2520 Broadcom shares, prorated to about 50% each. The $138.23 blended figure was lower than the cash price because it used Broadcom's share price on 25 May 2022. For Ansys, the $390.19 implied price combines $197.00 cash with 0.3450 Synopsys shares. Model this as =Cash_Per_Share + Exchange_Ratio * Acquirer_Price_At_Announcement and keep the acquirer price as a separate blue input. Never use the acquirer's price at close, because the multiple should reflect what the board agreed to on signing day.

Takeaway: Add three columns: "Unaffected date", "Unaffected price" and "Acquirer price at announcement". Most premium and EV disputes in review come from those three cells.

Step 4: Apply the Multiples to Value Your Target

With summary statistics in place, convert them into an implied valuation range.

Pick the primary multiple. EV/LTM EBITDA for profitable businesses, EV/Revenue for high-growth or loss-making ones, and EV/EBIT where capex is heavy. The Ryan O'Connell guide and the CorpDev.ai wiki both rank EV/LTM EBITDA as the most common primary metric.
Set the range at the interquartile band. Use the 25th to 75th percentile as the base range. Report the median as the reference point. The mean is easily distorted by one outlier in a 10-deal set.
Multiply by your target's metric. If your target has $150 million of LTM EBITDA and the interquartile range is 14.0x to 19.0x, implied EV is =150*14 to =150*19, or $2.1 billion to $2.85 billion.
Bridge to equity value. Subtract the target's net debt, preferred stock and minority interest. Divide by diluted shares for a per-share range.
Cross-check with premiums. If the target is public, apply the median premium from your table to its unaffected price. If the EBITDA method implies a 70% premium and your deal set's median is 32%, one of your inputs is off.
Plot it on the football field. Precedents sit alongside trading comps, DCF and LBO bars. Expect precedents to sit at or near the top, since they include control value.

Judgment matters most in the adjustments. If your target grows more slowly than the deal set's average, or its margins are lower, justify moving toward the 25th percentile and write that reasoning in a comment cell. Reviewers will accept a range that sits below the median when the reasoning is written next to it.

Takeaway: Build a one-line sensitivity grid (implied EV at the 25th percentile, median and 75th percentile, against three EBITDA cases) so the output updates when the target's numbers change.

Common Errors That Break a Precedent Transactions Spreadsheet

Mixing headline and equity values. Press releases quote enterprise value, equity value or "transaction value" with no consistency. The Synopsys/Ansys deal was reported as about $35 billion EV by the companies and about $34 billion by Bloomberg. Rebuild EV yourself from the filing.
Using post-announcement financials. Splunk's revenue after September 2023 is irrelevant to what Cisco paid. Lock the financials to the last period reported before signing.
Ignoring earnouts. For private deals with contingent payments, show both upfront and maximum total consideration in separate columns, as the CorpDev.ai wiki recommends.
Blending buyer types. Strategic buyers can pay for synergies, while sponsors are constrained by leverage and return targets. Run separate medians.
Hard-coding the stats. Typing a median into a cell instead of using =MEDIAN() means the summary goes stale the first time someone adds a deal.

Takeaway: Before sending, run a five-minute audit: recompute one deal's EV by hand from its 8-K, check every unaffected date, and confirm each "NM" has a footnote.

Why a Ready-Made Template Pays Off

A precedent transactions analysis covers about 15 columns and 4 or 5 formulas, but the details take the time. Analysts lose hours on blended consideration, unaffected dates, the EV bridge and LTM roll-forwards. The HPE/Juniper premium checks out in one cell. The Broadcom/VMware premium needs a proration assumption, an acquirer share price and a choice of reference date before the number is defensible.

A pre-built Excel template comes with input color coding, LTM calculators, consideration-mix formulas, quartile summaries and a football field output already linked. You spend your time screening deals and defending your adjustments rather than fixing cell references at 1 a.m. before a board meeting. ModelStack's IB and M&A templates follow this structure, so you can drop in your deal set and have a reviewable comps table the same day.