Use a stepwise process in excel to complete the following regression and report your analysis of each
Question:
Use a stepwise process in excel to complete the following regression and report your analysis of each regression including a correlation matrix, a coefficient of determination, ANOVA, regression equation, F test and goodness of fit analysis.
a. Y and X1 b. Y and X2 c. Y and X3 d. Y and X4 e. Y, X1 and X2 f. Y, X1 and X3 g. Y, X1 and X4 h. Y, X2 and X3 i. Y, X2 and X4 j. Y, X3 and X4 k. Y, X1, X2 and X3 l. Y X2, X3 and X4 m. Y, X1, X2, X3 and X4
2. Time series data. Use the data set provided to assess autocorrelation, check the ACF, determine the differencing needed, run the regression and give your analysis.
3. Time series data. Use the data set provided to do a moving average estimation, give your analysis of the results.
Y | X1 | X2 | X3 | |
n | sales (000) | advertising (000) | number of accounts | number of competitors |
1 | 79.3 | 5.5 | 31 | 10 |
2 | 200.1 | 2.5 | 55 | 8 |
3 | 163.2 | 8 | 67 | 12 |
4 | 200.1 | 3 | 50 | 7 |
5 | 146 | 3 | 38 | 8 |
6 | 177.7 | 2.9 | 71 | 12 |
7 | 30.9 | 8 | 30 | 12 |
8 | 291.9 | 9 | 56 | 5 |
9 | 160 | 4 | 42 | 8 |
10 | 339.4 | 6.5 | 73 | 5 |
11 | 159.6 | 5.5 | 60 | 11 |
12 | 86.3 | 5 | 44 | 12 |
13 | 237.5 | 6 | 50 | 8 |
14 | 107.2 | 5 | 39 | 10 |
15 | 155 | 3.5 | 55 | 10 |
16 | 291.4 | 8 | 70 | 6 |
17 | 100.2 | 6 | 40 | 11 |
18 | 135.8 | 4 | 50 | 11 |
19 | 223.3 | 7.5 | 62 | 9 |
20 | 195 | 7 | 59 | 9 |
21 | 73.4 | 6.7 | 53 | 13 |
22 | 47.7 | 6.1 | 38 | 13 |
23 | 140.7 | 3.6 | 43 | 9 |
24 | 93.5 | 4.2 | 26 | 8 |
25 | 259 | 4.5 | 75 | 8 |
26 | 331.2 | 5.6 | 71 | 4 |
27 | 79.4 | 5.6 | 32 | 10 |
28 | 201.1 | 2.6 | 56 | 9 |
29 | 164.3 | 8.1 | 67 | 13 |
30 | 200.1 | 3 | 51 | 8 |
31 | 147 | 3 | 39 | 8 |
32 | 178.7 | 2.9 | 72 | 13 |
33 | 31.9 | 8.1 | 31 | 13 |
34 | 292.9 | 8.8 | 56 | 6 |
35 | 161 | 4.1 | 43 | 9 |
36 | 339.6 | 6.5 | 74 | 6 |
37 | 158.9 | 5.6 | 61 | 12 |
38 | 87.3 | 5.1 | 45 | 13 |
39 | 238.5 | 6.2 | 51 | 7 |
40 | 107.2 | 5.6 | 40 | 11 |
41 | 155 | 3.4 | 56 | 11 |
42 | 292.4 | 8 | 71 | 7 |
43 | 101.2 | 6.2 | 41 | 12 |
44 | 136.8 | 4.2 | 51 | 12 |
45 | 224.3 | 7.6 | 63 | 10 |
46 | 196 | 7 | 60 | 10 |
47 | 74.4 | 6.8 | 54 | 12 |
48 | 48.7 | 6.2 | 39 | 12 |
49 | 141.7 | 3.6 | 44 | 9 |
50 | 93.4 | 4.6 | 27 | 8 |