Use MS Excel to solve the problems

A marketing manager for a chain of hardware stores needed more information about the effectiveness of the four types of advertising that the chain used. The four advertising types are direct mailing, newspaper advertising, radio advertising, and television commercials.

Data in the file XR25-20, posted on Isaak, contain the weekly gross sales, and advertising expenditures for the four types of advertising. All values are in thousands of dollars.

a) Determine the regression equation. (2 Marks)

b) What is the coefficient of determination and what does it tell you about the model? (5 Marks)

c) Test the validity of the model. Explicitly specify the null and alternative hypotheses, and the reasons for either accepting or rejecting the null hypothesis. (5 Marks)

d) Are there signs of the presence of multicollinearity? Explain. (3 Marks)

e) Should any of the independent variables be removed from the model? Explain your answer. (5 Marks)

1 1.92 1.18 1.27 1.141 22 25 26 | A | B | C D E Sales Direct Radio Newspaper Television 2 15.98 0.56 0.60 1.35 2.301 15.98 0.69 0.56 1.01 0.64 16.02 0.42 0.55 1.73 1.261 16.04 0.63 0.50 1.01 2.00! 6 16.34 0.68 0.55 1.44 2.40 16.51 0.73 0.57 1.19 0.981 16.58 0.64 0.60 1.71 1.941 16.62 0.57 0.59 1.35 1.69 10 16.69 0.58 0.71 2.19 1.551 16.95 0.44 0.79 0.80! 12 16.98 0.56 0.93 1.42 1.02 13 17.03 0.80 0.80 2.02 2.001 14 17.08 0.42 0.71 1.08 2.32! 17.40 0.95 0.74 1.82 2.07 17.49 0.54 0.92 1.62 2.251 17 17.68 0.27 0.83 2.04 1.77! 18 17.84 0.50 0.79 2.11 17.88 0.66 0.92 1.14 1.501 20 18.32 0.81 0.84 1.75 2.31! 18.42 0.92 0.94 1.47 18.50 0.75 0.70 1.87 23 18.86 0.41 0.72 1.98 1.61! 24 18.91 0.43 0.76 1.68 2.04 18.96 0.55 0.64 1.17 2.071. 19.52 0.67 0.57 1.81 2.371 27 19.80 0.98 0.74 2.26 2.16! 28 19.83 0.59 0.67 1.72 2.121 19.97 0.54 0.93 2.25 1.05! 20.01 0.31 0.78 1.15 2.47 20.09 0.47 0.65 1.72 0.751 20.10 0.75 0.64 1.61 1.791 20.38 0.35 0.50 1.15 1.56 20.57 0.52 0.67 2.31 1.151 20.77 0.84 0.79 1.98 1.84! 21.59 0.72 0.92 2.25 1.76 21.62 0.28 0.58 1.31 1.881 21.71 0.51 0.99 1.02 1.23! 21.99 0.85 0.58 1.94 1.09 22.10 0.30 0.85 1.07 1.79 22.12 0.57 0.85 1.64 1.89! 22.53 0.32 0.92 2.20 22.54 0.28 0.91 1.07 2.211 22.67 0.56 0.92 1.26 2.01! 22.79 0.69 0.95 1.54 2.17 23.58 0.62 1.10 1.88 23.87 0.53 1.00 1.54 1.271 23.98 0.93 1.03 1.77 0.53 24.64 0.72 1.22 1.76 0.511 24.91 0.25 1.20 1.25 2.29! 25.47 0.80 1.20 1.79 1.71 25.47 0.44 2.31 0.531 22.02 0.82 0.85 1.81 15.41 0.76 0.98 1.95 2.00 23.57 0.66 0.90 2.50 0.791 0.76 2.041 1.25 1.911 1.45 1.68] 1.63 2.12 1.40 1.46 2.19l 1.18 1.90 156 1.53|| | 55 23.5 0.bb 0.90 2.50 0.79] 23.41 10.71 0.75 1.39 1.15 23.36 0.86 0.53 1.07 1.08|| 19.41 10.59 0.51 1.79 1.47/ | 59 | 20.34 0.75 0.86 1.64 1.02 16.52 10.75 0.51 1.89 1.53 || | 61 19.88 10.59 0.55 18.14 10.31 0.51 1.97 1.64 | 63 || 23.65 10.43 0.85 1.06|| 15.77 10.40 1.00 1.79 2.28! 16.51 10.58 0.98 1.07 15.24 10.33 0.78 1.79 1.82|| 67 17.01 10.90 0.80 1.75! | 68 19.88 0.87 0.66 1.10 1.69 | 6| 20.98 10.60 0.54 0.95|| 16.28 | 0.57 0.61 1.02 (74) 18.76 079 0.65 1.75|| 17.30 0.94 0.55 1.20 1.05|| | 73 23.00 10.37 0.69 10.67] 20.60 0.84 0.69 2.04 1.98|| 75 | 22.02 10.72 0.85 2.40|| 23.56 0.57 1.00 1.84 2.30 | 77 22.72 0.62 0.76 1.67 78 17.94 10.85 0.79 1.79 0.99|| 79 19.64 10.41 0.69 1.57 2.35] 16.61 10.71 0.50 2.40 23.47 10.40 0.67 1.61 0.90 | 20.27 0.95 1.00 1.33 22.16 0.84 0.89 1.72 1.51 18.90 0.34 0.78 1.71| 16.06 0.98 0.66 2.38] 20.14 0.89 0.86 1.77 1.26 17.43 0.48 0.50 0.96|| 21.75 0.33 0.84 2.24 0.63 22.33 0.87 0.77 2.23 1.01 23.18 0.90 0.74 2.45 1.81| 22.84 0.91 0.61 1.24 2.161 18.87 10.31 0.68 1.04 1.96 18.23 0.83 0.61 1.57| 18.82 10.65 0.73 1.74 2.09 16.63 10.62 0.92 1.58 2.40 15.38 10.32 0.92 1.26 114| 20.33 0.62 0.75 2.40 11ol 19.00 10.89 1.00 1.35 2.05 16.44 0.37 0.59 1.32 1.63|| | 18.33 10.52 0.98 1.49 1.73. | 101) 20.94 0.61 0.59 1.38 1.59 102_19.97 -0.48... 0.73..----2.41 -0.60 1.25 1.55 1.98 1.95 1.01 1.88