Mergers & Acquisitions Financial Model Template
This M&A financial model in Excel gives corporate development teams, investment bankers, private equity professionals and finance students a complete, fully integrated merger model template for evaluating an acquisition from start to finish. It combines three years of monthly historical financials for both the acquirer and the target with a five-year monthly forecast. Each year rolls up into an annual summary column, so you can analyse a deal at the level of detail lenders and investment committees expect while still presenting clean annual numbers.
5 Year Financial Model for Mergers & Acquisitions
At its core, this accretion dilution model shows whether a deal increases or reduces the buyer’s earnings per share, and explains why. The workbook links every major deal mechanic: target valuation (trading comparables, precedent transactions and DCF), sources and uses of funds, a monthly debt schedule with cash sweep and revolver, purchase price allocation (PPA) with goodwill and deferred tax, and cost and revenue synergies. These flow into a fully integrated pro forma income statement, balance sheet and cash flow statement that balance every month.
The model is built for speed and confidence. A single start date on the Control Panel re-dates the entire workbook. A scenario selector switches between Base, Upside and Downside cases instantly, and a dedicated checks tab confirms that the balance sheet balances, sources equal uses, and cash and debt reconcile. With over 53,000 live formulas, colour-coded inputs and lender-ready formatting, this M&A Excel template is ready to adapt to a real transaction.
What’s Inside: Tab-by-Tab Guide
01. Cover & Control Panel
The Cover & Control Panel is the command centre of this M&A model. Change one master start date to re-date the entire workbook, switch deal scenarios, and view headline outputs, including offer price, enterprise value, goodwill, EPS accretion and buyer IRR, at a glance.
- Master start date drives every date, year label and timeline
- Base / Upside / Downside scenario selector with adjustable levers
- Key outputs dashboard and five-year accretion/dilution summary
- Colour legend and hyperlinked model map to every tab
02. Historical Financials – Acquirer
This tab holds three years of monthly acquirer historical financial statements: income statement, balance sheet and cash flow statement. The cash flow is derived by formula from balance sheet movements, so it always reconciles, and a full suite of historical KPIs supports benchmarking and forecasting.
- Monthly income statement with EPS, dividends per share and adjusted EBITDA
- Full balance sheet with built-in balance check
- Indirect cash flow statement with free cash flow, levered and unlevered FCF
- KPIs including ROIC, ROE, DSO, DIO, DPO, cash conversion cycle and leverage ratios
Mergers & Acquisitions Valuations & Purchasers
03. Historical Financials – Target
This tab provides the acquisition target’s three-year monthly financial statements, plus a dedicated EBITDA normalisation schedule. Quality-of-earnings adjustments strip out one-off restructuring, legal and transaction costs to produce the normalised EBITDA used to value the business and benchmark deal multiples.
- Complete monthly income statement, balance sheet and cash flow statement
- EBITDA normalisation for non-recurring costs, owner compensation and gains
- Customer concentration, retention, recurring revenue and backlog KPIs
- Headcount and revenue-per-employee tracking for due diligence
04. Forecasts & Operating Assumptions
This is the forecasting engine of the merger model. Five-year monthly standalone projections for both companies are built from transparent drivers: volume, price, customer growth, mix, FX, cost inflation, headcount, capex and working capital days, plus standalone tax and net income.
- Driver-based revenue builds for acquirer and target
- Fixed and variable COGS, operating expenses and EBITDA margins
- Depreciation, capex and working capital (DSO, DIO, DPO) schedules
- Tax assumptions, including NOL utilisation and the deferred tax bridge
05. Valuation & Purchase Price
This tab determines what to pay for the target. Calculate the offer price from the share price and premium, fully diluted shares using the treasury stock method, and enterprise value. Then cross-check against trading comparables, precedent transactions and a discounted cash flow valuation.
- Offer price, premium and treasury-stock-method diluted share count
- Equity-to-enterprise-value bridge
- Trading comparables and precedent transactions with median, mean and quartiles
- DCF using perpetuity growth and exit multiple methods, plus a valuation summary
06. Sources & Uses
This tab sets out the full transaction funding structure at closing. Uses cover the equity purchase price, target debt refinancing, advisory, legal and financing fees. Sources cover term loans, senior notes, revolver, cash and stock consideration, with acquirer cash automatically balancing the deal.
- Detailed uses of funds, including fees and original issue discount
- Flexible sources: TLA, TLB, revolver, notes, mezzanine, stock and seller notes
- New share issuance calculated from the acquirer’s share price
- Funding mix percentages and an unfunded uses check
Mergers & Acquisitions Allocations & Adjustments
07. Financing & Debt Schedule
This tab is a monthly acquisition debt schedule with floating and fixed-rate tranches, interest rate hedging, mandatory amortisation, optional prepayments, an automatic cash sweep and a revolving credit facility. It tracks leverage, interest coverage and covenant headroom without circular references.
- Term Loan A, Term Loan B, revolver and senior notes roll-forwards
- Benchmark rate, floor, spreads and hedge percentage
- Cash flow available for debt repayment and cash sweep waterfall
- Net debt/EBITDA, interest coverage and covenant headroom
08. Purchase Price Allocation (PPA)
This tab handles acquisition accounting. Allocate purchase consideration to the fair value of the target’s assets and liabilities, recognise identifiable intangibles, calculate deferred taxes and goodwill, and project the incremental depreciation and amortisation that affects pro forma earnings.
- Cash, stock, seller note and contingent consideration
- Fair value step-ups for PP&E, inventory and real estate
- Customer relationships, trade names and technology intangibles
- Goodwill, bargain purchase test and deferred tax liability unwind
09. Transaction Adjustments & Synergies
This tab quantifies the value creation behind the deal. Model cost and revenue synergies with phased realisation, one-off integration costs, transaction fees, and purchase accounting adjustments such as intercompany eliminations and the deferred revenue fair value haircut.
- Eight cost synergy categories and six revenue synergy categories
- Annual realisation phasing with scenario factors
- Integration cost timing and transaction cost tax deductibility
- Pro forma earnings adjustment bridge with a built-in tie-out check
Key Financial Metrics for Mergers & Acquisitions
10. Income Statement
The pro forma income statement combines acquirer and target results with synergies, PPA amortisation, financing costs and tax effects. It produces combined EBITDA, EBIT, net income and pro forma basic and diluted EPS, monthly and annually.
- Pro forma revenue, COGS, gross profit and operating expenses
- EBITDA bridge from standalone results to pro forma
- Detailed interest, financing fee and tax adjustments
- Pro forma share count, basic EPS and diluted EPS
11. Balance Sheet
This tab builds the pro forma closing balance sheet at the transaction date, combining both companies with all acquisition adjustments, then rolls it forward monthly for five years. Goodwill, intangibles, new debt and equity issuance all reconcile, with automatic balance checks.
- Closing balance sheet with cash funding and debt refinancing adjustments
- Goodwill, fair-valued intangibles and step-up run-off
- New acquisition debt, net of capitalised issuance costs
- Balance, intercompany, debt and goodwill reconciliation checks
12. Cash Flow Statement
The pro forma cash flow statement is built using the indirect method directly from balance sheet movements. Operating, investing and financing cash flows flow through to ending cash that ties exactly to the balance sheet, alongside free cash flow and debt service metrics.
- Operating cash flow, including non-cash PPA and financing fee add-backs
- Capex, debt amortisation, cash sweep, revolver and dividends
- Free cash flow, levered and unlevered free cash flow
- Cash conversion and cash-flow-to-debt-service ratios
13. Accretion Dilution Analysis
This is the heart of the merger model. Compare acquirer standalone EPS with pro forma EPS, rebuild pro forma earnings from after-tax deal adjustments, and decompose accretion or dilution into its drivers, identifying exactly what makes the deal accretive or dilutive.
- Standalone acquirer and target earnings contribution
- After-tax transaction adjustments and pro forma net income
- Year 1–5 EPS impact and the first accretive year
- Driver analysis: purchase price, financing, synergies, PPA and share issuance
14. Returns & Sensitivity Analysis
This tab stress-tests the deal. Sensitivity tables flex the offer premium, synergy realisation, funding mix, interest rates and operating performance, while a two-way grid and a strategic buyer returns analysis calculate IRR and MOIC for investment committee decisions.
- Offer premium and purchase price sensitivities
- Synergy, financing structure and interest rate sensitivities
- Operating sensitivities for growth, margins, inflation, capex and working capital
- Buyer IRR, MOIC and model integrity summary
15. Checks & Model Audit
The audit tab gives you confidence in every number. It tests every month for balance sheet balance, sources equalling uses, cash and debt reconciliation, goodwill and EPS ties, minimum cash, revolver capacity and covenant breaches, then returns an overall PASS or REVIEW status.
- Period-by-period PASS / REVIEW status
- Historical balance sheet and cash reconciliation checks
- Liquidity, revolver capacity and covenant breach flags
- Missing assumption and circular reference flags
Why Buy This Model?
Building a fully integrated M&A model from scratch can take weeks of specialist work, and a single broken link can undermine an entire investment decision. This M&A financial model gives you a professionally structured, lender-ready framework that covers everything in one workbook: valuation, sources and uses, debt financing, purchase price allocation, synergies, three integrated pro forma statements and accretion/dilution analysis. Every forecast is formula-driven, every input is clearly colour-coded, and the entire timeline updates from a single start date. Built-in audit checks confirm that the model balances in every period. Whether you are preparing a board paper, pitching a deal, screening an acquisition target or learning how merger models work, this template saves time, reduces errors and helps you present your analysis with confidence.
Mergers & Acquisitions Frequently Asked Questions
What type of transaction is this M&A model designed for? It is designed for a strategic acquisition, where one company buys another using a mix of cash, new debt and acquirer shares. It also includes an optional buyer returns analysis (IRR and MOIC), so it works for corporate acquirers and for investors assessing deal returns.
Can I use my own company’s financial data?
Yes. All historical figures, comparables and deal terms are illustrative placeholders in blue input cells. Replace them with your acquirer and target data in tabs 02 and 03, and the forecasts, statements and outputs update automatically.
How do I change the model’s dates?
Change the single Model Start Date on the 01. Cover & Control Panel tab. Every date, month label, year header, closing date and fiscal year-end across all 15 tabs updates automatically, with no manual re-dating.
Does the model show whether the deal is accretive or dilutive?
Yes. Tab 13 compares standalone and pro forma EPS for each forecast year, shows the percentage accretion or dilution, identifies the first accretive year, and breaks the result down into drivers such as financing cost, synergies, PPA amortisation and share issuance.
Does the model include purchase price allocation and goodwill?
Yes. Tab 08 allocates the purchase price to fair-valued assets, liabilities and identifiable intangibles such as customer relationships, trade names and technology. It calculates deferred taxes and goodwill, then projects the incremental amortisation into pro forma earnings.
How is the acquisition debt modelled?
The debt schedule is monthly and includes Term Loan A, Term Loan B, a revolving credit facility and senior notes. It covers floating rates with floors and hedging, mandatory amortisation, optional prepayments, a cash sweep, and leverage and covenant tracking.
Will I run into circular references?
No. Interest is calculated on opening debt balances, so the model is non-circular by design. You don’t need to enable iterative calculation, and the model stays stable when you change inputs.
Can I run different scenarios?
Yes. A Base, Upside and Downside selector on the Control Panel adjusts revenue growth, cost of sales, synergy realisation and interest rates. Tab 14 also includes sensitivity tables for offer premium, synergies, funding mix, interest rates and operating assumptions.
How do I know the model is working correctly?
Tab 15 checks every period for balance sheet balance, sources equalling uses, cash and debt reconciliation, goodwill and EPS consistency, minimum cash and covenant compliance. An overall PASS or REVIEW status also appears on the Cover tab.
Are synergies included?
Yes. Tab 09 models eight cost synergy categories and six revenue synergy categories, with annual realisation phasing, one-off integration costs and transaction fees. All of these flow through to pro forma earnings, cash flow and EPS.
What currency and units does the model use?
The model is set up in US dollars, with values in thousands ($000) and per-share data in dollars. Currency and unit labels can be changed on the Control Panel.
What software do I need?
Microsoft Excel (desktop version recommended). The model uses standard Excel functions with no macros, so it opens without security warnings and is easy to audit.
Is this model suitable for learning M&A modelling?
Absolutely. Clear tab-by-tab flow, colour-coded inputs, summary title bands and explanatory notes make it a strong learning tool for finance students, analysts and anyone preparing for investment banking or private equity roles.
Final Notes on the Financial Model
This 5 Year Merger & Acquisitions Financial Model Excel Template focuses on a projection that combines the standalone financial performance of the acquirer and target with the transaction financing, purchase accounting, synergies, integration costs, and resulting pro forma financial statements ensures sustainable profitability and cash flow stability.
Further Reading
Leveraged Buyout (LBO) Model: Build accurate, bank-ready financial projections for your LBO. Download our professional Excel template for feasibility and valuation analysis.
- Real Estate Private Equity Fund Financial Projection Model: Professional-grade Real Estate Private Equity (REPE) fund financial model in Excel.
