Finance 350 Team Case 3 AirThread Connections: Capital Budgeting (M&A) - Complete this assignment as a group outside of class. Provide a typed written report of your analysis (not a list of answers to the questions. This should include a statement of the problem along with any issues you believe are relevant for your analysis. Use Exhibits, Tables or Figures to illustrate any calculations. Label and format them correctly (5 points). See the resources provided in the team case folder. Participation and group attendance are required for the case discussion on Wednesday, November 13 and are worth 10% of your grade. 1. (2 Points) Briefly describe the situation presented in the case. In doing this, be sure to address the following: a. Who is Robert Zimmerman and what is his role in the company and regarding this analysis? b. Summarize Air Thread's competitive environment and recent financial performance. Why is a merger of American Cable and Air Thread in the best interest of both companies? 2. (6 points) AirThread is a private company and does not have a published beta. Use the Pure Play method to calculate AirThreads beta. a. Choose a competitor (or competitors) that is (are) most similar to AirThread to use for the Pure Play company companies). Justify your choice(s) for Pure Play companies. Assume 30% tax rates for all competitors. b. Relever the beta for AirThread considering three capital structures: 39% debt, 50% debt, and the average amount of debt expected once the bullet payment is made and the debt to purchase AirThread is paid down. c. Ignore the illiquidity discount for private companies. 3. (12 points) Calculate the cost of equity (r) and WACC for all three capital structures considered in part 2 above a. Use the 10 year U.S. Treasury yield and the market risk premium referenced in the case. b. The debt yield spread given is 125 basis points. This is equivalent to 1.25% c. The 5.5% borrowing cost and BBB rating provided are applicable for a structure with 39% debt. The cost of debt when using additional debt in the capital structure increases to 5.875%, and decreases to 5.325% with less debt in the structure 4. (24 points) Complete a valuation of Air Thread Connections based on the provided actual financial information and projections for 2008 through 2012 a. Calculate projected after-tax operating cash flows (NOPAT). b. Calculate projected investments in net operating working capital 1. This will require finding the projected values of operating current assets and operating current liabilities first. li. Helpful hints: Find the averages of operating current asset and current liability accounts as a percent of sales) based on current financial statements and use these to determine projected net operating working capital values. Finance 350 Team Case 3 C. Calculate the FCF for Air Thread. FCF = [EBIT(1-T)+Depreciation] - [Investment in Gross Fixed Assets and NOWC) d. Determine the terminal (horizon, continuing) value at the end of 2012 1. Consider that the growth rate is necessary to calculate a terminal value. il. Think about realistic growth rates into perpetuity for a mature company like AirThread. Consider market potential for AirThread products/sales. ill. Because the capital structure will change after five years you will use the terminal WACC in the terminal value calculation. e. Calculate the value of operations (V) and the entity value (Ve ) Because the capital structure will change after five years you will use the terminal WACC to discount the terminal value and the optimal WACC to discount the FCF. 5. (5 points) Perform intrinsic value sensitivity analysis using a data table in Excel. You can find this in the Data Tab/What-if Analysis/Data Table. a. Consider the terminal growth rate, optimal WACC, and terminal WACC. b. Note that you must use spreadsheet modeling (formulas with cell references) throughout your analysis in order for your data table to work properly. c. Determine which input variable your analysis is most sensitive to and how this might affect your recommended intrinsic value. 6. (6 points) Explain your calculations and results to Robert Zimmerman in your analysis and in your class presentation. He will be expecting you to provide an intrinsic value for Air Thread as a starting point to use as a price for the merger. Considering the risk involved with large investments (especially mergers) and how that might affect the WACC and value, justify the recommended value based on your analysis, WACC adjustments if any), and the sensitivity analysis. a. Prepare a 1-2 minute summary to present your findings to Zimmerman, and advise him about what you believe to be the value of AirThread. Justify your value. Include your presentation summary here that you will present in class. b. All members should be prepared to discuss the team's valuation analysis including justification for assumptions made and inputs used in your calculations. Exhibit 1 Exhibit 2 Exhibit 3 Exhibit 4 AIRTHREAD ACQUISITION Revenue Projections Service Revenue Service Revenue Growth 2008 4.1943 14.0% 2009 4.781, 5 14.096 2010 5379. 2 12.5% 5 2011 ,917.2 100% 2012 6,331.4 7.0% Equipment Revenue Equipment Revenue/Service Revenue (1) 314.8 7.5% 358.8 7.5% 4752 7.5% 7.5% 7.5% Operating Expenses: System Operating Expenses System Operating Exp./Service Revenue 838.9 20.0% 956.3 20.0% 1.075.8 20.0% 1.183.4 20.0% 1.266.3 20.0% Cost of Equipment Sold Equipment COGS 755.5 240.0% 861.2 240.0% 968.9 240.0% 1.065.8 240.0% 1.140.4 240.0% 1.803.6 Selling. General & Administrative SG&A Total Revenue 2.056.2 40.096 2,313.2 40.096 2.544.5 40.096 2.722.6 40.0% Depreciation & Amortization 705.2 804.0 867.4 9529 Tax Rate 40.0% 40.0% 40.0% 40.0% 40.0% Working Capital Ac tions Accounts Receivable Days Sales Equip. Rev. Prepaid Expenses Accounts Payable Deferred Serv. Revenue Accrued Liabilities 41.67x 154 36x 1.38% 35.54x 14.01x 6.85% 41.67x 154.36% 1.38% 35.54 14.01x 6 85% 41.67% 154.36x 1.38% 35.54% 14.01x 6.85% 41.67% 154.36x 1.38% 35.54x 14.01x 6.85% 41.67x 154.36x 1.38% 35.54x 14.01% 6.85% Capital Expenditures (2): Capital Expenditures Cap-x/Total Revenue 631.3 719.7 8674 15.0% 970.1 15.2% 1,055.0 15.5% (1) Based on a 360-day year. Days Payable, Deferred Service Revenue, and Days Accrued Liabilities are based on total cash operating expenses. (2) Includes investments in property, plant & equipment, as well as licenses and customer lists. Total Consolidated Markets: Region Central US Mid-Atlantic New England Northwest New York Total Population 65,096 11,677 2,830 2,287 Customers 3,846 1,180 518 Penetration 5.9% 10.1% 18.3% 18.8% 30,6% 7.4% 481 431 147 6,122 82,371 Total Operating Markets: Region Central US Mid-Atlantic New England Northwest New York Total Population 32,497 7,346 2,344 2,287 481 44,955 Customers 3,846 1,180 518 431 Penetration 11.8% 16.1% 22.1% 18.8% 30.6% 13.6% 147 6,122 Exhibit 1 Exhibit 2 Exhibit 3 AIRTHREAD ACQUISITION 2005 301 2006 310 Customer Data: Net Customer Additions Cost Per Customer Addition Cost of New Customer Additions 2007 477 487 232,299 372 385 111,972 119,350 Cost of Equipment Sold/Equipment Revenue 251.3% 219.9% 239.8% Monthly Churn Rate 2.1% 2.1% 1.7% 2006 2007 Revenue Per Minute Monthly ARPU Customer Minutes Per Month Revenue Per Minute 2005 45.24 625 0.0724 47.23 704 0.0671 51.13 859 0.0595 Assets: Cash & Cash Equivalents Marketable Securities Accounts Receivable Inventory Prepaid Expenses Deferred Taxes Other Current Assets Total Current Assets 2005 29.0 0.0 362.4 92.7 32.1 2006 32.9 249.0 407.4 117.2 35.0 0.0 13.4 854.9 2007 204.5 16.4 435.5 101.0 41.6 18.6 16.2 833.8 82 15.5 539.9 Property, Plan & Equipment Licenses Customer Lists Marketable Equity Securities Investments in Affiliated Entities Long Term Note Receivable Goodwill Other Long Term Assets Total Assets 2,553.0 1,362,3 47.6 225.4 172.1 2,628.8 1.494.3 26.2 2.595.1 1.482.4 15.4 0.0 157.7 150.3 45 485.5 481.2 30.0 5,416.2 31.1 491.3 31.8 5.680.6 5.611.9 Liabilities & Owners' Equity: Accounts Payable Deferred Revenue & Deposits Accrued Liabilities Taxes Payable Deferred Taxes Note Payable Forward Contract Derivative Liability Other Current Liabilities Total Current Liabilities 254.1 111.4 429 36.7 0.0 135.0 260.8 143.4 59.2 43.1 0.0 254.9 123.3 47.8 26,9 26.3 35.0 159.9 88.8 93.7 856.7 00 0.0 0.0 662.7 604.2 Long Term Debt Forward Contracts Derivative Liability Deferred Tax Liability Asset Retirement Obligation Other Deferred Liabilities 1,001.4 159.9 25.8 647.1 90.2 46.2 1.001.8 0.0 0.0 601.5 127.6 62.9 1.0023 0.0 0.0 554.4 126.8 84.5 Minority Interest 41.9 36.7 Common Stock & Paid-In Capital Retained Earnings Total Liabilities & Owners' Equity 1,375.0 1.366.0 5,416.2 1,378,9 1,6144 5,680.6 1,404.1 1.792.1 5,611.9 Exhibit 1 Exhibit 2 Exhibit 3 Exhibit 4 Exhibit 5 Exhibit 6 AIRTHREAD ACQUISITION Am Term Loan Amortization Annual Interest America Period Payment Principal Period Payment Interest Principal Balance 1998 Date 110 Balance 1758 3,734 3710 30611 17 31/10 16 5.619 3,615 31/5/05 DS 31/7/08 31/8/OS 22554 2525 2.496 11 31/10/11 301011 31/12/11 31/1/12 3/12 31/3/12 30-412 11/12 1,542 2457 2008 2378 2.148 11/10/DS WOO 31/120 10 22/09 3.492 1441 31/1112 22 3,418 3.393 3,367 20359 M04/09 31/5/09 31/10/12 30/11/12 11 12 2.166 // 25 1214 IN 1161 3,135 3.109 02 3.055 10.30 1/10 MI 31/12/09 11/10 21/10 MIVIO MU10 VIVIO MWIO 12/10 INIO 9/10 11/1010 011/10 31/1210 11/11 22/11 WWII 14/11 3 / 2.4 2030 3.IN 210 2,726 2.69 = Han Net Equity Market Value Debt Debt Equity 58.3% Value Equity Betal 0.86 Net Income EBIT 11.795 36.% 29.5% 41.96 0.89 7,020 Comparable Companies Universal Mobile Neuberger Wireless Agile Connections Big Country Communications Rocky Mountain Wireless Average 118.497 189.470 21.079 26.25 Debt 09.130 79,351 S.OND X135 3,268 Revenue 43.82 42.64 34.698 38.896 4,064 EBITDA 16049 14,099 9014 12.614 4.102 (30) 1.17 0.97 1.631 6,702 24,1% 30 % 28.1% 31.7 44.44 7160 1.13 510 1.025 200 1.00 1) Equity batas were estimated based on weekly stock returns over a three year period. Note: The current industry and competitor leverage ratios reflect the historical averages that existed over the past three years Exhibit 1 Exhibit 2 Exhibit 3 Ext AIRTHREAD ACQUISITION Historical Operating Results 2006 3,214.4 258.7 3,473.2 639.7 568.9 Operating Results: Service Revenue Plus: Equipment Sales Total Revenue Less: System Operating Expenses Less: Cost of Equipment Sold Less: Selling, General & Administrative EBITDA Less: Depreciation & Amortization EBIT Less: Interest Expense Plus: Equity in Earnings of Affiliates Plus: Gains (Losses) on Investments Plus: Other Income EBT Less: Taxes Income Before Minority Interest Less: Minority Interest Net Income 2005 2,827.0 203.7 3,030.8 604.1 511.9 1.217.7 697.0 490.1 206.9 84.9 66.7 18.1 54.5 261.3 95.9 165.5 10.5 155.0 1,399.6 865.0 555.5 309.5 93.7 93.1 50.8 (46.6) 313.1 120.6 192.5 13.0 2007 3,679.2 267.0 3,946.3 717.1 640.2 1,555.6 1,033.3 582.3 451.1 84.7 90.0 83.1 7.0 546.5 216.7 329.8 15.1 314.7 179.5 Finance 350 Team Case 3 AirThread Connections: Capital Budgeting (M&A) - Complete this assignment as a group outside of class. Provide a typed written report of your analysis (not a list of answers to the questions. This should include a statement of the problem along with any issues you believe are relevant for your analysis. Use Exhibits, Tables or Figures to illustrate any calculations. Label and format them correctly (5 points). See the resources provided in the team case folder. Participation and group attendance are required for the case discussion on Wednesday, November 13 and are worth 10% of your grade. 1. (2 Points) Briefly describe the situation presented in the case. In doing this, be sure to address the following: a. Who is Robert Zimmerman and what is his role in the company and regarding this analysis? b. Summarize Air Thread's competitive environment and recent financial performance. Why is a merger of American Cable and Air Thread in the best interest of both companies? 2. (6 points) AirThread is a private company and does not have a published beta. Use the Pure Play method to calculate AirThreads beta. a. Choose a competitor (or competitors) that is (are) most similar to AirThread to use for the Pure Play company companies). Justify your choice(s) for Pure Play companies. Assume 30% tax rates for all competitors. b. Relever the beta for AirThread considering three capital structures: 39% debt, 50% debt, and the average amount of debt expected once the bullet payment is made and the debt to purchase AirThread is paid down. c. Ignore the illiquidity discount for private companies. 3. (12 points) Calculate the cost of equity (r) and WACC for all three capital structures considered in part 2 above a. Use the 10 year U.S. Treasury yield and the market risk premium referenced in the case. b. The debt yield spread given is 125 basis points. This is equivalent to 1.25% c. The 5.5% borrowing cost and BBB rating provided are applicable for a structure with 39% debt. The cost of debt when using additional debt in the capital structure increases to 5.875%, and decreases to 5.325% with less debt in the structure 4. (24 points) Complete a valuation of Air Thread Connections based on the provided actual financial information and projections for 2008 through 2012 a. Calculate projected after-tax operating cash flows (NOPAT). b. Calculate projected investments in net operating working capital 1. This will require finding the projected values of operating current assets and operating current liabilities first. li. Helpful hints: Find the averages of operating current asset and current liability accounts as a percent of sales) based on current financial statements and use these to determine projected net operating working capital values. Finance 350 Team Case 3 C. Calculate the FCF for Air Thread. FCF = [EBIT(1-T)+Depreciation] - [Investment in Gross Fixed Assets and NOWC) d. Determine the terminal (horizon, continuing) value at the end of 2012 1. Consider that the growth rate is necessary to calculate a terminal value. il. Think about realistic growth rates into perpetuity for a mature company like AirThread. Consider market potential for AirThread products/sales. ill. Because the capital structure will change after five years you will use the terminal WACC in the terminal value calculation. e. Calculate the value of operations (V) and the entity value (Ve ) Because the capital structure will change after five years you will use the terminal WACC to discount the terminal value and the optimal WACC to discount the FCF. 5. (5 points) Perform intrinsic value sensitivity analysis using a data table in Excel. You can find this in the Data Tab/What-if Analysis/Data Table. a. Consider the terminal growth rate, optimal WACC, and terminal WACC. b. Note that you must use spreadsheet modeling (formulas with cell references) throughout your analysis in order for your data table to work properly. c. Determine which input variable your analysis is most sensitive to and how this might affect your recommended intrinsic value. 6. (6 points) Explain your calculations and results to Robert Zimmerman in your analysis and in your class presentation. He will be expecting you to provide an intrinsic value for Air Thread as a starting point to use as a price for the merger. Considering the risk involved with large investments (especially mergers) and how that might affect the WACC and value, justify the recommended value based on your analysis, WACC adjustments if any), and the sensitivity analysis. a. Prepare a 1-2 minute summary to present your findings to Zimmerman, and advise him about what you believe to be the value of AirThread. Justify your value. Include your presentation summary here that you will present in class. b. All members should be prepared to discuss the team's valuation analysis including justification for assumptions made and inputs used in your calculations. Exhibit 1 Exhibit 2 Exhibit 3 Exhibit 4 AIRTHREAD ACQUISITION Revenue Projections Service Revenue Service Revenue Growth 2008 4.1943 14.0% 2009 4.781, 5 14.096 2010 5379. 2 12.5% 5 2011 ,917.2 100% 2012 6,331.4 7.0% Equipment Revenue Equipment Revenue/Service Revenue (1) 314.8 7.5% 358.8 7.5% 4752 7.5% 7.5% 7.5% Operating Expenses: System Operating Expenses System Operating Exp./Service Revenue 838.9 20.0% 956.3 20.0% 1.075.8 20.0% 1.183.4 20.0% 1.266.3 20.0% Cost of Equipment Sold Equipment COGS 755.5 240.0% 861.2 240.0% 968.9 240.0% 1.065.8 240.0% 1.140.4 240.0% 1.803.6 Selling. General & Administrative SG&A Total Revenue 2.056.2 40.096 2,313.2 40.096 2.544.5 40.096 2.722.6 40.0% Depreciation & Amortization 705.2 804.0 867.4 9529 Tax Rate 40.0% 40.0% 40.0% 40.0% 40.0% Working Capital Ac tions Accounts Receivable Days Sales Equip. Rev. Prepaid Expenses Accounts Payable Deferred Serv. Revenue Accrued Liabilities 41.67x 154 36x 1.38% 35.54x 14.01x 6.85% 41.67x 154.36% 1.38% 35.54 14.01x 6 85% 41.67% 154.36x 1.38% 35.54% 14.01x 6.85% 41.67% 154.36x 1.38% 35.54x 14.01x 6.85% 41.67x 154.36x 1.38% 35.54x 14.01% 6.85% Capital Expenditures (2): Capital Expenditures Cap-x/Total Revenue 631.3 719.7 8674 15.0% 970.1 15.2% 1,055.0 15.5% (1) Based on a 360-day year. Days Payable, Deferred Service Revenue, and Days Accrued Liabilities are based on total cash operating expenses. (2) Includes investments in property, plant & equipment, as well as licenses and customer lists. Total Consolidated Markets: Region Central US Mid-Atlantic New England Northwest New York Total Population 65,096 11,677 2,830 2,287 Customers 3,846 1,180 518 Penetration 5.9% 10.1% 18.3% 18.8% 30,6% 7.4% 481 431 147 6,122 82,371 Total Operating Markets: Region Central US Mid-Atlantic New England Northwest New York Total Population 32,497 7,346 2,344 2,287 481 44,955 Customers 3,846 1,180 518 431 Penetration 11.8% 16.1% 22.1% 18.8% 30.6% 13.6% 147 6,122 Exhibit 1 Exhibit 2 Exhibit 3 AIRTHREAD ACQUISITION 2005 301 2006 310 Customer Data: Net Customer Additions Cost Per Customer Addition Cost of New Customer Additions 2007 477 487 232,299 372 385 111,972 119,350 Cost of Equipment Sold/Equipment Revenue 251.3% 219.9% 239.8% Monthly Churn Rate 2.1% 2.1% 1.7% 2006 2007 Revenue Per Minute Monthly ARPU Customer Minutes Per Month Revenue Per Minute 2005 45.24 625 0.0724 47.23 704 0.0671 51.13 859 0.0595 Assets: Cash & Cash Equivalents Marketable Securities Accounts Receivable Inventory Prepaid Expenses Deferred Taxes Other Current Assets Total Current Assets 2005 29.0 0.0 362.4 92.7 32.1 2006 32.9 249.0 407.4 117.2 35.0 0.0 13.4 854.9 2007 204.5 16.4 435.5 101.0 41.6 18.6 16.2 833.8 82 15.5 539.9 Property, Plan & Equipment Licenses Customer Lists Marketable Equity Securities Investments in Affiliated Entities Long Term Note Receivable Goodwill Other Long Term Assets Total Assets 2,553.0 1,362,3 47.6 225.4 172.1 2,628.8 1.494.3 26.2 2.595.1 1.482.4 15.4 0.0 157.7 150.3 45 485.5 481.2 30.0 5,416.2 31.1 491.3 31.8 5.680.6 5.611.9 Liabilities & Owners' Equity: Accounts Payable Deferred Revenue & Deposits Accrued Liabilities Taxes Payable Deferred Taxes Note Payable Forward Contract Derivative Liability Other Current Liabilities Total Current Liabilities 254.1 111.4 429 36.7 0.0 135.0 260.8 143.4 59.2 43.1 0.0 254.9 123.3 47.8 26,9 26.3 35.0 159.9 88.8 93.7 856.7 00 0.0 0.0 662.7 604.2 Long Term Debt Forward Contracts Derivative Liability Deferred Tax Liability Asset Retirement Obligation Other Deferred Liabilities 1,001.4 159.9 25.8 647.1 90.2 46.2 1.001.8 0.0 0.0 601.5 127.6 62.9 1.0023 0.0 0.0 554.4 126.8 84.5 Minority Interest 41.9 36.7 Common Stock & Paid-In Capital Retained Earnings Total Liabilities & Owners' Equity 1,375.0 1.366.0 5,416.2 1,378,9 1,6144 5,680.6 1,404.1 1.792.1 5,611.9 Exhibit 1 Exhibit 2 Exhibit 3 Exhibit 4 Exhibit 5 Exhibit 6 AIRTHREAD ACQUISITION Am Term Loan Amortization Annual Interest America Period Payment Principal Period Payment Interest Principal Balance 1998 Date 110 Balance 1758 3,734 3710 30611 17 31/10 16 5.619 3,615 31/5/05 DS 31/7/08 31/8/OS 22554 2525 2.496 11 31/10/11 301011 31/12/11 31/1/12 3/12 31/3/12 30-412 11/12 1,542 2457 2008 2378 2.148 11/10/DS WOO 31/120 10 22/09 3.492 1441 31/1112 22 3,418 3.393 3,367 20359 M04/09 31/5/09 31/10/12 30/11/12 11 12 2.166 // 25 1214 IN 1161 3,135 3.109 02 3.055 10.30 1/10 MI 31/12/09 11/10 21/10 MIVIO MU10 VIVIO MWIO 12/10 INIO 9/10 11/1010 011/10 31/1210 11/11 22/11 WWII 14/11 3 / 2.4 2030 3.IN 210 2,726 2.69 = Han Net Equity Market Value Debt Debt Equity 58.3% Value Equity Betal 0.86 Net Income EBIT 11.795 36.% 29.5% 41.96 0.89 7,020 Comparable Companies Universal Mobile Neuberger Wireless Agile Connections Big Country Communications Rocky Mountain Wireless Average 118.497 189.470 21.079 26.25 Debt 09.130 79,351 S.OND X135 3,268 Revenue 43.82 42.64 34.698 38.896 4,064 EBITDA 16049 14,099 9014 12.614 4.102 (30) 1.17 0.97 1.631 6,702 24,1% 30 % 28.1% 31.7 44.44 7160 1.13 510 1.025 200 1.00 1) Equity batas were estimated based on weekly stock returns over a three year period. Note: The current industry and competitor leverage ratios reflect the historical averages that existed over the past three years Exhibit 1 Exhibit 2 Exhibit 3 Ext AIRTHREAD ACQUISITION Historical Operating Results 2006 3,214.4 258.7 3,473.2 639.7 568.9 Operating Results: Service Revenue Plus: Equipment Sales Total Revenue Less: System Operating Expenses Less: Cost of Equipment Sold Less: Selling, General & Administrative EBITDA Less: Depreciation & Amortization EBIT Less: Interest Expense Plus: Equity in Earnings of Affiliates Plus: Gains (Losses) on Investments Plus: Other Income EBT Less: Taxes Income Before Minority Interest Less: Minority Interest Net Income 2005 2,827.0 203.7 3,030.8 604.1 511.9 1.217.7 697.0 490.1 206.9 84.9 66.7 18.1 54.5 261.3 95.9 165.5 10.5 155.0 1,399.6 865.0 555.5 309.5 93.7 93.1 50.8 (46.6) 313.1 120.6 192.5 13.0 2007 3,679.2 267.0 3,946.3 717.1 640.2 1,555.6 1,033.3 582.3 451.1 84.7 90.0 83.1 7.0 546.5 216.7 329.8 15.1 314.7 179.5