Microsoft Excel File Edit View Insert Format Tools Data Window Help AutoSave Yassin_Brooks_Chapter_7_2_Start Home Insert Draw Page Layout Formulas Data Review View Automate Tell me Times New Roman Conditional Format M46 A B fx Formatting as Table Styles 1 ADVANCED PROBLEM 7-2 FOR SPREADSHEET APPLICATION 3 Changing stock price year by year. The price of a stock can vary from year to year. Using the dividend model, calculate the dividend and stock price each year and then graph the changing price of the stock if the current dividend is $2.80, the dividend growth rate is 4.0%, and the required returns are given in the following table. \begin{tabular}{|c|c|c|c|} \hline Given Data & & & \\ \hline & & & \\ \hline \multicolumn{2}{|c|}{ Most recent paid dividend at year t=0} & $2.80 & \\ \hline \multicolumn{2}{|c|}{ Annual dividend growth rate } & 4.00% & \\ \hline & & & \\ \hline Solution & & & \\ \hline & & & \\ \hline Year (t) & RequiredReturn(duringyeart) & NewDividend(attheendofyeart) & StockPrice(atthebeginningofyeart) \\ \hline 1 & 11.20% & & \\ \hline 2 & 12.15% & & \\ \hline 3 & 10.98% & & \\ \hline 4 & 11.45% & & \\ \hline 5 & 12.06% & & \\ \hline 6 & 12.98% & & \\ \hline 7 & 11.55% & & \\ \hline 8 & 10.83% & & \\ \hline 9 & 10.22% & & \\ \hline 10 & 11.73% & & \\ \hline 11 & 11.98% & & \\ \hline 12 & 11.42% & & \\ \hline 13 & 10.96% & & \\ \hline 14 & 12.15% & & \\ \hline 15 & 11.55% & & \\ \hline \end{tabular} 31 7-2 Ready Accessibility: Investigate