EXCEL FINANCE HOMEWORK DUE TOMORROW

  

FIN3310: INTERMEDIATE FINANCIAL ANALYSIS

SPREADSHEET ASSIGNMENT: Chapter 3

1. Read sections 3.5a through 3.5d in the textbook.

2. 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 mutual fund (Fidelity Magellan), and 3-month Treasury

 bills. All data begin in row 2 and end in row 49.

3. 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 the Input Y Range and 

Input X Range, respectively. Important note: The Analysis

Toolpak is required for regression analysis in Excel.

4. Change the column width to 12 for each column of Summary 

Output. Print using a landscape orientation.

5. 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 Input Y Range and Input X Range, respectively. (Reminder: 

The original columns of data are available in Sheet 1).

6. 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.)

7. 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 Input Y Range and Input X Range, respectively. 

8. Change the column width to 12 for each column of Summary 

Output. Print using a landscape orientation.

9. 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 returns model 

(subtracts rRF from both the mutual fund portfolio returns and

 Market returns). These are the Input Y Range and Input X

 Range, respectively. 

10. 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.)

11. 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.