188 CHAPTER 3 Linear and Quadratic Functions 3. Finding beta. To find beta requires that we find the line of best fit using least-squares regression. The easiest approach is to click inside the scatter plot. Select the Chart Elements icon . ( )+ Check the box for Trendline, select the arrow to the right, and choose More Options. Select Linear and check the box for Display Equation on chart. The line of best fit appears on the scatter plot. See below. Series 1 Linear (series 1) x 20.05 20.1 0.05 0.1 O y 0.04 0.08 0.02 20.02 20.04 20.06 0.06 y 5 0.9046x 1 0.0024 R2 5 0.4887 The line of best fit for this data is y x 0.9046 0.0024. = + You may click on Chart Title or either axis title and insert the appropriate names. The beta is the slope of the line of best fit, 0.9046. We interpret this by saying, “If the S&P500 increases by 1%, then this stock will increase by 0.9%, on average.” Find the beta of your stock and provide an interpretation. NOTE: Another way to use Excel to find the line of best fit requires using the Data Analysis Tool Pack under add-ins. Chapter Project The Beta of a Stock You want to invest in the stock market but are not sure which stock to purchase. Information is the key to making an informed investment decision. One piece of information that many stock analysts use is the beta of the stock. Go to Wikipedia (http://en.wikipedia.org/wiki/Beta_(finance)) and research what beta measures and what it represents. 1. Approximating the beta of a stock. Choose a well-known company such as Google or Coca-Cola. Go to a website such as Yahoo! Finance (http://finance.yahoo.com/) and find the weekly closing price of the company’s stock for the past year. Then find the closing price of the Standard & Poor’s 500 (S&P500) for the same time period. To get the historical prices in Yahoo! Finance, select Historical Data from the menu. Choose the appropriate time period. Select Weekly and Apply. Finally, select Download Data, and Open with Microsoft Excel. Repeat this for the S&P500, and copy the data into the same spreadsheet. Finally, rearrange the data in chronological order. Be sure to expand the selection to sort all the data. Now, using the adjusted close price, compute the percentage change in price for each week, using the formula P P P % change . 1 0 0 = − For example, if week 1 price is in cell D1 and week 2 price is in cell D2, then % D D D change 2 1 1 . = − Repeat this for the S&P500 data. 2. Using Excel to draw a scatter plot. Treat the percentage change in the S&P500 as the independent variable and the percentage change in the stock you chose as the dependent variable. The easiest way to draw a scatter plot in Excel is to place the two columns of data next to each other (for example, have the percentage change in the S&P500 in column F and the percentage change in the stock you chose in column G). Then highlight the data and select the Scatter Plot icon under Insert. Comment on the type of relation that appears to exist between the two variables. Internet-based Project Credit: Gorodenkoff/Shutterstock
RkJQdWJsaXNoZXIy NjM5ODQ=