In this problem, you will calculate the annual dividend and stock price and graph the changing stock prices.

Steps to Perform: Step Instructions Points Possible 1 Start Excel. Download and open the workbook named: Brooks_Chapter_7_2_Start. 0 2 In cell E14, by using cell references, calculate the dividend for year 1. Use the number of years in cell C14 and absolute references to the relevant cells from the Given Data section. 1 3 In cell F14, by using cell references, calculate the stock price for year 1. Use the new dividend in cell E14 and the relevant cells from the table and the Given Data section. 1 4 In cell range E15:F28, by using cell references, calculate the dividends and stock prices for years 2 through 15. Copy the contents from cell range E14:F14 down the columns to row 28. 1 5 In cells C31:F46, insert a Line with Markers Chart for the Stock Price versus Year data. Inserting Chart Select the Line with Markers Chart from the provided chart options in the Charts group of the Insert tab of the Ribbon. Selecting Data Series Click the chart and then choose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add new series using the Add button. Select cells F14:F28 as the data range. Note that the Stock Prices should stand for the Y values and Years for the X values. Leave the series name as Series1. Edit Chart Elements Go to the Add Chart Elements dropdown list in the Design tab of the Ribbon. Delete the legend. Go to Axis Titles. Add Year as the title for the horizontal axis. Next add Stock Price as the title for the vertical axis. Next click anywhere in the Chart. Go to the Current Selection group of the Format tab of the Ribbon. Select Chart Area in the dropdown list in the upper-left part of the Current Selection Group and then click Format Selection below. In the Format Chart Area dialog box, click Fill & Line option, then click Border group sign and select Solid Line option. Then go to the Color option and select Black, Text 1. Go to top of the dialog box and select Plot Area from the dropdown menu of the Chart Options. Click Fill & Line option, then click Border group sign and select Solid Line. Next change the color option to White, Background 1, Darker 15%. Chart Size and Position Go to the Format tab on the Ribbon. Set the chart height to 4 inches and the chart width to 7 inches. Drag the chart to position the entire chart so that it fits within cells C31:F46.

I need to know the formulas to put in excel not just the completed excel sheet.

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