with the firm you've been following during the course. You will estimate beta using two different length windows The purpose of this assignment is to practice beta estimation for your company's stock. You will continue to work of returns, 60 months and 36 months. These two window lengths correspond to what Yahoo Finance and MSN Assignment 14 Estimating Beta Money use Group Number Class meeting Morning / Afternoon Coca-Cola Company Name Company Ticker ke- Financial Website Betas Equity Beta (Yahoo est) Equity Beta (MSN's estimate) Now, using the instructions on the next page, estimate beta over 60 months and separately do it over 36 months. 60 months Beta = (using regression technique in 10b) 36 months Beta = (using regression technique in 10b) Optional (using covariance technique in 10) 60 months Beta = (using covariance technique in 10) 36 months Beta = you describe why your answers are the same using the regression vs. covariance What do you think would happen if you used the "sample-covariance" technique? Optional 2 (hard) Can technique? be Estimating Beta To estimate the beta on your firm's stock: 1. On the Web go to Yahoo!Finance 2. Type in your stock's ticker symbol in the search box. 3. Select "Historical Data". 4. Select Historical Prices 5. Change the dates so that you have the latest 61 months of data. Click on Frequency: Monthly. Then click on Time Period (the drop-down arrow) and click the 5Y button. Finally, change the "day" (within the start date) to 1, and then click Done. Click the blue Apply button. 6. Beneath the blue Apply button, click the link to download data, and save the file to your computer. Give it a new name that is easily remembered, so you can locate it in a moment. 7. Repeat steps 2-6 for the ticker symbol GSPC to get the data for the S&P500 (i.e., market return). Make sure to include the "A" 8. Bring up both files in excel and combine into a single spreadsheet. 9. The "Adjusted Close" heading from your stock is the closing price for the stock adjusted for dividends and splits so you can calculate monthly returns as PA Close - PA4 Clone Novo De 98 Repeat this for the S&P500 index to get the returns on the S&P500. Note: the index does not include dividends for the S&P500. It would be better to get a series of S&P500 data that adjusts for dividends but I am unaware of one that is easily and freely available. 10. Now in excel calculate the covariance between the company's stock return and the S&P500 (market) return using the function COVARIANCE.P. This will create the numerator of the beta formula shown below. Use the STDEV.P function to get the standard deviation of the S&P500 (market) return. Remember, you must square this standard deviation to get the variance, which is what you plug into the denominator of the beta formula shown below. Finally, BE 10b. Alternatively, you may run a regression in excel. First, you need to add the data analysis packet into excel. Go to file, options, add-ins. Then, highlight the analysis tool pack and then click the go button. When you click the go button, an add-ins window pops up. Check the box next to analysis tool pack. Click ok button. You then go back to the data tab within excel. The far-right box should say data analysis in it. Click it. Now you have a bunch of analysis tool choices available, and you should choose the regression tool. Select the individual stock's returns as the y-range. Select the market's returns as the x-range. Make sure the output is directed to someplace "away from" the raw data. Click ok. The beta is the coefficient on the x variable. 95 with the firm you've been following during the course. You will estimate beta using two different length windows The purpose of this assignment is to practice beta estimation for your company's stock. You will continue to work of returns, 60 months and 36 months. These two window lengths correspond to what Yahoo Finance and MSN Assignment 14 Estimating Beta Money use Group Number Class meeting Morning / Afternoon Coca-Cola Company Name Company Ticker ke- Financial Website Betas Equity Beta (Yahoo est) Equity Beta (MSN's estimate) Now, using the instructions on the next page, estimate beta over 60 months and separately do it over 36 months. 60 months Beta = (using regression technique in 10b) 36 months Beta = (using regression technique in 10b) Optional (using covariance technique in 10) 60 months Beta = (using covariance technique in 10) 36 months Beta = you describe why your answers are the same using the regression vs. covariance What do you think would happen if you used the "sample-covariance" technique? Optional 2 (hard) Can technique? be Estimating Beta To estimate the beta on your firm's stock: 1. On the Web go to Yahoo!Finance 2. Type in your stock's ticker symbol in the search box. 3. Select "Historical Data". 4. Select Historical Prices 5. Change the dates so that you have the latest 61 months of data. Click on Frequency: Monthly. Then click on Time Period (the drop-down arrow) and click the 5Y button. Finally, change the "day" (within the start date) to 1, and then click Done. Click the blue Apply button. 6. Beneath the blue Apply button, click the link to download data, and save the file to your computer. Give it a new name that is easily remembered, so you can locate it in a moment. 7. Repeat steps 2-6 for the ticker symbol GSPC to get the data for the S&P500 (i.e., market return). Make sure to include the "A" 8. Bring up both files in excel and combine into a single spreadsheet. 9. The "Adjusted Close" heading from your stock is the closing price for the stock adjusted for dividends and splits so you can calculate monthly returns as PA Close - PA4 Clone Novo De 98 Repeat this for the S&P500 index to get the returns on the S&P500. Note: the index does not include dividends for the S&P500. It would be better to get a series of S&P500 data that adjusts for dividends but I am unaware of one that is easily and freely available. 10. Now in excel calculate the covariance between the company's stock return and the S&P500 (market) return using the function COVARIANCE.P. This will create the numerator of the beta formula shown below. Use the STDEV.P function to get the standard deviation of the S&P500 (market) return. Remember, you must square this standard deviation to get the variance, which is what you plug into the denominator of the beta formula shown below. Finally, BE 10b. Alternatively, you may run a regression in excel. First, you need to add the data analysis packet into excel. Go to file, options, add-ins. Then, highlight the analysis tool pack and then click the go button. When you click the go button, an add-ins window pops up. Check the box next to analysis tool pack. Click ok button. You then go back to the data tab within excel. The far-right box should say data analysis in it. Click it. Now you have a bunch of analysis tool choices available, and you should choose the regression tool. Select the individual stock's returns as the y-range. Select the market's returns as the x-range. Make sure the output is directed to someplace "away from" the raw data. Click ok. The beta is the coefficient on the x variable. 95