Skip to content

How to Create a Merger and Acquisition Model

This tutorial shows how to create a Merger and Acquisition Financial Model in Microsoft Excel, including synergies, debt, and valuation calculations.

It starts with analysing the cash flows of the target and the acquiring company. Then, it looks into the combined performance after the acquisition. In particular, it shows how to forecast synergies arising from new revenue opportunities and cost savings. Finally, it shows how to calculate the contribution of the acquired company to the overall valuation.

Download my Merger and Acquisition Model Template to follow the examples used.

You can also watch the video version of the tutorial at the end of this post.

Step 1. Compare the Historical Performance of the Target and the Acquiring Company

Start by showing the historical revenues and costs of the two companies subject to an acquisition or merger. Present the figures uniformly, so comparing them like to like is easy.

For example, my Merger and Acquisition Model Template lists the monthly historical cash flow of the two companies by grouping them by categories such as monthly users, revenues or costs.

It then contrasts the main drivers, such as the number of customers or sales and shows their impact on the cash flow.

monthly revenue merger acquisition

Step 1. An example of the monthly revenues of two companies subject to a merger or acquisition. Notice the difference between both companies’ addressable market and customer reach and its impact on the overall revenues. You can download the entire spreadsheet here.

Grouping the cash flows by companies’ revenues and cost categories will make forecasting the synergies resulting from the merger or acquisition easier.

Learn More: Visit my tutorial on How to Build a Cash Flow Forecast in Microsoft Excel for more examples of estimating revenues and costs.

Step 2. Forecast Revenue Gains after the Merger or Acquisition

Analyse how the merger or acquisition affects the overall revenue after the transaction. Specifically, focus on gains or losses through a calculation of synergies.

For example, my Merger and Acquisition Model Template estimates revenues based on the addressable users, market reach and average pricing.

The model assumes that acquiring a new company will result in an overall increase in the addressable market and customer reach. Therefore, combining two entities will translate into increased revenues.

revenue synergy acquisition forecast financial model excel template

Step 2. An example of forecasting revenue gains and synergies resulting from an acquisition or a merger of two companies. The transaction generates higher cash inflow as the financial model calculates income as a function of the addressable market and customer uptake. You can download the full Excel template here.

Notice that by explicitly showing the synergies, the spreadsheet allows you to calculate any revenue gains related to the acquisition.

Learn More: Visit my tutorial on How to Create a Subscription Model with Churn Calculation for more examples of calculating revenue in a cash flow forecast.

Step 3. Forecast the Impact of a Merger or Acquisition on Variable Costs

You can think of variable costs as expenses directly related to changing one or more of the financial model’s drivers. For example, they can link advertising expenses to new customers (e.g. via acquisition costs) or service costs to the customers already acquired.

Acquiring a new company and increasing its size may result in efficiencies in an average cost per unit (e.g. through the economy of scale) or an increase as a company may struggle to support or add more customers.

My Merger and Acquisition Model Template calculates variable costs using three different methods:

  • Customer Acquisition Costs – driven by the number of new customers or sales,
  • Customer Support Costs – linked to the number of existing users,
  • Variable Costs – calculated as a percentage of revenues.

The model forecasts the expenses by linking the customer numbers to an average cost per unit.

merger acquisition opex synergy financial model excel template

Step 3. An example of calculating variable costs before and after a merger or acquisition. Notice that the financial model assumes a change in customer support and acquisition costs due to the transaction. You can download the entire spreadsheet here.

Learn More: Visit my tutorial on How to Create a Telecom Financial Model for more examples of including variable costs in a financial forecast.

Step 4. Calculate Efficiencies in the Headcount and Operating Costs

A consolidation of two companies will likely result in a change to the operating expense structure. In particular, it may reduce the headcount numbers as some roles overlap or save central costs as some expenses, such as an additional office, become redundant.

My Merger and Acquisition Template lists the acquired company’s employees before the transaction and then shows what happens to them after the consolidation.

Then, based on the new numbers, it calculates the resulting synergies.

staff acquisition synergy forecast financial model excel template

Step 4a. An example of calculating synergies after a company acquisition. Notice the financial model starts with the headcount structure before the transaction. Then, it shows what happens to the employees after the consolidation. Finally, it forecasts the new costs and savings using average salary assumptions. You can download the entire Excel file here.

Use the same approach to calculate central and other operating expense synergies.

merger acquisition operating cost spreadsheet example

Step 4b. An example of showing operating cost and synergies resulting from a merger or acquisition. You can download the entire template here.

Learn More: Visit my tutorial on How to Create a Capital Investment Plan for more examples of planning resources and operating expenses.

Step 5. Analyse the Results of the Financial Model

Summarise the output of the financial model to show the effect of the merger or acquisition on the consolidated cash flow.

For example, my Merger and Acquisition Model Template aggregates the key financials into an annual view. The summary shows the historical performance of the acquiring company and the target before the transaction. Then, it shows the incremental contribution of the acquired company to the overall cash flow.

merger acquisition excel financial model summary

Step 5a. An example of a Financial Summary showing the impact of two companies’ consolidation. The annual view shows the historical financials of both companies before the transaction and the effect of the acquisition afterwards. Notice the placement of the main assumptions to the right of the table to enable a sensitivity analysis and stress testing of the model. You can download the entire Excel template here.

In addition, the template also shows the impact of the consolidation on the Profit and Loss Statement and Net Income.

merger acquisition profit synergy loss statement excel template

Step 5b. An example of a Profit and Loss Statement resulting from a merger or acquisition. The summary shows the impact of the transaction on the Net Income, particularly the effect of tax savings or change in the depreciation. Notice the additional interest expense to service new debt linked to the transaction’s financing. You can download the entire Excel file here.

Finally, add some graphs to make analysing synergies and other financial trends easier.

merger acquisition financial model graph

Step 5c. An example of a graph showing the impact of company acquisition on consolidated revenues. The Synergy Gain represents the impact of the transaction on the overall income. You can download the entire spreadsheet here.

Learn More: Visit my tutorial on How to Create a Real Estate Investment Model for more examples of analysing and presenting financial forecast results.

Step 6. How to Add Debt Financing to a Merger and Acquisition Model

To add debt to a financial plan, estimate the capital required to finance the merger or acquisition. Then, set the proportion of the amount financed through debt (i.e. debt leverage). Finally, estimate debt repayments, interest expense and other related fees.

For example, to calculate the capital required to finance the transaction, my Merger and Acquisition Model Template starts with estimating a fair value of the target company. Then, it adjusts it for any additional costs to arrive at the final figure.

fair value acquisition calculation excel

Step 6a. An example of estimating a fair value and the capital required to finance a merger or acquisition. Notice that the calculation uses the historical revenue of the target company and a multiplier for the calculation. Then, it adjusts the amount through a premium % assumption to arrive at the final figure. You can download the entire spreadsheet here.

Finally, set the percentage of the capital financed by debt. Remember to show loan repayments over time, separating the interest charge and the principal repayment.

debt model excel pmt function

Step 6b. An example of a debt model calculation. The amount of debt raised results from multiplying the total capital required to finance a merger or acquisition by a leverage % assumption. The model calculates the total repayment amounts using Microsoft Excel’s PMT formulas. The principal is the difference between the repayment and the interest expense. You can download the entire template here.

The fair value and debt leverage calculations will impact the valuation and balance sheet discussed in the next step of this tutorial.

Learn More: Visit my tutorial on How to Create a Financial Model with Debt Financing for an example of including debt calculations into a cash flow projection.

Step 7. Calculate the Impact of a Merger or Acquisition on the Valuation and Balance Sheet

Calculate the effect of the merger or acquisition on the company’s assets and liabilities. Also, estimate the valuation of a company before and after the transaction to show whether the investment had a positive or negative impact.

For example, my Meger and Acquisition Model Template assumes that the acquiring company will fully absorb the assets and liabilities of the new company.

The goodwill in the balance sheet reflects the excess paid above the fair value of the acquiring company.

merger acquisition synergy balance sheet forecast template

Step 7a. An example of adjustment to a Balance Sheet before and after an acquisition. Notice additional lines accounting for the excess of fair over book value of acquired assets and goodwill. Company A’s cash figure reflects the money raised to finance the transaction. You can download the entire Excel template here.

To calculate the value of the company before and after the consolidation, my template uses an EBITDA multiplier to calculate the terminal value. Then, it uses Microsoft Excel’s IRR and NPV formulas to estimate the impact of the transaction.

acquisition investment synergy return calculation

Step 7b. An example of calculating a company’s value using an EBITDA multiplier. Notice that the financial model splits the calculation to estimate the impact of the consolidation on the overall valuation. You can download the entire spreadsheet here.

Please note that financial accounting is beyond the scope of this tutorial. The accounting rules related to mergers and acquisitions can be quite complex and may differ on a case-by-case basis. Hence, always get professional advice before making final adjustments. You can read more about Merger and Acquisition Accounting here [external link].

Learn More: Visit my tutorial on How to Create a Marketing Investment Plan for an example of using an alternative way to value customer revenues and a company.

Step 8. Find Weak Spots in a Financial Model

Use the financial metrics and valuation to stress your Merger and Acquisition Model. Try to find the most sensitive assumptions that may negatively impact the feasibility of the transaction.

For example, while keeping everything constant, test how a change in the post-merger market share affects the valuation. Specifically, check whether the returns justify the acquisition cost.

My Merger and Acquisition Model Template uses Microsoft Excel’s Data Tables to compare various scenarios and their impact on the key financial metrics.

scenario analysis excel financial model data table

Step 8. An example of using a data table to stress test a financial model. In this example, my template tests how a change in debt leverage and premium paid over fair value affects the IRR of a merger or acquisition. The right table tests the impact of changes in Market Reach and Customer Acquisition Costs on overall returns. You can download the entire Excel file here.

Stressing the model is an effective way of finding its sensitivities and weak spots. It also allows a business to prepare for changes in market conditions and mitigate their consequences.

Learn More: Visit my tutorial on How to Create a Project Finance Model for another example of including a sensitivity analysis in a cash flow projection.

Summary: How to Create a Merger and Acquisition Model

This tutorial has shown how to create a Merger and Acquisition Model in Microsoft Excel.

It started by analysing historical financials for both the acquired and acquiring companies. Then, it moved to model synergies resulting from the mergers. Finally, it looked into the topics of valuation and scenario analysis.

The tutorial focused on the following steps:

  1. Analyse the historical financial performance of the companies. Group the revenues and costs into uniform categories for a like-to-like assessment.
  2. Forecast the revenues after the acquisition or merger. Focus on the drivers of income, such as an increased market share or a change in the number of addressable customers.
  3. Assess the impact of the merger or acquisition transaction on the variable costs. For example, you may present improvements in such direct cost as cost per acquisition.
  4. Show synergies in the operating and staff costs. For example, you may find an overlap in roles between the two companies or savings in migrating to a shared IT system.
  5. Summarise the financial model in an easy-to-follow format. Aggregate the monthly financials into annual Cash Flow, P&L and Balance Sheet statements.
  6. Calculate the required investment to finance the merger or acquisition. Consider adding leverage by introducing debt into the financial model.
  7. Assess the valuation of the company before and after the merger. Show how the synergies between the companies will affect the returns on investment.
  8. Stress test the financial model to find any weak spots. Consider using Microsoft Excel’s data table to run sensitivity and scenario analysis.

Download Merger and Acquisition Model Template

Click the button below to download my Capital Investment Plan Template.

The Excel spreadsheet calculates the cash flow, P&L and Balance sheet resulting from a merger or acquisition. It also estimates the resulting synergies and the impact of the transaction on the valuation.

The template consists of the following spreadsheets and sections:

  • Monthly Financial Model: calculates cash flows of the acquiring and target companies before and after an acquisition. It includes a forecast of synergies in revenues, costs and net income.
  • Financial Model Summary: presents the financial model in a yearly view. It also includes a table with the primary assumptions for sensitivity analysis and graphs.
  • Profit and Loss Statement: lists revenue and costs (including depreciation and interest expense) and their contribution to the income.
  • Cash Flow Statement: shows the cash flow operations, investment and debt and equity financing used in a merger or acquisition transaction.
  • Balance Sheet: shows the effect of consolidation on assets and liabilities. It also includes a calculation of fair value and goodwill consideration.
  • Return on Investment Calculation: estimates the impact and contribution of a merger or acquisition to the overall equity returns.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].


*Order risk-free with a 30-day Money Back Guarantee


34.00 – Download My Merger and Acquisition Model Template
*Order risk-free with a 30-day Money Back Guarantee

Video: How to Use the Merger and Acquisition Model Template

For more details and a step-by-step explanation of using and creating the Merger and Acquisition Model Template in Microsoft Excel, watch my video tutorial below:

 

Get in Touch

challengejp_data_analystHi, my name is Jacek, and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Merger and Acquisition Plan in particular or financial analysis in general, don’t hesitate to get in touch.

Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.

Note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

Cash Flow Forecast in Microsoft Excel – read more about estimating revenues and costs with scenario analysis and valuation estimates.

Subscription Model with Churn Calculation – learn how to create a cash flow forecast with customer revenues, costs and lifetime value calculations.

Monthly Budget Forecast – another example of converting actuals into long-term projections.

Telecom Financial Model – explore building a forecast for an infrastructure project with more examples of estimating variable costs and debt.

Capital Investment Plan – another example of forecasting resources, capital and operating expenses and their return on investment.

Real Estate Investment Model – learn how to estimate and value cash flow and yield from initial capital investment.

Financial Model with Debt Financing – explore including debt calculations into a cash flow projection based on future revenues.

Marketing Investment Plan – see how the cost of acquiring users impacts future cash flow and company valuation.

Project Finance Model – learn how to consolidate multiple projects into one cash flow forecast.

Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial – a refresher of the spreadsheet creation basics.

Leave a Reply

Your email address will not be published. Required fields are marked *