Excel Project 2: Create an Excel File for Calculating a Stock's Beta Coefficient Using 3-year Weekly Data. (c) The excel worksheet should be a dynamic one. In other words, the cells for beta coefficient, variance and covariance should contain formulas. When you update with new sets of data, the numbers in these cells should be updated automatically. (d) Go to Yahoo Finance to download the historical weekly data for Microsoft Corporation's common stock price and the S&P 500 index from 5/25/2015 5/21/2018. (e) Copy and paste the "date" and the "Close" columns for Microsoft and the S&P 500 index into the worksheet and calculate the weekly rates of returns for the Microsoft stock price and the S&P 500 index. Make sure that the weekly rate of return is calculated as (this week's price-the prior week's price)/the prior week's price. (f) Estimate Microsoft's beta with two different methods. The first method uses the Excel's SLOPE function. The 2nd method involves the formula 11.14 in the text in that there should be three different cells showing the covariance of returns between Microsoft and the S&P 500 index, the variance of returns for the S&P 500 index, and the beta of Microsoft respectively. (8) All the columns for the weekly data and the output cells should be clearly labeled so that other people can easily follow and understand your work (h) Construct a scatter diagram graph that shows the Microsoft's stock returns on the vertical axis and the S&P 500 index returns on the horizontal axis. (i) Run a regression of Microsoft stock's return (for Y) on the S&P 500 index's return (for X) Demonstrate that the slope coefficient is the same as the beta calculated earlier. Please note that you may need to activate the Data Analysis Tool Pak addin program first. Click "Data", then "Data Analysis", and choose "Regression" Excel Project 2: Create an Excel File for Calculating a Stock's Beta Coefficient Using 3-year Weekly Data. (c) The excel worksheet should be a dynamic one. In other words, the cells for beta coefficient, variance and covariance should contain formulas. When you update with new sets of data, the numbers in these cells should be updated automatically. (d) Go to Yahoo Finance to download the historical weekly data for Microsoft Corporation's common stock price and the S&P 500 index from 5/25/2015 5/21/2018. (e) Copy and paste the "date" and the "Close" columns for Microsoft and the S&P 500 index into the worksheet and calculate the weekly rates of returns for the Microsoft stock price and the S&P 500 index. Make sure that the weekly rate of return is calculated as (this week's price-the prior week's price)/the prior week's price. (f) Estimate Microsoft's beta with two different methods. The first method uses the Excel's SLOPE function. The 2nd method involves the formula 11.14 in the text in that there should be three different cells showing the covariance of returns between Microsoft and the S&P 500 index, the variance of returns for the S&P 500 index, and the beta of Microsoft respectively. (8) All the columns for the weekly data and the output cells should be clearly labeled so that other people can easily follow and understand your work (h) Construct a scatter diagram graph that shows the Microsoft's stock returns on the vertical axis and the S&P 500 index returns on the horizontal axis. (i) Run a regression of Microsoft stock's return (for Y) on the S&P 500 index's return (for X) Demonstrate that the slope coefficient is the same as the beta calculated earlier. Please note that you may need to activate the Data Analysis Tool Pak addin program first. Click "Data", then "Data Analysis", and choose "Regression
