Problem and info format needed for excel is above. Please provide formulas used as well. Thank you!

Julio de la Renta, an analyst at Blackwell Investment group is preparing a dividend forecast for Yokohama Industries for the next five years. Julio uses the following model assumptions: Sales are $100 million in the first year. Sales are expected to grow by 20% in the second year, 15% in year three, and 10% in years 4 and 5. Earnings before interest and taxes (EBIT) are 20% of sales in the first and second years, respectively. EBIT is 18% of sales in the third year, and 16% of sales in Years 4 and 5. Interest charges are 10% of total debt for the current year. The income tax rate is 40%. Yokohama pays out 20% of earnings in dividends in the first and second years, 30% in the third year, 40% in Year 4, and 50% in the fifth year. Retained earnings are added to equity in the following year. Total assets are 80% of the current year's sales in all years. In the first year, debt is 40 million and shareholder's equity is $40 million Debt equals total assets less shareholders' equity. Shareholders' equity will equal prior year's shareholders' equity plus the addition to retained earnings from the prior year. Yokohama has 4 million shares outstanding. The risk-free rate is 2.5%. The shares of Yokohama have an estimated beta of 1.15, and the equity risk premium is estimated at 7%. The value of the company at the end of the fifth year is expected to be 10 times earnings. Your task is to help Julio de la Renta estimate Yokohama's current value per share. Clipboard 5 Font Alignment Number L6 B D E F M G . 3 1 2 4 5 1 2 Problem 4 5 growth rate EBIT/Sales Debt Interest rate Tax rate Payout Ratio Retention Ratio Total Assets Equity Retained Earnings #shares 2 3 4 5 1 100 20 20 20 18 15 16 10 16 10 5 7 8 9 TA/Sales 10 11 12 13 14 15 16 17 Risk-free rate 18 Equity Risk Premium 19 Beta 20 Cost of Equity 21 22 23 24 25 26 27 Terminal Value 10'NI 28 29 30 31 32 3 34 35 Year Sales EBIT Interest EBT Tax Net Income Dividend DPS Terminal Value/sh Cashflow Stock Value Today