A public-to-public merger model is a linked Excel build that forecasts combined financials, ownership, and valuation effects when one listed company buys or merges with another. A stepwise build means you construct the model in clean, modular blocks: inputs, mechanics, accounting, statements, outputs, with toggles to test scenarios without re-plumbing the file. The end product helps a board decide two things quickly: what it does to EPS and what it does to leverage.
This guide shows how to structure the model so management, lenders, and regulators can trust the math. The payoff is a decision-grade model that answers EPS accretion or dilution, leverage trajectory, and execution risk with speed and clarity.
Scope and objectives: translate terms into decision-grade outputs
You are not valuing either company in a vacuum. You are translating agreed terms and market prices into pro forma earnings, ownership, leverage, and cash flow under U.S. GAAP or IFRS, and into SEC Article 11 pro formas when relevant. The core audience – the buyer’s board, investment committee, lenders, ratings agencies, and regulators – wants credible EPS math, a leverage path they can support, free cash flow per share that improves over time, and a clear read on execution risk including timing, cost, and close certainty.
Build a modular, auditable model architecture
Keep the workbook modular and auditable so you can answer questions quickly and avoid logic drift as terms evolve.
- Control panel: versioning, reporting basis (US GAAP or IFRS), fiscal calendars, currency, presentation choices, and rounding or materiality policies.
- Market and share count: undisturbed and announcement prices; fully diluted share counts under ASC 260 using treasury stock method and if-converted; options, RSUs or PSUs, convertibles, and warrants.
- Consideration mechanics: cash, stock with fixed exchange ratio or fixed value, collars, caps or floors, and contingent consideration if any.
- Sources and uses: new debt and equity, cash on hand, target debt payoff, fees, and minimum and restricted cash.
- Synergies and dis-synergies: quantum, timing curve, cost to achieve, and confidence tiers.
- Purchase accounting: intangibles, useful lives, inventory step-up, deferred taxes, leases, pensions, contingencies, and goodwill plug.
- Pro forma statements: income statement, balance sheet, cash flow, diluted EPS, and credit metrics.
- Financing and covenants: rates, hedging, amortization, RCF capacity, and covenant headroom.
- Sensitivities: consideration mix, price or collar shocks, synergy bands, tax rate, interest rate, working capital normalization, and close timing.
Stand up clean outputs for management, downside, and no-synergy cases so the EPS and leverage story is obvious.
Inputs and data hygiene that withstand scrutiny
Use announcement-date closes for board materials and support with averages to show volatility. Tie price data to exchange prints and state your undisturbed date in the memo so optics are transparent.
Reconcile shares and equity awards to the latest 10-Q or 10-K, proxy, and plan documents. Build a control sheet by instrument: basic shares, options by strike and expiry, RSUs or PSUs by vesting tranche, in-the-money vs out-of-the-money. ASU 2020-06 changed convertible treatment; check for legacy features that changed or were extinguished.
Map debt and leases by instrument, maturity, call protection, and change-of-control terms. Split capital vs operating leases to avoid double counting. For tax, pull statutory rates, historical effective rates, NOLs and limits such as 163(j) in the U.S., and valuation allowances. Keep statutory and effective rates separate to avoid mixing apples and oranges.
Consideration mechanics and collars: code it right
You must code both cash and stock mechanics so the model behaves at collar edges and in stress cases.
- Fixed exchange ratio: a set number of buyer shares per target share; dollar value floats with buyer stock.
- Fixed value: a set dollar value per target share paid in buyer stock; the number of shares floats with buyer stock.
- Collars and walkaways: bands that limit value or ratio shifts; outside the band, the math changes and termination rights may exist.
Implement collar logic with a clear decision tree: identify FER vs FV; set lower and upper buyer price bounds; for FER with a collar, fix the ratio within the band and convert to FV using the boundary price outside; for FV with a collar, float the ratio inside and lock to max or min ratios at the bounds; then add caps on total shares or cash if negotiated. Model termination fees and walkaways in scenarios, not in the base. For clarity, disclose the reference price used and test collar edges to surface risk.
Illustration: Buyer price reference 50.00. FV offer 40.00 per target share in stock with a 45.00-55.00 collar. Within the band, shares per target = 40.00 / buyer price. At 44.00, use the floor: 40.00 / 45.00 = 0.8889. At 60.00, use the cap: 40.00 / 55.00 = 0.7273. Model both signing and closing cases because GAAP measures consideration at closing fair value while Article 11 bases adjustments on the pro forma date.
Ownership and diluted shares: get ASC 260 right
Build diluted share counts under ASC 260. For options, the treasury stock method incremental shares equal options minus proceeds divided by average market price. For RSUs or PSUs, use probability-weighted vesting for long-term EPS; for near-term Article 11 pro formas, include only incremental shares outstanding as of the pro forma date. For convertibles under ASU 2020-06, apply if-converted when more dilutive than TSM and add back after-tax interest. Pro forma buyer diluted shares equal legacy diluted plus merger shares issued. Treat deal-contingent raises and buybacks consistently with Article 11, and place post-close buybacks in a clearly labeled management case after confirming disclosure eligibility.
Sources and uses, fees, and working capital: design the grid
Create a strict grid. Uses include cash paid to target holders, target debt paid at par or assumed, transaction fees, financing fees, equity award cash-outs or rollovers, change-in-control payments, and any debt-like items treated as uses. For cash-and-stock, only the cash portion is a use. Sources include new term debt, bridge-to-bond, RCF draw, buyer cash, and target cash assumed, but delay access to target cash if minimum balances or trapped cash rules limit availability.
Expense transaction fees at close in GAAP; capitalize financing fees against debt and amortize via effective interest. Use a conservative fee stack and test both rate and original issue discount drags. Add ticking fees on committed financing and isolate derivative valuation for deal-contingent swaps. Normalize working capital on combined revenue, not a single quarter-end, and include only changes the buyer can enforce.
Purchase accounting under ASC 805 or IFRS 3
Apply the acquisition method: measure consideration at closing fair value; fair-value identifiable assets and liabilities such as customer relationships, technology, trade names, and contracts; record inventory step-up through COGS over sell-through, often 1 to 4 quarters; establish deferred tax balances on temporary differences using statutory rates; and book goodwill as the plug. Most public deals do not elect asset treatment for tax, and a 338(g) election is a special case that should be modeled only when contemplated and priced. For Article 11, transaction accounting adjustments include these effects; keep synergy and integration adjustments separate and evidence-based.
For a deeper overview of key accounting differences, see IFRS 3 vs ASC 805: Key Differences for M&A Valuations.
Synergies, dis-synergies, and costs to achieve
Stick to what you can execute. Cost synergies span headcount, procurement, facilities, and overhead. Tie realization to milestones and notices, front-load costs to achieve, and show gross and net savings so EPS is not overstated. Revenue synergies should be limited to contract-backed or pipeline-supported items and should phase slower. Dis-synergies cover attrition, channel conflict, brand rationalization, and required divestitures and should be modeled as negative revenue with margin effects. Build low, base, and stretch bands and do not assume immediate flow-through to EBITDA and EPS.
For practical benchmarking, review Evaluating Synergy Realization in M&A.
Statements and cash flow: build decision-grade outputs
Start from standalone projections or consensus with documented adjustments. Include revenue, gross margin, and opex detail retained by target line items for at least two years for integration tracking. Include stock-based compensation in GAAP and step up or accelerate when awards are modified. For pro forma D&A, add legacy plus new intangible amortization and step-ups, and show inventory step-up separately. Model interest using committed spreads and a reference rate forward curve, plus financing fee amortization. For tax, use a combined statutory basis with a clear reconciliation to the effective rate and include international regimes when relevant.
The opening balance sheet reflects purchase accounting at close plus any refinancing and equity issuance effects. Remeasure leases under ASC 842 if required. The cash flow statement reconciles net income to cash from operations, isolates working capital normalization, removes non-cash PPA items, and shows capex and integration cash costs.
EPS accretion or dilution: GAAP vs transaction-adjusted
Present GAAP and transaction-adjusted EPS. GAAP includes intangible amortization, inventory step-up, and expensed transaction costs. Transaction-adjusted excludes one-offs like transaction and integration costs and inventory step-up but keeps recurring amortization, financing, and deal taxes. Compute for each year: net income to common, adding back after-tax interest for if-converted when dilutive, over diluted shares post-merger. Show with and without synergy to make dependency explicit, and add a bridge from standalone buyer EPS to pro forma.
Credit metrics and ratings: keep lenders onside
Focus on net leverage, interest coverage, and liquidity. Clarify which adjustments count for covenants vs public guidance. For investment-grade buyers, include FFO to debt and potential equity credit for hybrids. For leveraged buyers, show covenant capacity and step-downs. Test 100 to 200 bps rate shifts and slower synergy capture to quantify risk.
Tax modeling: connect statutory and effective
Blend statutory rates by jurisdiction and reconcile to an effective rate that reflects non-deductible items, interest limits, and tax attributes. Model Section 382 limits for U.S. NOLs in a change of control, and consider possible reversal of valuation allowances if profitability improves. Keep intercompany financing and transfer pricing changes out of the pro forma unless directly attributable and supportable.
Regulatory and disclosure: plan for timing
Antitrust can force divestitures, so model revenue and margin loss, proceeds, and tax leakage. HSR and foreign merger control timelines can push close, so build delayed-close cases with ticking fees and slower synergy starts. For SEC Article 11, prepare transaction accounting adjustments cleanly and support any management adjustments.
Documentation map and economics
Cross-reference the model to the merger agreement, support agreements, equity award schedules, debt commitments and fee letters, fairness opinions and banker decks, disclosure schedules, and regulatory correspondence. One-time costs include advisory fees, integration and restructuring, severance, IT, and branding. Recurring changes include new intangible amortization, incremental public company costs, financing costs, and insurance. Use benchmarks and commitment letters to bound ranges.
Illustration: mechanics working together
Target equity value at signing: 10.0 billion. Consideration: 50 percent cash, 50 percent stock at buyer reference 50.00; exchange ratio = 25.00 / 50.00 = 0.50. Cash uses: 5.0 billion to holders plus 150 million fees. Sources: 5.15 billion new term debt at SOFR + 225 bps, 99 OID; financing fees 1.5 percent amortized over 7 years. PPA: 2.0 billion intangibles over 10 years; 150 million inventory step-up in year one; deferred tax on intangibles at 25 percent; goodwill plug. Synergies: 300 million run-rate, phased 40, 80, 100 percent over 3 years; one-time costs 350 million over 2 years. Run EPS with and without synergy and show the leverage path and coverage headroom.
US GAAP vs IFRS: align to reporting basis
ASC 805 and IFRS 3 both use the acquisition method with differences in measurement period nuances and some remeasurement rules. Diluted EPS follows ASC 260 and ASU 2020-06 in the U.S., while IFRS has separate guidance. For pro forma reporting, Article 11 governs U.S. filers and transaction accounting adjustments include new intangible amortization. Auditor comfort varies, so engage early to support close certainty.
Risks and edge cases: pre-mortem the deal
Test shocks that hit collars and trigger walkaways. Replace committed term debt with a bridge and reprice interest and fees if markets wobble. Slip close by quarters, rephase synergies and integration costs, roll share counts, and add ticking fees. If earnouts or contingent value rights appear, measure contingent consideration at fair value with through-earnings remeasurement post-close. Flag pensions, lease fair value changes, and any award accelerations for their EPS and leverage optics.
Alternatives and comparisons: pick the right structure
All-cash is simpler EPS math with higher leverage and more ratings pressure and can be faster accretion if synergies hold and rates cooperate. All-stock preserves leverage but increases dilution sensitivity; ownership and governance weigh more. Minority stake or a JV delivers less control and fewer synergies but simpler approvals. Spin-merge or RMT is tax-driven and mechanically complex, so model it as a separate workstream.
Execution timeline and ownership: assign roles early
Week 0-1: gather models, filings, equity awards, debt terms, market data; build share counts and price mechanics. Week 1-2: sources or uses, financing term sheet, and collar logic. Week 2-3: draft purchase accounting with valuation placeholders. Week 3-4: complete pro formas, EPS, and credit metrics across base, downside, and no-synergy cases. Pre-signing, align Article 11 approach and disclosure with counsel and auditors. Post-signing, update for market moves, financing, and regulatory timing; lock purchase accounting ranges.
Common pitfalls and kill tests: use a tight checklist
Code exchange mechanics and walkaways before running EPS. Reconcile diluted share counts across convertibles, PSUs, and anti-dilution to the proxy and footnotes. Separate transaction accounting from management adjustments. Maintain a synergy ledger to avoid double counting. Map tax treatment for each purchase accounting and financing line item. Reflect trapped cash access limits. Reconcile debt-like items to avoid use or PPA double counts. Keep Article 11 adjustments directly attributable and supportable.
Practical Excel notes: speed and control
Drive the model off a few flags: offer type, collar bounds, and closing price. Separate signing vs closing with toggles and centralize collar inputs so one function governs the math. Avoid hard circularity; use average debt for interest unless precision beyond materiality is required. Use a three-case selector and produce three crisp pages for committee: transaction summary, EPS and ownership, and credit.
Fresh idea: a fast collar stress test that impresses
Add a 10-minute Monte Carlo stress on buyer price to quantify collar risk. Sample daily buyer stock returns from recent volatility, simulate prices at signing and closing, and report the share issuance, ownership, and EPS under each draw. Then stack a simple histogram to show how often you hit collar edges and how often walkaway thresholds would trigger. This visualization turns abstract collar math into a probability-weighted risk that boards and lenders can grasp fast.
Closing Thoughts
A “good” public-to-public merger model delivers clear GAAP and transaction-adjusted EPS for three years with and without synergies, ownership splits sensitive to buyer price and collar edges, and a leverage trajectory that ratings can support. Purchase accounting rolls cleanly through the statements, and Article 11 pro formas are labeled and footnoted. If data gaps remain, call them out, show risk bands, and tie them to conditions or covenants. The result is a model that accelerates decisions and reduces surprises.