1. Approximating the beta of a stock. Choose a well-known compeny such as Google or Coco-Cola. Go to a website such os Yahoo! Finance (http://finance.yahoo.com/) and find the weekly closing price of the company's stock for the past year. Then find theclosing price of the Standord & Poor's 500 (S&P600) for the same time period.To get the historical prices in Yahoo! Finonce, select Historical Prices from the left menu. Choose the oppropriate time period. Select Weekly and Get Prices. Finally, select Downloadto Spreadsheet. Repeat this for the S&P500, and copy the dato into the same spreadsheet. Finally, rearrange the data in chronological order. Be sure to expend the selection to sort all the dato. Now, using the adjusted close price, compute the percentegechange in price for each week, using the formule % change =P.- P.„ForP.example, if week 1 price is in cell D1 and week 2 price is in cell D2, then % changeD2 - D1Repeat this for the S&P600 data.D12. Using Excel to draw a scatter diagram. Treat the percentage change in the S&P500 as the independent variable and the percentege change in the stock you chose as the dependent variable. The easiest way to drow a scatter diagram in Excel is to placethe two columns of date next to each other (for example, have the percentoge change in the S&P500 in column Fond the percentage change in the stock you chose in column G). Then highlight the doto and select the Scatter Diegram icon under Insert.Comment on the type of relotion that appears to exist between the two variables.3. Finding beta. To find beta requires that we find the line of best fit using least-squares regression. The easiest opproach is to click inside the scatter diagram. Select the Chart Elements icon (+). Check the box for Trendline, select the arrow to the right, andchoose More Options. Select Lineor and check the box for Display Equation on chart. The line of best fit appears on the scatter diagram.See below.0.080.04-0,1F0.050.001y= 0.9046x + 0.0024 R = 0.4887Series 1 - Linear (series 1)The line of best fit for this data is y = 0.9046x + 0.0024. You may click on Chart Title or either axis title and insert the appropriate names. The bete is the slope of the line of best fit, 0.9046. We interpret this by saying. "If the S&P500 increases by 1%, then thisstock will increose by 0.9%, on average." Find the beto of your stock and provide on interpretation. NOTE: Another way to use Excel to find the line of best fit requires using the Dote Analysis Tool Pock under add-ins.

Name: 1. Approximating the beta of a stock. Choose a well-known compeny suc
Uploaded: 2024-03-20T07:06:27.000Z
Channel: Bartleby
Description: 1. Approximating the beta of a stock. Choose a well-known compeny such as Google or Coco-Cola. Go to a website such os Yahoo! Finance (http://finance.yahoo.com/) and find the weekly closing price of the company's stock for the past year. Then find t

Answered: Image /qna-images/answer/36379e14-7320-44ac-a5f1-712c8c0236d8.jpg

Math

Statistics

1. Approximating the beta of a stock. Choose e well-known compeny such es Google or Coco-Cole. Go to a website such os Yahoo! Finance (http:/finance.yahoo.com/) and find the weekly closing price of the company's stock for the past closing price of the Standord & Poor's 500 (S&P500) for the same time period.To get the historical prices in Yahoo! Finance, select Historical Prices from the left menu. Choose the oppropriate time period. Select Weekly and Get Prices. Fin to Spreedsheet. Repect this for the S&P500, end copy the dote into the seme spreadsheet Finelly, rearrange the data in chronological order. Be sure to expend the selection to sort oll the dote. Now, using the adjusted close price, comput change in price for each week, using the formule % change P: - P.. For exemple, if week 1 price is in cell D1 and week 2 price is in cell D2, then % change = D2 - Di Repect this for the S&P500 deto. Di 2. Using Excel to draw a scatter diagram. Treat the percentage change in the S&PS00 as the independent variable and the percentoge change in the stock you chose os the dependent veriable. The easiest way to drow a scatter diagram the two columns of dete next to each other (for example, have the percentage change in the S&P500 in column F and the percentage chenge in the stock you chose in column G). Then highlight the dete and select the Scatter Diegram ico Comment on the type of relation that appears to exist between the two variables. 3. Finding beta. To find bete requires thet we find the line of best fit using least-squares regression. The easiest aopproech is to click inside the scetter diegram. Select the Chart Elements icon (+). Check the box for Trendline, select the ar choose More Options. Select Linear ond check the box for Display Equation on chart. The line of best fit appears on the scatter diagram. See below. 0.06 002 -005 01 y= 0.9046x + 0.0024 R - 0.4887 • Series 1 -Linear (series 1) The line of best fit for this data is y = 0.9046x + 0.0024. You may click on Chart Title or either oxis title and insert the appropriate names. The beta is the slope of the line of best fit, 0.9046. We interpret this by soying. "If the S&P600 increa stock will increcse by 0.9%, on everoge." Find the bete of your stock and provide an interpretetion. NOTE: Another way to use Excel to find the line of best fit requires using the Dete Analysis Tool Pock under add-ins.