Prepare Administration Consultant Project|Mathematics – Statistics
· Scenario: FedEx has reached out to your consulting group for assistance. FedEx would like to build a new location in Zzyzx, California. Data has been collected from a sample of shipments based on different regional centers surrounding Zzyzx.
· Q1: Look at the data and use descriptive statistics (mean, median, modes, graphs, tables, boxplots, etc.). Choose some of these and describe them to the company.
· Q2: Is there a difference in average profit between corporate (column: corporate) and non-corporate (column: noncorp) sales?
· Q3: Is there a correlation between the percent discount provided (column: discount) and the total sales (column: sales)?
· Q4: Does the Shipping Cost (column: shipcost) predict Sales (column: sales)?
· Q5: Is there any association between Ship Mode (column: shipmode) and Customer Segment (column: segment)?
· Tips: What graphs will you choose to show? Sometimes a table looks better than a graph, or the other way around! What recommendations will you make? “I don’t know” isn’t the answer the company or shareholders want to hear!Group 1 FedEx
Order ID sales discount shipmode Profit Unit Price shipcost segment Product Category corporate noncorp
5925 1733.3625 0.07 Regular Air -267.01 85.99 0.99 Home Office Technology 400.11
35047 148.17 0 Regular Air -7.06 4.71 0.7 Home Office Office Supplies -7.06
58278 281.5455 0.1 Regular Air -434.56 85.99 0.99 Corporate Technology -434.56
27174 477.5045 0.09 Regular Air -245.56 35.99 5 Corporate Technology -245.56
30659 3116.7715 0.08 Regular Air -106.51 85.99 0.99 Corporate Technology -106.51
47750 195.04 0.08 Regular Air -149.41 167.27 35 Corporate Office Supplies -149.41
14406 2329.646 0.1 Express Air -310.24 85.99 0.99 Corporate Technology -310.24
38528 2544.985 0.07 Regular Air -113.06 85.99 0.99 Small Business Technology -113.06
51524 2723.1 0.07 Regular Air -446.31 167.27 35 Small Business Office Supplies 446.31
355 1239.6315 0 Regular Air -172.55 85.99 0.99 Small Business Technology 172.55
3235 142.97 0.1 Express Air -12.26 4.71 0.7 Corporate Office Supplies -12.26
3942 1559.7755 0.03 Regular Air -247.18 85.99 0.99 Small Business Technology -247.18
48994 1086.39 0.03 Regular Air -311.23 167.27 35 Consumer Office Supplies -311.23
18951 324.3175 0.03 Express Air -169.74 35.99 5 Corporate Technology -169.74
40194 1094.9105 0.02 Regular Air -112.12 35.99 5 Consumer Technology -112.12
43200 1073.567 0.04 Regular Air -305.97 85.99 0.99 Small Business Technology -305.97
49988 7731.09 0 Regular Air -222.17 167.27 35 Home Office Office Supplies -222.17
31073 4322.26 0.07 Regular Air -618.95 167.27 35 Home Office Office Supplies 618.95
36896 3508.33 0.04 Regular Air -546.98 167.27 35 Corporate Office Supplies -546.98
14119 968.235 0.05 Regular Air -158.61 35.99 5 Home Office Technology -158.61
12097 1831.529 0 Express Air -139.98 85.99 0.99 Consumer Technology -139.98
31910 187.6375 0.03 Regular Air -190.03 35.99 5 Small Business Technology -190.03
40928 292.3405 0.01 Regular Air -420.59 85.99 0.99 Corporate Technology -420.59
18370 1271.0475 0 Regular Air -158.24 35.99 5 Consumer Technology -158.24
48868 7647.97 0.05 Express Air -1014.01 167.27 35 Corporate Office Supplies -1014.01
24193 1426.5125 0.04 Regular Air -175.18 85.99 0.99 Small Business Technology -175.18
9952 7890.89 0.01 Express Air -771.09 167.27 35 Home Office Office Supplies 771.09
1637 1225.52 0.1 Express Air -1191.13 35.48 35 Corporate Office Supplies -1191.13
19430 6742.49 0.03 Regular Air -502.05 167.27 35 Corporate Office Supplies -502.05
34243 1264.13 0.02 Regular Air -1087.36 35.48 35 Home Office Office Supplies 1087.36
34406 142.59 0.02 Regular Air -3.23 4.71 0.7 Home Office Office Supplies -3.23
21222 846.0815 0.02 Regular Air -120.93 35.99 5 Home Office Technology 120.93
32165 2939.6825 0.02 Regular Air 192.06 85.99 0.99 Small Business Technology 192.06
29317 140.69 0.06 Regular Air -14.54 4.71 0.7 Corporate Office Supplies -14.54
10820 151.19 0.06 Regular Air -13.45 4.71 0.7 Small Business Office Supplies -13.45
19521 351.06 0.02 Regular Air -306.32 35.48 35 Home Office Office Supplies -306.32
54501 783.96 0.05 Regular Air -1195.29 20.34 35 Corporate Office Supplies -1195.29
58117 134.86 0.04 Regular Air -62.84 3.14 1.92 Corporate Office Supplies -62.84
9573 133.23 0 Regular Air -59.91 3.14 1.92 Home Office Office Supplies -59.91
21856 77.03 0.08 Regular Air -47.75 3.14 1.92 Corporate Office Supplies -47.75
44098 5.06 0.01 Regular Air -2.64 3.14 1.92 Corporate Office Supplies -2.64
45155 111.55 0.05 Regular Air -54.25 3.14 1.92 Corporate Office Supplies -54.25
41026 59.76 0.1 Express Air -33.11 3.14 1.92 Home Office Office Supplies -33.11
13091 883.15 0.01 Regular Air -1148.19 20.34 35 Corporate Office Supplies -1148.19
13383 750.03 0.07 Regular Air -1072.97 20.34 35 Consumer Office Supplies 1072.97
37223 5176.27 0.01 Regular Air -743.45 135.31 35 Home Office Office Supplies 743.45
23782 169.75 0.09 Regular Air -194.85 20.34 35 Corporate Office Supplies -194.85
24099 825.82 0 Regular Air -1049.26 20.34 35 Corporate Office Supplies -1049.26
32613 5228.2 0 Regular Air -691.52 135.31 35 Consumer Office Supplies -691.52
20390 58.5 0 Regular Air -25.26 3.14 1.92 Home Office Office Supplies -25.26
33635 112.18 0.07 Regular Air -96.16 20.34 35 Small Business Office Supplies -96.16
19621 67.03 0.04 Regular Air -30.40 3.14 1.92 Corporate Office Supplies -30.40
9700 106.06 0.08 Regular Air -59.74 3.14 1.92 Consumer Office Supplies -59.74
17124 128.31 0.05 Express Air -55.72 3.14 1.92 Home Office Office Supplies -55.72
59939 130.96 0.05 Regular Air -66.74 3.14 1.92 Small Business Office Supplies -66.74
8293 75.03 0.02 Regular Air -31.52 3.14 1.92 Small Business Office Supplies -31.52
39043 750.89 0.07 Express Air -1176.28 20.34 35 Home Office Office Supplies 1176.28
41094 159.05 0.05 Regular Air -153.46 20.34 35 Corporate Office Supplies -153.46
53635 103.92 0.08 Regular Air -52.76 3.14 1.92 Small Business Office Supplies -52.76
13607 315.29 0 Regular Air -361.82 20.34 35 Small Business Office Supplies -361.82
194 20.19 0.04 Regular Air -13.44 3.14 1.92 Corporate Office Supplies -13.44
8995 89.41 0.01 Regular Air -21.49 1.89 0.76 Consumer Office Supplies -21.49
16193 1364.8025 0.07 Regular Air -183.68 55.99 5 Corporate Technology -183.68
16545 4.99 0.04 Regular Air -3.06 3.68 1.32 Small Business Office Supplies -3.06
20263 553.5625 0.08 Regular Air -76.86 20.99 1.25 Small Business Technology -76.86
31393 110.32 0.09 Regular Air -32.65 3.68 1.32 Corporate Office Supplies -32.65
36646 1168.15 0.04 Express Air -743.96 48.91 35 Small Business Office Supplies 743.96
44772 54.077 0.07 Express Air -232.99 55.99 5 Corporate Technology -232.99
54437 653.871 0.09 Regular Air -311.05 55.99 5 Corporate Technology -311.05
55138 1132.54 0 Regular Air -628.38 48.91 35 Corporate Office Supplies -628.38
10048 78.57 0.02 Regular Air -20.65 3.68 1.32 Corporate Office Supplies -20.65
8773 220.7875 0.09 Regular Air -305.32 55.99 5 Home Office Technology -305.32
18464 80.54 0.01 Regular Air -18.72 1.89 0.76 Small Business Office Supplies -18.72
20256 512.83 0.01 Regular Air -221.56 20.89 11.52 Small Business Office Supplies -221.56
22368 2788.04 0.01 Regular Air -849.18 80.98 35 Consumer Office Supplies 849.18
48230 2645.8 0 Regular Air -684.78 80.98 35 Corporate Office Supplies 684.78
17632 71.12 0.1 Regular Air -21.89 1.98 0.7 Corporate Office Supplies 1200.00
51524 347.2675 0.06 Express Air -71.09 20.99 0.99 Small Business Technology -71.09
4743 719.64 0.01 Regular Air -245.62 20.89 11.52 Home Office Office Supplies -245.62
6592 1621.1 0.07 Express Air -971.36 48.91 35 Consumer Office Supplies 971.36
17152 236.45 0.06 Regular Air -259.02 6.98 9.69 Home Office Office Supplies -259.02
18723 52.0965 0 Regular Air -232.80 55.99 2.5 Consumer Technology -232.80
22849 198.13 0.01 Regular Air -219.92 6.98 9.69 Corporate Office Supplies -219.92
24197 35.6405 0.05 Regular Air -110.40 20.99 1.25 Small Business Technology -110.40
36741 303.909 0.01 Regular Air -254.36 55.99 5 Home Office Technology 254.36
38503 503.2935 0.02 Regular Air -222.82 55.99 5 Corporate Technology -222.82
52391 857.84 0.02 Regular Air -309.43 20.89 11.52 Corporate Office Supplies -309.43
49668 93.22 0.06 Regular Air -15.37 3.74 0.94 Home Office Office Supplies -15.37
50309 243.18 0.08 Regular Air -314.22 6.98 9.69 Small Business Office Supplies -314.22
39040 19.36 0.04 Express Air -1.00 1.98 0.7 Corporate Office Supplies -1.00
10919 2040.95 0.07 Regular Air -1231.87 48.91 35 Corporate Office Supplies 1234.00
39040 1204.5095 0.03 Regular Air -221.25 55.99 5 Corporate Technology 224.55
55616 437.478 0.02 Regular Air -42.23 20.99 1.25 Consumer Technology -42.23
55077 143.9815 0.08 Regular Air -283.23 55.99 2.5 Corporate Technology -283.23
55744 415.055 0.02 Regular Air -44.13 20.99 0.99 Consumer Technology -44.13
4935 106.64 0.1 Regular Air -31.95 3.68 1.32 Corporate Office Supplies -31.95
13761 2421.44 0.09 Regular Air -949.09 78.8 35 Home Office Office Supplies 949.09
28225 1160.59 0.01 Regular Air -330.14 80.98 35 Corporate Office Supplies -330.14
50017 2014.13 0.1 Regular Air -794.94 89.83 35 Home Office Office Supplies
Ammara Farooq
Grayson Ricafort
Esmeralda Ramirez
Josiah Orozco
Alyssa Becerra
Denisse Ramos Talavera
Descriptive Statistics
The Average Shipping cost to ship a product on truck is 28.758, while on air it is 8.571.
This will let the company know that shipping through truck is more expensive. Shipment through air is far more reliable as noted in the pie chart. This is a possibility to decrease spending and increase revenue.
Average Shipping: 14.7908
Mean Shipping Cost:
6.18
Average Product Base Margin:
0.4918
Mean Product Base Margin:
0.5
Total Averages
median target PSP $5.79
average target PSP $7.22
median walmart PSP $5.79
average walmart PSP $7.14
The Average Discount percent of all Products 5
Comparing Target and Walmart Sales Prices
Both companies almost have the same prices.
Olay Regenerist Moisturizer, Hellmann’s Real Mayonnaise, and Folgers Classic Roast are the only ones with slight price differences.
4
Product sale price
Wal-Mart Folgers Classic Roast Hamburger Helper Frenchs Mustard Hellmanns Real Mayonnaise Kraft Miracle Whip Pringles Listerine Cool Mint Pantene Color Reserve Volume Shampoo Aveeno Active Naturals Daily Mositurizing Lotion Olay Regenerist Moisturizer Fancy Feast Wet Cat Food Gourmet Entree Nice n Easy Haircolor Cover Girl Lash Blast Volume Mascara 6.97 2.0 1.77 2.97 4.97 2.25 5.79 3.96 11.27 27.47 6.98 8.96 7.47 Target Folgers Classic Roast Hamburger Helper Frenchs Mustard Hellmanns Real Mayonnaise Kraft Miracle Whip Pringles Listerine Cool Mint Pantene Color Reserve Volume Shampoo Aveeno Active Naturals Daily Mositurizing Lotion Olay Regenerist Moisturizer Fancy Feast Wet Cat Food Gourmet Entree Nice n Easy Haircolor Cover Girl Lash Blast Volume Mascara 9.49 2.04 1.99 4.49 4.99 2.29 5.79 4.99 11.39 22.99 6.99 8.99 7.49
Products
Prices
Positive Correlation
2011 prices vs. Present prices
There is a perfect positive correlation
Correlation Coefficient r= 0.9885946 which is near 1, which makes the correlation almost the same.
Correlation bewteen walmart prices and walmart 2011 prices
Wal-Mart Folgers Classic Roast Hamburger Helper Frenchs Mustard Hellmanns Real Mayonnaise Kraft Miracle Whip Pringles Listerine Cool Mint Pantene Color Reserve Volume Shampoo Aveeno Active Naturals Daily Mositurizing Lotion Olay Regenerist Moisturizer Fancy Feast Wet Cat Food Gourmet Entree Nice n Easy Haircolor Cover Girl Lash Blast Volume Mascara 6.97 2.0 1.77 2.97 4.97 2.25 5.79 3.96 11.27 27.47 6.98 8.96 7.47 Wal-Mart (2011) Folgers Classic Roast Hamburger Helper Frenchs Mustard Hellmanns Real Mayonnaise Kraft Miracle Whip Pringles Listerine Cool Mint Pantene Color Reserve Volume Shampoo Aveeno Active Naturals Daily Mositurizing Lotion Olay Regenerist Moisturizer Fancy Feast Wet Cat Food Gourmet Entree Nice n Easy Haircolor Cover Girl Lash Blast Volume Mascara 9.47 2.67 2.17 3.97 4.37 1.67 4.99 4.96 11.27 32.97 7.48 7.78 7.47
Products
Prices
Product Correlation
Sales vs. Discounts
There is NO apparent correlation between product Sales and the Discount.
r= 0.0742363, which is near 0, meaning that there is no correlation.
244.57 124.56 905.08 716.8399999999999 10123.02 228.41 146.69 394.27 261.54 196.85 93.54 2781.82 4965.7595 7.000000000000001 1.0 5.0 8.0 4.0 8.0 1.0 0.0 9.0 3.0 4.0 7.000000000000001 7.000000000000001
Product sales
Discount
Relative Frequency
There is NO association between Ship Mode and Product Container.
