The spreadsheet file "Chapter 5 Problem 3.xlsx" (to find the student spreadsheets for Financial Analysis with Microsoft Excel, seventh edition, go to www.cengagebrain.com) contains monthly total returns for the S&P 500 index (using SPY as a proxy), Cypress Semiconductor (CY), and Fidelity Contrafund (FCNTX) from February 2009 to February 2014.
a. Create a scatter plot to show the relationship between the returns on CY and the S&P 500. Describe, in words, the relationship between the returns of CY 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 R on the chart. Does this equation confirm your guess from part a? How much of the variability in CY 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 CY returns, and the independent variable is the S&P 500 returns. Does this confirm the earlier results? The slope coefficient is CY'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?