Assignment: INTERMEDIATE FINANCIAL ANALYSIS
SPREADSHEET ASSIGNMENT: Chapter 3
1. Retrieve the file HWDataChapter3b. The data in the file refer to a single stock (General Electric), a broad measure of the stock market (Standard & Poor's 500 Stock Index), a stock portfolio mutualfund (Fidelity Magellan), and 3-month Treasury bills.All databegin in row 2 and end in row 49.
2. In Excel, select Data | Data Analysis | Regression. Click OK and enter the appropriate cell ranges for GE Return and Market Return using the market model. These are theInputY Range and Input X Range, respectively. Important note: The Analysis Toolpak is required for regression analysis in Excel.
3. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation.
4. In Excel, select Data | Data Analysis | Regression. Click OK and enter the appropriate cell ranges for GE Return and Market Return using the CAPM: Excess returns model (subtracts rRF from both the stock returns and market returns). These are the InputY Range and Input X Range, respectively. (Reminder: The original columns of data are available in Sheet 1).
5. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. (Note: The results for parts 4 and 6 should be similar.)
6. In Excel, select Data | Data Analysis | Regression. Click OK and enter the appropriate cell ranges for Fidelity Magellan Return and Market Return using the market model. These are The InputY Range and Input X Range, respectively.
7. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation.
8. In Excel, select Data | Data Analysis | Regression. Click OK and enter the appropriate cell ranges for Fidelity Magellan Return and Market Return using the CAPM: Excess returnsmodel (subtracts rRFfrom both the mutual fundportfolio returns and Marketreturns). These are the Input Y Range and Input X Range, respectively.
9. Change the column width to 12 for each column of Summary Output. Print using a landscape orientation. (Note: The results for parts 8 and 10 should be similar.)
10. Does the S&P 500 Stock Index explain more of the variability in returns of General Electric or Fidelity Magellan? Enter your answer at the bottom of the final regression analysis printout.
Attachment:- Data-Chapter-3b.rar