Question

Please use Excel, and show all functions.

3. Answer the following question for where Y has been regressed on X1, X2, and X3. Use the linear regression output in the Excel file. Your answers should be rounded to 2 decimal places. a. What is the equation for the line of best fit or regression line? b. The proportion is for the amount of the variability of Y that is explained or accounted for by the model. C. The correlation between the observed values of y and the expected values of y is d. The residual is for when X1=23, X2 = 220, and X3 = 11. e. What is the expected value of Y when X1=65 X2 = 200, and X3 = 10?

Answer 1

Solution_A:

Regression line is

y=-57.188053+2.53845209x1+0.26392813x2-1.0436732x3

Solution-B:

R sq=0.5956

59.56% variation in y is explained by model

Explained variance=59.56%

unexplained variance=100-59.56= 40.44%

Solution-c:

Correlation coeffcient,r=0.7718=0.77

Solution-d:

we have

y=-57.188053+2.53845209x1+0.26392813x2-1.0436732x3

for x1=23,x2=220 and x3=11

y=-57.188053+2.53845209*23+0.26392813*220-1.0436732*11

=47.78013

predicted y=47.78013

Residual=observed y-predicted y

=50-47.78013

=2.21987

=2.22

Solution-e:

expected value of y is nothing but predicted value of y

we have

y=-57.188053+2.53845209*65+0.26392813*200-1.0436732*10

y=150.1602

Expected value of y=150.1602

=150.16