Please help me solve this!! Some data is already done

Prof. Barrett Fin 405 Cost of Equity 1. The Cost of Equity a. Using Coca-Cola, go to the internet and find their current stock price, most recent ANNUAL dividend, their Beta and the analysts estimate of the next n (=5) year's growth. If you can't find the annual dividend, then you can use the most recent quarterly dividend and multiply by 4. b. Use the CAPM to compute their cost of equity. Call that k CAPM, and keep that separate from the next part. Assume rF = 1% and the market risk premium = 5%. c. Assume a 3% long-term growth rate and a 10% cost of equity capital k DDM, (keep this apart from part b). d. Make your cash flow table using IF statements such that when you change n, the table recomputes properly. e. Compute the hypothetical price of the stock given your assumptions and data. (use k DDM) f. Use Goal Seek to find the kDDM that makes your model price = given price. 2. Data Table Make 2 data tables: Do sensitivity on short-term growth rates and one on long-term growth rates. 3. Graph Data table. Graph each of your data tables. practice good formatting and labelling. Try doing something fancy. Do your work on the next page. qattachments_47bbc1949833bccd3c6e8bc42381ecbb12027886.xlsx 09/09/2016 1. The Cost of Equity a. Using Coca-Cola, go to the internet and find their current stock price, most recent ANNUAL dividend, their Beta and the analysts estimate of the next n (=5) year's growth. If you can't find the annual dividend, then you can use the most recent quarterly dividend and multiply by 4. Coca-Cola Stock Price D0 Annual Dividen b Beta gs 5-year growth es b. $43.64 $1.40 0.590 5.95% Use the CAPM to compute their cost of equity. Call that k(CAPM), and keep that separate from the next part. Assume the risk-free rate = 1.00% and the market risk premium = 5.00%. n rF MRP 5 1.00% 5.00% k(CAPM) = 59.00000% c. Assume a 3.00% long-term growth rate and a 10.00% cost of equity capital, k(DDM), (keep this apart from part b). g k(DDM) 3.00% 10.00% d. Make your cash flow table using IF statements such that when you change n, the table recomputes pro e. Compute the hypothetical price of the stock given your assumptions and data (use Year CF 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 PVCF PerpVal PV PerpVal Price (Calculated) f. Use Goal Seek to find the kDDM that makes your model price = current market price. Afterard, copy your answer to the yellow cell below and reset kDDM to 10%. Goal Seek Set Cell New KDDM = To Value By changing: 2. Make 2 data tables: Do sensitivity on short-term growth rates and one on long-term growth rates. 3. Graph your Data Tables: gs g nge n, the table recomputes properly. d data (use kDDM). Price (Calculated) market price. wth rates