Question

Data (adjacent worksheet) was collected for 45 mutual funds, which are part of the mutual fund portfolios offered through LMD investments. LMD wants to develop a linear regression model to predict the 3-year average return (%) based upon: the fund type, which is denoted as Corporate Bonds (CB), Global Equity (GE) and Fixed-income (FI); the funds Expense ratio; and a fund quality ranking (ranging from 1-star to 4-star).

Complete the following steps:

1. Use Excel to construct an (xy) scatterplot for y=3-year average return versus x=Expense ratio. Be sure to provide a meaningful title and informative axis labels.  

2. Run the regression model (use FI and 1-star as the reference categories for the categorical variables). Put your regression output in the worksheet "Regression Data".  Also generate  a proper Normal Probability Plot in the Data worksheet.  Use the regression output to answer questions a - g below:

a. Type the estimated regression function.

b. What percentage of the total variability in 3-year average return is explained by the regression model?

c.  What is the observed significance level of the estimated regression model?

d. Interpret the estimated regression coefficient for a 'GE' fund.

e. List and label each independent variables as: not significant (significance level > 0.1) or significant at the 0.1, 0.05, or 0.01 levels

f. State the 90% confidence interval for the coefficient of 'expense ratio'?

g. Predict the 3-year average return for a CB fund with a 3-star rating and an Expense ratio of 0.90% (report the final answer to one decimal place).

Fund	3-Year Average Return (%)	Quality Ranking	Fund Type	Expense Ratio (%)
1	14.39	1-Star	GE	0.67
2	30.53	2-Star	CB	1.41
3	3.34	3-Star	FI	0.49
4	10.88	2-Star	GE	0.99
5	11.32	1-Star	GE	1.03
6	24.95	2-Star	CB	1.23
7	15.67	2-Star	GE	1.18
8	16.77	4-Star	GE	1.31
9	18.14	3-Star	GE	1.08
10	15.85	3-Star	GE	1.20
11	17.25	2-Star	GE	1.02
12	17.77	3-Star	GE	1.32
13	17.23	2-Star	GE	0.53
14	4.31	3-Star	FI	0.44
15	18.23	4-Star	GE	1.00
16	17.99	4-Star	GE	0.89
17	4.41	4-Star	FI	0.45
18	23.46	3-Star	CB	0.90
19	13.50	2-Star	GE	0.89
20	2.76	2-Star	FI	0.45
21	14.4	3-Star	GE	0.56
22	4.63	2-Star	FI	0.62
23	16.70	3-Star	GE	1.36
24	12.46	2-Star	GE	1.07
25	12.81	2-Star	GE	0.90
26	12.31	1-Star	CB	0.86
27	15.31	2-Star	GE	1.32
28	5.14	4-Star	FI	0.60
29	15.16	4-Star	GE	1.31
30	32.70	2-Star	CB	1.16
31	15.33	3-Star	GE	1.08
32	9.51	1-Star	GE	1.05
33	13.57	2-Star	FI	1.25
34	23.68	3-Star	GE	1.36
35	51.10	3-Star	CB	1.24
36	16.91	3-Star	GE	0.80
37	15.91	2-Star	CB	1.01
38	15.46	3-Star	GE	1.27
39	4.31	2-Star	FI	0.62
40	13.41	3-Star	GE	0.29
41	21.77	4-Star	CB	0.64
42	4.25	4-Star	FI	0.21
43	2.37	2-Star	FI	0.16
44	17.01	2-Star	GE	0.23
45	13.98	3-Star	CB	1.19

Answer 1

1)

select both columns

Insert -> recommended chart,

you can select scatterplot

Please give me a thumbs-up if this helps you out. Thank you! :)

By HomeworkLib answering guidelines, we have to answer only one question at a time

Please post rest questions again as separate post if you want the solution to them too