Please answer in excel.

Casey Lambert is trying to value the stock of Resources Limited. To easily see how a change in one or more of her assumptions affects the estimated value of the stock, she is using a spreadsheet model. The model has projections for the next four years based on the following assumptions. Sales will be $500 million in Year 1. Sales will grow at 15 percent in Years 2 and 3 and 10 percent in Year 4. Operating profits (EBIT) will be 17 percent of sales in each year. Interest expense will be $10 million per year. Income tax rate is 30 percent. Earnings retention ratio would stay at 0.60. The per-share dividend growth rate will be constant from Year 4 forward, and this final growth rate will be 200 basis points (i.e.,2%) less than the dividend growth rate from Year 3 to Year 4. The company has 10 million shares outstanding. Casey has estimated the required return on Resources' stock to be 13 percent. A. Estimate the value of the stock at the end of Year 4 based on the above assumptions. B. Estimate the current value of the stock using the above assumptions. C. Casey is wondering how a change in the projected sales growth rate would affect the estimated value. Estimate the current value of the stock if the sales growth rate in Year 3 is 10 percent instead of 15 percent. D. Casey is wondering how a change in the projected sales growth rate and required rate of return would affect the estimated value. Estimate the current value of the stock if the sales growth rate in Year 3 is 12 percent instead of 15 percent and the required rate of return is 15 |percent instead of 13 percent. You may use the following template. Year 1 2 3 Casey Lambert is trying to value the stock of Resources Limited. To easily see how a change in one or more of her assumptions affects the estimated value of the stock, she is using a spreadsheet model. The model has projections for the next four years based on the following assumptions. Sales will be $500 million in Year 1. Sales will grow at 15 percent in Years 2 and 3 and 10 percent in Year 4. Operating profits (EBIT) will be 17 percent of sales in each year. Interest expense will be $10 million per year. Income tax rate is 30 percent. Earnings retention ratio would stay at 0.60. The per-share dividend growth rate will be constant from Year 4 forward, and this final growth rate will be 200 basis points (i.e.,2%) less than the dividend growth rate from Year 3 to Year 4. The company has 10 million shares outstanding. Casey has estimated the required return on Resources' stock to be 13 percent. A. Estimate the value of the stock at the end of Year 4 based on the above assumptions. B. Estimate the current value of the stock using the above assumptions. C. Casey is wondering how a change in the projected sales growth rate would affect the estimated value. Estimate the current value of the stock if the sales growth rate in Year 3 is 10 percent instead of 15 percent. D. Casey is wondering how a change in the projected sales growth rate and required rate of return would affect the estimated value. Estimate the current value of the stock if the sales growth rate in Year 3 is 12 percent instead of 15 percent and the required rate of return is 15 |percent instead of 13 percent. You may use the following template. Year 1 2 3