merger modeling

7/27/2019 Merger Modeling

1/7

Introduction

In this section we demonstrate how to model a merger of two public companies in Excel. Each topic contains a

spreadsheet with which you can interact within your browser to inspect cell equations and read comments, or

download and open in Excel. This is a very detailed M&A model, loaded with bells and whistles and built

using "best-practices" for financial modeling, that should address most of the M&A situations you mayencounter. However, it is not intended as a template for modeling allM&A transactions; just like snowflakes,

no two deals are alike. The topics in this section are presented in sequential order, as if you were building the

model from scratch, but you are welcome to skip to any page that fulfills your particular education needs.

Below some spreadsheets, we provide Excel tips and keyboard shortcuts that highlight best practices for

financial modeling or provide instruction on how to perform specific tasks within Excel. Most of these tips and

shortcuts assume the user is using Excel 2003 for Windows, as most finance professionals still are. However,

some tips and shortcuts are also applicable to Excel 2007.

Since our focus in this section is on modeling, we will not necessarily cover the accounting rules and

regulations and theories governing each topic. For most topics, you can find more detailed background

information elsewhere on this web site.

Company Information

We begin our M&A model by plugging into the spreadsheet some basic market data and corporate information

about the target ("TargetCo") and acquirer ("BuyerCo"). Market data can be obtained from a number of

sources, including FactSet, CapitalIQ,Google Finance,Reuters, and Bloomberg. Most corporate information

can be found in SEC filings available online at theEDGAR database. Some things to keep in mind:

Make sure that the 52-week highs/lows are closingprices, not intraday trading prices. Basic shares outstanding should be the most recent figure available, from the latest 10-Q, 8-K, or other source,

and should be adjusted for any transactions that would affect these figures (e.g. stock acquisitions) since they

were last reported.

Stock options and convertible securities details are usually found in the most recent 10-K, but can sometimesbe found in the latest 10-Q.

The LTM date is the ending date of the last fiscal quarter for which an 8-K earnings release or 10-Q/K isavailable. For example, if the last fiscal quarter ended June 30, but earnings have not been reported for that

quarter, the LTM date is March 31.

When inputting options data, be sure to use options outstanding rather than options exercisable. Optionsusually vest automatically upon a change in control.
http://finance.google.com/http://finance.google.com/http://finance.google.com/http://www.reuters.com/http://www.reuters.com/http://www.reuters.com/http://www.sec.gov/edgar.shtmlhttp://www.sec.gov/edgar.shtmlhttp://www.sec.gov/edgar.shtmlhttp://www.sec.gov/edgar.shtmlhttp://www.reuters.com/http://finance.google.com/


2/7

Sample options table taken from Intel's 2008 10-K.

Note that we have specified which analyst research reports we are using for financial projections. When

selecting a single research report, be sure that its key estimates (e.g. sales, EBITDA, EBIT, etc.) track

consistently with Wall Street consensus and that ample detail 2-3 years forward is provided. Using individual

research reports

rather than consensus

and citing your sources makes it easy for others to check your work.

Enterprise Value

Let's now turn our attention to computing the enterprise values of the target and acquirer. To do so, we first

need to calculate the number of fully diluted shares outstanding ("FDSO"). We start with the basic shares

outstanding found in the last step and add to that the number of share equivalents calculated using the treasury

method, which assumes that proceeds from the exercise of in-the-money ("ITM") options are used to

repurchase shares at the current market price. Next, we add the number of shares that would result from the

conversion of ITM convertible securities. Multiplying FDSO by the current share price gives equity value, to

which we add net debt to yield enterprise value. In order to calculate the Let's first look at what we did with the

options tables. See our section oncomputing enterprise valuefor further explanation of these steps.

Note that we have added three columns between the "Average Strike" and "Treasury Shares" columns in the

options data tables. For now, do not concern yourself with these columns; we have included them here so that

we can more easily compute FDSO for a range of transaction prices (i.e. the price per share paid by the

acquirer to the target's shareholders) in a subsequent step.

Deal Assumptions

When building a model, it is important that the model be flexible enough to consider a wide range of possible

scenarios. Let's now add some assumptions about the transaction that, when changed, will flow through the

model. The assumptions presented below are simply suggestions, and the specific transaction you are

contemplating may call for other assumptions. In any case, most or all of the deal assumptions should be

grouped into a single section of your model so that anyone can see, at a glance, what assumptions you are

making.

Note that, in the spreadsheet below, we have left the tax rate blank. The tax rate used is the acquirer's tax rate,

which we will determine in a subsequent step.
http://www.macabacus.com/valuation/enterprise-valuehttp://www.macabacus.com/valuation/enterprise-valuehttp://www.macabacus.com/valuation/enterprise-valuehttp://www.macabacus.com/valuation/enterprise-value


3/7

Buyer's Historical P&L

The first step in our merger analysis is to model BuyerCo's income statement to include historical results and

projections. When sufficient data is available, we can calculate both "cash" and GAAP net income and EPS

figures. Cash figures exclude the non-cash amortization and stock-based compensation ("SBC") expenses and

non-recurring charges that GAAP figures include.

It is important to calculate cash figures when BuyerCo trades on cash, rather than GAAP, multiples. For

example, BuyerCo might trade at 18.4x 2008 cash EPS. You can tell if a company trades on cash figures by

comparing GAAP EPS with the EPS figures that Wall Street research analysts compute. If these numbers are

different, then analysts are using cash figures.

Companies often embed SBC expenses in SG&A (or COGS). We separate SBC from SG&A (or COGS)

whenever possible to facilitate the calculation of cash figures and the reconciliation of cash to GAAP. You can

usually determine how much of SG&A (or COGS) expense is attributable to SBC by examining the footnotes

of a company's public filings.

We also separate depreciation and amortization expenses because 1) doing so facilitates calculation of cash

figures and the cash-to-GAAP reconciliation and 2) depreciation and amortization are projected differently into

the future as we will soon see.

Note that some line items are computed as "% Margin" and others as "% of Sales". The latter term generally

applies to expenses and the former generally applies to income/earnings.

Most of the historical inputs to our historical income statement come from either a BuyerCo's public filings or,

if the historical figures require adjustment to account for acquisitions, divestitures, etc., from Wall Street

research reports.

The year headings at the top of the P&L are actually numbers, although they are formatted as YYYYA (where

the "A" symbolizes actualhistorical results). It is helpful and good practice to format year headers using the A,

P (="projected"), and E (="estimated") convention. To do so, open Excel's Format Cells dialog box (Alt>o>e)

and enter the custom format 0000\A, 0000\P, or0000\E, as desired. By using actual numbers in the year

headers, you can use the years to compute figures like CAGRs and discount factors.

Cash-to-GAAP Reconciliation STEP 5

Our next step is to reconcile our "cash" net income and EPS figures to the "GAAP" figures reported by

BuyerCo in its public company filings. We do this by subtracting the tax-effected amortization and stock-based

compensation expenses, as well as one-time items, from cash net income. Recall that we omitted these charges

from our calculation of cash net income in the last step.

We'll also add a line for capital expenditures ("capex"). Although not part of the income statement, capex is

included here because when we project the income statement into the future, we should observe that capex and

depreciation expense tend to converge over time. Also, capex is often projected as a percent of sales, so it

makes sense to include capex in the P&L.


4/7

Wall Street Projections STEP 6

Now that we have put together the BuyerCo's historical income statement, let's focus on projecting the income

statement into the future. If the company we are considering is public, it is advisable to use analyst estimates

for the first two or three years of the projection period, as available. You can use either Wall Street consensus

(an average of analysts' estimates), or a single analyst research report that is sufficiently detailed and does notdeviate far from consensus.

In the spreadsheet below, we use a single Deutsche Bank research report for BuyerCo to project the P&L for

2008 through 2010. In the absence of any equity research on BuyerCo, make our own projections that might

include "flatlining" the results from the last reported year. We'll cover flatlining in the next step.

Our Own Estimates STEP 7

Of course, we would like to project the income statement more than three years out. Since Wall Street equity

research analysts typically project just one to three years of financial results for public companies, we must

make our own assumptions to forecast the P&L further into the future. We often "flatline" our assumptions,

meaning that we use margins and growth rates from the most recent year for which data is available (2010, in

our case), and carry them forward.

We should point out one assumption we made regarding BuyerCo's 2011 and 2012 capex. Had we flatlined

capex using 2010's capex as a percent of sales (e.g. 5.9%), we wouldn't see much convergence of capex with

depreciation expense by the end of the projection period. So, we chose some lower percentages for these years

that provide for better convergence. Additionally, we could have used our own capex estimates for 2008

through 2010 rather than Deutsche's figures to show even greater convergence.

We also added a column that calculates the compound annual growth rates ("CAGRs") for various line items

on the income statement over the projection period. Don't get carried away with applying CAGRsthey are

immaterial for such line items as SG&A and amortization expenses, for example. In general, only show

CAGRs for revenue and other line items that would be computed as percent margins, rather than percent of

sales.

Target's Income Statement STEP 8

We create TargetCo's P&L in nearly the exact same way that we put together BuyerCo's P&L, so we'll skip the

intermediate steps and go straight to the final product.

Since we are building a merger model, it is necessary to align the target's reporting period with the acquirer's

reporting period. For example, if TargetCo has a fiscal year end of December 31 and BuyerCo has a fiscal year

end of September 30, we need to time-shift TargetCo's P&L to match BuyerCo's reporting period. In this

example, we assume that TargetCo and BuyerCo both have a fiscal year end of September 30.

Additionally, we apply the acquirer's tax rate to the target's income statement when building a merger model.

So, we link the tax rates in TargetCo's income statement to those in BuyerCo's P&L. It may sometimes be


5/7

helpful to plug the target's actual historical tax rates into the spreadsheet first to ensure that the GAAP figures

you calculate match those that were reported, then link to the acquirer's tax rates.

The are two assumptions we make in the TargetCo P&L that are worth noting. First, we think that applying

2010's 0.5% revenue growth to years 2011 and 2012 is a bit too conservative, so we instead input a growth rate

of 1.0%. Second, capital expenditures ("capex") was not available for 2010 in the Wachovia research report

used, so we flatlined 2010 capex using 2009's capex as a percent of sales

LTM Balance Sheets STEP 9

In this step we simply recreate the target's and acquirer's balance sheets using public company filings (e.g. 10-

Ks, 10-Qs, or 8-Ks) from the last twelve-month ("LTM") period. We will revisit this tab later to add in the

balance sheet adjustments that must be made to account for the acquisition.

When building a balance sheet, always include a "model check" as shown above to show that the balance sheet

actually balances. If you prefer to conceal these cells for aesthetic purposes, you can color them white, group

and hide the row (Alt>d>g>g + Alt>d>g>h), or set the row height to zero (Alt>o>r>e). It is best-practice to

output all model checks to a single worksheet so that any user can quickly tell if the model contains errors

PPREnterprise Value STEP 10

Our next step is to begin the purchase price ratio analysis ("PPR"). This analysis will show us what premiums

and multiples the acquirer would be paying for the target at various transaction prices per share (i.e. the

amount offered by the acquirer for each target share).

By evaluating the premiums and multiples at various transaction prices, we can determine what is an

appropriate price to pay for the target. You might use transaction comparables analysis to see at what multiples

the target's peers were acquired, and select an offer price that is inline with precedent transactions, but adjusted

for the specific circumstances surrounding the contemplated transaction. For example, if TargetCo has no otherviable options than to be acquired by BuyerCo, BuyerCo might seek to pay a multiple of EBITDA representing

a discount to EBITDA multiples paid in precedent transactions.

The calculation of premiums and multiples is straightforward. The tricky part here is making sure you properly

calculate fully diluted equity value and net debt, since whether options and convertible securities are in-the-

money ("ITM") depends on the transaction price. ITM convertibles are treated as equity and out-of-the-money

("OTM") convertibles are treated as debt.







for the specific circumstances surrounding the contemplated transaction. For example, if TargetCo has no other


6/7

viable options than to be acquired by BuyerCo, BuyerCo might seek to pay a multiple of EBITDA representing




money ("ITM") depends on the transaction price. ITM convertibles are treated as equity and out-of-the-money("OTM") convertibles are treated as debt.







for the specific circumstances surrounding the contemplated transaction. For example, if TargetCo has no other

viable options than to be acquired by BuyerCo, BuyerCo might seek to pay a multiple of EBITDA representing




money ("ITM") depends on the transaction price. ITM convertibles are treated as equity and out-of-the-money

("OTM") convertibles are treated as debt.

PPR Premiums Analysis STEP 11

The next step in the purchase price ratio analysis is to calculate the premium (or discount) each transaction

price represents relative to various historical TargetCo stock prices and TargetCo's current stock price. We can

then compare the premiums thus calculated to premiums paid in comparable transactions to see what

transaction, or "offer", price per TargetCo share might make sense in this deal.

PPRMultiples Analysis STEP 12

The last step in the purchase price ratio analysis is to calculate the valuation multiples at the various

transaction prices. Doing so allows us to refine our conclusions drawn in the previous step about what

constitutes a reasonable offer price by comparing the multiples thus computed with multiples paid in

comparable transactions.

For a given transaction price, you should generally observe that like multiples decrease from one year to the

next as the denominator (e.g. revenue, EBITDA, etc.) increases year-over-year in healthy companies. For

TargetCo, we see that this is not the case.

Sales (revenue) multiples are expressed to two decimal places, while EBITDA and P/E multiples are expressed

to one decimal place. To format multiples in your spreadsheet, open the Format Cells dialog box (Alt>o>e)


7/7

and enter the custom number format 0.00x_);(0.00x);0.00x_);@_) or

0.0x_);(0.0x);0.0x_);@_) , as desired.

Black-Scholes Option Value STEP 13

In our model we will assume that TargetCo's stock options held by employees of TargetCo are replaced upon

consummation of the transaction with new BuyerCo options. Employee stock options can be eithervestedor

unvested. To calculate the value of the replacement options, we must first determine the fair value of

TargetCo's employee stock options using the Black-Scholes or similar method.

Since the value of the TargetCo's employee stock options changes with the transaction price per share, we must

calculate the fair value of these options over a range of possible transaction prices. Most inputs the the Black-

Scholes formula shown in the spreadsheet below can be obtained from TargetCo's most recent 10-K filing.

Let's start a new tab called "GAAP" where we will enter our options and other accounting calculations.

Replacement Options STEP 14

Now that we have calculated the fair values of TargetCo's vested and unvested employee stock options

("ESOs"), we need only to multiply by the number of options outstanding to get the aggregate values of the

vested and unvested ESOs. BuyerCo will replace TargetCo's ESOs with BuyerCo ESOs of equal value, and

these values will be reflected in the purchase price as we will soon see.

merger modeling

Documents