Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribed
image text in transcribed
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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Fundamentals Of Financial Management

Authors: Eugene F. Brigham, Joel F. Houston

16th Edition

0357517571, 978-0357517574

More Books

Students also viewed these Finance questions

Question

1. Describe the Good Lives Model of offender rehabilitation

Answered: 1 week ago