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.