Firm: Coca-Cola Divi =$1.76 Enter eipected dividend lor nest year here. tatimating dividend crewth rate: Wisorical divdend: 1. Download attached Excel template. Note that there are cells shaded grey indicating you need to enter data to calculate estimated intrinsic values for a stock chosen from the following three choices: Johnson \& Johson (JNJ). Procter \& Gamble (PG), or 3 M Company (MMM). The file template currently contains data for Coca-Cola (KO). 2. Enter the chosen company name in cell D1. Enter in cell D2 the value for the expected dividend to be paid over the next year for the chosen company. You can look up this value in yahoo finance or some other website. While in the yahoo finance page for the chosen company look up the value of beta for the stock and enter it in cell F26. 3. Enter the historic dividend paid by the chosen company in cells E17, F17, and F17 for the years indicated in the cells above. You may look up this information on Morningstarcom under "Key ratios" and then "Full ratios data." 4. Based on the dividend data entered, Excel will automatically calculate the dividend growth over the most recent 5 and 9 years. it will then calculate the average of the two in cell G21. That number will automatically feed cell F5 with a blue background. 5. The value entered for beta in cell F26 will drive the calculation for the discount rate using the CAPM model. The output is in cell F28 in blue. This result wil automatically feed cell C8 in blue. The DDM sensitivity analysis table will automatically calculate intrinsic values for the stock based on various dividend growth and discount rate combinations. 6. Ignore the results that are negative, MDIV/OL, or \#IN 1. Recall that when the discount rate is equal to or lower than the dividend growth rate, the DDM model is not meaningful. 7. Compare the current market price of the chosen stock to the values in the sensitivity analysis table. Does the chosen stock appear undervalued, fairly valued, or overvalued to you? Write a one or two sentence response explaining your reason in the greyed out space on the worksheet. 8. Rename the Excel file as: HW-Week6 yourinitiol_For example. HW-Week6_J_Smith. 9. Submit the completed Excel flie to the homework link on Canvasi Firm: Coca-Cola Divi =$1.76 Enter eipected dividend lor nest year here. tatimating dividend crewth rate: Wisorical divdend: 1. Download attached Excel template. Note that there are cells shaded grey indicating you need to enter data to calculate estimated intrinsic values for a stock chosen from the following three choices: Johnson \& Johson (JNJ). Procter \& Gamble (PG), or 3 M Company (MMM). The file template currently contains data for Coca-Cola (KO). 2. Enter the chosen company name in cell D1. Enter in cell D2 the value for the expected dividend to be paid over the next year for the chosen company. You can look up this value in yahoo finance or some other website. While in the yahoo finance page for the chosen company look up the value of beta for the stock and enter it in cell F26. 3. Enter the historic dividend paid by the chosen company in cells E17, F17, and F17 for the years indicated in the cells above. You may look up this information on Morningstarcom under "Key ratios" and then "Full ratios data." 4. Based on the dividend data entered, Excel will automatically calculate the dividend growth over the most recent 5 and 9 years. it will then calculate the average of the two in cell G21. That number will automatically feed cell F5 with a blue background. 5. The value entered for beta in cell F26 will drive the calculation for the discount rate using the CAPM model. The output is in cell F28 in blue. This result wil automatically feed cell C8 in blue. The DDM sensitivity analysis table will automatically calculate intrinsic values for the stock based on various dividend growth and discount rate combinations. 6. Ignore the results that are negative, MDIV/OL, or \#IN 1. Recall that when the discount rate is equal to or lower than the dividend growth rate, the DDM model is not meaningful. 7. Compare the current market price of the chosen stock to the values in the sensitivity analysis table. Does the chosen stock appear undervalued, fairly valued, or overvalued to you? Write a one or two sentence response explaining your reason in the greyed out space on the worksheet. 8. Rename the Excel file as: HW-Week6 yourinitiol_For example. HW-Week6_J_Smith. 9. Submit the completed Excel flie to the homework link on Canvasi