Assignment Paper

Posted on by

Part 1
Three hundred consumers between 21 and 49 years old were randomly selected. After sampling a new wine cooler, each was asked to rate the appeal of the phrase: “Not sweet like wine coolers, not filling like beer, and more refreshing than wine or mixed drinks” as it relates to the new wine cooler. The rating was made on a scale from 1 to 5, with 5 representing “extremely appealing” and with 1 representing “not at all appealing”.

As a manager overseeing the development of the concept, you bottle the wine cooler and placed it into distribution in one test store.

Your manager has asked you to assess the data and determine the most likely customer based on the ratings. Additionally, your manager would like you to review sales in the test store.

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

  1. Estimate the probability that a randomly selected 21 to 49 year old consumer:
    • Would give the phrase a rating of 5
    • Would give the phrase a rating of 3 or higher
    • Is in the 21-24 age group
    • Is a male who gives the phrase a rating of 4
    • Is a 35 to 49 year old who gives the phrase a rating of 1
    • Based on the probabilities for the ratings of 4 and 5, which age/gender demographic would be the best target audience for the new concept? 
  2. Create a probability distribution using the data which shows how many cartons of the wine cooler were bought per customer in a month. 
    • Calculate the mean and the standard deviation of your probability distribution. 
    • Calculate the probability that exactly 3 six packs will be bought in a month.
    • Calculate the probability that between 4 and 8 six packs will be bought in a month.
    • Calculate the probability that at least 5 six packs will be bought in a month.
    • Calculate the probability that no more than 5 six packs will be bought in a month. 
  3. Create a relative frequency distribution based on the wine cooler drinking temperatures. 
    • Create 6 bins with the same interval in each. 
    • Create a histogram
  4. Considering the mean and standard deviation for the ideal drinking temperature: 
    • Calculate z values then refer to Table 6.1 – Cumulative Areas Under the Standard Normal Curve 
    • Calculate the probability of the wine cooler being less than 45 degrees.
    • Calculate the probability of the wine cooler being greater than 60 degrees.
    • Calculate the percentage of wine coolers served at the ideal temperature, between 49 and 55 degrees.

 

 

Week 2

Sample Car # Color MPG Suggest Retail Price Option Package Days in Inventory
1 Grey 27.6 $ 24,390.00 LX 1
2 Grey 32.4 $ 21,780.00 Touring 10
3 Blue 33.2 $ 21,149.00 Touring 28
4 Black 34.7 $ 22,069.00 LX 21
5 Blue 27.2 $ 22,532.00 Touring 16
6 Blue 26.6 $ 20,345.00 Touring 22
7 Red 37.3 $ 22,112.00 EX 12
8 Silver 34.9 $ 21,289.00 Touring 23
9 Silver 29.6 $ 24,871.00 LX 15
10 Silver 32.3 $ 25,389.00 EX 8
11 Grey 31.9 $ 25,998.00 EX 28
12 Red 26.4 $ 19,713.00 LX 55
13 Black 34.8 $ 25,213.00 EX 2
14 Silver 35.9 $ 24,467.00 Touring 33
15 Black 34.6 $ 21,402.00 LX 17
16 Black 33.3 $ 20,351.00 LX 14
17 Grey 33.1 $ 23,732.00 Touring 1
18 Red 37.5 $ 24,558.00 Touring 2
19 Red 27.8 $ 19,167.00 LX 18
20 Red 36.1 $ 19,903.00 Touring 22
21 Blue 28.8 $ 22,140.00 Touring 19
22 Red 26.2 $ 22,180.00 EX 41
23 Silver 27.4 $ 23,120.00 Touring 33
24 Black 34.9 $ 19,837.00 Touring 21
25 Red 36.5 $ 24,666.00 EX 31
26 Red 26.3 $ 19,446.00 EX 35
27 Blue 31.4 $ 23,954.00 Touring 11
28 Blue 30.9 $ 21,201.00 LX 3
29 Red 27.4 $ 21,346.00 EX 26
30 Red 36 $ 19,406.00 EX 31

Week 3

RESULTS OF CONCEPT RATING FOR NEW WINE COOLER IDEAL TEMPERATURE
Rating of the appeal of the phrase as it relates to the new wine cooler Total Sample Gender Age 42
Male Female 21 – 24 25 -34 35-49 62
Extremely appealing (5) 151 68 83 48 63 40 49
Somewhat appealing (4) 91 51 40 36 32 23 51
Neither appealing nor not appealing (3) 36 21 15 9 16 11 56
Somewhat unappealing (2) 13 7 6 4 6 3 43
Not at all appealing (1) 9 3 6 4 3 2 41
40
48
55
QUANTITY PURCHASED PER PERSON IN ONE MONTH 54
# of six packs purchased # people who purchased this amount 44
0 7 58
1 6 47
2 6 54
3 13 55
4 14 44
5 12 49
6 10 47
7 5 57
8 5 41
9 5 62
10 2 47
56
62
41
49
42
47
55

Week 4

SAMPLE OF WEEKLY SALES
Sales Rep # AverageWeekly Sales($) Week # Weekly Sales($) – Rep A Weekly Sales($) – Rep B
1 1228 1 4657 5839
2 7374 2 6133 2602
3 1055 3 3438 2830
4 1859 4 7394 4763
5 3938 5 4327 3740
6 1692 6 2552 1315
7 569 7 7063 1599
8 4059 8 7844 1629
9 3689 9 6898 2416
10 607 10 4003 2107
11 1370 11 6884 4237
12 3735 12 4007 6322
13 3305 13 7214 2710
14 7228 14 2358 5890
15 6279 15 7745 5119
16 1671 16 1337 5184
17 5708 17 1052 3439
18 2569 18 6056 4828
19 4163 19 1495 3667
20 1519 20 3530 2518
21 7734 21 4749 6073
22 784 22 3833 5566
23 6766 23 7869 4555
24 7261 24 4541 5867
25 5034 25 6882 6039
26 7115 26 3868 1032
27 6291 27 5934 4834
28 6287 28 4447 3687
29 2080 29 5504 2214
30 7621 30 5554 4659
31 1047
32 6517
33 5172
34 3876
35 5429
36 4538
37 3786
38 2510
39 4863
40 7246
41 1175
42 641
43 4269
44 7034
45 3406
46 2256
47 3182
48 5178
49 4428
50 1189

Week 5

ORDERS VS. SHIPMENTS CUSTOMERS IN PAST 6 MONTHS MONTHLY SALES ($)
Size # Ordered # Received Customer # # Visits $ Purchases Month $ Sales
Extra Small 30 23 1 8 468 Jan 1375
Small 50 54 2 6 384 Feb 1319
Medium 85 92 3 8 463 Mar 1222
Large 95 91 4 2 189 Apr 1328
Extra Large 60 63 5 10 542 May 1493
2X Large 45 42 6 4 299 Jun 1492
7 6 345 Jul 1489
8 2 197 Aug 1354
9 4 293 Sep 1530
10 1 119 Oct 1483
11 3 211 Nov 1450
12 9 479 Dec 1495
13 7 430 Jan 1545
14 7 404 Feb 1454
15 6 359 Mar 1322
16 10 544 Apr 1492
17 9 522 May 1678
18 5 327 Jun 1645
19 6 353 Jul 1580
20 7 405 Aug 1493
21 4 289 Sep 1719
22 7 386 Oct 1573
23 7 403 Nov 1629
24 1 146 Dec 1680
25 7 416
26 9 485
27 3 333
28 7 241
29 2 391
30 6 268