Marketing Analytics

Drudown121
CopyofUnit2_Regression_Example.xlsx
Home>Business & Finance homework help>Marketing homework help>Marketing Analytics

data

Source Economist Pocket World in Figures 2011
Country Pop (millions) Computer Sales Sales/Capita GNP per head Unemployment rate %age spend on education
1 Austria 8.4 941.2 $112.05 $49,600 4.2 5.8
2 Belgium 10.5 1681.9 $160.18 $47,090 8.1 5.9
3 Bulgaria 7.6 154 $20.26 $6,550 13.5 3.5
4 Czech Rep. 10.2 1028.7 $100.85 $20,670 6.6 4.4
5 Denmark 5.5 935.4 $170.07 $62,120 5.2 8.4
6 Finland 5.3 1971 $371.89 $51,320 9.9 6.3
7 France 61.9 5928.9 $95.78 $44,510 10 5.7
8 Germany 82.5 6824.3 $82.72 $44,450 9.1 4.6
9 Greece 11.2 813 $72.59 $31,670 9.9 3.9
10 Hungary 10 449 $44.90 $15,410 7.3 5.1
11 Ireland 4.4 576.9 $131.11 $60,460 6.3 4.3
12 Italy 58.9 3858.2 $65.50 $38,490 9.3 5
13 Netherlands 16.5 2168.5 $131.42 $52,960 4.4 5
14 Poland 38 2847 $74.92 $13,850 14.4 5.6
15 Portugal 10.7 728.6 $68.09 $22,920 6.3 5.9
16 Romania 21.3 687.2 $32.26 $9,300 7 3.3
17 Spain 44.8 4745.8 $105.93 $35,220 14.2 4.4
18 Switzerland 7.5 1130.4 $150.72 $64,430 3.6 5.6
19 Sweden 9.2 2113.4 $229.72 $51,950 6.3 7.6
20 Turkey 75.8 2879 $37.98 $9,940 8.6 3.7
21 UK 61 9887.2 $162.09 $43,540 5.9 4.8

Slides 14-16

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.73
R Square 0.53
Adjusted R Square 0.45
Standard Error 58.43
Observations 21
ANOVA
df SS MS F Significance F
Regression 3 66638 22213 6.51 0.004
Residual 17 58032 3414
Total 20 124670
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -114.84 78.29 -1.47 0.16 -280.01 50.34 -280.01 50.34
GNP per head 0.00 0.00 2.41 0.03 0.00 0.00 0.00 0.00
Unemployment rate 4.22 4.84 0.87 0.40 -5.99 14.43 -5.99 14.43
%age spend on education 21.42 12.74 1.68 0.11 -5.45 48.29 -5.45 48.29
RESIDUAL OUTPUT
Observation Predicted Sales/Capita Residuals
1 141.1050110113 -29.0573919637
2 153.9361699264 6.2447824546
3 32.1578881741 -11.8947302793
4 54.7672884559 46.0856527206
5 229.7909035734 -59.7181763006
6 179.8197146177 192.0670778351 Finland
7 151.7406303742 -55.9587240737
8 124.2402274129 -41.5214395341
9 83.2552007781 -10.6659150638
10 60.6308801233 -15.7308801233
11 142.7851158825 -11.6714795188
12 119.9588490055 -54.4546045233
13 132.5310691088 -1.1068266846
14 97.7164232545 -22.7953706229
15 90.8053302087 -22.7118722648
16 6.7651463829 25.4977644152
17 120.2673826729 -14.3343469586
18 168.3638233859 -17.6438233859
19 193.9264924164 35.790898888
20 23.5560006075 14.4255297355
21 112.9313706531 49.1538752485

Finland out

Source Economist Pocket World in Figures 2011
Country Pop (millions) Computer Sales Sales/Capita GNP per head Unemployment rate %age spend on education
1 Austria 8.4 941.2 $112.05 49600 4.2 5.8
2 Belgium 10.5 1681.9 $160.18 47090 8.1 5.9
3 Bulgaria 7.6 154 $20.26 6550 13.5 3.5
4 Czech Rep. 10.2 1028.7 $100.85 20670 6.6 4.4
5 Denmark 5.5 935.4 $170.07 62120 5.2 8.4
7 France 61.9 5928.9 $95.78 44510 10 5.7
8 Germany 82.5 6824.3 $82.72 44450 9.1 4.6
9 Greece 11.2 813 $72.59 31670 9.9 3.9
10 Hungary 10 449 $44.90 15410 7.3 5.1
11 Ireland 4.4 576.9 $131.11 60460 6.3 4.3
12 Italy 58.9 3858.2 $65.50 38490 9.3 5
13 Netherlands 16.5 2168.5 $131.42 52960 4.4 5
14 Poland 38 2847 $74.92 13850 14.4 5.6
15 Portugal 10.7 728.6 $68.09 22920 6.3 5.9
16 Romania 21.3 687.2 $32.26 9300 7 3.3
17 Spain 44.8 4745.8 $105.93 35220 14.2 4.4
18 Switzerland 7.5 1130.4 $150.72 64430 3.6 5.6
19 Sweden 9.2 2113.4 $229.72 51950 6.3 7.6
20 Turkey 75.8 2879 $37.98 9940 8.6 3.7
21 UK 61 9887.2 $162.09 43540 5.9 4.8

Regression2

SUMMARY OUTPUT **Regression with Finland (outlier) removed
Regression Statistics
Multiple R 0.86
R Square 0.74
Adjusted R Square 0.69
Standard Error 29.98
Observations 20
ANOVA
df SS MS F Significance F
Regression 3 41150 13717 15.26 0.000
Residual 16 14384 899
Total 19 55535
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -32.21 41.89 -0.77 0.45 -121.01 56.60 -121.01 56.60
GNP per head 0.00 0.00 3.38 0.00 0.00 0.00 0.00 0.00
Unemployment rate -0.53 2.58 -0.20 0.84 -5.99 4.93 -5.99 4.93
%age spend on education 15.23 6.60 2.31 0.03 1.24 29.21 1.24 29.21
RESIDUAL OUTPUT
Observation Predicted Sales/Capita Residuals
1 137.1439896071 -25.0963705595
2 132.3952470051 27.7857053758
3 24.9554160194 -4.6922581247
4 66.0018192089 34.8511219676
5 197.2217719175 -27.1490446448
6 124.0164561638 -28.2345498633
7 107.6404225426 -24.9216346638
8 75.108615856 -2.5193301417
9 67.4631931047 -22.5631931047
10 131.4216039245 -0.3079675608
11 103.6225451408 -38.1183006587
12 130.4957796366 0.9284627877
13 68.7108290703 6.2102235612
14 92.7780831901 -24.6846252462
15 29.9566686787 2.3062421195
16 86.410987695 19.5220480193
17 159.3060963628 -8.5860963628
18 167.3895074617 62.3278838426
19 36.2773255918 1.7042047512
20 110.8477673956 51.237478506

Unemployment out

Source Economist Pocket World in Figures 2011
Country Pop (millions) Computer Sales Sales/Capita GNP per head %age spend on education
1 Austria 8.4 941.2 $112.05 49600 5.8
2 Belgium 10.5 1681.9 $160.18 47090 5.9
3 Bulgaria 7.6 154 $20.26 6550 3.5
4 Czech Rep. 10.2 1028.7 $100.85 20670 4.4
5 Denmark 5.5 935.4 $170.07 62120 8.4
7 France 61.9 5928.9 $95.78 44510 5.7
8 Germany 82.5 6824.3 $82.72 44450 4.6
9 Greece 11.2 813 $72.59 31670 3.9
10 Hungary 10 449 $44.90 15410 5.1
11 Ireland 4.4 576.9 $131.11 60460 4.3
12 Italy 58.9 3858.2 $65.50 38490 5
13 Netherlands 16.5 2168.5 $131.42 52960 5
14 Poland 38 2847 $74.92 13850 5.6
15 Portugal 10.7 728.6 $68.09 22920 5.9
16 Romania 21.3 687.2 $32.26 9300 3.3
17 Spain 44.8 4745.8 $105.93 35220 4.4
18 Switzerland 7.5 1130.4 $150.72 64430 5.6
19 Sweden 9.2 2113.4 $229.72 51950 7.6
20 Turkey 75.8 2879 $37.98 9940 3.7
21 UK 61 9887.2 $162.09 43540 4.8

Slide 19

SUMMARY OUTPUT **Regression with Finland (outlier) removed and Unemployment (insignificant variable) removed
Regression Statistics
Multiple R 0.86
R Square 0.74
Adjusted R Square 0.71
Standard Error 29.13
Observations 20
ANOVA
df SS MS F Significance F
Regression 2 41113 20556 24.23 0.000
Residual 17 14422 848
Total 19 55535
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept -38.48 27.79 -1.38 0.18 -97.11 20.15 -97.11 20.15
GNP per head 0.002 0.00 3.98 0.00 0.00 0.00 0.00 0.00
%age spend on education 15.31 6.40 2.39 0.03 1.82 28.80 1.82 28.80
Sales Per Capita = Intercept + GNP per head (x) + %age spend on education (x)
GNP per head %age spend on education Sales per Capita
$ 36,490 5.2 $ 104.01
skewness 0.8317848946
RESIDUAL OUTPUT kurtosis 0.1798015122
Observation Predicted Sales/Capita Residuals abs resid
1 135.7853766433 -23.7377575957 23.7377575957
2 132.9911982381 27.1897541428 27.1897541428 SUMMARY OUTPUT
3 26.390580466 -6.1274225713 6.1274225713
4 64.5004824649 36.3524587116 36.3524587116 Regression Statistics
5 197.1647671107 -27.092039838 27.092039838 Multiple R 0.4031625579
6 125.4834761025 -29.701569802 29.701569802 R Square 0.1625400481 p-value>.05 so no significant heteroscedacity
7 108.5393721866 -25.8205843078 25.8205843078 Adjusted R Square 0.1160144952
8 75.8004636532 -3.2111779389 3.2111779389 Standard Error 16.4937627235
9 66.1534351845 -21.2534351845 21.2534351845 Observations 20
10 131.5343744366 -0.4207380729 0.4207380729
11 104.3931851579 -38.8889406757 38.8889406757 ANOVA
12 129.3274302037 2.0968122206 2.0968122206 df SS MS F Significance F
13 71.1201625817 3.8008900498 3.8008900498 Regression 1 950.4041552658 950.4041552658 3.4935651067 0.0779681637
14 91.3422208534 -23.2487629095 23.2487629095 Residual 18 4896.7957580359 272.0442087798
15 28.0673452754 4.1955655227 4.1955655227 Total 19 5847.1999133016
16 89.5725809733 16.360454741 16.360454741
17 158.2780143036 -7.5580143036 7.5580143036 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
18 167.3923538107 62.3250374937 62.3250374937 Intercept 5.1279973743 9.114043699 0.562647881 0.5806154642 -14.0198979087 24.2758926574 -14.0198979087 24.2758926574
19 35.2940689103 2.6874614327 2.6874614327 Predicted Sales/Capita 0.1520429404 0.0813451828 1.8691081046 0.0779681637 -0.018856947 0.3229428277 -0.018856947 0.3229428277
20 110.0332370167 52.0520088849 52.0520088849