Please some help with these! The exercise is on the Practice 1 file!! Some data is already complete.

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. The Better Biscuit Company (BBC) is considering a new product launch, "Healthy Biscuits." If they decide to go forward, they project that they can earn $0 million in the first year (before taxes), and that number will grow at 0.00% for about 0 years until they reach a plateau. After that, the growth will settle down to a long-term average 0.00%.Their tax rate is 0.00% and their cost of capital is 0.00%. The initial investment will be depreciated in a year straight-line with no expected salvage value, for 0 years. If the initial cost of the project is $0 million, should they go ahead with the investment? initial investment End of year 1 before-tax operating profit near-term growth rate long-term growth rate years before growth settles down, n tax rate cost of capital depreciable life of assets named n (can be between 5 and 15) named t named l, Straight-Line, No Sal Val, always