This assignment is to be completed by the same teams that worked on the case study project. You should answer each question completely and provide the Excel files, tables, calculations, plots, etc. that you assembled to answer the questions. Each question needs to write at least one page.
1. Use either finance.yahoo.com or Bloomberg and download five years of monthly closing prices for five stocks and the S&P 500 Index (ticker symbol “GSPC”). Do not use Apple or Monsanto. 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 each stock’s returns against the S&P 500. Now for each scatter plot chart, select one of the data points, and right-click to obtain a shortcut menu allowing you to enter a trend line. This is the company’s characteristic line, and the slope of the line is the company’s beta. You can use the slope function In Excel to compute the slope of the line, which is beta. Compile the betas of each company in a table and provide copies of each company’s characteristic line (scatter plot). Based on your results, how are your companies similar and different? Which companies are aggressive, and which are defensive?
2. Use the monthly returns data for the eight stocks and the S&P 500 Composite Index in Question 1. a. Using the Excel functions for average (AVERAGE) and sample standard deviation (STDEV), calculate the average and the standard deviation of the returns for each of the firms and the S&P 500 Composite Index. Prepare a table showing the results of your calculations
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 coefficients? (Alternative: You may use Excel’s Data Analysis Tool to generate the correlation matrix.)