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.

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.

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.

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.

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

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.

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

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

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.

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.

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.

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.

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.

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].

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

Hi, 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.