1. Calculate Coca Cola's intrinsic stock price using the constant dividend growth model, assuming that the growth rate calculated in Step 2.c for Coca Cola is the constant growth rate going forward 2. Calculate Pepsi's intrinsic stock price using the multiple-growth dividend rate model, assuming that the that the growth rate calculated in Step 2.c for Pepsi is the growth rate for the next five years and that the growth rate for year 6 and going forward is 6.10% \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline 4 & A & B & c & \multicolumn{2}{|r|}{ D } & E & \multicolumn{2}{|c|}{F} \\ \hline \multicolumn{3}{|l|}{1} & & & & & & \\ \hline 2 & Year & Risk Free Rate & & & & & & \\ \hline 3 & 2010 & 3.22% & & & & & & \\ \hline 4 & 2011 & 2.78% & & & & & & \\ \hline 5 & 2012 & 1.80% & & & & & & \\ \hline 6 & 2013 & 2.35% & & & & & & \\ \hline 7 & 2014 & 2.54% & & & & & & \\ \hline 8 & 2015 & 2.14% & & & & & & \\ \hline 9 & 2016 & 1.84% & & & & & & \\ \hline 10 & 2017 & 2.33% & & & & & & \\ \hline 11 & 2018 & 2.91% & & & & & & \\ \hline 12 & 2019 & 2.14% & & & & & & \\ \hline 13 & & & & & & & & \\ \hline 14 & \multirow{2}{*}{ Date } & \multirow{2}{*}{\begin{tabular}{c} Market (S\&P 500) \\ Close \end{tabular}} & \multicolumn{3}{|c|}{ Coca Cola } & \multicolumn{3}{|c|}{ Pepsi } \\ \hline 15 & & & Adj Close & \multicolumn{2}{|c|}{ Dividend } & Adj Close & \multicolumn{2}{|c|}{ Dividend } \\ \hline 16 & 12/31/09 & 1,115.10 & 18.95 & $ & 1.64 & $443.96 & $ & 1.78 \\ \hline 17 & 12/31/10 & 1,257.64 & 23.27 & $ & 1.76 & & $ & 2.37 \\ \hline 18 & 12/30/11 & 1,257.60 & 26.21 & $ & 1.88 & $51.36 & $ & 2.03 \\ \hline 19 & 12/31/12 & 1,426.19 & 28.33 & $ & 1.53 & $54.64 & $ & 2.13 \\ \hline 20 & 12/31/13 & 1,848.36 & 33.21 & $ & 1.12 & $68.11 & $ & 2.24 \\ \hline 21 & 12/31/14 & 2,058.90 & 34.96 & $ & 1.22 & $79.87 & $ & 2.53 \\ \hline 22 & 12/31/15 & 2,043.94 & 36.75 & $ & 1.32 & $86.86 & $ & 2.76 \\ \hline 23 & 12/30/16 & 2,238.83 & 36.62 & $ & 1.40 & $93.63 & $ & 2.96 \\ \hline 24 & 12/29/17 & 2,673.61 & 41.89 & $ & 1.48 & $110.31 & $ & 3.17 \\ \hline 25 & 12/31/18 & 2,506.85 & 44.73 & $ & 1.56 & $105.00 & $ & 3.59 \\ \hline 26 & 12/30/19 & 3,221.29 & 53.86 & $ & 1.60 & $133.88 & $ & 3.79 \\ \hline \end{tabular} Step 1: 1. Calculate the Total Return for each year 2010 - 2019 for the Market, Coca Cola and Pepsi 2. Calculate the Dividend Yield for each year 2010 - 2019 for Coca Cola and Pepsi 3. Calculate the Dividend Growth for each year 2010 - 2019 for Coca Cola and Pepsi 4. Calculate the Return Relative for each year 2010 - 2019 for the Market, Coca Cola and Pepsi Step 2: 1. Calculate the Expected (Average) Return from 2010 - 2019 for the Risk Free Rate, Market 2 Coca Cola, and Pepsi 2. Calculate the Average Dividend Yield from 2010 - 2019 for Coca Cola and Pepsi 3. Calculate the Average Dividend Growth from 2010 - 2019 for Coca Cola and Pepsi 4. Calculate Geometric Mean Return from 2010 - 2019 for the Market, Coca Cola and Pepsi 5. Calculate Cumulative Wealth Index from 2010 - 2019 for the Market, Coca Cola and Pepsi 6. Calculate the value of $5,000 invested in 2010 at the end of 2019 for the Market, Coca Cola and Pepsi 7. Calculate Standard Deviation from 2010 - 2019 for the Market, Coca Cola and Pepsi (please note for the standard deviation, this is a sample of the data) 8. Calculate Beta from 2010 - 2019 for the Market, Coca Cola and Pepsi 9. Calculate CAPM Required Rate of Return for the Market, Coca Cola and Pepsi 10. Calculate the Coefficient of Determination (R2) for the Market, Coca Cola and Pepsi
