- - - - PLEASE SHOW HOW TO SOLVE IN EXCEL - - - -

DATA

Case Problem 5: Mutual Funds Copy the third sheet in QMB3200-Homework#9Data.xlsx called Mutual Funds to your file. The data set Mutual Funds contains information for 45 mutual funds that are part of the Morningstar Funds 500 for 2008. The data set includes the following five variables: (i) Fund Type: The type of fund, labeled DE (Domestic Equity), IE (International Equity), and FI (Fixed Income). (ii) Net Asset Value ($): The closing price per share on December 31, 2007. (iii) 5-Year Average Return (%): The average annual return for the fund over the past five years. (iv) Expense Ratio (%): The percentage of assets deducted each fiscal year for fund expenses. 3 (v) Morningstar Rank: The risk adjusted star rating for each fund; Morningstar ranks go from a low of 1-Star to a high of 5-Stars. a) Develop an estimated regression equation that can be used to predict the 5-year average return given the type of fund. At .05 level of significance, test for a significant relationship. b) Did the estimated regression equation developed in part (a) provide a good fit to the data? Explain. c) Develop the estimated regression equation that can be used to predict the 5-year average return given the type of fund, the net asset value, and the expense ratio. At .05 level of significance, test for a significant relationship. Do you think any variables should be deleted from the estimated regression equation? Explain. d) Develop an estimated regression equation that can be used to predict the 5-year average return given the type of fund, the expense ratio, and the Morningstar Rank. Using a = .05, remove any in- dependent variables that are not significant. e) Use the estimated regression equation developed in part (d) to predict the 5-year average return for a domestic equity fund with an expense ratio of 1.05% and a 3-Star Morningstar Rating. 4.63 Fund Name Amer Cent Inc & Growth Inv American Century Intl. Disc American Century Tax-Free Bond American Century Ultra Ariel Artisan Intl Val Artisan Small Cap Baron Asset Brandywine Brown Cap Small Buffalo Mid Cap Delafield DFA U.S. Micro Cap Dodge & Cox Income Fairholme Fidelity Contrafund Fidelity Municipal Income Fidelity Overseas Fidelity Sel Electronics Fidelity Sh-Term Bond Fideliy FPA New Income Gabelli Asset AAA Greenspring Janus Janus Worldwide Kalmar Gr Val Sm Cp Managers Freemont Bond Marsico 21st Century Mathews Pacific Tiger Meridan Value Oakmark PIMCO Emerg Mkts Bd D RS Value A T. Rowe Price Latin Am. T. Rowe Price Mid Val Templeton Growth A Thornburg Value A USAA Income Vanguard Equity-Inc Vanguard Global Equity Vanguard GNMA Vanguard Sht-Tm TE Vanguard Sm Cp Idx Wasatch Sm Cp Growth Fund Type Net Asset Value ($) 5 Year Average Return (%) Expense Ratio (%) Morningstar Rank DE 28.88 12.39 0.67 2-Star IE 14.37 30.53 1.41 3-Star FI 10.73 3.34 0.49 4-Star DE 24.94 10.88 0.99 3-Star DE 46.39 11.32 1.03 2-Star IE 25.52 24.95 1.23 3-Star DE 16.92 15.67 1.18 3-Star DE 50.67 16.77 1.31 5-Star DE 36.58 18.14 1.08 4-Star DE 35.73 15.85 1.2 4-Star DE 15.29 17.25 1.02 3-Star DE 24.32 17.77 1.32 4-Star DE 13.47 17.23 0.53 3-Star FI 12.51 4.31 0.44 4-Star DE 31.86 18.23 1 5-Star DE 73.11 17.99 0.89 5-Star FI 12.58 4.41 0.45 5-Star IE 48.39 23.46 0.9 4-Star DE 45.6 13.5 0.89 3-Star FI 8.6 2.76 0.45 3-Star DE 39.85 14.4 0.56 4-Star FI 10.95 0.62 3-Star DE 49.81 16.7 1.36 4-Star DE 23.59 12.46 1.07 3-Star DE 32.26 12.81 0.9 3-Star IE 54.83 12.31 0.86 2-Star DE 15.3 15.31 1.32 3-Star FI 10.56 5.14 0.6 5-Star DE 17.44 15.16 1.31 5-Star IE 27.86 32.7 1.16 3-Star DE 31.92 15.33 1.08 4-Star DE 40.37 9.51 1.05 2-Star FI 10.68 13.57 1.25 3-Star DE 26.27 23.68 1.36 4-Star IE 53.89 51.1 1.24 4-Star DE 22.46 16.91 0.8 4-Star IE 24.07 15.91 1.01 3-Star DE 37.53 15.46 1.27 4-Star FI 12.1 4.31 0.62 3-Star DE 24.42 13.41 0.29 4-Star IE 23.71 21.77 0.64 5-Star FI 10.37 4.25 0.21 5-Star FI 15.68 0.16 3-Star DE 32.58 17.01 0.23 3-Star DE 35.41 1.19 4-Star 2.37 13.98 Case Problem 5: Mutual Funds Copy the third sheet in QMB3200-Homework#9Data.xlsx called Mutual Funds to your file. The data set Mutual Funds contains information for 45 mutual funds that are part of the Morningstar Funds 500 for 2008. The data set includes the following five variables: (i) Fund Type: The type of fund, labeled DE (Domestic Equity), IE (International Equity), and FI (Fixed Income). (ii) Net Asset Value ($): The closing price per share on December 31, 2007. (iii) 5-Year Average Return (%): The average annual return for the fund over the past five years. (iv) Expense Ratio (%): The percentage of assets deducted each fiscal year for fund expenses. 3 (v) Morningstar Rank: The risk adjusted star rating for each fund; Morningstar ranks go from a low of 1-Star to a high of 5-Stars. a) Develop an estimated regression equation that can be used to predict the 5-year average return given the type of fund. At .05 level of significance, test for a significant relationship. b) Did the estimated regression equation developed in part (a) provide a good fit to the data? Explain. c) Develop the estimated regression equation that can be used to predict the 5-year average return given the type of fund, the net asset value, and the expense ratio. At .05 level of significance, test for a significant relationship. Do you think any variables should be deleted from the estimated regression equation? Explain. d) Develop an estimated regression equation that can be used to predict the 5-year average return given the type of fund, the expense ratio, and the Morningstar Rank. Using a = .05, remove any in- dependent variables that are not significant. e) Use the estimated regression equation developed in part (d) to predict the 5-year average return for a domestic equity fund with an expense ratio of 1.05% and a 3-Star Morningstar Rating. 4.63 Fund Name Amer Cent Inc & Growth Inv American Century Intl. Disc American Century Tax-Free Bond American Century Ultra Ariel Artisan Intl Val Artisan Small Cap Baron Asset Brandywine Brown Cap Small Buffalo Mid Cap Delafield DFA U.S. Micro Cap Dodge & Cox Income Fairholme Fidelity Contrafund Fidelity Municipal Income Fidelity Overseas Fidelity Sel Electronics Fidelity Sh-Term Bond Fideliy FPA New Income Gabelli Asset AAA Greenspring Janus Janus Worldwide Kalmar Gr Val Sm Cp Managers Freemont Bond Marsico 21st Century Mathews Pacific Tiger Meridan Value Oakmark PIMCO Emerg Mkts Bd D RS Value A T. Rowe Price Latin Am. T. Rowe Price Mid Val Templeton Growth A Thornburg Value A USAA Income Vanguard Equity-Inc Vanguard Global Equity Vanguard GNMA Vanguard Sht-Tm TE Vanguard Sm Cp Idx Wasatch Sm Cp Growth Fund Type Net Asset Value ($) 5 Year Average Return (%) Expense Ratio (%) Morningstar Rank DE 28.88 12.39 0.67 2-Star IE 14.37 30.53 1.41 3-Star FI 10.73 3.34 0.49 4-Star DE 24.94 10.88 0.99 3-Star DE 46.39 11.32 1.03 2-Star IE 25.52 24.95 1.23 3-Star DE 16.92 15.67 1.18 3-Star DE 50.67 16.77 1.31 5-Star DE 36.58 18.14 1.08 4-Star DE 35.73 15.85 1.2 4-Star DE 15.29 17.25 1.02 3-Star DE 24.32 17.77 1.32 4-Star DE 13.47 17.23 0.53 3-Star FI 12.51 4.31 0.44 4-Star DE 31.86 18.23 1 5-Star DE 73.11 17.99 0.89 5-Star FI 12.58 4.41 0.45 5-Star IE 48.39 23.46 0.9 4-Star DE 45.6 13.5 0.89 3-Star FI 8.6 2.76 0.45 3-Star DE 39.85 14.4 0.56 4-Star FI 10.95 0.62 3-Star DE 49.81 16.7 1.36 4-Star DE 23.59 12.46 1.07 3-Star DE 32.26 12.81 0.9 3-Star IE 54.83 12.31 0.86 2-Star DE 15.3 15.31 1.32 3-Star FI 10.56 5.14 0.6 5-Star DE 17.44 15.16 1.31 5-Star IE 27.86 32.7 1.16 3-Star DE 31.92 15.33 1.08 4-Star DE 40.37 9.51 1.05 2-Star FI 10.68 13.57 1.25 3-Star DE 26.27 23.68 1.36 4-Star IE 53.89 51.1 1.24 4-Star DE 22.46 16.91 0.8 4-Star IE 24.07 15.91 1.01 3-Star DE 37.53 15.46 1.27 4-Star FI 12.1 4.31 0.62 3-Star DE 24.42 13.41 0.29 4-Star IE 23.71 21.77 0.64 5-Star FI 10.37 4.25 0.21 5-Star FI 15.68 0.16 3-Star DE 32.58 17.01 0.23 3-Star DE 35.41 1.19 4-Star 2.37 13.98