Question

Investors in mutual funds keep a sharp eye on the total return on their money. They also are aware of the risk involved in their investment, commonly measured by a fund’s volatility (the greater the volatility, the higher the risk). Below is a list of 30 mutual funds randomly selected in 1998 from Fortune’s list of stock and bond funds, together with their 5-year total return (%) and risk assessment: Fund Name                Total Return       Risk                              MFS Emerging Growth                       21.5                        20.6                                        Kaufmann                                             19.7                        18.4                                        AIM Constellation A                            17.6                        18.4                                        Weitz Hickory                                       29.9                        19.7                                        Oak Value                                             25.6                        13.0                                        Gabelli Westwood Equity                   23.0                        12.3                                        Nationwide                                           24.3                        12.0                                        Fidelity Growth/Income                      22.6                        13.0                                        Stratton Growth                                    21.3                        11.8                                        GAM International A                            22.6                        19.9                                        Scudder International                         14.3                        13.7                                        . Janus Worldwide                                23.6                        13.7                                        Oppenheimer Global A                      19.0                        14.4                                        New Perspective                                 18.8                        12.1                                        Putnam Europe Growth A                 22.7                        14.6                                        AIM Balanced A 1                                  5.9                        10.8 Delaware A                                           13.6                          8.6 Greenspring                                         14.0                          7.2 Calamos Convertible A                      14.3                          9.9 Managers Bond                                   10.3                          5.4 Harbor Bond                                           7.3                          4.4 Northeast Investors                               3.6                          5.5 Strong Gov’t. Securities                        7.0                          4.4 Lexington GNMA Income                   6.9                          3.5 Marshall Gov’t. Income                        5.8                          3.7 Wright U.S. Treasury                             6.3                          7.5 Excelsior Tax-Exempt                          7.6                          6.7 Vanguard Municipal                             6.5                          5.5 Goldman Sachs Global                        7.2                          4.1 Capital World Bond                               5.9                          4.9 Suppose that the question you wish to address using this data is whether the total return on the investment is affected by the risk of the investment. You also want to know if you can predict the total return based on the risk. You will attempt a Linear Regression Analysis Using Microsoft Excel 2010 to help you answer these questions.

Use Micrsoft Excel 2010 to run a linear regression analysis. What is the value of R² (R-squared)?

QUESTION 5

What does this value for R² (R-squared) suggest about the linear equation that Excel determines as the line of best fit?

		This equation will be a very good predictor of related data values.
		This equation will be an acceptable predictor of related data values.
		This equation will be a poor predictor of related data values.
		Inconclusive value

QUESTION 6

Identify the coefficients from the Excel Output summary that are used to write the line of best fit for this data. If the equation was written as

                                                y = b₁x + b₀,

where y if the total return value and x is the risk,  what is the value of b₀?

QUESTION 7

Identify the coefficients from the Excel Output summary that are used to write the line of best fit for this data. If the equation was written as

                                                y = b₁x + b₀,

where y if the total return value and x is the risk,  what is  the value of b₁?

QUESTION 10

Use the line of best fit to predict the total return (y-value) for a risk of  x = 11?

Total Return = _________  ?

Answer 1

Solution:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.825124
R Square	0.68083
Adjusted R Square	0.669431
Standard Error	4.445219
Observations	30
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1180.214	1180.214	59.72752	2.03E-08
Residual	28	553.2793	19.75998
Total	29	1733.494
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	2.250724	1.833473	1.227574	0.229828	-1.50498	6.006424	-1.50498	6.006424
X Variable 1	1.1923	0.154276	7.728358	2.03E-08	0.87628	1.50832	0.87628	1.50832

Value of r-square = 0.6808

Answer 5:

This equation will be an acceptable predictor of related data values.

Answer 6:

b0 = 2.2507

Answer 7:

b1= 1.1923

Answer 8:

When x = 11, the total return would be

y = 2.2507 + 1.1923*11

y = 15.366