We set this formula up a bit differently in our model and only included Financing
Fees rather than all Fees & ... Both sides must balance, or else your combined
balance sheet will be off and ..... flow can be used to pay off more of the principal.
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com Transaction Structure & Assumptions Equity Purchase Price = Diluted Shares Outstanding * Per Share Purchase Price For private companies, you don’t have shares outstanding or share prices, so the equity purchase price is just a lump sum amount. Note that the buyer might assume options rather than paying for them in cash, but effectively they’re still part of the purchase price. Funds Required = Equity Purchase Price + Refinanced Debt + Minority Interest Purchased + Fees & Expenses “Funds Required” is simply how much money the buyer needs to come up with to buy the seller. We don’t include assumed debt or assumed minority interest, because the balance sheet totals are not changing for anything that is assumed rather than purchased or re-financed. We set this formula up a bit differently in our model and only included Financing Fees rather than all Fees & Expenses, because we’re assuming that some fees are always paid in cash. Financing Fees = Debt Tranche 1 * Percentage + Debt Tranche 2 * Percentage + Debt Tranche 3 * Percentage… Advisory Fees = Percentage * Equity Purchase Price Legal & Miscellaneous Fees = Fixed Number Fees & Expenses = Financing Fees + Advisory Fees + Legal & Miscellaneous Fees Debt financing fee percentages are higher (sometimes 2-3%) than M&A advisory fees (often 1% or less). These percentages decrease as the deal size increases. Note that under the new accounting rules, Financing Fees are capitalized rather than expensed. Sources & Uses Sources tell you where your money is coming from, and Uses tell you what it’s being used for in a transaction. Both sides must balance, or else your combined balance sheet will be off and the model will not work correctly. Potential Sources: Cash from Buyer New Stock Issued New Debt Issued Preferred Stock Issued Debt Assumed Minority Interest Assumed Management Rollover of Equity
Potential Uses: Equity Value of Company Transaction Fees Capitalized Financing Fees Refinanced Debt Debt Assumed Minority Interest Assumed Minority Interest Purchased Management Rollover of Equity
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com In our model, we’re only looking at a few of these because not everything is relevant. Note that anything assumed by the buyer will show up under both sources and uses, so items like Debt Assumed and Minority Interest Assumed “cancel each other out.” You could add items to either of these columns as long as everything balances – for example, if Yahoo! had pension obligations that had to paid off (similar to debt), then we might add that to Uses. Purchase Price Allocation & Creation of Goodwill New Goodwill Created = Equity Purchase Price – Seller Book Value + Seller’s Existing Goodwill – PP&E Write-Up – Intangibles Write-Up – Seller’s Existing Deferred Tax Liability + Write-Down of Seller’s Existing Deferred Tax Asset + Newly Created Deferred Tax Liability We subtract the Seller’s Book Value and add its Existing Goodwill here because we assume they are both written off in the transaction. “Book Value” is just Shareholders’ Equity in this calculation. Intangibles Write-Up = (Equity Purchase Price – Seller Book Value + Seller’s Existing Goodwill) * Percentage PP&E Write-Up = (Equity Purchase Price – Seller Book Value + Seller’s Existing Goodwill) * Percentage OR Existing PP&E Balance * Write-Up Percentage New Deferred Tax Liability = $0 in an Asset or 338(h)(10) Purchase; Buyer Tax Rate * (PP&E Write-Up + Intangibles Write-Up) in a Stock Purchase Write-Down of Existing Deferred Tax Asset = Entire NOL Balance in an Asset of 338(h)(10) Purchase; Buyer Tax Rate * MAX(0, NOL Balance – Allowed Annual NOL Usage * Expiration Period in Years) Deferred Revenue Write-Down = Seller’s Deferred Revenue Balance * (1 – Fair Value %) Note that the new goodwill creation, intangibles write-up, PP&E write-up, new deferred tax liability creation, and write-down of the deferred tax asset all occur “at the instant” of the transaction, whereas deferred revenue is written down after the transaction closes. Combining & Adjusting the Balance Sheets Remember that when you adjust the Balance Sheet in this type of transaction, a debit represents an addition to the Assets side and a subtraction from the Liabilities & Shareholders’ Equity side. A credit represents a subtraction from the Assets side and an addition to the Liabilities & Shareholders’ Equity side. With that in mind, here’s a quick run-down of how you adjust common Balance Sheet items in a merger model:
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com Assets Cash: Add Buyer + Seller’s Cash and subtract any cash used in transaction. Short-Term Investments / Other Cash-Equivalents: Add Buyer + Seller balances. Accounts Receivable: Add Buyer + Seller balances; sometimes adjust for inter-company receivables. Inventory: Add Buyer + Seller balances. Other Current Assets: Add Buyer + Seller balances. Capitalized Financing Fees: Add this item to Assets and create it in transaction. Net PP&E: Add Buyer + Seller balances and add PP&E Write-Up. Equity Investments: Add Buyer + Seller balances. Goodwill: Add Buyer + Seller balances; subtract Seller’s balance; add Newly Created Goodwill. Net Intangible Assets: Add Buyer + Seller balances and add Intangibles Write-Up. Deferred Income Taxes: Add Buyer + Seller balances; could also adjust for DTA Write-Down here. Other Long-Term Assets: Add Buyer + Seller balances. Liabilities & Shareholders’ Equity Accounts Payable: Add Buyer + Seller balances; sometimes adjust for inter-company payables. Accrued Expenses: Add Buyer + Seller balances. Deferred Revenue: Add Buyer + Seller balances (deferred revenue written down after transaction close). Other Current Liabilities: Add Buyer + Seller balances. Debt: Add Buyer + Seller balances; subtract any refinanced debt; add newly issued debt. Deferred Tax Liabilities: Add Buyer + Seller balances; subtract Seller’s Balance; add newly created DTL; add write-down of seller’s DTA if you haven’t already adjusted on the Assets side. Other Long-Term Liabilities: Add Buyer + Seller balances. Minority Interests: Add Buyer + Seller balances; subtract any purchased Minority Interests. Shareholders’ Equity: Add Buyer + Seller balances; subtract Seller’s Shareholders’ Equity; subtract expensed transaction fees; add value of common / preferred stock issued. You subtract expensed transaction fees in SE because you assume they come out of Retained Earnings. Synergies You could calculate revenue and expense synergies dozens of different ways, but the easiest and most “standard” methods are as follows: Revenue Synergies: Look at the seller’s revenue model and assume that their average selling price, average customer value, or some other “bottoms-up” metric like that goes up by a fixed percentage due to combining with the buyer.
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com Expenses Associated with Revenue Synergies: There’s no such thing as a free lunch. You need to assume a margin on all revenue synergies to reflect the higher COGS expenses as well – easiest way to do this is to assume the seller’s existing gross margin on the new revenue. Expense Synergies: There are three main areas where you could assume synergies here: employees, buildings, and capital expenditures. Employees: Go into the seller’s expense model and assume a certain percentage of employees are cut – or assume that the average expense per employee falls. Buildings: Look at the buyer and seller’s operating lease expenses in their filings and assume that a certain percentage of the buildings can be consolidated, reducing the combined company’s rental expense. CapEx: Assume that the seller’s CapEx spending needs fall by a certain percentage, and then reflect that on the Cash Flow Statement and also reduce their Depreciation expense to correspond with this drop in CapEx. Combining Income Statements Combined Revenue = Buyer Revenue + Seller Revenue + Revenue Synergies Combined Gross Profit = Buyer Gross Profit + Seller Gross Profit + Revenue Synergies – Expenses Associated with Revenue Synergies Combined Operating Income = Buyer Operating Income + Seller Operating Income + Revenue Synergies – Expenses Associated with Revenue Synergies + Expense Synergies Combined Pre-Tax Income = Combined Operating Income + Buyer Interest Income/Expense + Seller Interest Income/Expense + Acquisition Effects Combined Net Income = Combined Pre-Tax Income * Buyer Tax Rate Combined EPS = Combined Net Income / (Buyer’s Old Shares Outstanding + New Shares Issued) Acquisition Effects Acquisition effects can get tricky to fully take into account, but the basic idea is simple: use “year 1” as your base, and then for all subsequent years make sure you take into account what has happened previously and whether or not an item has been completely depreciated or completely amortized. Foregone Interest on Cash: Year 1 Expense = Buyer Cash Used in Transaction * Effective Cash Interest Rate Year 2 Expense = (Buyer Cash Used in Transaction + Year 1 Foregone Interest) * Effective Cash Interest Rate
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com And the pattern continues like that – each time you need to add all the previous foregone interest expenses. New Interest Expense on Debt = Average Balance of Tranche 1 * Tranche 1 Interest Rate + Average Balance of Tranche 2 * Tranche 2 Interest Rate + Average Balance of Tranche 3 * Tranche 3 Interest Rate… Technically you should make a debt schedule to determine these average yearly balances of each tranche of debt – but in practice you usually make a simple percentage estimate without bothering with a debt schedule. Interest Expense on Refinanced Debt: Simply look at what the seller’s old projected interest expense on debt was, and add this because it’s money that you’re “re-claiming” – the opposite of foregone interest on cash. New Amortization of Intangibles: Year 1 Expense = Intangibles Write-Up Amount / Amortization Period Year 2 Expense = MIN(Intangibles Write-Up Amount / Amortization Period, Intangibles Write-Up Amount – Total Amortized Thus Far) And the pattern continues – each time you check to make sure that not everything has been amortized. Financing Fees Amortization: Year 1 Expense = Capitalized Financing Fees / Amortization Period Year 2 Expense = MIN(Capitalized Financing Fees / Amortization Period, Capitalized Financing Fees – Total Amortized Thus Far) New Depreciation from PP&E Write-Up: Year 1 Expense = PP&E Write-Up / Depreciation Period Year 2 Expense = MIN(PP&E Write-Up / Depreciation Period, PP&E Write-Up – Total Depreciated Thus Far) Deferred Revenue Write-Down: Year 1 Expense = Deferred Revenue Write-Down / Write-Down Period Year 2 Expense = MIN(Deferred Revenue Write-Down / Write-Down Period, Deferred Revenue Write-Down – Total Written Down Thus Far) The pattern is the same for all of these – you simply project the Year 2 formula across to following years and always check to see whether everything has been amortized / depreciated / written down yet. Taxes Taxes cause a lot of confusion in merger models and LBO models, and even full-time bankers rarely know how to treat everything 100% correctly. The basic concept, though, is simple: you calculate the combined company’s book taxes based on the combined pre-tax income and the buyer’s tax rate.
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com Next you add back items that are not “really” tax-deductible and subtract items that are tax-deductible. Then, you take into account any NOLs you can use to reduce the taxable income even further to arrive at the cash tax number – what the company actually pays the government in cold, hard cash. Finally, you adjust the Deferred Tax Liability by this difference between the cash taxes and book taxes. Allowed NOL Usage Per Year = $0 in Asset / 338(h)(10) Purchase; Equity Purchase Price * Highest of Past 3 Months’ Adjusted Long-Term Rates in Stock Purchase Pre-NOL Taxable Income = Book Income Tax Expense + Book Amortization of Intangibles + Book Amortization of Goodwill + Book Depreciation – Tax Amortization of Intangibles – Tax Amortization of Goodwill – Tax Depreciation Cash Taxes Payable = Pre-NOL Taxable Income – Allowed NOL Usage Deferred Tax Liability: Decreases if Cash Taxes > Book Taxes, and increases if Book Taxes > Cash Taxes Stock Purchase – Book vs. Cash Taxes Book Taxable Income: Book Income Tax Expense:
Asset / 338(h)(10) Purchase – Book vs. Cash Taxes
GAAP FY 2010E FY 2011E FY 2012E $29,316 $32,248 $34,588 $8,795 $9,674 $10,376
Book Taxable Income: Book Income Tax Expense:
GAAP FY 2010E FY 2011E FY 2012E $29,316 $32,248 $34,588 $8,795 $9,674 $10,376
Cash Tax Calculation: Plus: Book Amortization of Intangibles Write-Up: Plus: Book Amortization of Goodwill: Plus: Book Depreciation of Asset Write-Up: Less: Tax Amortization of Intangibles Write-Up: Less: Tax Amortization of Goodwill: Less: Tax Depreciation of Asset Write-Up: Pre-NOL Taxable Income:
$1,491 $0 $23 $0 $0 $0 $30,830
$1,491 $1,491 $0 $0 $23 $23 $0 $0 $0 $0 $0 $0 $33,762 $36,102
Cash Tax Calculation: Plus: Book Amortization of Intangibles Write-Up: Plus: Book Amortization of Goodwill: Plus: Book Depreciation of Asset Write-Up: Less: Tax Amortization of Intangibles Write-Up: Less: Tax Amortization of Goodwill: Less: Tax Depreciation of Asset Write-Up: Pre-NOL Taxable Income:
$1,491 $1,491 $1,491 $0 $0 $0 $23 $23 $23 $497 $497 $497 $1,982 $1,982 $1,982 $30 $30 $30 $28,321 $31,252 $33,593
Potential NOL Usage: Allowed NOL Usage: NOL-Adjusted Pre-Tax Income: Remaining NOLs:
$1,162 $1,162 $29,668 $0
$0 $0 $33,762 $0
Potential NOL Usage: Allowed NOL Usage: NOL-Adjusted Pre-Tax Income: Remaining NOLs:
$0 $0 $28,321 $0
$0 $0 $31,252 $0
$0 $0 $33,593 $0
$8,496 $299
$9,376 $299
$10,078 $299
Cash Taxes Payable: Increase / Decrease in Deferred Tax Liability:
$0 $0 $36,102 $0
$8,900 $10,129 $10,831 ($106) ($454) ($454)
Cash Taxes Payable: Increase / Decrease in Deferred Tax Liability:
Book Amortization of Intangibles: Intangibles Write-Up / Book Amortization Period Book Amortization of Goodwill: $0 because Goodwill is never amortized for book purposes Book Depreciation of Asset Write-Up: PP&E Write-Up / Book Amortization Period Tax Amortization of Intangibles: $0 in Stock Purchase; Intangibles Write-Up / Tax Amortization Period in Asset / 338(h)(10) Purchase Tax Amortization of Goodwill: $0 in Stock Purchase; New Goodwill / Tax Amortization Period in Asset / 338(h)(10) Purchase Tax Depreciation of Asset Write-Up: $0 in Stock Purchase; PP&E Write-Up / Tax Depreciation Period in Asset / 338(h)(10) Purchase
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com Some notes on these book and tax amortization / depreciation periods: Book Amortization of Intangibles Period: Usually 5 years Tax Amortization of Intangibles Period: 15 years Book Amortization of Goodwill Period: N/A; Goodwill not amortized for book purposes Tax Amortization of Goodwill Period: 15 years Book Depreciation of PP&E Write-Up Period: Matches existing useful life for written-up assets Tax Depreciation of PP&E Write-Up Period: No standard, but often shorter than book depreciation period because companies like to depreciate assets more quickly for tax purposes Projecting the Combined Balance Sheets / Cash Flow Statements For most of these items, you simply combine the buyer and seller’s separate line items in future years and carry everything across, once you’ve made the transaction adjustments. Exceptions: Cash: This needs to flow in from the combined Cash Flow Statement. Capitalized Financing Fees: You need to amortize this appropriately based on the period you selected. Net PP&E: Add Buyer + Seller CapEx, subtract Buyer + Seller Depreciation, and reduce it by whatever you’ve chosen for CapEx synergies and the corresponding decrease in Depreciation. Goodwill: Assume this is constant at the new number you calculated; check for impairment each year. Net Intangible Assets: You need to take into account not only the existing amortization from the buyer and seller, but also the new amortization expense created in the transaction. Debt: You need to create a debt schedule for this one to accurately track how much is paid off each year. Deferred Tax Liabilities: This one goes up or down by the difference between book taxes and cash taxes as calculated on your tax schedule. Shareholders’ Equity: This one flows in from other items on the combined statements. Take the old balance, add Net Income, Stock-Based Compensation, add/subtract any other items you haven’t accounted for (e.g. dividends), common stock issued/re-purchased, and the effect of exchange rates. Cash Flow from Financing: Rather than adding the buyer and seller’s individual sections here, you normally just take the buyer’s items for Common Stock Issued / Re-Purchased etc. because you’re assuming the seller’s Shareholders’ Equity is wiped out in the transaction. Debt Schedules This will be covered in much greater detail in the Advanced LBO Model, but here’s the basic idea behind how you set up debt schedules for a merger model with bank debt (amortization) and high-yield debt (bullet maturity):
Advanced Merger Model – Quick Reference Common Formulas & Model Setup http://breakingintowallstreet.com Bank Debt: Assume a percentage mandatory repayment each year, and then assume that any “extra” cash flow can be used to pay off more of the principal. The formulas go something like this: Mandatory Repayment = MIN(Mandatory Repayment % * New Debt Balance, Beginning Balance Each Year) Optional Repayment = MIN(Cash Flow Available for Debt Repayment – Mandatory Repayment, Beginning Balance – Mandatory Repayment) Bank Debt Interest Expense = (Balance at Beginning of Year + Balance at End of Year) / 2 * Interest Rate Note that this creates a circular reference – to get rid of it, you can be more conservative and assume the interest expense is just based on the balance at the beginning of the year instead. High-Yield Debt: This has bullet maturity, so no principal is paid off each year, there’s no mandatory repayment, and you’re not “allowed” to repay any of the principal before it’s due. So there’s only 1 formula: High-Yield Debt Interest Expense = (Beginning Balance + Ending Balance) / 2 * Interest Rate These formulas assume that we only have 2 tranches of debt in our model. It gets more complex if you have, say, 10 tranches of debt and you need to take into account how much of your cash flow is being used to repay other tranches before calculating the optional repayment each year. This scenario will be covered in more detail in the Advanced LBO Model, because multiple tranches of debt are far more likely to occur in an LBO. Contribution Analysis The idea here is simple: you look at what % revenue, gross profit, EBITDA, pre-tax income, net income, etc. the buyer and seller are contributing and then use that to estimate what % of the combined company the buyer and seller should own, and what the seller’s valuation should be. Here are the formulas for an EBITDA-based analysis: Buyer % EBITDA = Buyer EBITDA / (Buyer EBITDA + Seller EBITDA) Pro-Forma Combined EV = Buyer Pre-Transaction Enterprise Value / Buyer % EBITDA Implied Seller EV = Pro-Forma Combined EV – Buyer Pre-Transaction Enterprise Value Implied Seller Equity Value = Implied Seller EV + Cash – Debt – Minority Interest – Preferred Stock Implied Seller Share Price = Implied Seller Equity Value / Diluted Shares Outstanding Implied Seller Ownership = Implied Seller Equity Value / (Implied Seller Equity Value + Buyer PreTransaction Equity Value)
