Please solve 6-26, and 6-27 using excel. Attach screenshots or explain how to re
ID: 3363103 • Letter: P
Question
Please solve 6-26, and 6-27 using excel. Attach screenshots or explain how to reproduce please!
10 12 12 13 14 50 65 42 110 305 18 16 29 28 98 15 21 63 Find the best regression model to predict the total expenses of a hospital. Discuss the accuracy of this model. Should both variables be included in the model? Why or why not? :6-26 A sample of 20 automobiles was taken, and the miles per gallon (MPG), horsepower, and total weight were recorded. Develop a linear regression model to predict MPG, using horsepower as the only independent variable. Develop another model with weight as the independent variable. Which of these two models is better? Explain. CAT Publi WEIGHT 1,844 1,998 1,752 Publi Public Public Public Public Public Public MPG HORSEPOWER 67 50 62 40Explanation / Answer
Solution
Let y = MPG; x = Horsepower and z = weight.
Back-up Theory
Let X and Y be two variables such that Y depends on X by the model Y = + X + , where is the error term, which is assumed to be Normally distributed with mean 0 and variance 2. Then,
Y is termed ‘dependent variable’ and X is termed ‘independent variable’.
Let (xi, yi) be a pair of sample observation on (X, Y), i= 1, 2, …., n, where n = sample size.
Then, Mean X = Xbar = (1/n)sum of xiover I = 1, 2, …., n; ……………….(1)
Sxx = sum of (xi – Xbar)2 over i = 1, 2, …., n ………………………………..(2)
Standard Deviation of X = SDX = sq.rt of V(X). ……………………………(3)
Similarly, Mean Y = Ybar =(1/n)sum of yiover i= 1, 2, …., n;…………….(4)
Syy = sum of (yi – Ybar)2 over i = 1, 2, …., n ………………………………(5)
Standard Deviation of Y = SDY = sq.rt of V(Y)…………….……………….(6)
Sxy = sum of {(xi– Xbar)(yi– Ybar)} over i = 1, 2, …., n……………………(7)
Correlation Coefficient of X and Y = rXY= Sxy/sq.rt(Sxx.Syy). ……………(8)
Estimated Regression of Y on X is given by: Y = a + bX, where
b = Sxy/Sxx and a = Ybar – b.Xbar..……………………………….……….(9)
Regression of MPG (y) on Horsepower (x)
Calculations
n
20
xbar
94
ybar
28.45
Sxx
16686.55
Syy
1572.95
Sxy
-4496.15
b
-0.2694475
a
53.8723763
So, regression of MPG on horsepower is: MPG = 53.87 – 0.27Horsepower ANSWER 1
Regression of MPG (y) on weight (z)
Calculations
n
20
zbar
2,696
ybar
28.45
Szz
9904407.75
Syy
1572.95
Szy
-106833.25
b
-0.0107864
a
57.5329253
So, regression of MPG on weight is: MPG = 57.53 – 0.01weight ANSWER 2
Correlation coefficient between MPG and horsepower: r2= 0.7702 implying that 77% of variation in MPG is explained by horsepower.
Correlation coefficient between MPG and weight: r2 = 0.7326 implying that 73% of variation in MPG is explained by weight.
Thus, horsepower has a marginally higher impact on MPG than weight. ANSWER
n
20
xbar
94
ybar
28.45
Sxx
16686.55
Syy
1572.95
Sxy
-4496.15
b
-0.2694475
a
53.8723763