Please solve using excel template. thank you!

Problem Set #7: Answer these four problems 1. The Clause Solution, Inc., a residential window and door manufacturer, has the following historical record of earnings per share (EPS) from 2013 to 2017: 2017 2016 $1.05 2015 $1.00 2014 $0.95 2013 $0.90 EPS $1.10 The company's payout ratio has been 60% over the last five years and the last quoted price of the firm's stock was $10. Flotation costs for new equity will be 7%. The company has 30,000,000 common shares outstanding and a debt-equity ratio of 0.50. a) If dividends are expected to grow at the same arithmetic average growth rate of the last five years, what is the dividend payment in 2018? b) Calculate the firm's cost of retained earnings and the cost of new common equity using the constant growth dividend discount model. c) If the Clause Solution's after-tax cost of debt is 9%, what is the WACC with retained earnings? With new common equity? A B D E F G I 1 2 2015 $1.00 2014 $0.95 2013 $0.90 3 4 5 6 The Clause Solution, Inc. 2017 2016 $1.10 $1.05 60% $10.00 7% 30,000,000 0.50 33.33% 66.67% 9.00% EPS Payout Ratio Stock Price Flotation Costs (Equity) Shares Outstanding Debt-Equity Ratio WeightDebt WeightEquity After-tax Cost of Debt 7 8 9 10 11 12 2013 2017 4.76% 5.15% 2016 5.00% 2015 5.26% 2014 5.56% 13 14 EPS Growth Rate 15 Average EPS Growth Rate 16 a) Dividend2018 17 b) Cost of RE 18 Cost of New Equity 19 20 WACC 21 C) With Retained Earnings 22 With New Equity 23 24 25 26