Merger Model Builder Reference

Purpose

This tool builds accretion/dilution analysis for M&A transactions. It models pro forma EPS impact, synergy sensitivities, and purchase price allocation. Use it when evaluating a potential acquisition, preparing merger consequences analysis for a pitch, or advising on deal terms.

Capabilities

• Calculate pro forma EPS and accretion/dilution percentage
• Perform sensitivity analysis on synergies vs. offer premium
• Perform sensitivity analysis on cash/stock mix
• Compute breakeven synergies for EPS neutrality
• Generate purchase price allocation (goodwill, intangible amortization)
• Output structured Excel workbook with multiple tabs

Prerequisites

Before using this tool, ensure you have:

• Access to a headless openpyxl environment
• Familiarity with excel-author skill conventions for cell coloring, formulas, named ranges, and sensitivity tables
• Ability to recalculate workbook using the provided script
• Access to financial data via native-mcp, web, or user input
• Understanding of accretion/dilution analysis and purchase price allocation

Parameters

All parameters are required unless noted otherwise.

Parameter	Meaning
Acquirer company name	Name of the acquiring company
Acquirer current share price	Current stock price of acquirer
Acquirer shares outstanding	Number of shares outstanding for acquirer
Acquirer LTM and NTM EPS (GAAP and adjusted)	Last twelve months and next twelve months earnings per share, both GAAP and adjusted
Acquirer P/E multiple	Price-to-earnings ratio of acquirer
Acquirer pre-tax cost of debt	Pre-tax interest rate on acquirer's debt
Acquirer tax rate	Marginal tax rate of acquirer
Acquirer cash on balance sheet	Cash and cash equivalents held by acquirer
Acquirer existing debt	Total outstanding debt of acquirer
Target company name	Name of the target company
Target current share price	Current stock price of target (if public)
Target shares outstanding	Number of shares outstanding for target (if public)
Target LTM and NTM EPS or net income	Earnings per share or net income for target
Target enterprise value or equity value	Current enterprise value or equity value of target
Offer price per share	Price offered per target share, or premium percentage
Consideration mix (% cash vs. % stock)	Percentage of deal consideration paid in cash vs. stock
New debt raised	Amount of new debt issued to fund cash portion
Expected synergies	Revenue and cost synergies, with phase-in timeline
Transaction fees	Advisory and other transaction-related fees
Financing costs	Costs associated with raising new debt or equity
Expected close date	Anticipated date of deal closing

Procedure: Build Merger Model

Follow these steps in order.

Step 1: Gather Acquirer Inputs

Collect the acquirer's company name, current share price, shares outstanding, LTM and NTM EPS (GAAP and adjusted), P/E multiple, pre-tax cost of debt, tax rate, cash on balance sheet, and existing debt.

Step 2: Gather Target Inputs

Collect the target's company name, current share price (if public), shares outstanding (if public), LTM and NTM EPS or net income, and enterprise value or equity value.

Step 3: Gather Deal Terms

Collect the offer price per share (or premium), consideration mix (% cash vs. % stock), new debt raised for the cash portion, expected synergies (revenue and cost) with phase-in timeline, transaction fees, financing costs, and expected close date.

Step 4: Perform Purchase Price Analysis

Compute the offer price per share, premium to current, equity value, add net debt assumed to get enterprise value, and implied EV/EBITDA and P/E.

Step 5: Build Sources & Uses Table

Sources include new debt, cash on hand, and new equity issued. Uses include equity purchase price, refinance target debt, transaction fees, and financing fees. Totals must balance.

Step 6: Calculate Pro Forma EPS Year-by-Year (Year 1-3)

Start with acquirer standalone net income, add target net income, add after-tax synergies, subtract foregone after-tax interest on cash used, subtract after-tax new debt interest, and subtract after-tax intangible amortization to get pro forma net income. Compute pro forma shares (including new shares issued). Divide to get pro forma EPS. Compute accretion/(dilution) % vs. standalone EPS.

Always show both GAAP and adjusted (cash) EPS where relevant. Stock deals use the acquirer's current price for the exchange ratio; note dilution from new shares. Include purchase price allocation — goodwill and intangible amortization matter for GAAP EPS. Synergy phase-in is critical — Year 1 is often only 25-50% of run-rate synergies. Include foregone interest income on cash used and new interest expense on debt raised. The tax rate on synergies and interest adjustments should match the acquirer's marginal rate.

Step 7: Build Sensitivity Table — Synergies vs. Offer Premium

Build a sensitivity table showing accretion/dilution vs. synergies ranging from $0 to $100 million and offer premium ranging from 15% to 30%. The table should include values at $25, $50, $75, and $100 for synergies, and at 15%, 20%, 25%, and 30% for premium.

Step 8: Build Sensitivity Table — Cash/Stock Mix

Build a sensitivity table showing accretion/dilution vs. cash/stock mix ranging from 100% cash to 100% stock for Year 1 and Year 2. Include the 50% cash/50% stock midpoint.

Step 9: Calculate Breakeven Synergies

Calculate the minimum synergies needed for EPS-neutral in Year 1.

Step 10: Output Excel Workbook

Output an Excel workbook with these tabs: Assumptions, Sources & Uses, Pro Forma Income Statement, Accretion/Dilution Summary, Sensitivity Tables, Breakeven Analysis.

Step 11: Generate Merger Consequences Summary

Generate a one-page merger consequences summary for a pitch book.

Step 12: Recalculate Workbook

Before delivery, recalculate the workbook using this command:

python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx

Data Sourcing

Prefer structured financial-data MCP for data. Fall back to web_search, web_extract, browser_navigate, or https://www.sec.gov/cgi-bin/browse-edgar for SEC filings. Use company IR pages or user-provided data as a last resort. Never fabricate data. Flag unsourced cells as [UNSOURCED] and surface them to the user.

Failure Modes

• Missing or incorrect acquirer/target financial data leads to inaccurate EPS calculations.
• Incorrect synergy phase-in assumptions distort Year 1-3 accretion/dilution.
• Omitting foregone interest or new debt interest causes pro forma net income errors.
• Failing to account for intangible amortization misstates GAAP EPS.
• Exchange ratio miscalculation in stock deals leads to wrong pro forma share count.
• Sources and uses table does not balance, breaking model integrity.
• Data cannot be sourced and is left as [UNSOURCED] without user notification.

Examples

• Sensitivity table: accretion/dilution vs. synergies ($0M to $100M) and offer premium (15% to 30%).
• Sensitivity table: accretion/dilution vs. cash/stock mix (100% cash to 100% stock) for Year 1 and Year 2.
• Breakeven analysis: minimum synergies for EPS-neutral in Year 1.