Choose your own company and repeat Problem 3. The data can be obtained from Yahoo! Finance (https://finance.yahoo.com).
Question:
Choose your own company and repeat Problem 3. The data can be obtained from Yahoo! Finance (https://finance.yahoo.com). Enter a ticker symbol and get a stock price quote. Click the “Historical Prices” link, set the dates for a five-year period and the frequency to monthly. Click the “Download Data” link at the top of the page to load the data into Excel. Now, repeat the steps using the ticker symbol SPY (an exchange traded fund that mimics the S&P 500). Combine the monthly closing prices onto one worksheet and calculate the monthly percentage changes. You now have the data necessary to answer the questions from Problem 3. Note that to improve your results, you can also get the dividends and calculate the monthly total returns.
Data from Problem 3
The spreadsheet file “Chapter 5 Problem 3.xlsx” (to find the student spreadsheets for Financial Analysis with Microsoft Excel, ninth edition, go to (www.cengage.com/finance/mayes/analysis/9e) contains monthly total returns for the S&P 500 index, Apple, Inc. (AAPL), and Fidelity Contrafund (FCNTX) from July 2014 to June 2019 (60 months).
Create a scatter plot to show the relationship between the returns on AAPL and the S&P 500. Describe, in words, the relationship between the returns of AAPL and the S&P 500. Estimate the slope of a regression equation of this data. Repeat for FCNTX.
Add a linear trend line to the chart, and place the equation and R2 on the chart. Does this equation confirm your guess from part a? How much of the variability in AAPL returns can be explained by variability in the broad market? Repeat for FCNTX.
Using the Analysis ToolPak add-in, run a regression analysis on this data. Your dependent variable is the AAPL returns, and the independent variable is the S&P 500 returns. Does this confirm the earlier results? The slope coefficient is AAPL’s beta. Is the beta of this stock statistically significant? Explain.
Repeat part c using the returns on FCNTX and the S&P 500. Compare the R2 from both regressions. What conclusions can you draw from the difference?
Step by Step Answer:
This question has not been answered yet.
You can Ask your question!
