Share
Explore BrainMass

Finance: Capital market analysis.

Please utilize the attach Excel file to respond to the following questions.
A . Create an XY (scatter) plot to show the relationship between the returns on AT&T and the S&P 500. Describe, in words, the relationship between the returns of AT&T and the S&P 500. Estimate the slope of a regression equation of this data. Repeat for Contrafund.
B. Add a linear trend line to the chart, and place the equation and R² on the chart. Does this equation confirm your guess from part a? How much of the variability in AT&T returns can be explained by variability in the broad market? Repeat for Contrafund.
C. Using the analysis ToolPak add-in, run a regression analysis on this data. Your dependent variable is the AT&T returns, and the independent variable is the S&P 500 returns. Does this confirm the earlier results? The Slope coefficient is AT&T's beta. Is the beta of this stock statistically significant? Explain
D. Repeat part c using the returns on Contrafund and the S&P 500. Compare the R² from both regressions. What conclusions can you draw from the difference?

Date S&P 500 AT&T Fidelity Contrafund
Sep-03 2.43% 6.63% 6.51%
Oct-03 1.09% -2.92% 1.83%
Nov-03 4.54% 11.98% 2.66%
Dec-03 1.98% -2.19% -0.05%
Jan-04 1.36% -5.84% 3.21%
Feb-04 -1.67% 2.21% 0.57%
Mar-04 -1.89% 1.47% -1.45%
Apr-04 1.71% -4.82% 1.64%
May-04 1.48% 2.32% 2.19%
Jun-04 -3.22% 4.49% -4.49%
Jul-04 0.24% 1.78% -0.17%
Aug-04 0.59% 0.62% 4.22%
Sep-04 1.29% -2.66% 0.87%
Oct-04 4.14% -0.36% 5.31%
Nov-04 2.53% 2.38% 2.66%
Dec-04 -2.24% -7.80% -1.35%
Jan-05 2.09% 1.26% 3.02%
Feb-05 -2.21% -1.54% -1.29%
Mar-05 -1.87% 0.46% -3.23%
Apr-05 3.22% -1.76% 4.60%
May-05 -0.25% 1.58% 1.73%
Jun-05 3.83% 2.95% 4.42%
Jul-05 -0.94% -1.51% 0.86%
Aug-05 0.38% -0.46% 2.92%
Sep-05 -2.37% -0.50% -1.61%
Oct-05 4.40% 4.44% 4.09%
Nov-05 -0.72% -1.69% 1.35%
Dec-05 2.40% 5.96% 5.59%
Jan-06 0.57% 6.32% -2.80%
Feb-06 1.25% -1.99% 2.05%
Mar-06 1.26% -3.07% 2.47%
Apr-06 -3.01% -0.57% -3.90%
May-06 -0.18% 7.02% 0.77%
Jun-06 0.45% 7.53% -1.39%
Jul-06 2.18% 3.80% 1.12%
Aug-06 2.25% 4.59% 1.09%
Sep-06 3.15% 5.19% 3.61%
Oct-06 1.99% -0.99% 2.65%
Nov-06 0.78% 5.43% 0.03%
Dec-06 1.50% 5.26% 2.17%
Jan-07 -1.96% -2.21% -2.09%
Feb-07 0.76% 7.15% 1.39%
Mar-07 4.43% -1.80% 3.49%
Apr-07 3.39% 6.77% 4.10%
May-07 -1.88% 0.39% -0.21%
Jun-07 -3.13% -5.64% -0.85%
Jul-07 1.28% 1.81% 1.84%
Aug-07 3.38% 6.12% 6.12%
Sep-07 1.36% -1.23% 5.02%
Oct-07 -3.87% -8.57% -2.73%
Nov-07 -1.65% 8.77% 0.40%
Dec-07 -6.05% -7.39% -9.51%
Jan-08 -2.58% -9.51% -0.32%
Feb-08 -1.38% 9.96% -1.56%
Mar-08 4.77% 1.07% 5.31%
Apr-08 1.51% 3.07% 3.30%
May-08 -8.81% -15.56% -4.91%
Jun-08 -0.90% -8.55% -4.14%
Jul-08 1.55% 3.83% -0.77%
Aug-08 -9.94% -12.72% -9.62%

Attachments

Solution Summary

The problem deals with creating a scatter plot, R-squared of returns for two companies.

$2.19