Question: 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
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 & Poors 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.
_________________________________________________
| rM, Market Return (S&P 500 Index) | ri, GE Return | rp, Fidelity Magellan Fund Return | rRF, Risk-Free Rate (Monthly Return on 3-Month T-Bill) | Excess market return (rM-rRF) | Excess stock return (ri-rRF) | Excess portfolio return (rp-rRF) |
| 4.95% | 5.09% | 6.62% | 0.04% | 4.91% | 5.05% | 6.58% |
| -1.50% | 0.22% | -1.68% | 0.05% | -1.55% | 0.17% | -1.73% |
| 2.08% | 4.66% | 3.58% | 0.04% | 2.04% | 4.62% | 3.54% |
| 1.81% | -3.62% | 1.01% | 0.06% | 1.75% | -3.68% | 0.95% |
| 3.60% | -0.43% | 3.12% | 0.09% | 3.51% | -0.52% | 3.03% |
| 1.11% | 5.11% | 0.69% | 0.10% | 1.01% | 5.01% | 0.59% |
| 5.04% | 6.15% | 5.07% | 0.07% | 4.97% | 6.08% | 5.00% |
| 0.71% | 0.24% | 0.79% | 0.07% | 0.64% | 0.17% | 0.72% |
| 0.28% | 0.29% | 0.95% | 0.09% | 0.19% | 0.20% | 0.86% |
| -1.98% | -7.27% | -2.57% | 0.10% | -2.08% | -7.37% | -2.67% |
| 2.42% | 10.53% | 3.21% | 0.11% | 2.31% | 10.42% | 3.10% |
| 1.98% | -0.20% | 3.33% | 0.10% | 1.88% | -0.30% | 3.23% |
| 1.26% | -0.45% | 1.17% | 0.10% | 1.16% | -0.55% | 1.07% |
| 3.96% | 10.10% | 3.37% | 0.09% | 3.87% | 10.01% | 3.28% |
| -6.27% | -2.50% | -7.18% | 0.09% | -6.36% | -2.59% | -7.27% |
| -0.75% | -2.44% | -1.20% | 0.08% | -0.83% | -2.52% | -1.28% |
| 3.13% | 5.36% | 3.59% | 0.08% | 3.05% | 5.28% | 3.51% |
| 4.06% | 2.70% | 5.97% | 0.09% | 3.97% | 2.61% | 5.88% |
| 4.36% | 4.52% | 6.00% | 0.03% | 4.33% | 4.49% | 5.97% |
| 0.85% | 13.61% | -0.26% | 0.01% | 0.84% | 13.60% | -0.27% |
| -0.51% | -4.76% | -2.27% | 0.01% | -0.52% | -4.77% | -2.28% |
| 10.77% | 9.76% | 10.86% | 0.02% | 10.75% | 9.74% | 10.84% |
| -7.18% | -5.78% | -11.66% | 0.01% | -7.19% | -5.79% | -11.67% |
| -5.68% | -8.92% | -7.70% | 0.02% | -5.70% | -8.94% | -7.72% |
| -2.15% | -5.06% | -1.45% | 0.04% | -2.19% | -5.10% | -1.49% |
| -1.83% | -3.19% | -3.62% | 0.04% | -1.87% | -3.23% | -3.66% |
| -1.35% | -3.96% | -2.22% | 0.04% | -1.39% | -4.00% | -2.26% |
| 2.85% | 1.99% | 2.52% | 0.06% | 2.79% | 1.93% | 2.46% |
| -0.10% | -4.13% | -0.41% | 0.10% | -0.20% | -4.23% | -0.51% |
| 3.20% | 4.59% | 4.17% | 0.13% | 3.07% | 4.46% | 4.04% |
| 2.26% | 10.11% | 1.62% | 0.15% | 2.11% | 9.96% | 1.47% |
| 6.53% | 16.41% | 6.65% | 0.14% | 6.39% | 16.27% | 6.51% |
| -0.23% | -1.16% | 1.38% | 0.14% | -0.37% | -1.30% | 1.24% |
| 3.69% | -1.42% | 3.61% | 0.13% | 3.56% | -1.55% | 3.48% |
| 8.76% | 13.04% | 10.84% | 0.15% | 8.61% | 12.89% | 10.69% |
| -4.74% | -10.14% | -5.92% | 0.16% | -4.90% | -10.30% | -6.08% |
| 6.88% | 11.80% | 5.06% | 0.16% | 6.72% | 11.64% | 4.90% |
| -5.39% | -11.28% | -6.47% | 0.12% | -5.51% | -11.40% | -6.59% |
| -8.20% | -13.27% | -8.11% | 0.16% | -8.36% | -13.43% | -8.27% |
| 1.48% | 3.60% | 1.50% | 0.16% | 1.32% | 3.44% | 1.34% |
| 5.88% | 13.29% | 6.63% | 0.15% | 5.73% | 13.14% | 6.48% |
| 2.85% | 0.56% | 3.01% | 0.11% | 2.74% | 0.45% | 2.90% |
| -3.70% | 6.29% | -4.38% | 0.06% | -3.76% | 6.23% | -4.44% |
| 1.78% | -4.99% | 4.35% | 0.05% | 1.73% | -5.04% | 4.30% |
| 5.74% | 12.40% | 5.45% | 0.05% | 5.69% | 12.35% | 5.40% |
| -1.98% | -13.17% | -5.43% | 0.07% | -2.05% | -13.24% | -5.50% |
| 3.57% | 18.83% | 5.31% | 0.12% | 3.45% | 18.71% | 5.19% |
| 3.36% | 3.72% | 2.16% | 0.17% | 3.19% | 3.55% | 1.99% |
| ( ) | ( ) | ( ) | ( ) | ( ) | ( ) | ( ) |
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help