How to Create a Merger Model from Excel: A Step-by-Step Guide
How to Create a Merger Model from Excel: A Step-by-Step Guide
DocuBridge Team
•
Feb 12, 2025
Table of Contents
How to Build a Merger Model from Excel
Step 1: Set Up Assumptions & Inputs
Step 2: Input Financial Data for Acquirer & Target
Step 3: Calculate Purchase Price & Consideration
Step 4: Create Pro Forma Financials
Step 5: Perform Accretion/Dilution Analysis
How DocuBridge Enhances the Merger Modeling Process
Conclusion
FAQ: Common Questions About Merger Models
How to Build a Merger Model from Excel
A Merger Model is a financial tool used to assess the impact of an M&A transaction on an acquiring company’s earnings per share (EPS). It determines whether a deal is accretive (increases EPS) or dilutive (decreases EPS). Investment bankers, private equity professionals, and corporate finance teams use merger models to evaluate deal feasibility and potential synergies.
If you’re wondering how to create a merger model from Excel, this guide will walk you through the process step by step, incorporating industry best practices and practical resources to streamline workflows.
Step 1: Set Up Assumptions & Inputs
Before starting the model, collect the necessary financial data and assumptions:
Define Purchase Price and Payment Method
Determine whether the transaction is financed through cash, stock, or a combination of both.
If stock is used, calculate the exchange ratio to determine how many new shares will be issued.
Consider any earnouts or contingent payments that could impact the purchase price.
Identify Synergies
Estimate cost synergies, such as reductions in overhead, supply chain efficiencies, and workforce consolidations.
Identify revenue synergies, such as cross-selling opportunities and market expansion.
Factor in the timeframe for synergies realization, as some benefits may take years to materialize.
Assess Financing Costs
If debt is used for financing, determine the interest rate and repayment schedule.
Evaluate the impact of financing on credit ratings and debt covenants.
Consider amortization of financing fees over the loan term.
Determine Tax Rates
Identify applicable corporate tax rates for both companies.
Factor in net operating losses (NOLs) that could reduce taxable income post-merger.
Consider tax implications of debt vs. equity financing.
Step 2: Input Financial Data for Acquirer & Target
Gather and standardize financial data from both the acquiring and target company:
Revenue: Total sales generated by each company.
EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization): Key profitability metric.
Net Income: Profit after taxes and expenses.
Total Debt and Cash Balance: Existing leverage and liquidity positions.
Shares Outstanding: Number of fully diluted shares.
P/E Ratio (Price-to-Earnings Ratio): Helps evaluate relative valuation multiples.
Ensure all accounting policies are standardized to prevent discrepancies in financial reporting.
Step 3: Calculate Purchase Price & Consideration
Determine Enterprise Value (EV):
Formula: EV = Equity Value + Debt - Cash
Calculate the equity value by multiplying the target’s share price by its outstanding shares.
Add the target’s total debt obligations and subtract its cash balance.
Calculate the Exchange Ratio (for stock deals):
Formula: Exchange Ratio = Offer Price per Share ÷ Acquirer Share Price
If the deal is 100% stock-financed, compute the new shares issued to target shareholders.
Update pro forma ownership structure accordingly.
Adjust for New Debt Issuance & Stock Dilution
If new debt is issued, calculate the interest expense impact.
If stock is issued, determine the impact on existing shareholder dilution.
Step 4: Create Pro Forma Financials
Build the combined financials of the acquiring and target company:
Combine Revenue, EBITDA, and Net Income: Add corresponding financials for both entities.
Incorporate Synergies: Adjust financial projections for expected cost and revenue synergies.
Subtract Additional Interest Expense: Account for new debt financing.
Adjust Outstanding Shares: Update share count if new stock is issued.
Recalculate Depreciation & Amortization: Adjust for new purchase price allocation and goodwill.
Step 5: Perform Accretion/Dilution Analysis
Calculate Pro Forma EPS:
Formula: Pro Forma EPS = Pro Forma Net Income ÷ Pro Forma Shares Outstanding
Use the adjusted net income and updated share count.
Determine Accretion/Dilution Impact:
Formula: Accretion/(Dilution) = (Pro Forma EPS ÷ Acquirer Standalone EPS) - 1
Evaluation:
A positive percentage indicates accretion (EPS increases).
A negative percentage signals dilution (EPS decreases).
Using Excel formulas like SUM, IF, INDEX-MATCH, and LOOKUP helps automate calculations and scenario analysis.
How DocuBridge Streamline Merger Model Creation
DocuBridge streamlines the creation of merger models by automating key financial workflows and enhancing accuracy through AI-driven insights.
Automated Financial Modeling: DocuBridge extracts, formats, and integrates data into firm-specific financial models, including three-statement models, merger models, and LBO models.
AI-Powered Analysis: The DocuBridge chatbot allows users to conduct in-depth financial document analysis, answering complex financial questions in real-time and providing contextual insights directly within Excel.
Transparency & Traceability: DocuBridge ensures every data point is traceable back to its original document, improving auditability, regulatory compliance, and reducing the risk of human errors.
Seamless Excel Integration: Unlike standalone AI tools, DocuBridge operates directly within Excel, enabling finance professionals to leverage AI without disrupting existing workflows.
Conclusion
Mastering how to create a merger model from Excel is an essential skill for finance professionals looking to refine their deal analysis and enhance decision-making. The evolution of AI in M&A has made the process more efficient by automating financial modeling, accelerating analysis, and improving transparency.
DocuBridge plays a pivotal role in this transformation by providing seamless automation for financial modeling, reducing tedious, manual data entry, and enhancing in-depth analysis. With its AI-driven capabilities, DocuBridge empowers professionals to focus on strategy and value creation, rather than getting bogged down in spreadsheets. By integrating intelligent automation directly into Excel, it ensures accuracy, compliance, and efficiency, making complex merger models more accessible and actionable.
Discover how DocuBridge can transform your financial workflows. Book an exclusive demo with our CEO Dhruv or reach out at dhruv@docubridge.ai to see the impact firsthand.
FAQ: Common Questions About Merger Models
What is a merger model used for?
A merger model is primarily used to evaluate the financial impact of a merger or acquisition. It helps determine whether the deal will be accretive or dilutive to the acquiring company’s earnings per share (EPS).
What are the key components of a merger model?
Key components include assumptions (purchase price, financing, synergies), financial statements of both companies, purchase price allocation, pro forma financials, and accretion/dilution analysis.
How do synergies affect a merger model?
Synergies can significantly impact a deal’s outcome. Cost synergies (e.g., reduced expenses) and revenue synergies (e.g., new revenue opportunities) can make an acquisition more attractive by improving financial performance.
What Excel functions are useful in a merger model?
Key Excel functions include SUM, IF, INDEX-MATCH, and LOOKUP to automate calculations and ensure accuracy in linking data between financial statements.
How long does it take to build a merger model?
Depending on complexity, a basic merger model can be built in a few hours, while more detailed models incorporating sensitivity analysis and multiple financing scenarios may take several days. With tools like DocuBridge, you can build a complicated merger model in less than 10 minutes.
What is the difference between accretion and dilution?
Accretion occurs when the acquiring company’s EPS increases post-merger.
Dilution happens when EPS decreases, often due to overpayment or excessive share issuance.
Why is purchase price allocation important in merger modeling?
Purchase price allocation ensures that assets and liabilities are fairly valued, affecting depreciation, amortization, and goodwill, which in turn impact financial projections.
Table of Contents
How to Build a Merger Model from Excel
Step 1: Set Up Assumptions & Inputs
Step 2: Input Financial Data for Acquirer & Target
Step 3: Calculate Purchase Price & Consideration
Step 4: Create Pro Forma Financials
Step 5: Perform Accretion/Dilution Analysis
How DocuBridge Enhances the Merger Modeling Process
Conclusion
FAQ: Common Questions About Merger Models
How to Build a Merger Model from Excel
A Merger Model is a financial tool used to assess the impact of an M&A transaction on an acquiring company’s earnings per share (EPS). It determines whether a deal is accretive (increases EPS) or dilutive (decreases EPS). Investment bankers, private equity professionals, and corporate finance teams use merger models to evaluate deal feasibility and potential synergies.
If you’re wondering how to create a merger model from Excel, this guide will walk you through the process step by step, incorporating industry best practices and practical resources to streamline workflows.
Step 1: Set Up Assumptions & Inputs
Before starting the model, collect the necessary financial data and assumptions:
Define Purchase Price and Payment Method
Determine whether the transaction is financed through cash, stock, or a combination of both.
If stock is used, calculate the exchange ratio to determine how many new shares will be issued.
Consider any earnouts or contingent payments that could impact the purchase price.
Identify Synergies
Estimate cost synergies, such as reductions in overhead, supply chain efficiencies, and workforce consolidations.
Identify revenue synergies, such as cross-selling opportunities and market expansion.
Factor in the timeframe for synergies realization, as some benefits may take years to materialize.
Assess Financing Costs
If debt is used for financing, determine the interest rate and repayment schedule.
Evaluate the impact of financing on credit ratings and debt covenants.
Consider amortization of financing fees over the loan term.
Determine Tax Rates
Identify applicable corporate tax rates for both companies.
Factor in net operating losses (NOLs) that could reduce taxable income post-merger.
Consider tax implications of debt vs. equity financing.
Step 2: Input Financial Data for Acquirer & Target
Gather and standardize financial data from both the acquiring and target company:
Revenue: Total sales generated by each company.
EBITDA (Earnings Before Interest, Taxes, Depreciation, and Amortization): Key profitability metric.
Net Income: Profit after taxes and expenses.
Total Debt and Cash Balance: Existing leverage and liquidity positions.
Shares Outstanding: Number of fully diluted shares.
P/E Ratio (Price-to-Earnings Ratio): Helps evaluate relative valuation multiples.
Ensure all accounting policies are standardized to prevent discrepancies in financial reporting.
Step 3: Calculate Purchase Price & Consideration
Determine Enterprise Value (EV):
Formula: EV = Equity Value + Debt - Cash
Calculate the equity value by multiplying the target’s share price by its outstanding shares.
Add the target’s total debt obligations and subtract its cash balance.
Calculate the Exchange Ratio (for stock deals):
Formula: Exchange Ratio = Offer Price per Share ÷ Acquirer Share Price
If the deal is 100% stock-financed, compute the new shares issued to target shareholders.
Update pro forma ownership structure accordingly.
Adjust for New Debt Issuance & Stock Dilution
If new debt is issued, calculate the interest expense impact.
If stock is issued, determine the impact on existing shareholder dilution.
Step 4: Create Pro Forma Financials
Build the combined financials of the acquiring and target company:
Combine Revenue, EBITDA, and Net Income: Add corresponding financials for both entities.
Incorporate Synergies: Adjust financial projections for expected cost and revenue synergies.
Subtract Additional Interest Expense: Account for new debt financing.
Adjust Outstanding Shares: Update share count if new stock is issued.
Recalculate Depreciation & Amortization: Adjust for new purchase price allocation and goodwill.
Step 5: Perform Accretion/Dilution Analysis
Calculate Pro Forma EPS:
Formula: Pro Forma EPS = Pro Forma Net Income ÷ Pro Forma Shares Outstanding
Use the adjusted net income and updated share count.
Determine Accretion/Dilution Impact:
Formula: Accretion/(Dilution) = (Pro Forma EPS ÷ Acquirer Standalone EPS) - 1
Evaluation:
A positive percentage indicates accretion (EPS increases).
A negative percentage signals dilution (EPS decreases).
Using Excel formulas like SUM, IF, INDEX-MATCH, and LOOKUP helps automate calculations and scenario analysis.
How DocuBridge Streamline Merger Model Creation
DocuBridge streamlines the creation of merger models by automating key financial workflows and enhancing accuracy through AI-driven insights.
Automated Financial Modeling: DocuBridge extracts, formats, and integrates data into firm-specific financial models, including three-statement models, merger models, and LBO models.
AI-Powered Analysis: The DocuBridge chatbot allows users to conduct in-depth financial document analysis, answering complex financial questions in real-time and providing contextual insights directly within Excel.
Transparency & Traceability: DocuBridge ensures every data point is traceable back to its original document, improving auditability, regulatory compliance, and reducing the risk of human errors.
Seamless Excel Integration: Unlike standalone AI tools, DocuBridge operates directly within Excel, enabling finance professionals to leverage AI without disrupting existing workflows.
Conclusion
Mastering how to create a merger model from Excel is an essential skill for finance professionals looking to refine their deal analysis and enhance decision-making. The evolution of AI in M&A has made the process more efficient by automating financial modeling, accelerating analysis, and improving transparency.
DocuBridge plays a pivotal role in this transformation by providing seamless automation for financial modeling, reducing tedious, manual data entry, and enhancing in-depth analysis. With its AI-driven capabilities, DocuBridge empowers professionals to focus on strategy and value creation, rather than getting bogged down in spreadsheets. By integrating intelligent automation directly into Excel, it ensures accuracy, compliance, and efficiency, making complex merger models more accessible and actionable.
Discover how DocuBridge can transform your financial workflows. Book an exclusive demo with our CEO Dhruv or reach out at dhruv@docubridge.ai to see the impact firsthand.
FAQ: Common Questions About Merger Models
What is a merger model used for?
A merger model is primarily used to evaluate the financial impact of a merger or acquisition. It helps determine whether the deal will be accretive or dilutive to the acquiring company’s earnings per share (EPS).
What are the key components of a merger model?
Key components include assumptions (purchase price, financing, synergies), financial statements of both companies, purchase price allocation, pro forma financials, and accretion/dilution analysis.
How do synergies affect a merger model?
Synergies can significantly impact a deal’s outcome. Cost synergies (e.g., reduced expenses) and revenue synergies (e.g., new revenue opportunities) can make an acquisition more attractive by improving financial performance.
What Excel functions are useful in a merger model?
Key Excel functions include SUM, IF, INDEX-MATCH, and LOOKUP to automate calculations and ensure accuracy in linking data between financial statements.
How long does it take to build a merger model?
Depending on complexity, a basic merger model can be built in a few hours, while more detailed models incorporating sensitivity analysis and multiple financing scenarios may take several days. With tools like DocuBridge, you can build a complicated merger model in less than 10 minutes.
What is the difference between accretion and dilution?
Accretion occurs when the acquiring company’s EPS increases post-merger.
Dilution happens when EPS decreases, often due to overpayment or excessive share issuance.
Why is purchase price allocation important in merger modeling?
Purchase price allocation ensures that assets and liabilities are fairly valued, affecting depreciation, amortization, and goodwill, which in turn impact financial projections.
