I Need question 3 answered only
WEB master 1. Go to finance.yahoo.com and download five years of monthly closing prices for Eli Lilly (ticker = LLY), Alcoa (AA), and the S&P 500 Index ("GSPC). Download the data into an Excel file and use the Adjusted-Close prices, which adjust for dividend payments, to calculate the monthly rate of return for each price series. Use an XY scatter plot chart with no line joining the points to plot Alcoa's returns against the S&P 500. Now select one of the data points, and right-click to obtain a shortcut menu allowing you to enter a trend line. This is Alcoa's characteristic line, and the slope is Alcoa's beta. Repeat this process for Lilly. What conclusions can you draw from each company's characteristic line? 2. Following the procedures in the previous question, find five years of monthly returns for Target. Using the first two years of data, what is Target's beta? What is the beta using the latest two years of data? How stable is the beta estimate? If you use all five years of data, how close is your estimate of beta to the estimate reported in Yahoo's Key Statistics section? 3. Following the procedures in the previous questions, find five years of monthly returns for the following firms: Genzyme Corporation, Sony, Cardinal Health, Inc., Black & Decker Corporation, and Kellogg Company. Copy the returns from these five firms into a single Excel workbook, with the returns for each company properly aligned. Use the full range of available data. Then do the following: a. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV.S), calculate the average and the standard deviation of the returns for each of the firms. b. Using Excel's correlation function (CORREL), construct the correlation matrix for the five stocks based on their monthly returns for the entire period. What are the lowest and the highest individual pairs of correlation WEB master 1. Go to finance.yahoo.com and download five years of monthly closing prices for Eli Lilly (ticker = LLY), Alcoa (AA), and the S&P 500 Index ("GSPC). Download the data into an Excel file and use the Adjusted-Close prices, which adjust for dividend payments, to calculate the monthly rate of return for each price series. Use an XY scatter plot chart with no line joining the points to plot Alcoa's returns against the S&P 500. Now select one of the data points, and right-click to obtain a shortcut menu allowing you to enter a trend line. This is Alcoa's characteristic line, and the slope is Alcoa's beta. Repeat this process for Lilly. What conclusions can you draw from each company's characteristic line? 2. Following the procedures in the previous question, find five years of monthly returns for Target. Using the first two years of data, what is Target's beta? What is the beta using the latest two years of data? How stable is the beta estimate? If you use all five years of data, how close is your estimate of beta to the estimate reported in Yahoo's Key Statistics section? 3. Following the procedures in the previous questions, find five years of monthly returns for the following firms: Genzyme Corporation, Sony, Cardinal Health, Inc., Black & Decker Corporation, and Kellogg Company. Copy the returns from these five firms into a single Excel workbook, with the returns for each company properly aligned. Use the full range of available data. Then do the following: a. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV.S), calculate the average and the standard deviation of the returns for each of the firms. b. Using Excel's correlation function (CORREL), construct the correlation matrix for the five stocks based on their monthly returns for the entire period. What are the lowest and the highest individual pairs of correlation
