The spreadsheet file Chapter 5 Problem 3.xlsx (to find the student spreadsheets for Financial Analysis with Microsoft
Question:
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 Contra fund (FCNTX) from July 2014 to June 2019 (60 months).
a. 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.
b. 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.
c. 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.
d. 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?
Data from Chapter 5 Problem 3.xlsx
| Month | S&P 500 | AAPL | FCNTX |
| Jul-14 | -1.38% | 2.87% | -1.44% |
| Aug-14 | 4.00% | 7.75% | 4.44% |
| Sep-14 | -1.40% | -1.71% | -1.13% |
| Oct-14 | 2.44% | 7.20% | 1.48% |
| Nov-14 | 2.69% | 10.60% | 2.15% |
| Dec-14 | -0.25% | -7.19% | -0.53% |
| Jan-15 | -3.00% | 6.14% | -1.34% |
| Feb-15 | 5.75% | 10.07% | 5.98% |
| Mar-15 | -1.58% | -3.14% | -0.49% |
| Apr-15 | 0.96% | 0.58% | -0.84% |
| May-15 | 1.29% | 4.53% | 2.16% |
| Jun-15 | -1.94% | -3.73% | -0.29% |
| Jul-15 | 2.10% | -3.29% | 3.46% |
| Aug-15 | -6.03% | -6.62% | -5.96% |
| Sep-15 | -2.47% | -2.18% | -2.06% |
| Oct-15 | 8.44% | 8.34% | 7.08% |
| Nov-15 | 0.30% | -0.58% | 0.64% |
| Dec-15 | -1.58% | -11.02% | -1.33% |
| Jan-16 | -4.96% | -7.52% | -5.71% |
| Feb-16 | -0.13% | -0.13% | -1.18% |
| Mar-16 | 6.78% | 12.72% | 5.59% |
| Apr-16 | 0.39% | -13.99% | 0.25% |
| May-16 | 1.80% | 7.18% | 1.67% |
| Jun-16 | 0.26% | -4.27% | -1.51% |
| Jul-16 | 3.69% | 9.01% | 4.48% |
| Aug-16 | 0.14% | 2.36% | 0.27% |
| Sep-16 | 0.02% | 6.55% | 0.43% |
| Oct-16 | -1.82% | 0.43% | -1.67% |
| Nov-16 | 3.70% | -2.15% | 0.60% |
| Dec-16 | 1.98% | 4.80% | 0.54% |
| Jan-17 | 1.90% | 4.77% | 4.37% |
| Feb-17 | 3.97% | 13.37% | 3.85% |
| Mar-17 | 0.12% | 4.87% | 1.56% |
| Apr-17 | 1.03% | -0.01% | 2.82% |
| May-17 | 1.41% | 6.78% | 3.59% |
| Jun-17 | 0.62% | -5.72% | -0.40% |
| Jul-17 | 2.06% | 3.27% | 3.54% |
| Aug-17 | 0.31% | 10.71% | 1.61% |
| Sep-17 | 2.06% | -6.02% | 0.85% |
| Oct-17 | 2.33% | 9.68% | 4.73% |
| Nov-17 | 3.07% | 2.03% | 1.59% |
| Dec-17 | 1.11% | -1.52% | 0.33% |
| Jan-18 | 5.73% | -1.06% | 9.28% |
| Feb-18 | -3.69% | 6.81% | -2.29% |
| Mar-18 | -2.54% | -5.81% | -3.52% |
| Apr-18 | 0.38% | -1.50% | 1.20% |
| May-18 | 2.41% | 13.51% | 4.06% |
| Jun-18 | 0.62% | -0.94% | 0.92% |
| Jul-18 | 3.72% | 2.80% | 1.94% |
| Aug-18 | 3.26% | 20.04% | 4.51% |
| Sep-18 | 0.57% | -0.83% | 0.14% |
| Oct-18 | -6.84% | -3.05% | -9.72% |
| Nov-18 | 2.04% | -18.12% | 0.71% |
| Dec-18 | -9.03% | -11.67% | -7.87% |
| Jan-19 | 8.01% | 5.52% | 9.45% |
| Feb-19 | 3.21% | 4.48% | 2.39% |
| Mar-19 | 1.94% | 9.70% | 2.21% |
| Apr-19 | 4.05% | 5.64% | 4.88% |
| May-19 | -6.35% | -12.42% | -5.72% |
| Jun-19 | 7.05% | 13.05% | 6.63% |
Step by Step Answer:
This question has not been answered yet.
You can Ask your question!
