M&A Merger Model: Accretion/Dilution, PPA & Synergies – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Solves the synergy the deal needs instead of making you drag an input until the accretion cell turns green, and proves it by running the model at that level.
2. Solves the highest price the acquirer can pay before earnings go backwards - the number that decides a competitive process, in closed form rather than by search.
3. Carries the purchase price allocation through to tax properly, so non-deductible intangible amortisation cannot shelter earnings it is not allowed to shelter.

M&A (MERGERS & ACQUISITIONS) EXCEL DESCRIPTION

M&A Merger Model: Accretion/Dilution, PPA & Synergies is an Excel template (XLSX) available for immediate download upon purchase.

Most merger models answer one question: is the deal accretive. This one answers the two that follow, and it answers them exactly rather than by goal-seek. How much synergy does this deal actually need before the dilution disappears, and what is the highest price per share the acquirer can pay before earnings start going backwards.

Both come out of closed-form algebra. Set the unknown as the synergy and the earnings per share identity is linear in it, so one expression returns the level at which accretion is exactly zero. Do the same with the offer value, which feeds the interest on new debt, the return forgone on cash and the shares issued all at once and all linearly, and the maximum price falls out the same way. Neither answer has to be re-run after an input changes, because neither was searched for in the first place. Both were independently confirmed by bisection, a completely different method from the algebra in the file.

In the example shipped in the file, a 120m-share acquirer trading on a 10.3x price-to-earnings multiple bids 30% for a target at 11.9x enterprise value to EBITDA, funded 40% cash, 35% new debt and 25% stock. The deal dilutes earnings per share by 28.6% in year one and 2.0% in year two, then turns accretive at 1.6% in year three. It needs $80.5m of the $95m of assumed run-rate synergies, an 18% cushion. And the acquirer could pay up to $31.09 per share, a 41.3% premium, before year three stops being accretive: 11.3 points of headroom over the 30% on the table.

WHAT YOU GET

Six worksheets: Read Me, Assumptions, Standalone, Consideration, Combined, Accretion and Checks. 233 live formulas across a five-year horizon. Every input sits on one Assumptions tab in blue. 22 integrity checks run in the workbook and all 22 pass on delivery. Beyond those, every output was re-derived by a separate independent script using a different method: 73 of 73 figures matched.

THREE THINGS THIS MODEL DOES THAT OTHER MERGER TEMPLATES DO NOT

1. The break-even synergy is solved. Most templates leave you dragging a synergy input up and down watching an accretion cell, which is slow and which gives a different answer every time somebody else changes the premium. Here it is one expression, and check 15 runs the model at that synergy level and confirms earnings per share comes out exactly flat.

2. The maximum price is solved too. The offer value drives three separate costs at once and every one of them is linear in it, which means the price at zero dilution has a closed form. Check 17 runs the model at that offer value and confirms the test year is exactly flat. Knowing what you can pay is the number that actually matters in a competitive process, and it is the number most models make you hunt for.

3. Purchase price allocation is carried through to tax properly. Intangible amortisation that is not deductible is added back before tax is computed, rather than being allowed to shelter earnings it is not permitted to shelter. Deductibility is an input, because it depends on how the deal is structured, and the deferred tax liability on the step-up is built and carried into goodwill rather than being waved away.

WHAT IS INSIDE

Standalone: both companies forecast on their own, with EBITDA holding its margin on revenue and standalone earnings per share for the acquirer, which is the benchmark everything else is measured against.

Consideration: the offer price per share from the unaffected price and the premium, equity value, enterprise value, the multiple it implies on target EBITDA, the split into cash, new debt and stock, the shares issued at the acquirer's own price, the target holders' resulting ownership of the combined company, and the full purchase price allocation from book equity through the intangible step-up and its deferred tax to goodwill.

Combined: the pro forma income statement with cost synergies phased in, the cost to achieve and transaction fees charged where they belong in year one, intangible amortisation from the deal, interest on the new debt, the return forgone on the cash used, the taxable profit adjustment and pro forma earnings per share.

Accretion: accretion or dilution per share and in per cent for every year with a verdict, a year-one earnings bridge that ties line by line to the combined net income, the break-even synergy with the headroom on the synergy assumed, and the maximum offer value, maximum price per share, maximum premium and the headroom on the premium offered.

A note on the test year. Year one carries the transaction fees and the cost to achieve the synergies, so judging a deal on year one alone is unfair to almost every transaction. The break-even tests therefore run on a test year you choose, defaulting to the first clean, fully phased year. The accretion table still shows every year, including the ugly one.

WHO USES IT

Corporate development teams screening targets before committing to diligence. Investment banking analysts preparing a board discussion on price. Private equity teams testing a bolt-on against a platform's earnings. CFOs who have to stand in front of a board and say what the deal does to earnings per share and what would have to be true for it to work. Candidates preparing for interviews, where accretion and dilution with a purchase price allocation is the standard technical question.

WHAT IT DOES NOT DO

It is an earnings model, not a cash flow or returns model. There is no debt paydown schedule, no revolver, no balance sheet roll-forward beyond the opening allocation, no minority interest, no contribution analysis and no collar on the exchange ratio. Revenue synergies are deliberately absent; the synergy line is cost only, because revenue synergies in a model this size are an assumption dressed up as an output. Goodwill is not amortised and not tested for impairment. Saying this up front is more useful than a feature list that quietly overstates what is inside.

FREQUENTLY ASKED QUESTIONS

Does it contain macros? No. The file is formula-only, with no VBA, no add-ins and no external links. It opens and calculates in Excel for Windows and Mac, in Google Sheets and in LibreOffice.

Can I run an all-cash or all-stock deal? Yes. The three consideration shares are inputs and a check enforces that they add to one hundred per cent. Set stock to zero for an all-cash deal, or cash and debt to zero for all-stock; the shares issued, the financing cost and every downstream number follow.

Is the intangible amortisation deductible? That is your input, not an assumption baked into the file. Set the flag to one where the structure gives a tax basis step-up, zero where it does not. The deferred tax liability and the tax computation both respond.

How do I know the numbers are right? Two ways, both visible to you. A Checks tab with 22 named tests that must all read PASS, covering the offer build, the consideration splitting back to the equity value, the purchase price allocation balancing to the price, the deferred tax liability, non-deductible amortisation being added back before tax in every year, the year-one earnings bridge tying to combined net income, earnings per share tying to net income over shares, and both closed-form answers reproducing exactly flat earnings when the model is run at them. And the independent re-derivation described above, 73 of 73 figures matched by a second method, including both closed forms confirmed by bisection.

Is it unlocked? Yes. No password, no hidden sheets, no protected cells. Every formula is readable and editable.

Can I use it outside the United States? Yes. Nothing in the mechanics is jurisdiction-specific. Purchase price allocation, deferred tax on a step-up and accretion arithmetic work the same way under IFRS and US GAAP at this level of detail.

Delivered as a single .xlsx file. One-time purchase, lifetime use, unlimited projects.

Source: Best Practices in M&A (Mergers & Acquisitions), Integrated Financial Model Excel: M&A Merger Model: Accretion/Dilution, PPA & Synergies Excel (XLSX) Spreadsheet, Paweł Dudek, Balancewright