Mergers and Acquisition (M&A) Financial Model (Excel XLSX)

Merger and Acquisition Model template consists of an excel model that assists the user to assess the financial viability of the resulting proforma merger of 2 companies and their synergies. Additionally, the user can feed the deal details, the buyer, target, as well as the merged company assumptions, and evaluate the merger in the tabs "Summary", "Merger Analysis", "Metrics" and "Graphs".

The model generates proforma post-merger financial statements, accretion/dilution analysis, merger increase in equity value, debt service coverage ratio, loan life coverage ratio, leverage ratios, purchase price allocation, and valuation of the buyer, target and post-merger company.

So, a quick overview of the model, in the contents tab you can see the structure of the model and by clicking on any of the headlines to be redirected to the relevant worksheet.

Additionally, there is a description of the color-coding of the model in the "Manual" tab. Inputs / Assumptions are always depicted with a yellow fill and blue letters, call up (that is direct links from other cells) are filled in light blue with blue letters while calculations are depicted with white fill and black characters.

In the "Deal Details" tab, there are four set of assumptions. The first set is related to the deal assumptions such as share prices, shares outstanding for both buyer and target, acquisition premium, cash proportion of the purchase price, equity and debt issuance fees, as well as restructuring and other transaction costs.

The second set is related to the debt refinancing into senior and sub-debt, the third set is related to the fair value of the acquired target assets, while the fourth set is related to the synergies from the merger (don't forget to set synergies as positive numbers).

The next step is for the user to set the assumptions for both the buyer and target financial statements, such as revenues, growth rates, costs, margins, tax rates, depreciation rates, interest rates, CAPEX, new debt, repayments, and working capital assumptions (account receivables, payables, and inventory). Additionally, the user needs to feed some valuation assumptions such as Weighted Average Cost of Capital, and E.V. to EBITDA exit multiple.

Regarding the tab "Merger FS", a similar set of assumptions such as balance sheet and valuation items need to be adjusted (profit and loss items, as well as debt refinancing come from the merger of the two companies).

The results are presented in the following tabs "Summary", "Merger Analysis", "Metrics" and "Graphs"

In the "Summary" tab, you will see the pre-merger buyer and target metrics, as well as the post-merger metrics. Additionally, you will see the financing assumptions, sources and uses of funds, as well as the revenues and EBITDA of the separate and combined company.

In the "Merger Analysis" tab, an accretion / dilution analysis is performed, as well as a calculation as to whether there is an increase in the equity value per share.

In the "Metrics" tab some leverage ratios are calculated along with the debt service and loan life coverage ratios.

In the "Graphs" tab, a series of graphs are presented which are related to the sources and uses of funds, synergies, purchase price allocation, earnings and operating flows per share, EBITDA and Free Cash Flows to the Firm, leverage ratios, coverage ratios, enterprise and equity value, as well as equity value per share.

Checks: A dedicated worksheet that makes sure that everything is working as it should!

Important Notice: Yellow indicates inputs and assumptions that the user can change, blue cells are used for called up cells, and white cells with black characters indicates calculation cells.