5. (25 points) The following table provides quarterly sales data of frozen pie b
ID: 1109452 • Letter: 5
Question
5. (25 points) The following table provides quarterly sales data of frozen pie by Edward’s Pie Company for the period 2004-2015
Unit Sales (Q)
Price ($)
Advertising Expenditures ($)
Competitors' Price ($)
Income ($)
Population
Period
Q
P
A
PX
M
Pop
1
2004
193,334
6.39
15,827
6.92
33,337
4,116,250
2
170,041
7.21
20,819
4.84
33,390
4,140,338
3
247,709
5.75
14,062
5.28
33,599
4,218,965
4
183,259
6.75
16,973
6.17
33,797
4,226,070
5
2005
282,118
6.36
18,815
6.36
33,879
4,278,912
6
203,396
5.98
14,176
4.88
34,186
4,359,442
7
167,447
6.64
17,030
5.22
35,691
4,363,494
8
361,677
5.30
14,456
5.80
35,950
4,380,084
9
2006
401,805
6.08
27,183
4.99
34,983
9,184,926
10
412,312
6.13
27,572
6.13
35,804
9,237,683
11
321,972
7.24
34,367
5.82
35,898
9,254,182
12
445,236
6.08
26,895
6.05
36,113
9,272,758
13
2007
479,713
6.40
30,539
5.37
36,252
9,300,401
14
459,379
6.00
26,679
4.86
36,449
9,322,168
15
444,040
5.96
26,607
5.29
37,327
9,323,331
16
376,046
7.21
32,760
4.89
37,841
9,348,725
17
2008
255,203
6.55
19,880
6.97
34,870
5,294,645
18
270,881
6.11
19,151
6.25
35,464
5,335,816
19
330,271
5.62
15,743
6.03
35,972
5,386,134
20
313,485
6.06
17,512
5.08
36,843
5,409,350
21
2009
311,500
5.83
16,984
5.29
37,573
5,409,358
22
370,780
5.38
15,698
6.19
37,781
5,425,001
23
152,338
7.41
22,057
6.94
37,854
5,429,300
24
320,804
6.19
17,460
6.38
39,231
5,442,595
25
2010
738,760
5.75
42,925
5.54
28,579
16,381,600
26
707,015
6.61
50,299
6.73
28,593
16,544,289
27
699,051
5.03
37,364
5.04
28,633
16,547,258
28
628,838
6.76
50,602
4.61
28,833
16,553,958
29
2011
631,934
7.04
53,562
5.85
29,242
16,587,432
30
651,162
6.70
48,911
5.63
29,876
16,680,782
31
765,124
6.54
49,422
6.94
30,327
16,716,936
32
741,364
5.73
44,061
6.37
30,411
16,717,938
33
2012
291,773
5.35
13,896
5.78
29,778
2,972,443
34
153,018
6.33
27,429
4.73
30,079
2,974,275
35
574,486
5.94
31,631
6.70
30,598
2,989,720
36
75,396
7.00
39,176
4.58
30,718
3,020,244
37
2013
590,190
5.19
33,538
5.17
30,922
3,021,618
38
288,112
7.02
53,643
5.15
31,199
3,025,298
39
276,619
7.02
60,284
5.46
31,354
3,042,834
40
522,446
5.23
53,595
6.06
31,422
3,063,011
41
2014
395,314
5.80
22,626
6.56
38,892
7,611,304
42
436,103
5.32
22,697
6.38
39,080
7,615,783
43
336,338
6.35
25,475
4.53
39,510
7,666,220
44
451,321
5.95
25,734
6.31
39,552
7,710,368
45
2015
352,181
6.01
23,777
6.24
39,776
7,713,007
46
317,322
7.02
27,544
4.86
41,068
7,752,393
47
422,455
5.71
23,852
4.86
41,471
7,754,204
48
290,963
7.36
30,487
5.32
41,989
7,782,654
Where Q = the quantity of pies sold during a quarter, P = retail price in dollars, A = advertising expenditure in dollars, Px= price charged by a competing pie company in dollars, M = disposable income per capita in the area, in dollars, Pop = population of the market area. The Joanne Smart is the manager of Edward’s Pie Company. She would like you to develop a model to forecast sales for the first quarter of 2016. a. Use Excel to estimate the regression equation Q = a0 + a1 P + a2 A + a3 Px + a4 M + a5 Pop relating quantity sold (Q) as a function of price charged by Edward’s Pie (P), advertising (A), price charged by the competitors (Px), and area population (Pop). Estimate this model and write the estimated model in equation form. b. Carefully interpret the estimated coefficients in the model c. Are the estimated coefficients statistically significant? Use the reported p values to carry out the tests. d. Discuss the explanatory power of the model. What percentage of variations in sales is explained by the estimated regression equation? e. Use the estimated regression model to forecast sales for the first quarter of 2016, for following values of the independent variables: P = $8.50, A = $40,000, Px = 7.50, M = $43,000, and Pop = 8,500,000
Unit Sales (Q)
Price ($)
Advertising Expenditures ($)
Competitors' Price ($)
Income ($)
Population
Period
Q
P
A
PX
M
Pop
1
2004
193,334
6.39
15,827
6.92
33,337
4,116,250
2
170,041
7.21
20,819
4.84
33,390
4,140,338
3
247,709
5.75
14,062
5.28
33,599
4,218,965
4
183,259
6.75
16,973
6.17
33,797
4,226,070
5
2005
282,118
6.36
18,815
6.36
33,879
4,278,912
6
203,396
5.98
14,176
4.88
34,186
4,359,442
7
167,447
6.64
17,030
5.22
35,691
4,363,494
8
361,677
5.30
14,456
5.80
35,950
4,380,084
9
2006
401,805
6.08
27,183
4.99
34,983
9,184,926
10
412,312
6.13
27,572
6.13
35,804
9,237,683
11
321,972
7.24
34,367
5.82
35,898
9,254,182
12
445,236
6.08
26,895
6.05
36,113
9,272,758
13
2007
479,713
6.40
30,539
5.37
36,252
9,300,401
14
459,379
6.00
26,679
4.86
36,449
9,322,168
15
444,040
5.96
26,607
5.29
37,327
9,323,331
16
376,046
7.21
32,760
4.89
37,841
9,348,725
17
2008
255,203
6.55
19,880
6.97
34,870
5,294,645
18
270,881
6.11
19,151
6.25
35,464
5,335,816
19
330,271
5.62
15,743
6.03
35,972
5,386,134
20
313,485
6.06
17,512
5.08
36,843
5,409,350
21
2009
311,500
5.83
16,984
5.29
37,573
5,409,358
22
370,780
5.38
15,698
6.19
37,781
5,425,001
23
152,338
7.41
22,057
6.94
37,854
5,429,300
24
320,804
6.19
17,460
6.38
39,231
5,442,595
25
2010
738,760
5.75
42,925
5.54
28,579
16,381,600
26
707,015
6.61
50,299
6.73
28,593
16,544,289
27
699,051
5.03
37,364
5.04
28,633
16,547,258
28
628,838
6.76
50,602
4.61
28,833
16,553,958
29
2011
631,934
7.04
53,562
5.85
29,242
16,587,432
30
651,162
6.70
48,911
5.63
29,876
16,680,782
31
765,124
6.54
49,422
6.94
30,327
16,716,936
32
741,364
5.73
44,061
6.37
30,411
16,717,938
33
2012
291,773
5.35
13,896
5.78
29,778
2,972,443
34
153,018
6.33
27,429
4.73
30,079
2,974,275
35
574,486
5.94
31,631
6.70
30,598
2,989,720
36
75,396
7.00
39,176
4.58
30,718
3,020,244
37
2013
590,190
5.19
33,538
5.17
30,922
3,021,618
38
288,112
7.02
53,643
5.15
31,199
3,025,298
39
276,619
7.02
60,284
5.46
31,354
3,042,834
40
522,446
5.23
53,595
6.06
31,422
3,063,011
41
2014
395,314
5.80
22,626
6.56
38,892
7,611,304
42
436,103
5.32
22,697
6.38
39,080
7,615,783
43
336,338
6.35
25,475
4.53
39,510
7,666,220
44
451,321
5.95
25,734
6.31
39,552
7,710,368
45
2015
352,181
6.01
23,777
6.24
39,776
7,713,007
46
317,322
7.02
27,544
4.86
41,068
7,752,393
47
422,455
5.71
23,852
4.86
41,471
7,754,204
48
290,963
7.36
30,487
5.32
41,989
7,782,654
Explanation / Answer
a.
Q = 617437.74 - 128020.30 P + 5.81 A + 28321.49 Px + 1.72 M + 0.02 Pop
b. estimated coefficients interpretation
As P increases by 1 unit, Q reduces by 128020.30 units
As A increases by 1 unit, Q increases by 5.81 units
As PX increases by 1 unit, Q increases by 28321.49 units
As M increases by 1 unit, Q increases by 1.72 units
As Pop increases by 1 unit, Q increases by 0.02 units
c.p-value of M coefficient is 0.55 > alpha (0.05)
Hence, M is insignificant.
Rest all coefficients have p-value<alpha, hence significant
d. 89.34% of variations in sales is explained by the estimated regression equation
Note: max. 4 parts
Intercept 617437.74 151305.62 4.08 0.00 312090.64 922784.85 312090.64 922784.85 P -128020.30 15102.04 -8.48 0.00 -158497.45 -97543.14 -158497.45 -97543.14 A 5.81 1.01 5.73 0.00 3.77 7.86 3.77 7.86 PX 28321.49 12347.35 2.29 0.03 3403.53 53239.45 3403.53 53239.45 M 1.72 2.87 0.60 0.55 -4.08 7.52 -4.08 7.52 Pop 0.02 0.00 10.16 0.00 0.02 0.03 0.02 0.03Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.