Question

i want the interpretation of t test and f test based on excel

2. You have been asked to develop a multiple regression model to predict the annual number of traffic deaths. The data file "traffic death rate" contains 10 years of annual data on the death rates per 100 mlion vehicle miles (y), total travel in billion vehicle miles (x1), and average speed in miles per hour of all vehicles (x2). Compute the multiple regression of y on xl and x2 and write a report discussing your findings Y X1 X2 738.0 52.6 5.2 5.3 767.0 53.8 5.4 805.0 55.8 5.6 847.0 55.9 5.5 889.0 56.4 5.7 930.0 57.3 5.5 962.0 58.0 1016.0 59.0 5.4 5.2 1071.0 60.0 4.9 1121.0 59.2 LO LO LO

Answer 1

2.

SP 1y (X1-Mx1)2 (x2-M2)2 X1-Mx1 SP 1x2 X2-M2 SPx2y Y-My -4.2 -0.17 30.022 0.714 741.72 -176.6 31187.56 17.64 10.332 0.21 442.8 -147.6 -3 -0.07 21785.76 9 -0.03 -109.6 -1 -3.288 109.6 0.03 12012.16 1 -67.6 -0.9 4569.76 -15.548 -0.207 60.84 0.23 0.81 10.24 -3.328 -0.052 -25.6 -0.4 0.13 655.36 0.16 15.4 0.5 5.082 0.165 7.7 0.33 237,16 0.25 6.162 0.156 56.88 47,4 1.2 0.13 2246.76 1.44 0.066 10281.96 3.042 223,08 101.4 2.2 0.03 4.84 156.4 -0.17 -26.588 -0.544 500.48 3.2 24460.96 10.24 -97.008 -1.128 495.36 206.4 2.4 -0.47 42600.96 5.76 SPX2Y: SPX1X2: SSX1: SPX1Y: SSX2 -0.65 2648.7 -91.12 150038.4 51.14

Sum of X₁ = 9146
Sum of X₂ = 568
Sum of Y = 53.7
Mean X₁ = 914.6
Mean X₂ = 56.8
Mean Y = 5.37
Sum of squares (SS_X1) = 150038.4
Sum of squares (SS_X2) = 51.14
Sum of products (SP_X1Y) = -91.12
Sum of products (SP_X2Y) = -0.65
Sum of products (SP_X1X2) = 2648.7

Regression Equation = ŷ = b₁X₁ + b₂X₂ + a

b₁ = ((SP_X1Y)*(SS_X2)-(SP_X1X2)*(SP_X2Y)) / ((SS_X1)*(SS_X2)-(SP_X1X2)*(SP_X1X2)) = -2938.22/657352.09 = -0.00447

b₂ = ((SP_X2Y)*(SS_X1)-(SP_X1X2)*(SP_X1Y)) / ((SS_X1)*(SS_X2)-(SP_X1X2)*(SP_X1X2)) = 143824.58/657352.09 = 0.21879

a = M_Y - b₁M_X1 - b₂M_X2 = 5.37 - (0*914.6) - (0.22*56.8) = -2.96943

ŷ = -0.00447X₁ + 0.21879X₂ - 2.96943