Solve in Excel

5. You are valuing Thunderball Enterprises, a publicly-traded firm, in December 2016 to decide whether to invest in it. Forecasts for certain Income statement and Balance sheet items are presented in Exhibit 1. There is also some additional data in Exhibit 2. You have estimated that the free cash flow in 2020 will be $37 million and would remain constant for eternity. Assume also that the company is currently at its optimal capital structure and has an "A" credit rating. (8 pts.) a) What are the FCFs of the company? b) What is the weighted average cost of capital for this company? c) What is the expected market value of equity? 2018 3,037 2,712 231 35 9 37 6 2019 3,060 2,730 233 37 12 39 7 5 Exhibit 1. (in millions) 2017 Revenues 3,015 Cost of goods sold 2,694 Selling, G&A expenses 230 Derr. & Amort. 33 Net working capital 13 Capital expenditures 35 Goodwill/intangibles *Exhibit 2. Relevant financial data (in millions) Revenues Goodwill/intangibles Excess cash and marketable securities Excess real estate Other nonoperating assets Underfunded pension funds Expected marginal tax rate Book value of debt Thunderball long-term Debt / Equity ratio Net working capital Average beta of comparable firms Average D/E of comparable firms Government 10 year bond rate Yield to maturity for "A" rated companies Market risk premium Prime corporate paper rate 2016 2,971 5 2 380 5 6 35% 123 45% 12 0.93 22% 10.95% 12.45% 5.50% 13.41% 5. You are valuing Thunderball Enterprises, a publicly-traded firm, in December 2016 to decide whether to invest in it. Forecasts for certain Income statement and Balance sheet items are presented in Exhibit 1. There is also some additional data in Exhibit 2. You have estimated that the free cash flow in 2020 will be $37 million and would remain constant for eternity. Assume also that the company is currently at its optimal capital structure and has an "A" credit rating. (8 pts.) a) What are the FCFs of the company? b) What is the weighted average cost of capital for this company? c) What is the expected market value of equity? 2018 3,037 2,712 231 35 9 37 6 2019 3,060 2,730 233 37 12 39 7 5 Exhibit 1. (in millions) 2017 Revenues 3,015 Cost of goods sold 2,694 Selling, G&A expenses 230 Derr. & Amort. 33 Net working capital 13 Capital expenditures 35 Goodwill/intangibles *Exhibit 2. Relevant financial data (in millions) Revenues Goodwill/intangibles Excess cash and marketable securities Excess real estate Other nonoperating assets Underfunded pension funds Expected marginal tax rate Book value of debt Thunderball long-term Debt / Equity ratio Net working capital Average beta of comparable firms Average D/E of comparable firms Government 10 year bond rate Yield to maturity for "A" rated companies Market risk premium Prime corporate paper rate 2016 2,971 5 2 380 5 6 35% 123 45% 12 0.93 22% 10.95% 12.45% 5.50% 13.41%