14 Question 5 The manager of Colonial Furniture has been reviewing weekly advert
ID: 3178281 • Letter: 1
Question
14 Question 5 The manager of Colonial Furniture has been reviewing weekly advertising expenditures. During the past 6 months, all advertisements for the store have appeared in the local newspaper. The number of ads per week has varied from one to seven. The store's sales staff has been tracking the number of customers who enter the store each week. The number of ads and the number of customers per week for the past 26 weeks were recorded. The manager believes that the higher the number of ads the higher the number of customers that arrive at his store. a. Create a suitable table in Excel that will help you calculate a and b for an estimated regression of the form a b X, b. Create another table in Excel that will help you calculate the residuals and, subsequently, the R- squared value for the model. Based on the R-squared value, does the estimated regression provide a good fit of the data?Explanation / Answer
Question 5
Part a
Here, we have to find the values for a and b for the regression equation. The formula for a and b is given as below:
Coefficient b = r*(Sy/Sx)
And a = Ybar – b*Xbar
So, we have to find mean for X, mean for Y, SD for X, SD for Y, correlation coefficient for X and Y. The calculation table along with all values is given as below:
No.
X
Y
X^2
Y^2
XY
(X - Xbar)^2
(Y - Ybar)^2
1
5
353
25
124609
1765
0.782543698
1011.729779
2
6
319
36
101761
1914
3.551773698
4330.653379
3
3
440
9
193600
1320
1.244083698
3046.189979
4
2
332
4
110224
664
4.474853698
2788.653179
5
4
172
16
29584
688
0.013313698
45287.11718
6
2
331
4
109561
662
4.474853698
2895.268579
7
4
344
16
118336
1376
0.013313698
1665.268379
8
2
483
4
233289
966
4.474853698
9641.727779
9
4
329
16
108241
1316
0.013313698
3114.499379
10
2
532
4
283024
1064
4.474853698
21665.57318
11
7
496
49
246016
3472
8.321003698
12363.72758
12
5
393
25
154449
1965
0.782543698
67.11377929
13
4
376
16
141376
1504
0.013313698
77.57557929
14
7
372
49
138384
2604
8.321003698
164.0371793
15
2
512
4
262144
1024
4.474853698
16177.88118
16
5
254
25
64516
1270
0.782543698
17110.65438
17
5
459
25
210681
2295
0.782543698
5504.497379
18
2
153
4
23409
306
4.474853698
53734.80978
19
1
426
1
181476
426
9.705623698
1696.805579
20
6
566
36
320356
3396
3.551773698
32830.64958
21
6
596
36
355216
3576
3.551773698
44602.18758
22
5
395
25
156025
1975
0.782543698
103.8829793
23
6
676
36
456976
4056
3.551773698
84792.95558
24
3
194
9
37636
582
1.244083698
36407.57838
25
2
135
4
18225
270
4.474853698
62403.88698
26
7
367
49
134689
2569
8.321003698
317.1141793
Total
107
10005
527
4313803
43025
86.65384615
463802.0385
Mean
4.115385
384.8077
Var
3.466153846
18552.08154
SD
1.861761
136.206
SD
1.861760953
136.2060261
r
0.291909
Mean = Total sum / total number of observations
Xbar = 107/26 = 4.115385
Ybar = 10005/26 = 384.8077
Var = (X – Xbar)^2/(n – 1)
Here, n – 1 = 26 – 1 = 25
Var(X) = 86.65384615/25 = 3.466153846
Sx = sqrt(3.466153846) = 1.861760953
Var(Y) = 463802.0385/25 = 18552.08154
Sy = sqrt(18552.08154) = 136.2060261
Correlation coefficient = r = 0.291909
b = r*(Sy/Sx) = 0.291909*(136.2060261/1.861760953) = 21.356
a = Ybar – b*Xbar = 384.8077 - 21.356*4.115385 = 296.9195
Regression equation is given as below:
Y = a + b*X
Y = 296.9195 + 21.356*X
Number of customers = 296.9195 + 21.356*Number of ads
Part b
Here, we have to find out residuals. The formula for residual is given as below:
Residual = Y - Yestimated
The table for residuals is given as below:
Observation
Y
Predicted Y
Residuals
1
353
403.6995118
-50.69951176
2
319
425.0554816
-106.0554816
3
440
360.9875721
79.01242787
4
332
339.6316023
-7.631602308
5
172
382.3435419
-210.3435419
6
331
339.6316023
-8.631602308
7
344
382.3435419
-38.34354194
8
483
339.6316023
143.3683977
9
329
382.3435419
-53.34354194
10
532
339.6316023
192.3683977
11
496
446.4114514
49.5885486
12
393
403.6995118
-10.69951176
13
376
382.3435419
-6.343541944
14
372
446.4114514
-74.4114514
15
512
339.6316023
172.3683977
16
254
403.6995118
-149.6995118
17
459
403.6995118
55.30048824
18
153
339.6316023
-186.6316023
19
426
318.2756325
107.7243675
20
566
425.0554816
140.9445184
21
596
425.0554816
170.9445184
22
395
403.6995118
-8.699511762
23
676
425.0554816
250.9445184
24
194
360.9875721
-166.9875721
25
135
339.6316023
-204.6316023
26
367
446.4114514
-79.4114514
Total
0.0000
We are given
Correlation coefficient = r = 0.291909
So, coefficient of determination = r^2 = 0.291909*0.291909 = 0.085211
This means only 8.5% of the variation in the dependent variable number of customers is explained by the independent variable number of ads. So, this regression does not provide a good fit for the data.
No.
X
Y
X^2
Y^2
XY
(X - Xbar)^2
(Y - Ybar)^2
1
5
353
25
124609
1765
0.782543698
1011.729779
2
6
319
36
101761
1914
3.551773698
4330.653379
3
3
440
9
193600
1320
1.244083698
3046.189979
4
2
332
4
110224
664
4.474853698
2788.653179
5
4
172
16
29584
688
0.013313698
45287.11718
6
2
331
4
109561
662
4.474853698
2895.268579
7
4
344
16
118336
1376
0.013313698
1665.268379
8
2
483
4
233289
966
4.474853698
9641.727779
9
4
329
16
108241
1316
0.013313698
3114.499379
10
2
532
4
283024
1064
4.474853698
21665.57318
11
7
496
49
246016
3472
8.321003698
12363.72758
12
5
393
25
154449
1965
0.782543698
67.11377929
13
4
376
16
141376
1504
0.013313698
77.57557929
14
7
372
49
138384
2604
8.321003698
164.0371793
15
2
512
4
262144
1024
4.474853698
16177.88118
16
5
254
25
64516
1270
0.782543698
17110.65438
17
5
459
25
210681
2295
0.782543698
5504.497379
18
2
153
4
23409
306
4.474853698
53734.80978
19
1
426
1
181476
426
9.705623698
1696.805579
20
6
566
36
320356
3396
3.551773698
32830.64958
21
6
596
36
355216
3576
3.551773698
44602.18758
22
5
395
25
156025
1975
0.782543698
103.8829793
23
6
676
36
456976
4056
3.551773698
84792.95558
24
3
194
9
37636
582
1.244083698
36407.57838
25
2
135
4
18225
270
4.474853698
62403.88698
26
7
367
49
134689
2569
8.321003698
317.1141793
Total
107
10005
527
4313803
43025
86.65384615
463802.0385
Mean
4.115385
384.8077
Var
3.466153846
18552.08154
SD
1.861761
136.206
SD
1.861760953
136.2060261
r
0.291909
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.