Refer to the Excel workbook titled ?WACC_Project_FinancialStatements.xlsx? for financial information and additional information in the bulleted list below

• The firm has 25 million shares of common stock outstanding. The beta is 1.4.
• The firm plans to increase dividends by 25% each year for the next 2 years and then by 3% per year after that.
• The expected return on the market portfolio is 13% and the risk-free rate is 3.5%.
• The firm has three bond issues outstanding. All bonds make annual coupon payments.
  • Bond 1 is a 5% coupon bond with face value of $1000. The YTM is 4% and the bonds mature in 14 years. There are 10,000 of these bonds.
  • Bond 2 is a zero coupon bond with face value of $5000. The YTM is 3% and the bonds mature in 22 years. There are 4,000 of these bonds.
  • Bond 3 is a 7% coupon bond with face value of $1000. The YTM is 5.5% and the bonds mature in 7 years. There are 65,000 of these bonds.
• The firm has 250,000 shares of preferred stock outstanding that pay a dividend of $5 per share annually. The current price of a share of preferred stock is $65.
• The company?s average tax rate is 30%.

• Find WACC using market values:
  • Find the cost of equity using CAPM.
  • Find the stock price using the dividend growth model.
  • Find the cost of debt by taking a weighted average of the bonds? YTMs:
  • Find bond value for each bond.
  • Find total value for each of the bond issues outstanding.
  • Use i. and ii. to find the weights and the YTMs to find the weighted average YTM.
  • Find the total market value of equity using your answer to b. and the number of shares outstanding.
  • Find the cost of preferred stock.
  • Find Wd, We, and Wp using market values.
  • Plug the numbers into the WACC formula to get the market value-based WACC.
• Find WACC using book values and ignoring the preferred stock:
  • Find the cost of equity using CAPM (already did this)
  • Find the weights using the most recent balance sheet.
  • Use your cost of debt from #1c above.
  • Plug the numbers in the WACC formula to get a book value-based WACC.

All numbers in $thousands Sales Cost of Goods Sold Depreciation EBIT Interest Taxable Income Taxes (35%) Net income Dividends Retained Earnings 2008 21,860.00 11,316.00 23,084.00 56,260.00 $ $ $ $ $ $ $ $ $ $ 2009 305,830.00 210,935.00 26,850.00 68,045.00 11,930.00 56,115.00 19,640.25 36,474.75 19,999.75 16,475.00 $ $ $ $ 22,050.00 13,850.00 24,650.00 60,550.00 Cash Accounts Receivable Inventory Total Current Assets $ $ $ $ Fixed Assets Accumulated Depreciation Net Fixed Assets $ 340,000.00 $ 393,307.00 $ 105,932.00 $ 132,782.00 $ 234,068.00 $ 260,525.00 Total Assets $ 290,328.00 $ 321,075.00 Accounts Payable Notes Payable Total Current Liabilities $ 19,320.00 $ 22,850.00 $ 19,643.00 $ 20,385.00 $ 38,963.00 $ 43,235.00 Long-term Debt $ 75,000.00 $ 85,000.00 Total Debt $ 113,963.00 $ 128,235.00 Comon Stock Accumulated Retained Earnings $ 25,000.00 $ 25,000.00 $ 151,365.00 $ 167,840.00 Total Equity $ 176,365.00 $ 192,840.00 Total Debt and Equity $ 290,328.00 $ 321,075.00 Refer to the data in midterm_data.xlsx for this problem. Perform ratio analysis with key ratios. How is the financial health o this company? What are this company's cash flows (OCF, NWC, NCS, TCF, CFC, CFS)? Create pro forma statements for next year for this company assuming growth at the sustainable growth rate What is the EFN? Choose a plug and apply it in a new pro forma statement. What is the new value for your plug? What has happened to the company's D/E ratio in your forecasted financial statements? Repeat part c using the internal growth rate. r this problem. w is the financial health of NWC, NCS, TCF, CFC, r for this company th rate orma statement. What is E ratio in your forecasted ate. Refer to the Excel workbook titled \"WACC_Project_FinancialStatements.xlsx\" for financial information and additional information in the bulleted list below The firm has 25 million shares of common stock outstanding. The beta is 1.4. The firm plans to increase dividends by 25% each year for the next 2 years and then by 3% per year after that. The expected return on the market portfolio is 13% and the risk-free rate is 3.5%. The firm has three bond issues outstanding. All bonds make annual coupon payments. o Bond 1 is a 5% coupon bond with face value of $1000. The YTM is 4% and the bonds mature in 14 years. There are 10,000 of these bonds. o Bond 2 is a zero coupon bond with face value of $5000. The YTM is 3% and the bonds mature in 22 years. There are 4,000 of these bonds. o Bond 3 is a 7% coupon bond with face value of $1000. The YTM is 5.5% and the bonds mature in 7 years. There are 65,000 of these bonds. The firm has 250,000 shares of preferred stock outstanding that pay a dividend of $5 per share annually. The current price of a share of preferred stock is $65. The company's average tax rate is 30%. 1. Find WACC using market values: a. Find the cost of equity using CAPM. b. Find the stock price using the dividend growth model. c. Find the cost of debt by taking a weighted average of the bonds' YTMs: i. Find bond value for each bond. ii. Find total value for each of the bond issues outstanding. iii. Use i. and ii. to find the weights and the YTMs to find the weighted average YTM. d. Find the total market value of equity using your answer to b. and the number of shares outstanding. e. Find the cost of preferred stock. f. Find Wd, We, and Wp using market values. g. Plug the numbers into the WACC formula to get the market value-based WACC. 2. Find WACC using book values and ignoring the preferred stock: a. Find the cost of equity using CAPM (already did this) b. Find the weights using the most recent balance sheet. c. Use your cost of debt from #1c above. d. Plug the numbers in the WACC formula to get a book value-based WACC