please excel to solve
5. (20 points) Nashua Inc., a manufacturer of air filter systems for industrial facilities, is considering the addition of a new system to its current product line. The following data has been forecasted: 2018 2019 2020 2021 Depreciation 50,000 55,000 70,000 75,000 EBIT 130,000 175,000 180,000 185,000 Changes in Operating Assets 40,000 50,000 60,000 70,000 The market value of the firm's debt is $300,000, and it has $150,000 in marketable securities (non-operating assets). The company also has 10,000 shares of preferred stock that pay an annual dividend of $0.75 per share. Investors require a rate of return of 7% on preferred stocks of similar risk. The firm has 100,000 shares of common stock outstanding, and its weighted average cost of capital is 12%. The expected tax rate is 35% in the next two years and 40% after that. 1) Calculate the free cash flow for each of the next four years and the present value of these four cash flows. 2) After 2021 the firm's free cash flow is expected to grow at 5% per year indefinitely. What is the value of the firm as of the end of A B D F H 1 2 3 2019 4 Nashua Inc. Free Cash Flow Forecast 2018 50,000 130,000 40,000 35% 5 Depreciation EBIT Changes in Operating Assets Tax Rate Net Operating Profit After Tax Operating Cash Flow After Tax 1) Free Cash Flow 55,000 175,000 50,000 35% 2020 70,000 180,000 60,000 40% 2021 75,000 185,000 70,000 40% 6 7 00 10 11 12 13 14 15 Current Market Value of Debt Non-Operating Assets FCF Growth Rate after 2021 Weighted Average Cost of Capital Common Shares Outstanding Preferred Shares Outstanding Required Return on Preferred Stock Preferred Dividend Per Share Current Price of Preferred Stock $300,000 $150,000 5% 129 100,000 10,000 7% $0.75 16 17 18 19 20 21 1) 2) Value of Firm's Operations a) Firm Value from 2018 to 2021 b) Future Firm Value as of the end of 2021 c) Present Value of b) d) Total Firm Value (a)+c)) 3) 3) 22 23 24 25 26 27 28 29 30 31 32 4) Value of Equity Per Share Value Current Price Over/Under-Valued? $18 5. (20 points) Nashua Inc., a manufacturer of air filter systems for industrial facilities, is considering the addition of a new system to its current product line. The following data has been forecasted: 2018 2019 2020 2021 Depreciation 50,000 55,000 70,000 75,000 EBIT 130,000 175,000 180,000 185,000 Changes in Operating Assets 40,000 50,000 60,000 70,000 The market value of the firm's debt is $300,000, and it has $150,000 in marketable securities (non-operating assets). The company also has 10,000 shares of preferred stock that pay an annual dividend of $0.75 per share. Investors require a rate of return of 7% on preferred stocks of similar risk. The firm has 100,000 shares of common stock outstanding, and its weighted average cost of capital is 12%. The expected tax rate is 35% in the next two years and 40% after that. 1) Calculate the free cash flow for each of the next four years and the present value of these four cash flows. 2) After 2021 the firm's free cash flow is expected to grow at 5% per year indefinitely. What is the value of the firm as of the end of A B D F H 1 2 3 2019 4 Nashua Inc. Free Cash Flow Forecast 2018 50,000 130,000 40,000 35% 5 Depreciation EBIT Changes in Operating Assets Tax Rate Net Operating Profit After Tax Operating Cash Flow After Tax 1) Free Cash Flow 55,000 175,000 50,000 35% 2020 70,000 180,000 60,000 40% 2021 75,000 185,000 70,000 40% 6 7 00 10 11 12 13 14 15 Current Market Value of Debt Non-Operating Assets FCF Growth Rate after 2021 Weighted Average Cost of Capital Common Shares Outstanding Preferred Shares Outstanding Required Return on Preferred Stock Preferred Dividend Per Share Current Price of Preferred Stock $300,000 $150,000 5% 129 100,000 10,000 7% $0.75 16 17 18 19 20 21 1) 2) Value of Firm's Operations a) Firm Value from 2018 to 2021 b) Future Firm Value as of the end of 2021 c) Present Value of b) d) Total Firm Value (a)+c)) 3) 3) 22 23 24 25 26 27 28 29 30 31 32 4) Value of Equity Per Share Value Current Price Over/Under-Valued? $18
