Historical dividends for four companies are given in the following table: Dividend Year Company 1 Company 2 Company 3 Company 4 2013 $0.70 $0.70 $1.20 $1.00 2014 $0.75 $0.77 $1.14 $1.20 2015 $0.80 $0.85 $1.08 $0.90 2016 $0.85 $0.93 $1.03 $1.10 2017 $0.90 $1.02 $0.98 $1.00 Required Return 10% 16% 12% 14% Current Price $27 $19 $7.00 $8 1. Calculate the compound average annual growth rate of the dividend for each company. If you expect the dividend to grow at that rate in the future, what is the price at which you would be willing to buy these companies' stocks? 2. How do the calculated intrinsic values (based on the compound average annual growth rate) compare to the current stock prices? Use an IF statement to display whether the stock is undervalued, overvalued, or fairly valued. 3. Calculate the arithmetic average annual growth rate of the dividend for each company. If you expect the dividend to grow at that rate in the future, what is the price at which you would be willing to buy these companies' stocks? 4. Use the TREND function to forecast the dividend growth rate of each company in 2018 (i.e., use annual growth rate of dividend as y-axis input for TREND function). If you expect the dividend to grow at that rate in the future, what is the price at which you would be willing to buy these companies' stocks?