# Use the Week 5 Data Set to create and calculate the following in Excel®

Anonymous
account_balance_wallet \$20

Question description

You are the manager of a retail store. You want to investigate how metrics can improve the way you manage your business.

Use the Week 5 Data Set to create and calculate the following in Excel®:

1. Conduct a goodness of fit analysis which assesses orders of a specific item by size and items you received by size.
• Conduct a hypothesis test with the objective of determining if there is a difference between what you ordered and what you received at the .05 level of significance.
• Identify the null and alternative hypotheses.
2. Generate a scatter plot, the correlation coefficient, and the linear equation that evaluates whether a relationship exists between the number of times a customer visited the store in the past 6 months and the total amount of money the customer spent.
• Set up a hypothesis test to evaluate the strength of the relationship between the two variables.
• Use a level of significance of .05.
3. Use the regression line formula to forecast how much a customer might spend on merchandise if that customer visited the store 13 times in a 6 month period. Consider the average monthly sales of 2014, \$1310, as your base to:
• Calculate indices for each month for the next two years.
• Graph a time series plot.
4. In the Data Analysis Toolpak, use Excel's Exponential Smoothing option.
• Apply a damping factor of .5, to your monthly sales data.
• Create a new time series graph that compares the original and the revised monthly sales data.

Sample Car # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Color Grey Grey Blue Black Blue Blue Red Silver Silver Silver Grey Red Black Silver Black Black Grey Red Red Red Blue Red Silver Black Red Red Blue Blue Red Red MPG 27.6 32.4 33.2 34.7 27.2 26.6 37.3 34.9 29.6 32.3 31.9 26.4 34.8 35.9 34.6 33.3 33.1 37.5 27.8 36.1 28.8 26.2 27.4 34.9 36.5 26.3 31.4 30.9 27.4 36 Suggest Retail Price \$ 24,390.00 \$ 21,780.00 \$ 21,149.00 \$ 22,069.00 \$ 22,532.00 \$ 20,345.00 \$ 22,112.00 \$ 21,289.00 \$ 24,871.00 \$ 25,389.00 \$ 25,998.00 \$ 19,713.00 \$ 25,213.00 \$ 24,467.00 \$ 21,402.00 \$ 20,351.00 \$ 23,732.00 \$ 24,558.00 \$ 19,167.00 \$ 19,903.00 \$ 22,140.00 \$ 22,180.00 \$ 23,120.00 \$ 19,837.00 \$ 24,666.00 \$ 19,446.00 \$ 23,954.00 \$ 21,201.00 \$ 21,346.00 \$ 19,406.00 Option Package LX Touring Touring LX Touring Touring EX Touring LX EX EX LX EX Touring LX LX Touring Touring LX Touring Touring EX Touring Touring EX EX Touring LX EX EX Days in Inventory 1 10 28 21 16 22 12 23 15 8 28 55 2 33 17 14 1 2 18 22 19 41 33 21 31 35 11 3 26 31 RESULTS OF CONCEPT RATING FOR NEW WINE COOLER Gender Age Rating of the appeal of the phrase as it Total relates to the new wine cooler Sample Male Female 21 - 24 25 -34 Extremely appealing (5) 151 68 83 48 63 Somewhat appealing (4) 91 51 40 36 32 Neither appealing nor not appealing (3) 36 21 15 9 16 Somewhat unappealing (2) 13 7 6 4 6 Not at all appealing (1) 9 3 6 4 3 QUANTITY PURCHASED PER PERSON IN ONE MONTH # people who purchased this amount # of six packs purchased 0 7 1 6 2 6 3 13 4 14 5 12 6 10 7 5 8 5 9 5 10 2 Age 35-49 40 23 11 3 2 IDEAL TEMPERATURE 52 46 50 42 52 50 39 44 49 39 47 46 43 52 49 47 49 52 50 47 49 38 57 49 49 57 45 58 54 44 Sales Rep # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 SAMPLE OF WEEKLY SALES Average Week # Weekly Weekly Weekly Sales(\$) - Sales(\$) Sales(\$) Rep A Rep B 1228 7374 1055 1859 3938 1692 569 4059 3689 607 1370 3735 3305 7228 6279 1671 5708 2569 4163 1519 7734 784 6766 7261 5034 7115 6291 6287 2080 7621 1047 6517 5172 3876 5429 4538 3786 2510 4863 7246 1175 641 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 4657 6133 3438 7394 4327 2552 7063 7844 6898 4003 6884 4007 7214 2358 7745 1337 1052 6056 1495 3530 4749 3833 7869 4541 6882 3868 5934 4447 5504 5554 5839 2602 2830 4763 3740 2315 1599 1629 2416 2107 4237 6322 3710 5890 5119 5184 3439 4828 3667 3518 6073 5566 4555 5867 6039 1032 4834 3687 5500 4659 43 44 45 46 47 48 49 50 4269 7034 3406 2256 3182 5178 4428 1189 ORDERS VS. SHIPMENTS Size # Ordered # Received Extra Small 30 23 Small 50 54 Medium 85 92 Large 95 91 Extra Large 60 63 2X Large 45 42 CUSTOMERS IN PAST 6 MONTHS Customer # # Visits 1 8 2 6 3 8 4 2 5 10 6 4 7 6 8 2 9 4 10 1 11 3 12 9 13 7 14 7 15 6 16 10 17 9 18 5 19 6 20 7 21 4 22 7 23 7 24 1 25 7 26 9 27 3 28 7 29 2 30 6 CUSTOMERS IN PAST 6 MONTHS \$ Purchases 468 384 463 189 542 299 345 197 293 119 211 479 430 404 359 544 522 327 353 405 289 386 403 146 416 485 333 241 391 268 MONTHLY SALES (\$) Month \$ Sales Jan 1375 Feb 1319 Mar 1222 Apr 1328 May 1493 Jun 1492 Jul 1489 Aug 1354 Sep 1530 Oct 1483 Nov 1450 Dec 1495 Jan 1545 Feb 1454 Mar 1322 Apr 1492 May 1678 Jun 1645 Jul 1580 Aug 1493 Sep 1719 Oct 1573 Nov 1629 Dec 1680

mickeygabz
School: UIUC

See attached

Sample
Car #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Color
Grey
Grey
Blue
Black
Blue
Blue
Red
Silver
Silver
Silver
Grey
Red
Black
Silver
Black
Black
Grey
Red
Red
Red
Blue
Red
Silver
Black
Red
Red
Blue
Blue
Red
Red

MPG
27,6
32,4
33,2
34,7
27,2
26,6
37,3
34,9
29,6
32,3
31,9
26,4
34,8
35,9
34,6
33,3
33,1
37,5
27,8
36,1
28,8
26,2
27,4
34,9
36,5
26,3
31,4
30,9
27,4
36

Suggest Retail
Price
\$ 24.390,00
\$ 21.780,00
\$ 21.149,00
\$ 22.069,00
\$ 22.532,00
\$ 20.345,00
\$ 22.112,00
\$ 21.289,00
\$ 24.871,00
\$ 25.389,00
\$ 25.998,00
\$ 19.713,00
\$ 25.213,00
\$ 24.467,00
\$ 21.402,00
\$ 20.351,00
\$ 23.732,00
\$ 24.558,00
\$ 19.167,00
\$ 19.903,00
\$ 22.140,00
\$ 22.180,00
\$ 23.120,00
\$ 19.837,00
\$ 24.666,00
\$ 19.446,00
\$ 23.954,00
\$ 21.201,00
\$ 21.346,00
\$ 19.406,00

Option
Package
LX
Touring
Touring
LX
Touring
Touring
EX
Touring
LX
EX
EX
LX
EX
Touring
LX
LX
Touring
Touring
LX
Touring
Touring
EX
Touring
Touring
EX
EX
Touring
LX
EX
EX

Days in
Inventory
1
10
28
21
16
22
12
23
15
8
28
55
2
33
17
14
1
2
18
22
19
41
33
21
31
35
11
3
26
31

RESULTS OF CONCEPT RATING FOR NEW WINE COOLER
Gender
Age
Rating of the appeal of the phrase as it
Total
relates to the new wine cooler
Sample
Male
Female 21 - 24 25 -34
Extremely appealing (5)
151
68
83
48
63
Somewhat appealing (4)
91
51
40
36
32
Neither appealing nor not appealing (3)
36
21
15
9
16
Somewhat unappealing (2)
13
7
6
4
6
Not at all appealing (1)
9
3
6
4
3

QUANTITY PURCHASED PER PERSON IN ONE MONTH
# people who purchased this amount
# of six packs purchased
0
7
1
6
2
6
3
13
4
14
5
12
6
10
7
5
8
5
9
5
10
2

Age
35-49
40
23
11
3
2

IDEAL TEMPERATURE
52
46
50
42
52
50
39
44
49
39
47
46
43
52
49
47
49
52
50
47
49
38
57
49
49
57
45
58
54
44

Sales
Rep #

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

SAMPLE OF WEEKLY SALES
Average
Week # Weekly Weekly
Weekly
Sales(\$) - Sales(\$) Sales(\$)
Rep A
Rep B
1228
7374
1055
1859
3938
1692
569
4059
3689
607
1370
3735
3305
7228
6279
1671
5708
2569
4163
1519
7734
784
6766
7261
5034
7115
6291
6287
2080
7621
1047
6517
5172
3876
5429
4538
3786
2510
4863
7246
1175
641

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

4657
6133
3438
7394
4327
2552
7063
7844
6898
4003
6884
4007
7214
2358
7745
1337
1052
6056
1495
3530
4749
3833
7869
4541
6882
3868
5934
4447
5504
5554

5839
2602
2830
4763
3740
2315
1599
1629
2416
2107
4237
6322
3710
5890
5119
5184
3439
4828
3667
3518
6073
5566
4555
5867
6039
1032
4834
3687
5500
4659

43
44
45
46
47
48
49
50

4269
7034
3406
2256
3182
5178
4428
1189

ORDERS VS. SHIPMENTS
Size
Extra Small
30
23
Small
50
54
Medium
85
92
Larg...

flag Report DMCA
Review

Anonymous
Outstanding Job!!!!

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors