Value Merger.xls
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Value Merger.xls as PDF for free.
More details
- Words: 3,621
- Pages: 15
Loading documents preview...
Value Merge Copyright © 2003 by Robert F. Bruner. Prepared by Mark Miles and Baocheng Yang under the direction of Robert F. Bruner. This model gives a template for DCF valuation of a target and buyer firm. The model also estimates EPS dilution/accretion for the buyer. To use the model, enter assumption values indicated in blue. Please see Chapter 9, "Valuing Firms" of Applied Mergers and Acquisitions by Robert Bruner for a discussion of this analysis. To use the model, please follow the instructions in the "Contents" page. The model intentionally incorporates "circular reference." Do not be alarmed if you are warned by Microsoft Excel about a circular reference. Simply set your software to "iterate" so that it produces estimates that are internally consistent--go to Tools/Options/Calculation and check "Iteration" and insert a number that you believe will be sufficient (50 iterations should do.) Prepared by Professor Robert F. Bruner, http://faculty.darden.edu/brunerb/. This software was prepared solely for non-commercial educational purposes as a supplement to the book, Applied Mergers and Acquisitions , by Robert F. Bruner. While it has been reviewed with care, and to the best of the author's knowledge performs as described in the book, the publisher and author do not represent that this software is error-free, and cannot be accountable for errors or omissions, nor for any liability arising from uses of this software. ALL WARRANTIES, EXPRESS AND IMPLIED, INCLUDING THE WARRANTY OF MERCHANTABILITY, AND FITNESS FOR A PARTICULAR PURPOSE, ARE HEREBY DISCLAIMED. NEITHER THE AUTHOR OR PUBLISHER SHALL BE LIABLE OR RESPONSIBLE FOR ANY LOSS, INJURY, CLAIM, LIABILITY, DAMAGE OR EXPENSE OF ANY KIND OR NATURE CAUSED BY OR ARISING IN CONNECTION WITH THE USE OF THIS SOFTWARE, INCLUDING BUT NOT LIMITED TO DEFECTS IN THE MEDIUM, OR ERRORS, OMISSIONS, OR INACCURACIES IN THE DATA PRODUCED BY THIS SOFTWARE. IF FOR ANY REASON, BY OPERATION OF LAW OR OTHERWISE, ANY PORTION OF THE FOREGOING LIMITATION OF LIABILITY SHALL BE VOID OR OTHERWISE UNENFORCEABLE, THEN, IN SUCH EVENT, THE MAXIMUM LIABILITY OF THE PUBLISHER OR AUTHOR, IF ANY, SHALL BE NOT EXCEED THE TOTAL AMOUNT ACTUALLY PAID TO THE PUBLISHER BY THE USER FOR THE SOFTWARE. This software is intended for use as an educational tool, and not as a substitute for legal, financial or other professional or expert advice. Use by the reader of this software constitutes assent to the terms of use and disclaimer of liability.
Contents
Instructions
TAB 1: "Financials" Operating Assumptions Income Statement Balance Sheet Statement of Cash Flows Financing Assumptions Depreciation & Amortization Schedule Summary Ratios
Row 4 42 70 104 136 170 203
BLUE cells are inputs Fill all of these in
TAB 2: "Valuation" Cost of Capital Terminal Value WACC Method Equity Residual Adjusted Present Value
Row 4 27 47 89 130
Model is set on automatic calculation, 100 iterations No need for user to change settings
TAB 3: "Merger Scenario" Assumptions Transaction Adjustments Summary Outputs
Row 5 34 56
RED cells are toggles Change these to run different scenarios BLACK cells are calculations Do not input numbers in these
Target financial data in "merger scenario" tab is linked to "financials" tab. To run scenarios, user must input acquiror's financial data in specified blue cells
For addition explanation, see appendix to Chapter 9, Applied Mergers and Acquisitions by Robert F. Bruner.
Company Name Assumptions
Note: "#DIV/0!" likely indicates the needs to enter data somewhere in the model.
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
2002 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2003 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
Depreciation, Amortization, & Other Existing Intangible Book Amortization Existing Intangible Tax Amortization
Years 40 yrs 15 yrs
x Declining 1.0 x 1.0 x
Existing PPE Book Depreciation Existing PPE Tax Depreciation Future CapEx Book Depreciation Future CapEx Tax Depreciation
5 yrs 4 yrs 20 yrs 7 yrs
1.0 x 1.0 x 1.0 x 2.0 x
Strike Price
Shares (000)
$0.00 $0.00
0 0
Operating Assumptions Revenue Growth COGS (Excl Depr) / Revenues SG&A / Revenues Other Exp (Income) / Revenues Effective Tax Rate Minority Interest / Pre-tax Income Income from Affiliates / Pre-tax Income Wgtd Average Shares Outstanding (000) Common Dividends per Share Minimum Cash Balance Days in Receivables Days in Inventory Days in Payables Other Current Assets / Revenues Other Current Liabilities / Revenues Capital Expenditures (excl Acquisitions)
2001
Balance Sheet Shares Outstanding (000)
1,000
Options / SARs Outstanding (000) Average Strike Price
1,000 $0.00
Financing Assumptions Straight Debt 1 Straight Debt 2 Straight Debt 3 Straight Debt 4 Straight Debt 5
Coupon
0.0% 0.0% 0.0% 0.0% 0.0%
Straight Preferred Stock Convertible Preferred Stock Convertible Debt
0.0% 0.0% 0.0%
Int. Rate on Excess Cash Use of Excess Cash Flow (1=Build Cash; 2=Repay Debt)
4.0% 1
Interest Rate on Revolver
0.0%
12/10/20147:41 AM Page 3
Company Name Income Statement
Revenues COGS (excluding Depreciation) Selling, General, & Administrative EBITDA Depreciation Amortization of Intangibles Other Expense (Income) EBIT Interest (Income) Interest Expense - Straight Debt Interest Expense - Convertible Debt Interest Expense - Revolver Pre-Tax Income Income Taxes Minority Interest (Income) from Affilitates Net Income Straight Preferred Dividends Convertible Preferred Dividends Net Income to Common Earnings per Share: Basic Fully Diluted
2001 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2002 $0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2003 $0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
NM NM
NM NM
NM NM
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
12/10/20147:41 AM Page 4
Company Name Balance Sheet 2001 0.0 0.0 0.0 0.0 0.0
2002 0.0 0.0 0.0 0.0 0.0
2003 0.0 0.0 0.0 0.0 0.0
2004 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Property , Plant, & Equipment Accumulated Depreciation Net Property, Plant, & Equipment Intangible assets Goodwill Equity in Income of Affiliates Other Long-Term Assets Total Assets
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
Accounts Payable Other Current Liabilities (excl. S/T Debt) Total Current Liabilities
0.0 0.0 0.0
0.0 0.0 0.0
0.0 0.0 0.0
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
Straight Debt Convertible Debt Revolver Total Debt
0.0 0.0
0.0 0.0
0.0 0.0
0.0
0.0
0.0
0.0 0.0 #DIV/0! 0.0
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
Deferred Taxes Minority Interest Other Long-Term Liabilities Total Liabilities
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
#DIV/0! 0.0 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
Straight Preferred Stock Convertible Preferred Stock Common Stock (Par + APIC+ Other Equity) Retained Earnings Total Liabilities & Stockholders' Equity
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Cash and Equivalents Accounts Receivable Inventory Other Current Assets Total Current Assets
#DIV/0!
12/10/20147:41 AM Page 5
Company Name Statement of Cash Flows 2002 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2003 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2004 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
2005 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
Projected 2006 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
2007 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
2008 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0
0.0 0.0
0.0 #DIV/0!
0.0 0.0
0.0 #DIV/0!
0.0 #DIV/0!
0.0 #DIV/0!
0.0 0.0
0.0 0.0
0.0
0.0
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
Straight Preferred Dividends Convertible Preferred Dividends Common Stock Dividends Equity Issuance / (Repurchases) Other Change in Cash and Equivalents
0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
Beginning Cash and Equivalents Ending Cash and Equivalents
0.0 0.0
0.0 0.0
0.0 #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
$0.00 $0.00
$0.00 $0.00
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
Net Income Depreciation Amortization of Intangible Increase in Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Charges (Increase) / Decrease in NWC Cash Flow from Operations Capital Expenditures Free Cash Flow Debt Issuance / (Repayments): Straight Debt Convertible Debt Revolver Debt Issuance / (Repayments)
Cash Flow from Operations per Share Free Cash Flow per Share
12/10/20147:41 AM Page 6
Company Name Financing Assumptions Amortization / (Issuance) Straight Debt 1 Straight Debt 2 Straight Debt 3 Straight Debt 4 Straight Debt 5 Convertible Debt Total Debt Amortization / (Issuance)
2003
Straight Preferred Stock Convertible Preferred Stock Common Equity Equity Repurchases / (Issuance)
2004 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2005 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Projected 2006 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2007 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2008 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
Debt / Preferred Balances Straight Debt 1 Straight Debt 2 Straight Debt 3 Straight Debt 4 Straight Debt 5 Revolver Total Straight Debt Convertible Debt Total Debt
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
Straight Preferred Stock Convertible Preferred Stock Common Equity (excludes R/E) Total Book Equity
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
Total Book Capital
0.0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
12/10/20147:41 AM Page 7
Company Name Depreciation & Amortization Schedule Book Depreciation Existing PP&E Capital Expenditures (2004-2008): 2004 2005 2006 2007 2008 Total Book Depreciation
Amount 0.0 0.0 0.0 0.0 0.0
Tax Depreciation Existing PP&E
Basis #DIV/0!
Capital Expenditures (2004-2008): 2004 2005 2006 2007 2008 Total Tax Depreciation
Amount 0.0 0.0 0.0 0.0 0.0
Excess of Tax over Book Depreciation Increase (Decrease) in Deferred Taxes Amortization of Existing Intangible Assets Tax Book Excess of Tax over Book Amortization Increase in Deferred Taxes
Life 15 yrs 40 yrs
2004 0.0
2005 0.0
0.0
0.0 0.0
0.0
Projected 2006 0.0
2007 0.0
2008 0.0
0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0
0.0
0.0
0.0 0.0 0.0 0.0 0.0 0.0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0
0.0 0.0
0.0 0.0 0.0
0.0 0.0 0.0 0.0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
12/10/20147:41 AM Page 8
Company Name Summary Ratios
Income Statement EBITDA / Revenues EBIT / Revenues Return on Revenues
2001 #DIV/0! #DIV/0! #DIV/0!
2002 #DIV/0! #DIV/0! #DIV/0!
2003 #DIV/0! #DIV/0! #DIV/0!
2004 #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0!
EBITDA / Interest Expense EBIT / Interest Expense
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
Return on Average Equity Return on Average Assets Return on Average (Debt+Equity) Capital Balance Sheet Total Debt / Book Capitalization Total Debt / Book Equity
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
Total Debt / EBITDA Tangible Book Value per Share
#DIV/0! $0.00
#DIV/0! $0.00
#DIV/0! $0.00
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
12/10/20147:41 AM Page 9
Company Name Cost of Capital and Terminal Value Assumptions Cost of Capital Assumptions Risk Free Rate Geometric Risk Premium Stock Price Equity Market Value ($MM) Current Debt / Mkt Equity
0.0% 0.0% $0.00 $0 #DIV/0!
Effective Tax Rate
0.0%
Cost of Debt (Kd): Pre-Tax Kd After-Tax Kd
0.0% 0.0%
Beta Calculations: Levered Unlevered
0.00 #DIV/0!
Unlevered Ke
#DIV/0!
Note: Relevered betas calculated each year in model Terminal Value Assumptions Method of Estimation (1=Multiple;2=Perpetuity)
1
Perpetuity Growth Rate
0.0%
EBITDA Multiple
0.0 x
P/E Multiple
0.0 x
Debt Tax Shield Growth Rate (APV Method)
0.0%
Note: We recommend that users run sensitivity analyses on terminal value assumptions
12/10/20147:41 AM Page 10
Company Name Diccounted Cash Flow Analysis WACC Method
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Depreciation Amortization Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Items Changes in Net Working Capital
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
Cash Flow from Operations
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0 0.0
0.0 0.0
0.0 0.0
0.0 0.0
0.0 0.0
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
Unlevered Free Cash Flows Net Income Interest Expense Tax Effect of Interest Expense After Tax Interest Expense
Now
NOPAT
Capital Expenditures Other Unlevered Free Cash Flow Terminal Value (EBITDA Multiple) Cash Flows to Capital Providers Valuation Firm Value (end of the year) Plus: Excess Cash Less: Debt Outstanding Less: Minority Interest Less: Preferred Stock Equity Value (end of the year) Price per Share WACC Calculation Debt/Market Equity Relevered Beta Ke WACC
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0% 0.00 0.0% 0.0%
12/10/20147:41 AM Page 11
Company Name Disounted Cash Flow Analysis Equity Residual Method
Residual Cash Flows Net Income Depreciation Amortization Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Items Changes in Net Working Capital
2004 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
2005 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
Projected 2006 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
2007 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
2008 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
0.0 $0.00
0.0 $0.00
0.0 #DIV/0!
0.0 #DIV/0!
0.0 #DIV/0!
0.0 #DIV/0!
0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
Now
Cash Flow from Operations Capital Expenditures Change in Debt Change in Preferred Preferred Dividends (includes convertible) Other Residual Cash Flows Terminal Value (P/E Multiple) Cash Flows to Common Equity Holders Valuation Equity Value (end of the year) Price per Share Plus: Debt Outstanding Plus: Minority Interest Plus: Preferred Stock Less: Excess Cash Firm Value (end of the year) Cost of Equity Calculation Debt/Market Equity Relevered Beta Ke
12/10/20147:41 AM Page 12
Company Name Discounted Cash Flow Analysis Adjusted Present Value Method
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Depreciation Amortization Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Items Changes in Net Working Capital Cash Flow from Operations
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
Capital Expenditures Other Unlevered Free Cash Flow Terminal Value (EBITDA Multiple) Cash Flows to Capital Providers
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
Unlevered Free Cash Flows Net Income Interest Expense Tax Effect of Interest Expense After Tax Interest Expense NOPAT
Valuation Unlevered Free Cash Flows Debt Tax Shield Firm Value (end of the year) Plus: Excess Cash Less: Debt Outstanding Less: Minority Interest Less: Preferred Stock Equity Value (end of the year) Price per Share
Now 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0!
12/10/20147:41 AM Page 13
ABC & XYZ Merger Scenario - Purchase Accounting (100% Stock) Transaction Assumptions Target Name Acquiror Name
ABC XYZ
% Cash (input % for all transaction types)
0.0%
Current Valuation Price per Share Merger Price Implied Premium Exchange Ratio
ABC $0.00 $0.00 #DIV/0! #DIV/0!
XYZ $0.00
1.0 0.0 1.0
0.0
2003 $0.00 #DIV/0! First Call
2004E #DIV/0! $0.00 1/1/1998
2003 Financial Data (in $MM) Revenues EBITDA EBIT Net Income to Common
ABC 0.0 0.0 0.0 0.0
XYZ 0.0 0.0 0.0 0.0
Existing Goodwill Amortization Total Cash & Equivalents Excess Cash Goodwill Total Assets Total Debt Preferred Equity Common Equity
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Shares Outstanding (in millions) Options / SARs / Converts Total Post-Deal Shares Refinance Target Debt (1=yes;2=no)
0.0
1
Refinancing Rate: YTM on 10-Year Treasuries Spread Refinancing Rate:
0.0% 0.0% 0.0%
Current Weighted Average Cost of Debt (Interest Expense / Debt Balance)
#DIV/0!
Effective Tax Rate
#DIV/0!
Base Year for Combination
Note: Combination model excludes effect of transaction expenses (i.e., legal, banking, accounting fees)
2003
Earnings per Share (basic) ABC XYZ Source of Projections
2005E #DIV/0! $0.00
No Entry 0.0 0.0 0.0 0.0 0.0 0.0 0.0
12/10/20147:41 AM Page 14
ABC & XYZ Transaction Adjustments Pro Forma Net Income (diluted) XYZ's Net Income ABC's Net Income Unadjusted Combined
2003 0.0 0.0 0.0
2004E 0.0 #DIV/0! #DIV/0!
2005E 0.0 #DIV/0! #DIV/0!
Goodwill Created Cost of Equity Acquired Plus: Liabilities Assumed Less: Excess Cash
0.0 0.0 0.0
Total Consideration Less: Adjusted Value of Assets Less: Identified Intangible Assets
0.0 0.0 0.0
Goodwill Created in Transaction
0.0
Transaction Adjustments Identifiable Intangible Assets Amortization New Interest Expense Refinancing Adjustments Assumed Combination Synergies Pre-Tax Adjustments Income Taxes on Adjustments After-Tax Adjustments
0.0 #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0! #DIV/0!
0.0 #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0! #DIV/0!
0.0 #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0! #DIV/0!
Adjusted Net Income Pro Forma E.P.S
#DIV/0! #DIV/0!
#DIV/0! $0.00
#DIV/0! $0.00
Pro Forma Earnings Impact XYZ's Stand-Alone EPS NewCo's Combined EPS
2003 #DIV/0! #DIV/0!
2004E $0.00 #REF!
2005E $0.00 #REF!
2003 Contribution Analysis Revenues EBITDA
ABC #DIV/0! #DIV/0!
XYZ #DIV/0! #DIV/0!
Accretion / (Dilution)
#DIV/0!
#REF!
#REF!
NewCo Shares % Ownership
#DIV/0! #DIV/0!
0.0 #DIV/0!
Additional Pre-tax Synergies Required for No Dilution
#DIV/0!
#REF!
#REF!
Intabgible Assets Amortization Period
40.0
Adjusted Asset Value Total Assets Less: Goodwill Less: Excess Cash Plus: Assumed Asset Write Up Adjusted Value of Assets
0.0 0.0 0.0 0.0 0.0
Summary Outputs
12/10/20147:41 AM Page 15
Contents
Instructions
TAB 1: "Financials" Operating Assumptions Income Statement Balance Sheet Statement of Cash Flows Financing Assumptions Depreciation & Amortization Schedule Summary Ratios
Row 4 42 70 104 136 170 203
BLUE cells are inputs Fill all of these in
TAB 2: "Valuation" Cost of Capital Terminal Value WACC Method Equity Residual Adjusted Present Value
Row 4 27 47 89 130
Model is set on automatic calculation, 100 iterations No need for user to change settings
TAB 3: "Merger Scenario" Assumptions Transaction Adjustments Summary Outputs
Row 5 34 56
RED cells are toggles Change these to run different scenarios BLACK cells are calculations Do not input numbers in these
Target financial data in "merger scenario" tab is linked to "financials" tab. To run scenarios, user must input acquiror's financial data in specified blue cells
For addition explanation, see appendix to Chapter 9, Applied Mergers and Acquisitions by Robert F. Bruner.
Company Name Assumptions
Note: "#DIV/0!" likely indicates the needs to enter data somewhere in the model.
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
2002 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2003 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
1,000 $0.00
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0
Depreciation, Amortization, & Other Existing Intangible Book Amortization Existing Intangible Tax Amortization
Years 40 yrs 15 yrs
x Declining 1.0 x 1.0 x
Existing PPE Book Depreciation Existing PPE Tax Depreciation Future CapEx Book Depreciation Future CapEx Tax Depreciation
5 yrs 4 yrs 20 yrs 7 yrs
1.0 x 1.0 x 1.0 x 2.0 x
Strike Price
Shares (000)
$0.00 $0.00
0 0
Operating Assumptions Revenue Growth COGS (Excl Depr) / Revenues SG&A / Revenues Other Exp (Income) / Revenues Effective Tax Rate Minority Interest / Pre-tax Income Income from Affiliates / Pre-tax Income Wgtd Average Shares Outstanding (000) Common Dividends per Share Minimum Cash Balance Days in Receivables Days in Inventory Days in Payables Other Current Assets / Revenues Other Current Liabilities / Revenues Capital Expenditures (excl Acquisitions)
2001
Balance Sheet Shares Outstanding (000)
1,000
Options / SARs Outstanding (000) Average Strike Price
1,000 $0.00
Financing Assumptions Straight Debt 1 Straight Debt 2 Straight Debt 3 Straight Debt 4 Straight Debt 5
Coupon
0.0% 0.0% 0.0% 0.0% 0.0%
Straight Preferred Stock Convertible Preferred Stock Convertible Debt
0.0% 0.0% 0.0%
Int. Rate on Excess Cash Use of Excess Cash Flow (1=Build Cash; 2=Repay Debt)
4.0% 1
Interest Rate on Revolver
0.0%
12/10/20147:41 AM Page 3
Company Name Income Statement
Revenues COGS (excluding Depreciation) Selling, General, & Administrative EBITDA Depreciation Amortization of Intangibles Other Expense (Income) EBIT Interest (Income) Interest Expense - Straight Debt Interest Expense - Convertible Debt Interest Expense - Revolver Pre-Tax Income Income Taxes Minority Interest (Income) from Affilitates Net Income Straight Preferred Dividends Convertible Preferred Dividends Net Income to Common Earnings per Share: Basic Fully Diluted
2001 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2002 $0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2003 $0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 0.0 #DIV/0!
NM NM
NM NM
NM NM
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
12/10/20147:41 AM Page 4
Company Name Balance Sheet 2001 0.0 0.0 0.0 0.0 0.0
2002 0.0 0.0 0.0 0.0 0.0
2003 0.0 0.0 0.0 0.0 0.0
2004 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Property , Plant, & Equipment Accumulated Depreciation Net Property, Plant, & Equipment Intangible assets Goodwill Equity in Income of Affiliates Other Long-Term Assets Total Assets
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! 0.0 #DIV/0!
Accounts Payable Other Current Liabilities (excl. S/T Debt) Total Current Liabilities
0.0 0.0 0.0
0.0 0.0 0.0
0.0 0.0 0.0
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
Straight Debt Convertible Debt Revolver Total Debt
0.0 0.0
0.0 0.0
0.0 0.0
0.0
0.0
0.0
0.0 0.0 #DIV/0! 0.0
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
Deferred Taxes Minority Interest Other Long-Term Liabilities Total Liabilities
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
#DIV/0! 0.0 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0!
Straight Preferred Stock Convertible Preferred Stock Common Stock (Par + APIC+ Other Equity) Retained Earnings Total Liabilities & Stockholders' Equity
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Cash and Equivalents Accounts Receivable Inventory Other Current Assets Total Current Assets
#DIV/0!
12/10/20147:41 AM Page 5
Company Name Statement of Cash Flows 2002 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2003 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2004 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
2005 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
Projected 2006 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
2007 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
2008 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0
0.0 0.0
0.0 #DIV/0!
0.0 0.0
0.0 #DIV/0!
0.0 #DIV/0!
0.0 #DIV/0!
0.0 0.0
0.0 0.0
0.0
0.0
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0!
Straight Preferred Dividends Convertible Preferred Dividends Common Stock Dividends Equity Issuance / (Repurchases) Other Change in Cash and Equivalents
0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
Beginning Cash and Equivalents Ending Cash and Equivalents
0.0 0.0
0.0 0.0
0.0 #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
$0.00 $0.00
$0.00 $0.00
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
Net Income Depreciation Amortization of Intangible Increase in Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Charges (Increase) / Decrease in NWC Cash Flow from Operations Capital Expenditures Free Cash Flow Debt Issuance / (Repayments): Straight Debt Convertible Debt Revolver Debt Issuance / (Repayments)
Cash Flow from Operations per Share Free Cash Flow per Share
12/10/20147:41 AM Page 6
Company Name Financing Assumptions Amortization / (Issuance) Straight Debt 1 Straight Debt 2 Straight Debt 3 Straight Debt 4 Straight Debt 5 Convertible Debt Total Debt Amortization / (Issuance)
2003
Straight Preferred Stock Convertible Preferred Stock Common Equity Equity Repurchases / (Issuance)
2004 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2005 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Projected 2006 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2007 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2008 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
Debt / Preferred Balances Straight Debt 1 Straight Debt 2 Straight Debt 3 Straight Debt 4 Straight Debt 5 Revolver Total Straight Debt Convertible Debt Total Debt
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0! 0.0 #DIV/0!
Straight Preferred Stock Convertible Preferred Stock Common Equity (excludes R/E) Total Book Equity
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0 0.0 0.0 0.0
Total Book Capital
0.0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
12/10/20147:41 AM Page 7
Company Name Depreciation & Amortization Schedule Book Depreciation Existing PP&E Capital Expenditures (2004-2008): 2004 2005 2006 2007 2008 Total Book Depreciation
Amount 0.0 0.0 0.0 0.0 0.0
Tax Depreciation Existing PP&E
Basis #DIV/0!
Capital Expenditures (2004-2008): 2004 2005 2006 2007 2008 Total Tax Depreciation
Amount 0.0 0.0 0.0 0.0 0.0
Excess of Tax over Book Depreciation Increase (Decrease) in Deferred Taxes Amortization of Existing Intangible Assets Tax Book Excess of Tax over Book Amortization Increase in Deferred Taxes
Life 15 yrs 40 yrs
2004 0.0
2005 0.0
0.0
0.0 0.0
0.0
Projected 2006 0.0
2007 0.0
2008 0.0
0.0 0.0 0.0
0.0 0.0 0.0 0.0
0.0
0.0
0.0
0.0 0.0 0.0 0.0 0.0 0.0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0
0.0 0.0
0.0 0.0 0.0
0.0 0.0 0.0 0.0
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0 0.0 0.0 0.0 0.0 #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 #DIV/0!
12/10/20147:41 AM Page 8
Company Name Summary Ratios
Income Statement EBITDA / Revenues EBIT / Revenues Return on Revenues
2001 #DIV/0! #DIV/0! #DIV/0!
2002 #DIV/0! #DIV/0! #DIV/0!
2003 #DIV/0! #DIV/0! #DIV/0!
2004 #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0!
EBITDA / Interest Expense EBIT / Interest Expense
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
Return on Average Equity Return on Average Assets Return on Average (Debt+Equity) Capital Balance Sheet Total Debt / Book Capitalization Total Debt / Book Equity
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
Total Debt / EBITDA Tangible Book Value per Share
#DIV/0! $0.00
#DIV/0! $0.00
#DIV/0! $0.00
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
#DIV/0! #DIV/0!
12/10/20147:41 AM Page 9
Company Name Cost of Capital and Terminal Value Assumptions Cost of Capital Assumptions Risk Free Rate Geometric Risk Premium Stock Price Equity Market Value ($MM) Current Debt / Mkt Equity
0.0% 0.0% $0.00 $0 #DIV/0!
Effective Tax Rate
0.0%
Cost of Debt (Kd): Pre-Tax Kd After-Tax Kd
0.0% 0.0%
Beta Calculations: Levered Unlevered
0.00 #DIV/0!
Unlevered Ke
#DIV/0!
Note: Relevered betas calculated each year in model Terminal Value Assumptions Method of Estimation (1=Multiple;2=Perpetuity)
1
Perpetuity Growth Rate
0.0%
EBITDA Multiple
0.0 x
P/E Multiple
0.0 x
Debt Tax Shield Growth Rate (APV Method)
0.0%
Note: We recommend that users run sensitivity analyses on terminal value assumptions
12/10/20147:41 AM Page 10
Company Name Diccounted Cash Flow Analysis WACC Method
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
Depreciation Amortization Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Items Changes in Net Working Capital
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
Cash Flow from Operations
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0 0.0
0.0 0.0
0.0 0.0
0.0 0.0
0.0 0.0
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0! #DIV/0!
Unlevered Free Cash Flows Net Income Interest Expense Tax Effect of Interest Expense After Tax Interest Expense
Now
NOPAT
Capital Expenditures Other Unlevered Free Cash Flow Terminal Value (EBITDA Multiple) Cash Flows to Capital Providers Valuation Firm Value (end of the year) Plus: Excess Cash Less: Debt Outstanding Less: Minority Interest Less: Preferred Stock Equity Value (end of the year) Price per Share WACC Calculation Debt/Market Equity Relevered Beta Ke WACC
0.0 0.0 0.0 0.0 0.0 0.0 $0.00
0.0% 0.00 0.0% 0.0%
12/10/20147:41 AM Page 11
Company Name Disounted Cash Flow Analysis Equity Residual Method
Residual Cash Flows Net Income Depreciation Amortization Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Items Changes in Net Working Capital
2004 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
2005 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
Projected 2006 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
2007 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
2008 #DIV/0! 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
0.0 #DIV/0! 0.0 0.0 0.0
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! 0.0 #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
0.0 $0.00
0.0 $0.00
0.0 #DIV/0!
0.0 #DIV/0!
0.0 #DIV/0!
0.0 #DIV/0!
0.0 0.0 0.0 0.0 #DIV/0!
0.0 0.0 0.0 0.0 #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
#DIV/0! #DIV/0! #DIV/0!
Now
Cash Flow from Operations Capital Expenditures Change in Debt Change in Preferred Preferred Dividends (includes convertible) Other Residual Cash Flows Terminal Value (P/E Multiple) Cash Flows to Common Equity Holders Valuation Equity Value (end of the year) Price per Share Plus: Debt Outstanding Plus: Minority Interest Plus: Preferred Stock Less: Excess Cash Firm Value (end of the year) Cost of Equity Calculation Debt/Market Equity Relevered Beta Ke
12/10/20147:41 AM Page 12
Company Name Discounted Cash Flow Analysis Adjusted Present Value Method
2004 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2005 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Projected 2006 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2007 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
2008 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Depreciation Amortization Deferred Taxes Minority Interest Income from Affiliates Other Non-Cash Items Changes in Net Working Capital Cash Flow from Operations
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
Capital Expenditures Other Unlevered Free Cash Flow Terminal Value (EBITDA Multiple) Cash Flows to Capital Providers
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 #DIV/0! #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
0.0 0.0 0.0 #DIV/0! #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0!
Unlevered Free Cash Flows Net Income Interest Expense Tax Effect of Interest Expense After Tax Interest Expense NOPAT
Valuation Unlevered Free Cash Flows Debt Tax Shield Firm Value (end of the year) Plus: Excess Cash Less: Debt Outstanding Less: Minority Interest Less: Preferred Stock Equity Value (end of the year) Price per Share
Now 0.0 #DIV/0!
0.0 0.0 #DIV/0! 0.0 0.0 0.0 0.0 #DIV/0! #DIV/0!
12/10/20147:41 AM Page 13
ABC & XYZ Merger Scenario - Purchase Accounting (100% Stock) Transaction Assumptions Target Name Acquiror Name
ABC XYZ
% Cash (input % for all transaction types)
0.0%
Current Valuation Price per Share Merger Price Implied Premium Exchange Ratio
ABC $0.00 $0.00 #DIV/0! #DIV/0!
XYZ $0.00
1.0 0.0 1.0
0.0
2003 $0.00 #DIV/0! First Call
2004E #DIV/0! $0.00 1/1/1998
2003 Financial Data (in $MM) Revenues EBITDA EBIT Net Income to Common
ABC 0.0 0.0 0.0 0.0
XYZ 0.0 0.0 0.0 0.0
Existing Goodwill Amortization Total Cash & Equivalents Excess Cash Goodwill Total Assets Total Debt Preferred Equity Common Equity
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Shares Outstanding (in millions) Options / SARs / Converts Total Post-Deal Shares Refinance Target Debt (1=yes;2=no)
0.0
1
Refinancing Rate: YTM on 10-Year Treasuries Spread Refinancing Rate:
0.0% 0.0% 0.0%
Current Weighted Average Cost of Debt (Interest Expense / Debt Balance)
#DIV/0!
Effective Tax Rate
#DIV/0!
Base Year for Combination
Note: Combination model excludes effect of transaction expenses (i.e., legal, banking, accounting fees)
2003
Earnings per Share (basic) ABC XYZ Source of Projections
2005E #DIV/0! $0.00
No Entry 0.0 0.0 0.0 0.0 0.0 0.0 0.0
12/10/20147:41 AM Page 14
ABC & XYZ Transaction Adjustments Pro Forma Net Income (diluted) XYZ's Net Income ABC's Net Income Unadjusted Combined
2003 0.0 0.0 0.0
2004E 0.0 #DIV/0! #DIV/0!
2005E 0.0 #DIV/0! #DIV/0!
Goodwill Created Cost of Equity Acquired Plus: Liabilities Assumed Less: Excess Cash
0.0 0.0 0.0
Total Consideration Less: Adjusted Value of Assets Less: Identified Intangible Assets
0.0 0.0 0.0
Goodwill Created in Transaction
0.0
Transaction Adjustments Identifiable Intangible Assets Amortization New Interest Expense Refinancing Adjustments Assumed Combination Synergies Pre-Tax Adjustments Income Taxes on Adjustments After-Tax Adjustments
0.0 #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0! #DIV/0!
0.0 #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0! #DIV/0!
0.0 #DIV/0! #DIV/0! 0.0 #DIV/0! #DIV/0! #DIV/0!
Adjusted Net Income Pro Forma E.P.S
#DIV/0! #DIV/0!
#DIV/0! $0.00
#DIV/0! $0.00
Pro Forma Earnings Impact XYZ's Stand-Alone EPS NewCo's Combined EPS
2003 #DIV/0! #DIV/0!
2004E $0.00 #REF!
2005E $0.00 #REF!
2003 Contribution Analysis Revenues EBITDA
ABC #DIV/0! #DIV/0!
XYZ #DIV/0! #DIV/0!
Accretion / (Dilution)
#DIV/0!
#REF!
#REF!
NewCo Shares % Ownership
#DIV/0! #DIV/0!
0.0 #DIV/0!
Additional Pre-tax Synergies Required for No Dilution
#DIV/0!
#REF!
#REF!
Intabgible Assets Amortization Period
40.0
Adjusted Asset Value Total Assets Less: Goodwill Less: Excess Cash Plus: Assumed Asset Write Up Adjusted Value of Assets
0.0 0.0 0.0 0.0 0.0
Summary Outputs
12/10/20147:41 AM Page 15
Related Documents
Value Merger.xls
February 2021 2
How To Value Stocks - By Value Spreadsheet
January 2021 4
Place Value
February 2021 0
Neda Value Analysis Handbook.pdf
January 2021 0
Amin Worldwide Value
February 2021 0
Vsm Value Stream Mapping
January 2021 2More Documents from "Elyes Bargui"