M&A Valuation Model in Excel: The 7 Tabs You Need to Price a Target Company

An M&A valuation model in Excel is a linked spreadsheet that works out what a target company is worth on its own and what a buyer can afford to pay for it. It uses three methods: a discounted cash flow (DCF), trading comparables and precedent transactions. The best versions split the work across seven tabs: inputs, historicals, projections, WACC, DCF, comps and precedents, and a summary of synergies and the football field. Every number then traces back to one assumption cell.

What an M&A Valuation Model in Excel Has to Answer

A deal model answers two questions. The first is what the target is worth on a standalone basis. The second is how much of the value the combination is expected to create the buyer can give to the seller and still create value for its own shareholders. Everything in the spreadsheet model either feeds one of those answers or should be cut.

The clearest public example is Microsoft's purchase of Activision Blizzard. Microsoft announced it on January 18, 2022 as an all-cash deal at $95.00 per share, valued at $68.7 billion including Activision's net cash. Activision's definitive proxy (DEFM14A) sets out the analysis behind the fairness opinion from its advisor, Allen & Company:

DCF: discount rates of 6.50% to 8.00% and perpetuity growth of 2.25% to 2.75%, implying $84.73 to $123.87 per share.
Selected public companies: 13.5x to 18.0x CY2022E EBITDA, implying $69.08 to $89.05 per share.
Selected precedent transactions: 14.0x to 20.0x LTM EBITDA, implying $72.40 to $99.49 per share.
Premium: $95.00 was about 45.3% above the January 14, 2022 closing price.

The board did not look for a single "right" number. It checked whether $95.00 fell inside or above the ranges that each method produced. Your model should give a decision-maker that same view.

Takeaway. Before you build anything, write down the decision the model supports, for example "Is $95 per share defensible?", and label every tab by which part of that answer it produces.

Tabs 1 to 3: Inputs, Historicals and Projections

Tab 1: Inputs and Deal Assumptions

This is the only tab where anyone should type a hard-coded number that drives the valuation. Keep inputs in blue font and formulas in black, and give each driver its own labelled cell. A typical inputs tab holds:

Offer price per share, diluted share count and the target's current share price
Net debt, which is total debt plus preferred stock and minority interest, minus cash
Valuation date and fiscal year end, which set the stub period and the discounting convention
Scenario selector (base, upside, downside) that switches the growth and margin cases
Tax rate, transaction fees and financing mix

Set up the scenario switch on day one. If you add it after the projections are built, you end up rewiring dozens of formulas.

Tab 2: Historical Financials and Normalisation

Pull three to five years of income statement, balance sheet and cash flow data from the target's 10-K filings or management accounts. Then normalise it. Remove one-off restructuring charges, litigation settlements and gains on asset sales, and show each adjustment as its own line so a reviewer can see it. Multiples are applied to adjusted EBITDA, so every dollar of add-back moves the price by the multiple. At 15x, a $10 million add-back adds $150 million of value.

Tab 3: Operating Projections

Project five to ten years, driven by revenue growth, gross margin, operating expenses as a percentage of revenue, capex and working capital days. Link each driver to the scenario selector on Tab 1. The output is unlevered free cash flow: EBIT less taxes, plus depreciation and amortisation, less capex, less the increase in net working capital.

Takeaway. Put a check row at the bottom of Tab 3 confirming the balance sheet balances and cash ties to the cash flow statement. A model that fails its own checks will not get through diligence.

Tabs 4 and 5: WACC and DCF in Your M&A Valuation Model in Excel

Tab 4: Weighted Average Cost of Capital

The discount rate is the input reviewers argue about most, so source every component. Build it as follows:

Risk-free rate. Use the spot 20-year US Treasury yield, or a normalised rate. Kroll recommends using the higher of its 3.5% normalised US risk-free rate or the spot 20-year Treasury yield on the valuation date.
Equity risk premium. Kroll reaffirmed its recommended US ERP at 5.0% as of September 2, 2025, after raising it to 5.5% in April 2025. Aswath Damodaran's implied ERP for the US was about 4.23% at the start of January 2026. Pick one source, cite it in the cell comment and stick to it.
Beta. Unlever the betas of five to ten peers, take the median and relever it at the target's capital structure.
Cost of debt. Use the yield on the target's traded bonds or an implied credit rating, then apply the tax shield.
Weights. Use the target or industry capital structure, at market values.

The ERP choice matters. The gap between 4.23% and 5.0% is 77 basis points of ERP. Multiplied by a beta of 1.1 and an 85% equity weight, that adds roughly 70 basis points to WACC, which is enough to move a DCF by double-digit percentages.

Tab 5: Discounted Cash Flow

Discount the unlevered free cash flows from Tab 3 at the WACC from Tab 4, using the mid-year convention. Calculate terminal value two ways: with the Gordon growth method and with an exit multiple. Then cross-check each against the other. If your perpetuity growth rate implies a 25x EBITDA exit multiple for a business whose peers trade at 12x, one of your assumptions is wrong.

The Activision proxy shows how wide a defensible DCF range can be. Allen & Company's 150 basis point discount rate band and 50 basis point growth band gave per-share values from $84.73 to $123.87, a spread of about $39. That is why Tab 5 needs a two-way data table, with WACC on one axis and terminal growth or exit multiple on the other. A single DCF point estimate tells a board very little.

Takeaway. Check the share of value that comes from the terminal value. If it is above 75%, most of the answer depends on the terminal assumptions, so spend your diligence time there.

Tab 6: Trading Comps and Precedent Transactions

Market-based methods test the DCF against what investors and acquirers actually pay. Keep them on one tab with two blocks, because they share the same peer logic and the same metric definitions.

Trading Comparables

Pick six to twelve peers with similar business model, growth and margins. Activision's advisor applied separate ranges to CY2022E and CY2023E EBITDA.
Calculate EV/Revenue, EV/EBITDA and P/E on LTM and forward figures, with the same adjustments you made to the target's numbers.
Use the interquartile range or the median. Do not use the mean, which outliers distort.
Remember that trading comps value a minority stake. They carry no control premium.

Precedent Transactions

Collect deals from the past five to ten years in the same sector, taken from merger proxies, 8-K filings and press releases.
Record the announcement date, the EV, the LTM multiples and the premium paid over the unaffected share price.
Expect precedent multiples to sit above trading multiples, because they include control premiums and the synergies buyers expected. In the Activision analysis, the precedent range (14.0x to 20.0x LTM) had a higher top end than the trading range (13.5x to 18.0x forward), and its implied ceiling of $99.49 was the only market-based figure above the $95.00 offer.

Takeaway. Write a one-line reason for including each comp and each precedent in a column next to it. When a buyer's banker challenges your peer set, those notes are your defence.

Tab 7: Synergies, Premium and the Football Field

This tab turns the analysis into a decision. It has three blocks.

Valuing Synergies

Value cost and revenue synergies separately, net of integration costs, with phase-in schedules and their own discount rates. Revenue synergies need a heavier haircut. McKinsey's research finds that companies capture 70 to 85 percent of announced cost synergies within 18 months of close, but only 25 to 35 percent of revenue synergies, and over a longer timeline of 18 to 36 months. In practice, model revenue synergies at a probability weight of 50% or less, and push them to years three to five.

Premium and Value Sharing

Compare the offer premium in dollars with the present value of net synergies. Microsoft's $95.00 was a 45.3% premium to Activision's January 14, 2022 close. If the premium in dollars is larger than the net present value of synergies, the buyer is betting on standalone upside that the seller's shareholders have already been paid for. Show this as a simple bridge, where standalone equity value, plus the NPV of synergies, minus premium paid, equals value created for the acquirer.

The Football Field Chart

Plot each method's per-share range as a horizontal bar, covering DCF, trading comps (both years), precedents and the 52-week trading range. Then draw the offer price as a vertical line. In the Activision case, a line at $95.00 would sit above both trading comps ranges, near the top of the precedents range and inside the lower half of the DCF range. That single chart tells a board why the price was fair.

Takeaway. Build the football field with stacked bar charts linked to the min and max of each method, so the chart redraws itself when any assumption changes. Hand-drawn football fields go stale after the first revision.

Building Your M&A Valuation Model in Excel Step by Step

Here is the build order that avoids rework when you start from a blank workbook or a template:

Set up Tab 1 with every driver, the scenario switch and colour coding before you write a formula.
Load and normalise the historicals on Tab 2. Agree the adjustments with the deal lead before you project.
Build the projections on Tab 3 off the drivers, and add balance and cash checks.
Build the WACC on Tab 4 with sourced ERP and risk-free inputs, and cite each source in a cell comment.
Run the DCF on Tab 5 with both terminal value methods and a WACC by growth sensitivity table.
Populate comps and precedents on Tab 6 from filings, using the same EBITDA definition as the target.
Value synergies, bridge the premium and link the football field on Tab 7.
Stress test it. Flip to the downside case and confirm every output tab updates with no #REF! errors.

A careful analyst can build this structure from scratch in two to four days. Most of that time goes on formatting, check rows and chart wiring rather than judgement. A ready-made Excel template with the seven tabs already linked gives you that time back, so you can spend it on the work that changes the price: normalising EBITDA, defending the peer set and haircutting the synergies. When you pick or build a template, check that it has a single inputs tab, two terminal value methods, a live sensitivity table and a football field driven by formulas. If any of those are missing, the model will slow down the next negotiation.

Allen & Company's work on the Activision deal used this same structure to support a price that Microsoft paid when the deal closed in October 2023. A seven-tab model gives your next deal that kind of documented, auditable answer.