How to Create a Merger and Acquisition Model in Excel

This tutorial shows how to create a merger and acquisition financial model 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.

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 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 the template also calculates the synergy effect resulting from the merger, allowing 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.

Rember to show the synergies generated by completing a merger or acquisition.

Learn More: Visit my tutorial on How to Create a Telecom Financial Model in Excel 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, becomes 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 in Excel 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 in Excel 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 allows adjusting the amount to arrive at the final figure.

Finally, the template allows for setting the percentage of the amount financed by debt. For example, if the merger or acquisition requires $1m of financing, you can select to fund the transaction with 65% of the amount through debt and the rest through equity or cash.

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 an Excel 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 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 in Excel 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 harm 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 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 in Excel 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 an Excel merger and acquisition model.

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 merger and acquisition model 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 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 in Excel.

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

Get in Touch

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

Click here if you would like to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you need help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

Learn More:

Cash Flow Forecast in Excel – another example of 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.

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 Excel – Beginner’s Crash Tutorial – a refresher of the spreadsheet creation basics.