Case Assignment week 7
marea3
fin660.v10r.excelassignmentspreadsheets.zipFIN660.v10R.Excel-Based_Spreadsheet_of_How_to_Adjust_Target_Firm_Financials.xlsx
Sheet1
| Adjusting Target Firm's Financials | ||||||
| Assumptions: | ||||||
| Revenue growth (%) | 1.05 | 1.05 | 1.05 | 1.05 | 1.05 | |
| Direct cost of sales as % of revenue | 0.62 | 0.62 | 0.62 | 0.62 | 0.62 | |
| Indirect cost of sales as % of revenue | ||||||
| Salaries & Benefits | 0.15 | 0.15 | 0.15 | 0.15 | 0.15 | |
| Rent | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | |
| Insurance | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | |
| Advertising | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | |
| Travel & Entertainment | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | |
| Director Fees | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | |
| Training | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | |
| All Other Indirect Expenses | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | |
| Financials as Submitted by Target Firm | ||||||
| Base Year | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |
| Revenue | 8000.0 | 8400.0 | 8820.0 | 9261.0 | 9724.1 | 10210.3 |
| Less: Direct Cost of Sales | 4960.0 | 5208.0 | 5468.4 | 5741.8 | 6028.9 | 6330.4 |
| Equals: Gross Profit | 3040.0 | 3192.0 | 3351.6 | 3519.2 | 3695.1 | 3879.9 |
| Less: Indirect Cost of Sales | ||||||
| Salaries & Benefits | 1200.0 | 1260.0 | 1323.0 | 1389.2 | 1458.6 | 1531.5 |
| Rent | 400.0 | 420.0 | 441.0 | 463.1 | 486.2 | 510.5 |
| Insurance | 160.0 | 168.0 | 176.4 | 185.2 | 194.5 | 204.2 |
| Advertising | 160.0 | 160.0 | 160.0 | 160.0 | 160.0 | 160.0 |
| Travel & Entertainment | 240.0 | 168.0 | 176.4 | 185.2 | 194.5 | 204.2 |
| Director Fees | 240.0 | 252.0 | 264.6 | 277.8 | 291.7 | 306.3 |
| Training | 80.0 | 84.0 | 88.2 | 92.6 | 97.2 | 102.1 |
| All Other Indirect Expenses | 160.0 | 168.0 | 176.4 | 185.2 | 194.5 | 204.2 |
| Equals: EBITDA | 400.0 | 512.0 | 545.6 | 580.9 | 617.9 | 656.8 |
| Explanation of Adjustments | Adjustments to Target Firm's Financials | |||||
| Eliminate part-time family member's salaries | 150.0 | 157.5 | 165.4 | 173.6 | 182.3 | |
| Eliminate owner's salary, benefits & directors' fees | 125.0 | 131.3 | 137.8 | 144.7 | 151.9 | |
| Increase targeted advertising | -75.0 | -78.8 | -82.7 | -86.8 | -91.2 | |
| Increase T&E expense | 50.0 | 52.5 | 55.1 | 57.9 | 60.8 | |
| Reduce office space (rent) | 100.0 | 100.0 | 100.0 | 100.0 | 100.0 | |
| Increase training budget | -100.0 | -100.0 | -100.0 | -100.0 | -100.0 | |
| Adjusted EBITDA | 1112.0 | 1165.6 | 1221.9 | 1281.0 | 1343.0 |
Sheet2
Sheet3
FIN660.v10R.Excel-Based_Decision-Tree_Valuation_Model.xlsx
Real Options Model
| Exhibit 7-17. M&A Valuation Using Real Options Decision Tree | ||||||||||
| Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | |
| First Branch: Option for Immediate Investment/Acquisition | ||||||||||
| Enterprise Cash Flows | Projected Target Firm Cash Flows | |||||||||
| Successful Case | -300 | 30 | 35 | 40 | 45 | 50 | 55 | 60 | 65 | |
| Unsuccessful Case | -300 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | |
| Weighted Cash Flows | ||||||||||
| Successful Case (60%) | 0 | 18 | 21 | 24 | 27 | 30 | 33 | 36 | 39 | |
| Unsuccessful Case (40% | 0 | -2 | -2 | -2 | -2 | -2 | -2 | -2 | -2 | |
| Expected Enterprise Cash Flow | -300 | 16 | 19 | 22 | 25 | 28 | 31 | 34 | 37 | |
| Expected NPV Yr 1-8 @ 15% | -166 | |||||||||
| Expected Terminal Value @ 13%; sustainable growth rate = 5% | 159 | |||||||||
| Expected Total NPV | -7 | |||||||||
| Second Branch: Option to Abandon (Divest or Liquidate) | ||||||||||
| Enterprise Cash Flows | Projected Target Firm Cash Flows | |||||||||
| Successful Case | -300 | 30 | 35 | 40 | 45 | 50 | 55 | 60 | 65 | |
| Unsuccessful Case | -300 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | -5 | |
| Weighted Cash Flows | ||||||||||
| Successful Case (60%) | 0 | 18 | 21 | 24 | 27 | 30 | 33 | 36 | 39 | |
| Unsuccessful Case (40%) | 0 | -2 | -2 | 150 | 0 | 0 | 0 | 0 | 0 | |
| Expected Enterprise Cash Flow | -300 | 16 | 19 | 174 | 27 | 30 | 33 | 36 | 39 | |
| Expected PV Yr 1-6 @15% | -75 | |||||||||
| Expected Terminal Value @ 13%; sustainable growth rate = 5% | 167 | |||||||||
| Expected Total PV | 92 | |||||||||
| Third Branch: Option to Delay Investment/Acquisition | ||||||||||
| Enterprise Cash Flows | Projected Target Firm Cash Flows | |||||||||
| Successful Case | 0 | -300 | 35 | 40 | 45 | 50 | 55 | 60 | 65 | 70 |
| Unsuccessful Case | 0 | -300 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Weighted Cash Flows | ||||||||||
| Successful Case (60%) | 0 | 0 | 21 | 24 | 27 | 30 | 33 | 36 | 39 | 42 |
| Unsuccessful Case (40%) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Expected Enterprise Cash Flow | 0 | -300 | 21 | 24 | 27 | 30 | 33 | 36 | 39 | 42 |
| Expected NPV @ 15% | -146 | |||||||||
| Expected Terminal Value @ 13%; sustainable growth rate = 5% | 180 | |||||||||
| Expected Total NPV | 34 | |||||||||
| Note: The NPV for the delay option is discounted at the end of year 1, while the other options are discounted from year 0 (I.e., the present). | ||||||||||
FIN660.v10R.Excel-Based_LBO_Valuation_and_Structuring_Model.xlsx
Overview
| LBO Structuring and Valuation Model Overview | |||
| Objectives: | |||
| Develop a model capable of simulating alternative capital structures and associated rates of returns. | |||
| Identify clearly all assumptions underlying the model's financial projections. | |||
| Summarize key performance metrics associated with the model. | |||
| Worksheets: | |||
| 1 | Model Overview | ||
| 2 | Model Output Summary | ||
| 3 | Income Statement and Forecast Assumptions | ||
| 4 | Balance Sheet and Forecast Assumptions | ||
| 5 | Cash Flow Statement and Forecast Assumptions | ||
| 6 | Loan Amortization Schedules and Forecast Assumptions | ||
| 7 | Internal Rate of Return Calculations and Assumptions | ||
| 8 | Present Value of Adjusted Equity Cash Flow |
Summary
| Model Output Summary | |||||||||||
| Sources and Uses of Funds: | Pro Forma Capital Structure | ||||||||||
| Interest | Market | % of Total | |||||||||
| Sources of Funds | Amount ($) | Rate (%) | Uses of Funds | Amount ($) | Revolving Loan | Value | Capital | ||||
| Cash From Balance Sheet | $0.0 | 0.0% | Cash to Owners | $70.0 | Revolving Loan | $12.0 | 16.7% | ||||
| New Revolving Loan | $12.0 | 9.0% | Seller Equity | $0.0 | Senior Debt | $20.0 | 27.8% | ||||
| New Senior Debt | $20.0 | 9.0% | Seller's Note | $0.0 | Subordinated Debt | $15.0 | 20.8% | ||||
| New Subordinated Debt | $15.0 | 12.0% | Excess Cash | $0.0 | Total Debt | $47.0 | 65.3% | ||||
| New Preferred Stock (PIK) | $22.0 | 12.0% | Paid to Owners | $70.0 | Preferred Equity | $22.0 | 30.6% | ||||
| New Common Stock | $3.0 | 0.0% | Debt Repayment | $0.0 | Common Equity | $3.0 | 4.2% | ||||
| $72.0 | Buyer Expenses | $2.0 | Total Equity | $25.0 | 34.7% | ||||||
| $72.0 | Total Capital | $72.0 | |||||||||
| Equity Investment: | |||||||||||
| Ownership Distribution ($) | % Distribution | Fully Diluted Ownership Distribution | |||||||||
| Pre-Option | Perform. | Fully Diluted | |||||||||
| Common | Preferred | Total | Common | Preferred | Common | Warrants | Ownership | Options | Ownership | ||
| Equity Investor | 1.5 | 22.0 | 23.5 | 50.0% | 100.0% | 50.0% | 0.0% | 50.0% | 0.0% | 50.0% | |
| Management | 1.5 | 0.0 | 1.5 | 50.0% | 0.0% | 50.0% | 0.0% | 50.0% | 0.0% | 50.0% | |
| Total | $3.0 | $22.0 | $25.0 | 100.0% | 100.0% | 100.0% | 0.0% | 100.0% | 0.0% | 100.0% | |
| Internal Rates of Return: | |||||||||||
| Total Investor Return (%) | Equity Investor Investment Gain ($) | Management Investment Gain ($) | |||||||||
| Year 4 | Year 5 | Year 6 | Year 4 | Year 5 | Year 6 | Year 4 | Year 5 | Year 6 | |||
| Multiple of Adjusted Equity Cash Flow1 | |||||||||||
| 8 | 0.42 | 0.35 | 0.33 | $66.6 | $78.9 | $96.0 | $4.3 | $5.0 | $6.1 | ||
| 9 | 0.46 | 0.39 | 0.35 | $73.8 | $86.6 | $104.5 | $4.7 | $5.5 | $6.7 | ||
| 10 | 0.51 | 0.42 | 0.37 | $81.0 | $94.2 | $113.0 | $5.2 | $6.0 | $7.2 | ||
| Financial Projections and Analysis: | |||||||||||
| 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | ||
| Net Sales | $177.6 | $183.5 | $190.4 | $197.1 | $205.0 | $214.2 | $223.8 | $233.9 | $244.4 | $255.4 | |
| Annual Growth Rate | 4.2% | 3.3% | 3.8% | 3.5% | 4.0% | 4.5% | 4.5% | 4.5% | 4.5% | 4.5% | |
| EBIT as % of Net Revenue | 5.5% | 1.3% | 5.1% | 8.5% | 9.5% | 10.2% | 11.2% | 11.4% | 11.4% | 11.4% | |
| Adjusted Enterprise Cash Flow2 | $4.2 | $0.2 | $0.1 | $9.5 | $9.6 | $10.8 | $13.0 | $13.4 | $14.2 | $14.9 | |
| Adjusted Equity Cash Flow | $4.2 | $0.2 | $0.1 | $0.3 | $0.2 | $1.8 | $7.4 | $7.7 | $8.1 | $8.5 | |
| Total Debt Outstanding | 0 | 0 | $47.0 | $39.5 | $31.5 | $23.8 | $19.2 | $14.3 | $8.8 | $2.7 | |
| Total Debt/Adjusted Enterprise Cash Flow | 0.0 | 0.0 | NA | 4.1 | 3.3 | 2.2 | 1.5 | 1.1 | 0.6 | 0.2 | |
| EBIT/Interest Expense | 0 | 0 | 0 | 3.6 | 4.9 | 6.6 | 10.1 | 13.3 | 18.6 | 30.9 | |
| PV of Adj. Equity Cash Flow @ 26% | $57.2 | ||||||||||
| 2004-10 Adj. Eq. CF/Terminal Value | 28.1% | ||||||||||
| 1Net Income + Interest Expense(1-t) + Depreciation & Amortization - Gross Capital Spending - Chg. In Working Capital - Principal Repayments - Chg. In | |||||||||||
| Invest. Available for Sale (I.e., Increases in such investments are a negative cash flow entry, but represent cash in excess of normal operating needs.) | |||||||||||
| 2EBIT(1-t) + Depreciation & Amortization - Gross Capital Spending - Chg. in Working Capital - Chg. In Invest Available for Sale | |||||||||||
Income Statement
| Income Statement and Forecast Assumptions | ||||||||||||
| Historical Period | Projections: Twelve Months Ending December 31, | |||||||||||
| Income Statement Assumptions: | 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | ||
| Net Sales Growth (%) | 0.042 | 0.033 | 0.038 | 0.035 | 0.040 | 0.045 | 0.045 | 0.045 | 0.045 | 0.045 | ||
| Cost of Sales as % of Sales | 0.805 | 0.814 | 0.780 | 0.765 | 0.758 | 0.755 | 0.750 | 0.750 | 0.750 | 0.750 | ||
| SG&A as % of Sales | 0.133 | 0.144 | 0.142 | 0.135 | 0.130 | 0.125 | 0.120 | 0.120 | 0.120 | 0.120 | ||
| Effective Tax Rate (%) | 0.400 | 0.400 | 0.400 | 0.400 | 0.400 | 0.400 | 0.400 | 0.400 | 0.400 | 0.400 | ||
| Net Sales | $177.6 | $183.5 | $190.4 | $197.1 | $205.0 | $214.2 | $223.8 | $233.9 | $244.4 | $255.4 | ||
| Cost of Sales | 143.0 | 149.3 | 148.5 | 150.8 | 155.4 | 161.7 | 167.9 | 175.4 | 183.3 | 191.6 | ||
| Gross Profit | 34.6 | 34.1 | 41.9 | 46.3 | 49.6 | 52.5 | 56.0 | 58.5 | 61.1 | 63.9 | ||
| Depreciation | 1.3 | 5.4 | 5.1 | 2.4 | 2.9 | 3.4 | 3.5 | 3.7 | 3.8 | 4.0 | ||
| Amortization of Financing Fees | 0.5 | 0.5 | 0.5 | 0.5 | ||||||||
| Total Depreciation & Amortization | 1.3 | 5.4 | 5.1 | 2.9 | 3.4 | 3.9 | 4.0 | 3.7 | 3.8 | 4.0 | ||
| SG&A | 23.6 | 26.4 | 27.0 | 26.6 | 26.6 | 26.8 | 26.9 | 28.1 | 29.3 | 30.7 | ||
| Management Fee | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | |||||
| Operating Income (EBIT) | 9.7 | 2.3 | 9.7 | 16.7 | 19.5 | 21.7 | 25.0 | 26.6 | 27.8 | 29.1 | ||
| (Interest Income) | 0.1 | 0.1 | 0.1 | 0.0 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | ||
| New Revolver Interest Expense | 1.0 | 0.7 | 0.4 | 0.0 | 0.0 | 0.0 | 0.0 | |||||
| New Senior Debt Interest Expense | 1.8 | 1.6 | 1.4 | 1.2 | 0.9 | 0.6 | 0.3 | |||||
| Subordinated Debt Interest Expense | 1.8 | 1.7 | 1.5 | 1.3 | 1.1 | 0.9 | 0.6 | |||||
| Total Interest Expense | 0 | 0 | 0 | 4.6 | 4.0 | 3.3 | 2.5 | 2.0 | 1.5 | 0.9 | ||
| Earnings Before Taxes | 9.8 | 2.4 | 9.8 | 12.1 | 15.6 | 18.5 | 22.6 | 24.7 | 26.4 | 28.3 | ||
| Taxes @40% | 3.9 | 0.9 | 3.9 | 4.8 | 6.2 | 7.4 | 9.0 | 9.9 | 10.6 | 11.3 | ||
| Net Income | $5.9 | $1.4 | $5.9 | $7.3 | $9.4 | $11.1 | $13.6 | $14.8 | $15.9 | $17.0 | ||
| PIK Preferred Dividend | 2.6 | 3.0 | 3.3 | 3.7 | 4.2 | 4.7 | 5.2 | |||||
| Net Income to Common | $5.9 | $1.4 | $5.9 | $4.6 | $6.4 | $7.8 | $9.9 | $10.7 | $11.2 | $11.7 |
Balance Sheet
| Balance Sheet and Forecast Assumptions | ||||||||||||
| Historical Period | Adjust. | Closing | Projections: Twelve Months Ended December, | |||||||||
| 2001 | 2002 | 2003 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | ||
| Balance Sheet Assumptions: | ||||||||||||
| Cash & Mkt. Securities (%Sales) | 0.02 | 0.02 | 0.02 | 0.0 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 |
| Accounts Receivable (%Sales) | 0.161 | 0.158 | 0.167 | 0.0 | 0.167 | 0.155 | 0.155 | 0.155 | 0.155 | 0.155 | 0.155 | 0.155 |
| Other Current Assets (%Sales) | 0.054 | 0.057 | 0.063 | 0.0 | 0.063 | 0.055 | 0.055 | 0.055 | 0.055 | 0.055 | 0.055 | 0.055 |
| Gross Prop., Plant & Equip (%Sales) | 0.473 | 0.5 | 0.52 | 0.0 | 0.52 | 0.52 | 0.52 | 0.52 | 0.52 | 0.52 | 0.52 | 0.52 |
| Accumulated Depreciation (%GP&E) | 0.7 | 0.7 | 0.7 | 0.0 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 |
| Accounts Payable (%Sales) | 0.08 | 0.083 | 0.084 | 0.0 | 0.084 | 0.078 | 0.078 | 0.078 | 0.078 | 0.078 | 0.078 | 0.078 |
| Other Current Liabilities (%Sales) | 0.074 | 0.079 | 0.076 | 0.0 | 0.076 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 |
| Assets: | ||||||||||||
| Current Assets | ||||||||||||
| Cash and Mkt. Securities | $3.6 | $3.7 | $3.8 | $0.0 | $3.8 | $4.1 | $4.3 | $4.5 | $4.7 | $4.9 | $5.1 | $5.1 |
| Accounts Receivable | 28.6 | 29.0 | 31.8 | 0.0 | 31.8 | 30.6 | 31.8 | 33.2 | 34.7 | 36.3 | 37.9 | 39.6 |
| Other Current Assets | 9.6 | 10.5 | 12.0 | 0.0 | 12.0 | 10.8 | 11.3 | 11.8 | 12.3 | 12.9 | 13.4 | 14.0 |
| Total Current Assets | 41.7 | 43.1 | 47.6 | 0.0 | 47.6 | 45.5 | 47.3 | 49.5 | 51.7 | 54.0 | 56.4 | 58.8 |
| Investments Available for Sale | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 8.9 | 16.3 | 24.2 | 32.7 | |||
| Gross Property, Plant & Equipment | 84.0 | 91.7 | 99.0 | 0.0 | 99.0 | 102.5 | 106.6 | 111.4 | 116.4 | 121.6 | 127.1 | 132.8 |
| Less: Accumulated Depreciation | 58.8 | 64.2 | 69.3 | 0.0 | 69.3 | 71.7 | 74.6 | 78.0 | 81.5 | 85.1 | 89.0 | 93.0 |
| Net Property, Plant & Equipment | 25.2 | 27.5 | 29.7 | 0.0 | 29.7 | 30.7 | 32.0 | 33.4 | 34.9 | 36.5 | 38.1 | 39.8 |
| Transaction Fees and Expenses | 0.0 | 0.0 | 0.0 | 2.0 | 2.0 | 1.5 | 1.0 | 0.5 | 0.0 | 0.0 | 0.0 | 0.0 |
| Purchase price in excess of book value | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||||
| Total Assets | 66.9 | 70.6 | 77.3 | 2.0 | 79.3 | 77.7 | 80.3 | 83.4 | 95.5 | 106.8 | 118.8 | 131.3 |
| Liabilities & Shareholders' Equity | ||||||||||||
| Current Liabilities: | ||||||||||||
| Accounts Payable | 14.2 | 15.2 | 16.0 | 0.0 | 16.0 | 15.4 | 16.0 | 16.7 | 17.5 | 18.2 | 19.1 | 19.9 |
| Other Current Liabilitiles | 13.1 | 14.5 | 14.5 | 0.0 | 14.5 | 13.8 | 14.3 | 15.0 | 15.7 | 16.4 | 17.1 | 17.9 |
| Total Current Liabilities | 27.4 | 29.7 | 30.5 | 0.0 | 30.5 | 29.2 | 30.3 | 31.7 | 33.1 | 34.6 | 36.2 | 37.8 |
| Long-Term Debt: | ||||||||||||
| Revolving Loan | 12.0 | 12.0 | 7.9 | 3.7 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |||
| Senior Debt | 20.0 | 20.0 | 17.8 | 15.5 | 12.9 | 10.1 | 7.0 | 3.7 | 0.0 | |||
| Subordinated Debt | 15.0 | 15.0 | 13.8 | 12.4 | 10.9 | 9.2 | 7.2 | 5.1 | 2.7 | |||
| Total Long-Term Debt | 0.0 | 0.0 | 0.0 | 47.0 | 39.5 | 31.5 | 23.8 | 19.2 | 14.3 | 8.8 | 2.7 | |
| Shareholders' Equity | ||||||||||||
| Preferred Stock (PIK) | 22.0 | 22.0 | 24.6 | 27.6 | 30.9 | 34.6 | 38.8 | 43.4 | 48.6 | |||
| Common Stock | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | |||
| Additional Paid in Capital | (70.0) | (70.0) | (70.0) | (70.0) | (70.0) | (70.0) | (70.0) | (70.0) | (70.0) | |||
| Retained Earnings | 39.5 | 40.9 | 46.8 | 0.0 | 46.8 | 51.4 | 57.8 | 65.7 | 75.5 | 86.2 | 97.4 | 109.1 |
| Total Shareholders' Equity | 39.5 | 40.9 | 46.8 | 1.8 | 9.1 | 18.4 | 29.6 | 43.1 | 58.0 | 73.8 | 90.8 | |
| Total Liab. & Shareholders' Equity | 66.9 | 70.6 | 77.3 | 2.0 | 79.3 | 77.7 | 80.3 | 85.0 | 95.5 | 106.8 | 118.8 | 131.3 |
| Addendum: | ||||||||||||
| Chg. in Op. Working Capital (WC) | 1.1 | (3.6) | (13.3) | 1.1 | (0.5) | (0.6) | (0.6) | (0.6) | (0.7) | (0.7) | ||
| Ending Cash Balances1 | $3.6 | $3.7 | $3.8 | $3.8 | $4.1 | $4.3 | $4.5 | $13.6 | $21.2 | $29.3 | $37.8 | |
| 1Cash balances equal the sum of cash & marketable securities and investments available for sale. |
Cash Flow
| Cash Flow Statement and Analysis | ||||||||||||
| Historical Data | Projections: Twelve Months Ended December 31, | |||||||||||
| 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | |||
| GAAP Cash Flow | ||||||||||||
| Cash Flow from Operating Activities: | ||||||||||||
| Net Income to Common | 5.9 | 1.4 | 5.9 | 4.6 | 6.4 | 7.8 | 9.9 | 10.7 | 11.2 | 11.7 | ||
| Adjustments to Reconcile Net Income to Net Cash Flow | ||||||||||||
| Depreciation | 1.3 | 5.4 | 5.1 | 2.4 | 2.9 | 3.4 | 3.5 | 3.7 | 3.8 | 4.0 | ||
| Amortization of Financing Fees | 0.0 | 0.0 | 0.0 | 0.5 | 0.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.0 | ||
| PIK Preferred Dividends | 0.0 | 0.0 | 0.0 | 2.6 | 3.0 | 3.3 | 3.7 | 4.2 | 4.7 | 5.2 | ||
| Net Change in Working Capital | 0.0 | 1.1 | (3.6) | 1.1 | (0.5) | (0.6) | (0.6) | (0.6) | (0.7) | (0.7) | ||
| Net Cash Flow from Operations | 7.2 | 5.7 | 14.6 | 11.3 | 12.2 | 14.4 | 17.0 | 17.9 | 19.0 | 20.3 | ||
| Cash Flow from Investing Activities | ||||||||||||
| (Increase) Decrease in Invest Available for Sale. | 0.0 | 0.0 | 0.0 | (8.9) | (7.4) | (7.9) | (8.5) | |||||
| (Increase) Decrease in Gross Property, Plant & Equip. | (3.5) | (4.1) | (4.8) | (5.0) | (5.2) | (5.5) | (5.7) | |||||
| Net Cash Used in Investments | 0.0 | 0.0 | 0.0 | (3.5) | (4.1) | (4.8) | (13.9) | (12.7) | (13.3) | (14.2) | ||
| Cash Flows from Financing Activities: | ||||||||||||
| Net Debt (Repayment) or Issuance | 0.0 | 0.0 | 0.0 | (7.5) | (8.0) | (7.8) | (4.5) | (5.0) | (5.5) | (6.1) | ||
| Net Cash (Used in) Provided by Financing Activities | 0.0 | 0.0 | 0.0 | (7.5) | (8.0) | (7.8) | (4.5) | (5.0) | (5.5) | (6.1) | ||
| Net Increase (Decrease) in Cash & Marketable Securities | 0.3 | 0.2 | 1.8 | (1.5) | 0.2 | 0.2 | 0.0 | |||||
| Beginning Balances--Cash & Marketable Securities | 3.8 | 4.1 | 4.3 | 6.1 | 4.7 | 4.9 | 5.1 | |||||
| Ending Balances--Cash & Marketable Securities | 4.1 | 4.3 | 6.1 | 4.7 | 4.9 | 5.1 | 5.1 | |||||
| Valuation Cash Flow | ||||||||||||
| Net Income to Common | 5.9 | 1.4 | 5.9 | 4.6 | 6.4 | 7.8 | 9.9 | 10.7 | 11.2 | 11.7 | ||
| After-Tax Net Interest Expense (Income) | 0 | 0 | 0 | 1.7 | 1.4 | 1.2 | 1.0 | 0.8 | 0.6 | 0.4 | ||
| Depreciation | 1.3 | 5.4 | 5.1 | 2.4 | 2.9 | 3.4 | 3.5 | 3.7 | 3.8 | 4.0 | ||
| Amortization of Financing Fees | 0 | 0 | 0 | 0.5 | 0.5 | 0.5 | 0.5 | 0 | 0 | 0 | ||
| PIK Preferred Dividend | 0 | 0 | 0 | 2.6 | 3.0 | 3.3 | 3.7 | 4.2 | 4.7 | 5.2 | ||
| Net Cash Flow Before Working Capital | 7.2 | 6.8 | 11.0 | 11.9 | 14.2 | 16.1 | 18.6 | 19.3 | 20.3 | 21.3 | ||
| Net Change in Working Capital | 0.0 | 1.1 | (3.6) | 1.1 | (0.5) | (0.6) | (0.6) | (0.6) | (0.7) | (0.7) | ||
| Net Cash Flow Before Gross Property, Plant & Equip. Spending | 7.2 | 7.9 | 7.4 | 13.0 | 13.7 | 15.6 | 18.0 | 18.7 | 19.6 | 20.7 | ||
| (Increase) Decrease in Investments Available for Sale | 0.0 | 0.0 | 0.0 | (8.9) | (7.4) | (7.9) | (8.5) | |||||
| (Increase) Decrease in Gross Property, Plant & Equipment | (3.0) | (7.7) | (7.3) | (3.5) | (4.1) | (4.8) | (5.0) | (5.2) | (5.5) | (5.7) | ||
| Enterprise Cash Flow | 4.2 | 0.2 | 0.1 | 9.5 | 9.6 | 10.8 | 4.1 | 6.0 | 6.3 | 6.5 | ||
| After-Tax Net Interest Expense (Income) | 0.0 | 0.0 | 0.0 | 1.7 | 1.4 | 1.2 | 1.0 | 0.8 | 0.6 | 0.4 | ||
| Net Debt (Repayments) or Issuance | 0.0 | 0.0 | 0.0 | (7.5) | (8.0) | (7.8) | (4.5) | (5.0) | (5.5) | (6.0) | ||
| Equity Cash Flow | 4.2 | 0.2 | 0.1 | 0.3 | 0.2 | 1.8 | (1.5) | 0.2 | 0.2 | 0.0 | ||
| Dividends on Common Stock | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| Net Stock (Repurchase) or Issuance | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| Net Increase (Decrease) in Cash Balance | 4.2 | 0.2 | 0.1 | 0.3 | 0.2 | 1.8 | (1.5) | 0.2 | 0.2 | 0.0 | ||
| Beginning Balances--Cash & Marketable Securities | 3.6 | 3.8 | 3.9 | 4.2 | 4.4 | 6.2 | 4.8 | 5.0 | 5.2 | |||
| Ending Balances--Cash & Marketable Securities | 3.6 | 3.8 | 3.9 | 4.2 | 4.4 | 6.2 | 4.8 | 5.0 | 5.2 | 5.2 | ||
| Adjusted Equity Cash Flow1 | 4.2 | 0.2 | 0.1 | 0.3 | 0.2 | 1.8 | 7.4 | 7.7 | 8.1 | 8.5 | ||
| Adjusted Enterprise Cash Flow | 4.2 | 0.2 | 0.1 | 9.5 | 9.6 | 10.8 | 13.0 | 13.4 | 14.2 | 14.9 | ||
| 1Free cash flow available to pay dividends or buy-back stock and is the sum of equity cash flow minus the change in investments available for sale. |
Financing
| Financing Assumptions | ||||||||||
| Interest Rate on Cash & Marketable Securities = 2.0% | ||||||||||
| Projections: Twelve Months Ending December 31, | ||||||||||
| 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | 2011 | |||
| Interest Rates on Debt (%) | ||||||||||
| New Revolving Loan | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | ||
| New Senior Debt | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | 9.0 | ||
| New Subordinated Debt | 12.0 | 12.0 | 12.0 | 12.0 | 12.0 | 12.0 | 12.0 | 12.0 | ||
| New Preferred Stock (PIK) | 12.0 | 15.0 | 15.0 | 15.0 | 15.0 | 15.0 | 15.0 | 15.0 | ||
| Annual Payment Schedule1($) | ||||||||||
| New Revolving Loan | ||||||||||
| New Senior Debt2 | 4.0 | 4.0 | 4.0 | 4.0 | 4.0 | 4.0 | 4.0 | |||
| New Subordinated Debt3 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | ||
| Total Annual Payments | 7.0 | 7.0 | 7.0 | 7.0 | 7.0 | 7.0 | 7.0 | 3.0 | ||
| Interest Expense Schedule ($) | ||||||||||
| New Revolving Loan | 0.7 | 0.3 | 0.0 | |||||||
| New Senior Debt | 1.8 | 1.6 | 1.4 | 1.2 | 0.9 | 0.6 | 0.3 | |||
| New Subordinated Debt | 1.8 | 1.7 | 1.5 | 1.3 | 1.1 | 0.9 | 0.6 | 0.3 | ||
| Total Interest Expense | 4.3 | 3.6 | 2.9 | 2.5 | 2.0 | 1.5 | 0.9 | 0.3 | ||
| Principal Repayment Schedule ($) | ||||||||||
| New Revolving Loan | 4.1 | 4.2 | 3.7 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||
| New Senior Debt | 2.2 | 2.4 | 2.6 | 2.8 | 3.1 | 3.3 | 3.6 | |||
| New Subordinated Debt | 1.2 | 1.4 | 1.5 | 1.7 | 1.9 | 2.2 | 2.4 | 2.7 | ||
| Total Principal Repayments | 7.5 | 8.0 | 7.8 | 4.5 | 5.0 | 5.5 | 6.0 | 2.7 | ||
| Post-LBO Debt Outstanding ($) | New Debt | |||||||||
| New Revolving Loan | 12.0 | 7.9 | 3.7 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | |
| New Senior Debt | 20.0 | 17.8 | 15.5 | 12.9 | 10.1 | 7.0 | 3.7 | 0.0 | ||
| New Subordinated Debt | 15.0 | 13.8 | 12.4 | 10.9 | 9.2 | 7.2 | 5.1 | 2.7 | 0.0 | |
| Total Debt Outstanding | 47.0 | 39.5 | 31.5 | 23.8 | 19.2 | 14.3 | 8.8 | 2.7 | 0.0 | |
| 1Includes interest expense and principal repayment | ||||||||||
| 2Equal annual payments (PMT) over 7 years, where = PVA (PV of an annuity) / PVIAF7,9. PVIAF is the PV interest factor for an annuity for 7 years | ||||||||||
| at 9% interest. | ||||||||||
| 3Equal annual payments over 8 years. |
IRR
| Internal Rates of Return and Net Present Value Calculations | |||||||||||
| Historical Period | Projections | ||||||||||
| 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 | ||
| Adjusted Enterprise Cash Flow | $4.2 | $0.2 | $0.1 | $9.5 | $9.6 | $10.8 | $13.0 | $13.4 | $14.2 | $14.9 | |
| Adjusted Equity Cash Flow | $4.2 | $0.2 | $0.1 | $0.3 | $0.2 | $1.8 | $7.4 | $7.7 | $8.1 | $8.5 | |
| Terminal Value for Internal Rate of Return Calculation: | Multiple | Year 4 | Year 5 | Year 6 | |||||||
| Multiple of Adjusted Equity Cash Flow1 | 8 | $59.53 | $61.25 | $64.64 | |||||||
| 9 | $66.97 | $68.91 | $72.72 | ||||||||
| 10 | $74.41 | $76.56 | $80.80 | ||||||||
| Initial Cash Investment = $25 | -25 | ||||||||||
| Multiple 8 | Equity Cash Flows Used to Calculate IRRs | ||||||||||
| IRR (Year 4) | 42% | -$24.7 | $0.2 | $1.8 | $66.97 | ||||||
| IRR (Year 5) | 35% | -$24.7 | $0.2 | $1.8 | $7.4 | $68.91 | |||||
| IRR (Year 6) | 33% | -$24.7 | $0.2 | $1.8 | $7.4 | $7.7 | $72.72 | ||||
| Multiple 9 | |||||||||||
| IRR (Year 4) | 46% | -$24.7 | $0.2 | $1.8 | $74.4 | ||||||
| IRR (Year 5) | 39% | -$24.7 | $0.2 | $1.8 | $7.4 | $76.56 | |||||
| IRR (Year 6) | 35% | -$24.7 | $0.2 | $1.8 | $7.4 | $7.7 | $80.80 | ||||
| Multiple 10 | |||||||||||
| IRR (Year 4) | 51% | -$24.7 | $0.2 | $1.8 | $81.85 | ||||||
| IRR (Year 5) | 42% | -$24.7 | $0.2 | $1.8 | $7.4 | $84.22 | |||||
| IRR (Year 6) | 37% | -$24.7 | $0.2 | $1.8 | $7.4 | $7.7 | $88.88 | ||||
| Terminal Value for Internal Rate of Return Calculation: | Multiple | Year 4 | Year 5 | Year 6 | |||||||
| Multiple of Enterprise Cash Flow | 4 | $51.81 | $53.77 | $56.68 | |||||||
| 5 | $64.76 | $67.21 | $70.84 | ||||||||
| 6 | $77.71 | $80.65 | $85.01 | ||||||||
| Multiple 4 | |||||||||||
| IRR (Year 4) | 91% | -$15.5 | $9.6 | $10.8 | $51.81 | ||||||
| IRR (Year 5) | 83% | -$15.5 | $9.6 | $10.8 | $13.0 | $53.77 | |||||
| IRR (Year 6) | 79% | -$15.5 | $9.6 | $10.8 | $13.0 | $13.4 | $56.68 | ||||
| Multiple 5 | |||||||||||
| IRR (Year 4) | 101% | -$15.5 | $9.6 | $10.8 | $64.8 | ||||||
| IRR (Year 5) | 88% | -$15.5 | $9.6 | $10.8 | $13.0 | $67.21 | |||||
| IRR (Year 6) | 82% | -$15.5 | $9.6 | $10.8 | $13.0 | $13.4 | $70.84 | ||||
| Multiple 6 | |||||||||||
| IRR (Year 4) | 110% | -$15.5 | $9.6 | $10.8 | $77.71 | ||||||
| IRR (Year 5) | 93% | -$15.5 | $9.6 | $10.8 | $13.0 | $80.7 | |||||
| IRR (Year 6) | 85% | -$15.5 | $9.6 | $10.8 | $13.0 | $13.4 | $85.01 | ||||
| 1Free cash flow available to pay dividends or buy-back stock and is the sum of equity cash flow minus the change in investments available for sale. | |||||||||||
| Note that increases in such investments are denoted as negative cash flow entries, but represent cash in excess of what is required to meet | |||||||||||
| normal operating requirements. |
NPV
| Present Value of Adjusted Equity Cash Flow | |||||||
| 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 |
| Assumptions: Adjusted Equity Cash Flow | |||||||
| Market Value of Preferred Equity 22 | 24.6 | 27.6 | 30.9 | 34.6 | 38.8 | 43.4 | 48.6 |
| Market Value of Common Equity 3 | 2.3 | 3.3 | 4.0 | 5.0 | 5.4 | 5.7 | 6.0 |
| Equity1 25 | $27.0 | $30.9 | $34.9 | $39.6 | $44.2 | $49.1 | $54.6 |
| Debt 47 | $39.5 | $31.5 | $23.8 | $19.2 | $14.3 | $8.8 | $2.7 |
| Comparable Firm Price-to-Earnings Ratio | 6.0 | ||||||
| Comparable Firm Levered Beta (Bl) | 2.4 | ||||||
| Comparable Firm D/E Ratio | 0.3 | ||||||
| Comparable Firm Unlevered Beta2 (Bu) | 2.0 | ||||||
| Marginal Tax Rate | 0.4 | ||||||
| 10-Year Treasury Bond Rate | 0.05 | ||||||
| Risk Premium on Stocks | 0.055 | ||||||
| Terminal Periond Growth Rate | 0.045 | ||||||
| Cost of Equity During Terminal Period | 0.1 | ||||||
| Debt to | Leveraged | Cost of | Cum.Discount | Adj. Equity | PV of | ||
| Year | Equity Ratio | Beta3 | Equity | Factor4 | Cash Flow | Equity CF | |
| 2004 | 1.5 | 3.8 | 0.260 | 0.7936 | $0.3 | $0.3 | |
| 2005 | 1.0 | 3.3 | 0.230 | 0.6449 | $0.2 | $0.1 | |
| 2006 | 0.7 | 2.9 | 0.208 | 0.5341 | $1.8 | $1.0 | |
| 2007 | 0.5 | 2.6 | 0.194 | 0.4471 | $7.4 | $3.3 | |
| 2008 | 0.3 | 2.4 | 0.184 | 0.3778 | $7.7 | $2.9 | |
| 2009 | 0.2 | 2.3 | 0.174 | 0.3218 | $8.1 | $2.6 | |
| 2010 | 0.0 | 2.1 | 0.165 | 0.2762 | $8.5 | $2.4 | |
| PV (2004-2010) | $12.5 | ||||||
| Terminal Value | 44.7 | ||||||
| Total PV | 57.2 | ||||||
| 1Market value of common equity is assumed to grow by the rate of growth in income available to common; preferred equity is assumed equal | |||||||
| to its book value; and debt is equal to its repayment schedule. | |||||||
| 2Comparable firm unlevered Bu = Bl/(1+(D/E)(1-t)) | |||||||
| 3Firm's levered beta Bl = Bu(1.0+(D/E)(1-t)) | |||||||
| 4Because of the changing D/E ratio, the the discount factor is expressed in multiplicative form to reflect the differing cash flow streams | |||||||
| from generated by investment made at each level of the D/E ratio. For example, present value in 206 is expressed as follows: | |||||||
| Pv2006 = 1.0 / ((1.258)(1.220)(1.199)) = .5438 | |||||||
FIN660.v10R.Excel-Based_Model_to_Estimate_Firm_Borrowing_Capacity.xlsx
Sheet1
| Table 13-13. Determining Borrowing Capacity | |||||||||
| Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | |
| Assumptions: | |||||||||
| Sales Growth % | 0 | 1.05 | 1.05 | 1.05 | 1.05 | 1.05 | 1.05 | 1.05 | 1.05 |
| Cost of Sales (COS) as % of Sales | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |
| Sales, General & Admin. Exp. as % of Sales | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 |
| Depreciation as % of Sales | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 |
| Amortization as % of Sales | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| Interest on Cash & Marketable Securities % | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 |
| Interest on Senior Debt % | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 | 0.07 |
| Interest on Subordinated Debt % | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 | 0.09 |
| Tax rate | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 |
| Cash & Marketable Securities as % Sales | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 | 0.01 |
| Change in Working Capital as % of Sales | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 | 0.02 |
| Capital Expenditues as % of Sales | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 |
| ($Millions) | |||||||||
| Sales | 500.0 | 525.0 | 551.3 | 578.8 | 607.8 | 638.1 | 670.0 | 703.6 | 738.7 |
| Less: Cost of Sales | 250.0 | 262.5 | 275.6 | 289.4 | 303.9 | 319.1 | 335.0 | 351.8 | 369.4 |
| Less: Sales, General Admin. Exp. | 50.0 | 52.5 | 55.1 | 57.9 | 60.8 | 63.8 | 67.0 | 70.4 | 73.9 |
| Equals: EBITDA | 200.0 | 210.0 | 220.5 | 231.5 | 243.1 | 255.3 | 268.0 | 281.4 | 295.5 |
| Less: Depreciation | 15.0 | 15.8 | 16.5 | 17.4 | 18.2 | 19.1 | 20.1 | 21.1 | 22.2 |
| Less: Amortization | 5.0 | 5.3 | 5.5 | 5.8 | 6.1 | 6.4 | 6.7 | 7.0 | 7.4 |
| Plus: Interest Income | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 | 0.2 |
| Less: Interest Expense | |||||||||
| Senior Debt | 52.2 | 47.9 | 43.1 | 37.7 | 31.7 | 24.9 | 17.4 | 9.1 | |
| Subordinated Debt | 27.0 | 27.0 | 27.0 | 27.0 | 27.0 | 27.0 | 27.0 | 27.0 | |
| Total Interest Expense | 79.2 | 74.9 | 70.1 | 64.7 | 58.7 | 51.9 | 44.4 | 36.1 | |
| Equals: Income Before Tax | 110.0 | 123.7 | 138.4 | 154.3 | 171.3 | 189.5 | 209.1 | 230.0 | |
| Less: Taxes Paid | 44.0 | 49.5 | 55.4 | 61.7 | 68.5 | 75.8 | 83.6 | 92.0 | |
| Equals: Net Income After Tax | 66.0 | 74.2 | 83.1 | 92.6 | 102.8 | 113.7 | 125.4 | 138.0 | |
| Plus: Depreciation & Amortization Expense | 21.0 | 22.1 | 23.2 | 24.3 | 25.5 | 26.8 | 28.1 | 29.5 | |
| Less Change in Working Capital | 10.5 | 11.0 | 11.6 | 12.2 | 12.8 | 13.4 | 14.1 | 14.8 | |
| Less Capital Expenditures | 15.8 | 16.5 | 17.4 | 18.2 | 19.1 | 20.1 | 21.1 | 22.2 | |
| Equals: Cash Available for Debt Reduction | 60.7 | 68.7 | 77.3 | 86.5 | 96.4 | 107.0 | 118.4 | 130.6 | |
| Cash Balance | 5.0 | 5.3 | 5.5 | 5.8 | 6.1 | 6.4 | 6.7 | 7.0 | 7.4 |
| Senior Debt Outstanding at yearend1 | 745.6 | 684.8 | 616.1 | 538.9 | 452.4 | 356.0 | 249.0 | 130.6 | -0.0 |
| Subordinated Debt Outstanding at yearend2 | 300.0 | 300.0 | 300.0 | 300.0 | 300.0 | 300.0 | 300.0 | 300.0 | 300.0 |
| Total Debt | 1045.6 | 984.8 | 916.1 | 838.9 | 752.4 | 656.0 | 549.0 | 430.6 | 300.0 |
| Net Debt to EBITDA Ratio | 5.20 | 4.66 | 4.13 | 3.60 | 3.07 | 2.55 | 2.02 | 1.51 | 0.99 |
| Interest Coverage (EBITDA/Net Interest Exp. | 0.00 | 2.66 | 2.95 | 3.31 | 3.77 | 4.37 | 5.18 | 6.36 | 8.23 |
| 1Assumes 100% of cash available for debt reduction is used to pay off senior debt. 2Subordinated debt payable as a balloon note in year 10. |
Sheet2
Sheet3
FIN660.v10R.Excel-Based_Model_to_Value_Firms_Experiencing_Financial_Distress.xlsx
Sheet1
| Exhibit 16- 5 Present Value of McClatchey Company Using the Adjusted Present Value Method | ||||||
| 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | |
| General Assumptions: | ||||||
| Comparable Company Unlevered Betaa | 1.1344 | |||||
| 10 Year Treasury Bond Rate (%) | 3.65 | |||||
| Cum. Probability of Default for Firm Rated B-b | 0.4212 | |||||
| Equity Premium | 0.055 | |||||
| Specific Company Data: McClatchy | ||||||
| Price/sh @3/10/2010 | 5.19 | |||||
| Fully Diluted Shares outstanding | 84,470,000 | |||||
| Market Value (MV) of Equity 3/10/2010 | $438,399,300 | $650,896,030 | $854,892,890 | $1,054,809,813 | $1,307,204,929 | $1,616,136,550 |
| Market Value (MV) of Debt 3/10/2010c | $740,249,905 | $666,224,915 | $599,602,423 | $539,642,181 | $485,677,963 | $437,110,166 |
| MV of Debt / MV of Equity Ratio | 1.69 | 1.02 | 0.70 | 0.51 | 0.37 | 0.27 |
| Weighted average maturity of Mclatchy debt | 9.6 years | |||||
| McClatchy Credit Rating | B- | A | ||||
| McClatchy Company Assumptions: | ||||||
| Price to Earnings Ratio | 6.00 | 4.00 | 4.00 | 4.00 | 5.00 | 6.00 |
| Target Debt / Equity Ratio @2015 | 0.33 | |||||
| Implied Target Debt/Total Capital Ratio @2015d | 0.25 | |||||
| Current Cost of Debte | 10.65 | 0.08 | ||||
| Expected Cost of Bankruptcy (% of Firm Value) | .15 - .25 | |||||
| Terminal Period Growth Rate | 0.03 | |||||
| Unlevered Cost of Equity (2011 - 2015)f | 0.0989 | |||||
| Terminal Period WACCg | 0.0862 | |||||
| Revenue | $1,471,584,000 | $1,398,004,800 | $1,342,084,608 | $1,315,242,916 | $1,328,395,345 | $1,354,963,252 |
| Net Income (3.8% of revenue) | $54,090,000 | $53,124,182 | $50,999,215 | $49,979,231 | $50,479,023 | $51,488,604 |
| Depreciation (8% of revenue) | $142,889,000 | $111,840,384 | $107,366,769 | $105,219,433 | $106,271,628 | $108,397,060 |
| Change in Working Capital (3.5% of revenue) | $73,579,000 | $48,930,168 | $46,972,961 | $46,033,502 | $46,493,837 | $54,198,530 |
| Gross Capital Spending | $13,574,000 | $13,980,048 | $20,131,269 | $26,304,858 | $33,209,884 | $33,874,081 |
| Principal Repayments | $64,200,000 | $74,024,991 | $66,622,491 | $59,960,242 | $53,964,218 | $48,567,796 |
| Dividends Paid | $14,905,000 | $10,905,000 | $5,905,000 | $5,905,000 | $5,905,000 | $5,905,000 |
| Equity Cash Flow | $30,721,000 | $17,124,360 | $18,734,262 | $16,995,061 | $17,177,712 | $17,340,256 |
| Interest expense | $107,353,000 | $96,617,700 | $86,955,930 | $78,260,337 | $70,434,303 | $63,390,873 |
| Tax Shield (40% of interest expense) | $42,941,200 | $38,647,080 | $34,782,372 | $31,304,135 | $28,173,721 | $25,356,349 |
| Present Value: | ||||||
| PV Equity Cash Flow @9.89% | $2,026,598 | |||||
| PV Terminal Value | $1,445,999,847 | |||||
| Total PV | $1,448,026,445 | |||||
| Plus: PV of Tax Shield | $3,868,418 | |||||
| Adjusted Present Value of Firm | $1,451,894,863 | |||||
| Less: Expected Cost of Bankruptcy @15% | $217,784,229 | |||||
| Expected Cost of Bankruptcy @20% | $290,378,973 | |||||
| Expected Cost of Bankruptcy @25% | $362,973,716 | |||||
| Expected Cost of Bankruptcy @30% | $435,568,459 | |||||
| Expected Cost of Bankruptcy @35% | $508,163,202 | |||||
| Expected Cost of Bankruptcy @40% | $580,757,945 | |||||
| Less: Market Value of Debt | $740,249,837 | $740,249,837 | $740,249,837 | $740,249,837 | $740,249,837 | $740,249,837 |
| Equals: Equity Value | $493,860,796 | $421,266,053 | $348,671,310 | $276,076,567 | $203,481,824 | $130,887,081 |
| Equals: Equity Value Per Share | $5.85 | $4.99 | $4.13 | $3.27 | $2.41 | $1.55 |
| Notes: | ||||||
| aComparable company unlevered beta = comparable company levered beta / (1 + (1 - marginal tax rate) x comparable company debt / equity) = | ||||||
| = 1.924 / (1 + (1 - .4) x 1.16) = 1.1344 (Based on top 10 U.S. newspapers in terms of market capitalization. | ||||||
| bSee Exhibit 13 - in Chapter 13. | ||||||
| cMarket value of debt = Interest Expense x {[1 - 1 / (1 + i)n ] / 1+ i} + Face value of debt / (1+ i)n = | ||||||
| = $107,353,000 x {[1 - 1 / (1.1065)9.6 ] / 1.1065} + 1,796,436,000 / (1.1065)9.6 = 60,298,095 + 679,951,810 = $740,249,905 | ||||||
| dImplied debt to total capital ratio = (D/E) / (1 + D/E) =.33 / 1.33 = .25 | ||||||
| eCost of debt equals the risk free rate of 3.65 percent plus a 7 percent default spread based on McClatchy's rating of B- from S&P | ||||||
| fUnlevered Cost of Equity = .0365 + 1.1344 (.055) = .0989 | ||||||
| gTerminal Period WACC = .25 x (1 - .4) x .08 + .75 x .0989 = .012 + .0742 = .0862 |
Sheet2
Sheet3
FIN660.v10R.Excel-Based_Offer_Price_Simulation_Model.xlsx
Sheet1
| Offer Price Simulation Model | ||||||||||
| Deal Terms and Conditions | ||||||||||
| Cash Portion of Offer Price (%) | 0.25 | |||||||||
| Equity Portion of Offer Price (%) | 0.75 | |||||||||
| Percent of Anticipated Synergy Shared with Target % | 0.3 | |||||||||
| Specific Firm Data | ||||||||||
| Acquirer Share Price ($/Share) | 16.03 | |||||||||
| Target Share Price ($/Share) | 14.25 | |||||||||
| Target Shares Outstanding (Millions) | 19.10 | |||||||||
| Acquirer Shares Outstanding, Pre-Closing (Millions) | 426.00 | |||||||||
| PV of Anticipated Net Synergy ($M) | 368.00 | |||||||||
| Altenative Scenarios Based on Different Amounts of Synergy Shared with Target | ||||||||||
| % Shared | Offer Price | Offer Price | Post-Acq. | Post-Acquisition.EPS | ||||||
| Synergy | ($M) | Per Share | Total Shares | 2008 | 2009 | 2010 | 2011 | 2012 | ||
| Calculated Data | 0.1 | 309 | 16.18 | 445 | 1.09 | 1.29 | 1.43 | 1.60 | 1.61 | |
| Minimum Offer Price ($M) | 272 | 0.2 | 346 | 18.10 | 448 | 1.08 | 1.28 | 1.42 | 1.59 | 1.61 |
| Maximum Offer Price ($M) | 640 | 0.3 | 383 | 20.03 | 450 | 1.08 | 1.27 | 1.41 | 1.58 | 1.60 |
| Initial Offer Price ($M) | 383 | 0.4 | 419 | 21.96 | 452 | 1.07 | 1.27 | 1.40 | 1.57 | 1.59 |
| Initial Offer Price Per Share ($) | 20.03 | 0.5 | 456 | 23.88 | 454 | 1.07 | 1.26 | 1.40 | 1.57 | 1.58 |
| Purchase Price Premium Per Share (%) | 0.41 | 0.6 | 493 | 25.81 | 457 | 1.06 | 1.25 | 1.39 | 1.56 | 1.57 |
| Composition of Purchase Price Per Target Share | 0.7 | 530 | 27.74 | 459 | 1.06 | 1.25 | 1.38 | 1.55 | 1.57 | |
| Acquirer Equity Per Target Share | 15.02 | 0.8 | 567 | 29.66 | 461 | 1.05 | 1.24 | 1.38 | 1.54 | 1.56 |
| Cash Per Target Share ($) | 5.01 | 0.9 | 603 | 31.59 | 464 | 1.05 | 1.24 | 1.37 | 1.54 | 1.55 |
| Share Exchange Ratio | 1.25 | 1.0 | 640 | 33.52 | 466 | 1.04 | 1.23 | 1.36 | 1.53 | 1.54 |
| New Shares Issued by Acquirer | 23.87 | |||||||||
| Acquirer Shares Outstanding, Post-Closing (Milions) | 449.87 | |||||||||
| Ownership Distribution in New Firm | ||||||||||
| Acquirer Shareholders (%) | 0.95 | |||||||||
| Target Shareholders (%) | 0.05 | |||||||||
| Consolidated Acquirer & Target Net Income | ||||||||||
| 2009 | 2010 | 2011 | 2012 | 2013 | ||||||
| Post-Acquisition Consolidated Net Income ($M) | 485 | 573 | 635 | 712 | 719 |
