Question 5 A company's beta can be estimated from the slope of the security characteristic line (SCL). The first step is to plot the return on the firm's stock (y-axis) versus the return on a broad market index (x-axis). Next, a regression line is estimated to find the slope. Required: (a) Go to finance.yahoo.com, enter the ticker symbol for Coca Cola and click on historical data. Choose monthly frequency and enter starting and ending dates which correspond to the most recent 5 years. Download the data to a spreadsheet. (b) Repeat the process to obtain comparable data for the S&P500 Index (ticker ^GSPC). Download the data and copy it into the same spreadsheet as Coke with dates aligned. (c) I Determine the excess return on the equity and the return on the index for each month using the adjusted closing prices, which include dividend income.(You can find monthly government bond rates at the St. Louis Fed's website fred.stlouisfed.org. Search for T-bill rates.) (d) Prepare an xy scatter plot with no line inserted. Be sure that the firm's excess returns represent the y-variable and the market's excess returns represent the x- variable. Select one of the data points by pointing to it and clicking the left mouse button. After the point is selected, right-click to pull up a shortcut menu. Select Add Trendline, choose the linear type, then click on the Options tab and select Display Equation on Chart. When you click OK, the trendline and the Question 5 A company's beta can be estimated from the slope of the security characteristic line (SCL). The first step is to plot the return on the firm's stock (y-axis) versus the return on a broad market index (x-axis). Next, a regression line is estimated to find the slope. Required: (a) Go to finance.yahoo.com, enter the ticker symbol for Coca Cola and click on historical data. Choose monthly frequency and enter starting and ending dates which correspond to the most recent 5 years. Download the data to a spreadsheet. (b) Repeat the process to obtain comparable data for the S&P500 Index (ticker ^GSPC). Download the data and copy it into the same spreadsheet as Coke with dates aligned. (c) I Determine the excess return on the equity and the return on the index for each month using the adjusted closing prices, which include dividend income.(You can find monthly government bond rates at the St. Louis Fed's website fred.stlouisfed.org. Search for T-bill rates.) (d) Prepare an xy scatter plot with no line inserted. Be sure that the firm's excess returns represent the y-variable and the market's excess returns represent the x- variable. Select one of the data points by pointing to it and clicking the left mouse button. After the point is selected, right-click to pull up a shortcut menu. Select Add Trendline, choose the linear type, then click on the Options tab and select Display Equation on Chart. When you click OK, the trendline and the
