Multiple regression in Excel

Great Plains Roofing and Siding Company inc, sells roofing and siding products to home repair retailers, such as Lowe's and Home Depot, and commercial contractors. The owner is interested in studying the effects of several variables on the value of the shingles sold($000). The marketing manager is arguing that the company should spend more money on advertising, while a market researcher suggests it should focus more on making its brand and product more distinct from its competitors.

The company has divided the USA into 26 marketing districts. in each district it collected information on the following variables: volume of sales (in thousands of dollars), advertising (thousands of dollars), number of active accounts, number of competing brands, and a rating of district potential.

Sales advertising number of accounts number of competitors market potential
79.3 5.5 31 10 8
200.1 2.5 55 8 6
163.2 8 67 12 9
200.1 3 50 7 16
146 3 38 8 15
177.7 2.9 71 12 17
30.9 8 30 12 8
291.9 9 56 5 10
160 4 42 8 4
339.4 6.5 73 5 16
159.6 5.5 60 11 7
86.3 5 44 12 12
237.5 6 50 6 6
107.2 5 39 10 4
115 3.5 55 10 4
291.4 8 70 6 14
100.2 6 40 11 6
135.8 4 50 11 8
223.3 7.5 62 9 13
195 7 59 9 11
73.4 6.7 53 13 5
47.7 6.1 38 13 10
140.7 3.6 43 9 17
93.5 4.2 26 8 3
259 4.5 75 8 19
331.2 5.6 71 4 9

Conduct a multiple regression analysis to find the best predictors of sales.

a. Draw a scatter diagram comparing sales volumes with each of the independent variables. Comment on the results.
b. Develop a correlation matrix. Do you see any problems? Does it appear there are any redundant independent variables?
c. Develop a regression equation. Conduct a global test. Can we conclude that some of the independent variables are useful in explaining the variation in the dependent variables?
d. Conduct a test of each of the independent variables. Are there any that should be dropped?
e. Reined the regression equation so the remaining variables are all significant.
f. Develop a histogram of the residual and a normal probability plot. Are there any problems?
g. Determine the variance inflation factor for each of the independent variables. Are there any problems?