Merger Model Example: Walk Through Microsoft's $68.7B Activision Deal in Excel, Tab by Tab

A merger model shows what an acquisition does to the buyer's earnings per share. You build it by combining the two companies' income statements, adjusting for how the deal is paid for and for purchase accounting, and comparing pro forma EPS with the buyer's standalone EPS. This merger model example uses Microsoft's $68.7 billion all-cash purchase of Activision Blizzard and goes through the Excel workbook one tab at a time, using figures from SEC filings.

The deal is a good one to learn on. It was paid entirely in cash, so no new shares were issued. The final purchase price allocation is public in Microsoft's 10-K. It also took 21 months to close, which makes the termination fee clauses part of the model. Below are the six tabs you need, the numbers that go into each, and the checks a senior banker would run before the model goes into a committee deck.

Merger Model Example Tab 1: Transaction Assumptions

All the hard-coded deal inputs go on one tab. Every other tab links back to it. If an input is typed directly into a formula on a downstream tab, the model is broken, even if the numbers look right.

Microsoft announced the definitive agreement on January 18, 2022. The terms were $95.00 per share in cash, a total value of $68.7 billion inclusive of Activision Blizzard's net cash, and an expected close in Microsoft's fiscal 2023. The release also said the deal would be "accretive to non-GAAP earnings per share upon close" (Activision Blizzard 8-K, Exhibit 99.1). Press coverage at the time put the premium at about 45% over Activision's closing price on Friday, January 14.

Put these inputs in blue font, one per row:

Offer price per share: $95.00
Consideration mix: 100% cash, 0% stock
Headline transaction value: $68.7B (net of target cash)
Final total purchase price: $75.4B, as reported in Microsoft's FY2024 10-K after the October 13, 2023 close
Cash acquired: $12.976B, from the same 10-K note
Pre-tax yield on Microsoft's cash: 1.5% base case (an assumption to flex later)
Marginal tax rate on adjustments: 21% (an assumption)

The difference between $68.7B and $75.4B comes from netting. The headline figure subtracts the target's cash, and the purchase price allocation does not. Keep both on the tab and label which one feeds which calculation. The $75.4B gross price minus $13.0B of acquired cash gives a net cash outflow of about $62.4B, and that is the figure the forgone interest calculation uses.

Takeaway: Before linking anything, write down the source filing and page for every input next to the cell. Reviewers will ask where the numbers came from.

Tab 2: Standalone Financials and Calendarization

Microsoft's fiscal year ends June 30. Activision Blizzard reported on a calendar year. If you put the two income statements side by side without adjusting, you combine different periods, and junior models get this wrong all the time.

Acquirer: Microsoft FY2022

For the fiscal year ended June 30, 2022, Microsoft reported revenue of $198.3B, operating income of $83.4B, GAAP net income of $72.7B and GAAP diluted EPS of $9.65 (Microsoft FY22 Q4 earnings release). GAAP net income included a one-time $3.3B net tax benefit from an intangible property transfer, which added $0.44 to EPS. Non-GAAP net income was $69.4B and non-GAAP EPS was $9.21. Use the normalized $69.4B as the base, because a one-time tax gain says nothing about how the deal performs over time. Dividing $69.4B by $9.21 gives about 7.535 billion diluted shares.

Target: Activision Blizzard CY2021

Activision Blizzard reported 2021 GAAP net revenues of $8.803B (up from $8.086B in 2020), GAAP diluted EPS of $3.44, non-GAAP diluted EPS of $4.08 and a GAAP operating margin of 37% (Activision Blizzard Q4 2021 results, February 2, 2022). GAAP net income was about $2.7B.

Here is how to calendarize properly:

Pull the target's quarterly results for Q3 2021 through Q2 2022.
Add up the four quarters so the twelve months match Microsoft's July to June fiscal year.
Keep a check row that compares your sum with the reported annual figure for any overlapping year.

For a first pass, this walkthrough uses calendar 2021 net income of $2.7B as a proxy. In a live model, replace it with the trailing four quarters.

Takeaway: Add a "period end" row above each company's column and use conditional formatting to turn it red when the two dates are more than 45 days apart.

Tab 3: Sources and Uses, Plus Deal Protection

Every merger model needs a sources and uses table, and the two sides have to balance. In an all-cash deal the table is short. In this one it also has to show how much cash is leaving Microsoft's balance sheet.

Uses: purchase of equity and equity awards, $75.4B. Add transaction fees as a separate line if you have them. Microsoft did not break them out in the press release, so leave the line as a flagged assumption.
Sources: Microsoft balance sheet cash, $62.4B net, plus target cash acquired, $13.0B. No new debt and no new shares.

Below the table, add a deal protection block. This deal shows why it belongs in the model. Microsoft agreed to a tiered reverse termination fee of $2.0B, $2.5B or $3.0B depending on when an antitrust-related termination happened, and Activision's fee to Microsoft was about $2.27B (Wccftech's summary of the merger agreement filing). In July 2023, after the FTC lost its injunction bid, the parties moved the outside date to October 18, 2023. The fee rose to $3.5B for a termination after August 29 and to $4.5B after September 15 (Fortune, July 19, 2023). The deal closed on October 13, 2023, once the UK Competition and Markets Authority approved a restructured version in which Activision's cloud streaming rights outside the EEA went to Ubisoft (Computing, October 2023).

Takeaway: Model the reverse termination fee as an after-tax hit to the buyer's EPS in a "deal breaks" case. For Microsoft, $3B was about 4% of a year's normalized net income, which is the number a board needs when it decides how much regulatory risk to accept.

Tab 4: Purchase Price Allocation, Step by Step

The purchase price allocation (PPA) is where cash deals turn dilutive on a GAAP basis. Most free download templates either skip it or plug a single goodwill number. With Activision you can use the real allocation.

Microsoft's FY2024 10-K (Note 8) reports a total purchase price of $75,408M. Of that, $50,969M was preliminary goodwill and $21,969M was identified intangible assets. Later filings finalized goodwill at $51,001M. The intangibles break down as follows:

Marketing-related (franchises such as Call of Duty and Candy Crush): $11,619M, 24-year weighted average life
Technology-based (game engines and software): $9,689M, 4-year life
Customer-related: $661M, 4-year life

Goodwill was assigned to the More Personal Computing segment, and substantially all of it is not deductible for tax. Goodwill is not amortized, but the intangibles are. Build the amortization schedule like this:

Technology: $9,689M / 4 = $2,422M per year
Customer: $661M / 4 = $165M per year
Marketing: $11,619M / 24 = $484M per year
Total pre-tax amortization: about $3,071M per year, or $2,426M after tax at 21%

The short four-year life on technology drives the result. About 79% of the annual charge comes from that bucket, and it falls off sharply in year five. If your Excel template uses one blended life for all intangibles, it smooths out the step-down that makes years five and later look much better than years one to four.

Takeaway: Give each intangible class its own row with its own life, and run the schedule over at least six years so the step-down shows up.

Tab 5: Accretion/Dilution in the Merger Model Example

This tab produces the result everyone wants to see. Because the deal is all cash, the share count does not change, and the whole EPS effect sits in the numerator.

Line item ($B)	Base case
Microsoft normalized net income	69.40
+ Activision net income	2.70
− Forgone interest on $62.4B at 1.5%, after tax	(0.74)
− New intangible amortization, after tax	(2.43)
Pro forma net income	68.93
Diluted shares (B), unchanged	7.535
Pro forma EPS	$9.15
Standalone EPS	$9.21
Accretion / (dilution)	(0.7%)

If you add back the amortization, as many acquirers do when they report adjusted results, pro forma net income is $71.36B and EPS is $9.47, about 2.8% accretive. The cash cost of the deal and the strength of Activision's earnings are the same in both calculations. The only difference is the purchase accounting entries, and that is how the same transaction can be presented as accretive on an adjusted basis and still be slightly dilutive under GAAP in its early years. In this simple model, Microsoft's January 2022 accretion claim holds up only on the adjusted basis.

Microsoft's own unaudited pro forma figures in the FY2024 10-K, which assume the deal closed on July 1, 2022, show diluted EPS of $9.55 for fiscal 2023 and $11.82 for fiscal 2024. Compare your output with filed pro forma figures like these whenever they exist.

Takeaway: Show both GAAP and adjusted accretion on the output tab, with the amortization line between them. A committee should see both numbers.

Tab 6: Sensitivity Tables and Excel Template Checks

A merger model with only one answer is incomplete. The two inputs that most affect an all-cash deal are the return the buyer gives up on its cash and the earnings the target contributes. Build a two-way data table (Data, What-If Analysis, Data Table) on those, and add a one-way table on the cash yield:

Pre-tax cash yield	GAAP EPS	GAAP accretion	Ex-amortization accretion
0.5%	$9.21	0.0%	+3.5%
1.5%	$9.15	(0.7%)	+2.8%
4.0%	$8.98	(2.5%)	+1.1%

The timing of the close matters for this table. Short-term rates were close to zero when the deal was signed in January 2022. By the October 2023 close they were above 5%, which made the cash Microsoft spent more expensive than it was at signing. If you rerun the base case at closing-date rates, adjusted accretion shrinks and GAAP dilution widens. Run that rerun any time a deal takes more than a year to close.

Before the spreadsheet model leaves your desk, run these integrity checks:

Sources equal uses, to the dollar.
Total PPA (goodwill plus intangibles plus net tangible assets) equals the purchase price.
Amortization for each intangible class adds up to its fair value over its life.
The share count changes only when stock consideration is above 0%.
The period-end flags from Tab 2 are not red.

Takeaway: Put every check on one dashboard row that returns TRUE or FALSE, and conditionally format the whole workbook header red if any of them returns FALSE.

Building Your Own Merger Model From This Example

The Activision deal shows the full process on public data. You lock the assumptions, calendarize the periods, balance sources and uses, allocate the purchase price by intangible class, and then stress-test the EPS result. Starting from a blank sheet usually takes an analyst two to three days, and most of that time goes into structure: linking, formatting the checks, and setting up the data tables. Very little of it is spent on the deal-specific judgment calls.

Starting from a ready-made Excel template reverses that split. The tabs, the check rows and the sensitivity tables are already built, so your time goes into the questions that need judgment: which period to calendarize, which yield to charge on the cash, and how to present GAAP against adjusted accretion. If you pull up the Activision figures above and rebuild the six tabs in a ModelStack M&A model, you will have a working, auditable merger model before your next deal review.