In: Operations Management
QSO 320 Milestone Three
Sales | City | Date | Sales Rep | Type Wine | |
$7,451 | Los Angeles | Feb | Bill | Red | |
$11,221 | Los Angeles | Jun | Joe | Organic | |
$9,525 | San Francisco | Jun | Jane | Red | |
$3,986 | San Diego | May | Jane | Organic | |
$11,667 | San Diego | Apr | Bill | White | |
$11,649 | San Francisco | Mar | Jane | Red | |
$9,010 | San Diego | Feb | Jane | Red | |
$5,686 | Los Angeles | Jan | Joe | Red | |
$9,121 | San Francisco | Mar | Jane | Organic | |
$8,703 | San Francisco | Apr | Jane | Red | |
$4,369 | San Diego | Feb | Jane | Organic | |
$5,936 | Los Angeles | Feb | Bill | White | |
$9,990 | San Diego | May | Joe | Organic | |
$5,217 | San Diego | Apr | Bill | White | |
$5,582 | San Francisco | Apr | Joe | Red | |
$7,913 | Los Angeles | Jun | Jane | White | |
$8,581 | San Francisco | Jan | Joe | Red | |
$7,472 | San Diego | Feb | Bill | Red | |
$4,716 | San Francisco | Feb | Joe | White | |
$3,020 | San Diego | Jan | Jane | Organic | |
$11,552 | San Francisco | Apr | Jane | Red | |
$8,507 | Los Angeles | Mar | Joe | White | |
$8,573 | San Francisco | Mar | Bill | White | |
$4,827 | San Diego | Jun | Jane | Red | |
$11,146 | San Diego | Jun | Joe | Organic | |
$10,898 | San Diego | Apr | Jane | Red | |
$10,424 | San Francisco | Apr | Bill | White | |
$6,077 | Los Angeles | Apr | Bill | White | |
$4,908 | Los Angeles | Feb | Jane | White | |
$4,652 | San Francisco | Jan | Jane | White | |
$7,498 | San Diego | Apr | Joe | Organic | |
$4,641 | San Diego | Jun | Bill | White | |
$10,440 | San Diego | Jan | Jane | White | |
$4,168 | San Diego | Mar | Joe | Red | |
$4,031 | San Francisco | Feb | Bill | White | |
$4,031 | San Francisco | Feb | Jane | White | |
$7,498 | San Diego | Apr | Jane | Organic | |
$8,305 | San Diego | Feb | Jane | Red | |
$4,788 | San Francisco | May | Jane | White | |
$11,953 | San Diego | May | Joe | White | |
$11,482 | San Francisco | Jun | Jane | Red | |
$11,959 | San Diego | Jan | Jane | White | |
$8,681 | San Francisco | Jun | Jane | Organic | |
$10,399 | Los Angeles | Mar | Joe | Red | |
$11,310 | Los Angeles | Feb | Joe | Red | |
$6,981 | San Francisco | Jun | Jane | Organic | |
$8,758 | Los Angeles | May | Jane | Organic | |
$9,837 | San Diego | May | Bill | Red | |
$4,276 | San Francisco | Apr | Joe | White | |
$7,515 | San Francisco | Apr | Bill | Red | |
$10,497 | Los Angeles | May | Bill | Red | |
$8,587 | San Francisco | Jan | Bill | White | |
$7,373 | Los Angeles | Feb | Joe | White | |
$8,722 | San Francisco | Jan | Joe | Red | |
$5,607 | San Francisco | Jun | Joe | White | |
$11,029 | San Francisco | Jan | Bill | White | |
$6,262 | San Francisco | Mar | Bill | Organic | |
$9,432 | San Diego | May | Bill | Organic | |
$6,685 | San Diego | Apr | Joe | Organic | |
$3,913 | San Diego | Jan | Joe | Red | |
$7,642 | Los Angeles | Mar | Bill | Organic | |
$9,549 | San Diego | Mar | Jane | White | |
$9,014 | San Diego | Feb | Bill | White | |
$7,390 | San Francisco | May | Jane | Organic | |
$3,390 | Los Angeles | Feb | Bill | Red | |
$4,670 | San Francisco | Jun | Joe | Organic | |
$8,535 | Los Angeles | Jun | Jane | Red | |
$9,112 | San Francisco | May | Bill | Red | |
$8,197 | San Francisco | Jan | Joe | Red | |
$3,287 | Los Angeles | Jan | Jane | Red | |
$6,388 | Los Angeles | Apr | Bill | White | |
$9,887 | San Diego | Jan | Joe | Red | |
$6,326 | Los Angeles | Feb | Joe | Organic | |
$10,694 | Los Angeles | Jan | Joe | White | |
$9,779 | San Francisco | Jun | Bill | Red | |
$10,157 | San Francisco | May | Joe | Red | |
$5,286 | San Francisco | Jan | Joe | Red | |
$8,516 | Los Angeles | May | Bill | White | |
$7,177 | San Diego | Apr | Bill | White | |
$6,312 | Los Angeles | May | Joe | Organic | |
$5,546 | San Diego | Feb | Bill | Red | |
$5,294 | San Francisco | Jan | Jane | White | |
$7,879 | San Francisco | Mar | Jane | White | |
$6,437 | San Francisco | Jun | Bill | Red | |
$6,061 | San Diego | Jan | Jane | Organic | |
$7,763 | San Francisco | Jun | Jane | White | |
$5,761 | Los Angeles | Jun | Bill | Red | |
$9,248 | San Francisco | Apr | Joe | Organic | |
$10,919 | San Francisco | Feb | Joe | White | |
$11,498 | Los Angeles | Jan | Bill | White | |
$8,950 | San Diego | May | Jane | White | |
$9,133 | Los Angeles | Mar | Joe | White | |
$9,332 | San Francisco | Apr | Bill | Red | |
$5,437 | Los Angeles | Feb | Joe | White | |
$7,914 | San Diego | Feb | Joe | White | |
$8,559 | Los Angeles | May | Jane | Red | |
$8,274 | San Francisco | Mar | Bill | White | |
$3,877 | San Diego | Jan | Jane | Organic | |
$3,407 | San Francisco | Jan | Bill | Red | |
$5,605 | San Diego | Jun | Jane | Red | |
1. For White and Organic wine, what were the Measures of Central Tendency based on the sales figures?
2. What would the total profit for each type of wine be given the following production costs for the wine; White wine 17% of total sales, red wine is 12%, and organic wine is 21%?
3. Deduct the sales commision discussed in question 2 from Milestone Two: Data Analysis Part II and recalculate. Draft and present the answers.
4. How would you summarize the provided data? Develop pivot tables using the cle
Commision Data
F. What would the sales commision be for each month if each salesperson earned 5% of the sales? | ||||||||
Monthly Sales | ||||||||
Sales Rep | Jan | Feb | Mar | April | May | June | 6 Month Total | |
Bill | $34,521.00 | $42,840.00 | $30,751.00 | $63,797.00 | $47,394.00 | $26,618.00 | $245,921.00 | |
Jane | $48,590.00 | $30,623.00 | $38,198.00 | $38,651.00 | $42,431.00 | $71,312.00 | $269,805.00 | |
Joe | $60,966.00 | $53,995.00 | $32,207.00 | $33,289.00 | $38,412.00 | $32,644.00 | $251,513.00 | |
Total Sales | $767,239.00 | |||||||
Sales Commision | 5% of sales | |||||||
Monthly Commision by Sales Rep | ||||||||
Sales Rep | Jan | Feb | Mar | April | May | June | 6 Month Total | |
Bill | $1,726.05 | $2,142.00 | $1,537.55 | $3,189.85 | $2,369.70 | $1,330.90 | $12,296.05 | |
Jane | $2,429.50 | $1,531.15 | $1,909.90 | $1,932.55 | $2,121.55 | $3,565.60 | $13,490.25 | |
Joe | $3,048.30 | $2,699.75 | $1,610.35 | $1,664.45 | $1,920.60 | $1,632.20 | $12,575.65 | |
Total Commision | $38,361.95 |
1.
For White
Sales | City | Date | Sales Rep | Type Wine |
11667.00 | San Diego | Apr | Bill | White |
5936.00 | Los Angeles | Feb | Bill | White |
5217.00 | San Diego | Apr | Bill | White |
7913.00 | Los Angeles | Jun | Jane | White |
4716.00 | San Francisco | Feb | Joe | White |
8507.00 | Los Angeles | Mar | Joe | White |
8573.00 | San Francisco | Mar | Bill | White |
10424.00 | San Francisco | Apr | Bill | White |
6077.00 | Los Angeles | Apr | Bill | White |
4908.00 | Los Angeles | Feb | Jane | White |
4652.00 | San Francisco | Jan | Jane | White |
4641.00 | San Diego | Jun | Bill | White |
10440.00 | San Diego | Jan | Jane | White |
4031.00 | San Francisco | Feb | Bill | White |
4031.00 | San Francisco | Feb | Jane | White |
4788.00 | San Francisco | May | Jane | White |
11953.00 | San Diego | May | Joe | White |
11959.00 | San Diego | Jan | Jane | White |
4276.00 | San Francisco | Apr | Joe | White |
8587.00 | San Francisco | Jan | Bill | White |
7373.00 | Los Angeles | Feb | Joe | White |
5607.00 | San Francisco | Jun | Joe | White |
11029.00 | San Francisco | Jan | Bill | White |
9549.00 | San Diego | Mar | Jane | White |
9014.00 | San Diego | Feb | Bill | White |
6388.00 | Los Angeles | Apr | Bill | White |
10694.00 | Los Angeles | Jan | Joe | White |
8516.00 | Los Angeles | May | Bill | White |
7177.00 | San Diego | Apr | Bill | White |
5294.00 | San Francisco | Jan | Jane | White |
7879.00 | San Francisco | Mar | Jane | White |
7763.00 | San Francisco | Jun | Jane | White |
10919.00 | San Francisco | Feb | Joe | White |
11498.00 | Los Angeles | Jan | Bill | White |
8950.00 | San Diego | May | Jane | White |
9133.00 | Los Angeles | Mar | Joe | White |
5437.00 | Los Angeles | Feb | Joe | White |
7914.00 | San Diego | Feb | Joe | White |
8274.00 | San Francisco | Mar | Bill | White |
Mean | 7736.00 | |||
Median | 7913.00 | |||
Mode | 4031 |
For organic:
Sales | City | Date | Sales Rep | Type Wine |
11221.00 | Los Angeles | Jun | Joe | Organic |
3986.00 | San Diego | May | Jane | Organic |
9121.00 | San Francisco | Mar | Jane | Organic |
4369.00 | San Diego | Feb | Jane | Organic |
9990.00 | San Diego | May | Joe | Organic |
3020.00 | San Diego | Jan | Jane | Organic |
11146.00 | San Diego | Jun | Joe | Organic |
7498.00 | San Diego | Apr | Joe | Organic |
7498.00 | San Diego | Apr | Jane | Organic |
8681.00 | San Francisco | Jun | Jane | Organic |
6981.00 | San Francisco | Jun | Jane | Organic |
8758.00 | Los Angeles | May | Jane | Organic |
6262.00 | San Francisco | Mar | Bill | Organic |
9432.00 | San Diego | May | Bill | Organic |
6685.00 | San Diego | Apr | Joe | Organic |
7642.00 | Los Angeles | Mar | Bill | Organic |
7390.00 | San Francisco | May | Jane | Organic |
4670.00 | San Francisco | Jun | Joe | Organic |
6326.00 | Los Angeles | Feb | Joe | Organic |
6312.00 | Los Angeles | May | Joe | Organic |
6061.00 | San Diego | Jan | Jane | Organic |
9248.00 | San Francisco | Apr | Joe | Organic |
3877.00 | San Diego | Jan | Jane | Organic |
Mean | 7224.96 | |||
Median | 7390.00 | |||
Mode | 7498 |
Combined White and organic
Sales | City | Date | Sales Rep | Type Wine |
11221.00 | Los Angeles | Jun | Joe | Organic |
3986.00 | San Diego | May | Jane | Organic |
11667.00 | San Diego | Apr | Bill | White |
9121.00 | San Francisco | Mar | Jane | Organic |
4369.00 | San Diego | Feb | Jane | Organic |
5936.00 | Los Angeles | Feb | Bill | White |
9990.00 | San Diego | May | Joe | Organic |
5217.00 | San Diego | Apr | Bill | White |
7913.00 | Los Angeles | Jun | Jane | White |
4716.00 | San Francisco | Feb | Joe | White |
3020.00 | San Diego | Jan | Jane | Organic |
8507.00 | Los Angeles | Mar | Joe | White |
8573.00 | San Francisco | Mar | Bill | White |
11146.00 | San Diego | Jun | Joe | Organic |
10424.00 | San Francisco | Apr | Bill | White |
6077.00 | Los Angeles | Apr | Bill | White |
4908.00 | Los Angeles | Feb | Jane | White |
4652.00 | San Francisco | Jan | Jane | White |
7498.00 | San Diego | Apr | Joe | Organic |
4641.00 | San Diego | Jun | Bill | White |
10440.00 | San Diego | Jan | Jane | White |
4031.00 | San Francisco | Feb | Bill | White |
4031.00 | San Francisco | Feb | Jane | White |
7498.00 | San Diego | Apr | Jane | Organic |
4788.00 | San Francisco | May | Jane | White |
11953.00 | San Diego | May | Joe | White |
11959.00 | San Diego | Jan | Jane | White |
8681.00 | San Francisco | Jun | Jane | Organic |
6981.00 | San Francisco | Jun | Jane | Organic |
8758.00 | Los Angeles | May | Jane | Organic |
4276.00 | San Francisco | Apr | Joe | White |
8587.00 | San Francisco | Jan | Bill | White |
7373.00 | Los Angeles | Feb | Joe | White |
5607.00 | San Francisco | Jun | Joe | White |
11029.00 | San Francisco | Jan | Bill | White |
6262.00 | San Francisco | Mar | Bill | Organic |
9432.00 | San Diego | May | Bill | Organic |
6685.00 | San Diego | Apr | Joe | Organic |
7642.00 | Los Angeles | Mar | Bill | Organic |
9549.00 | San Diego | Mar | Jane | White |
9014.00 | San Diego | Feb | Bill | White |
7390.00 | San Francisco | May | Jane | Organic |
4670.00 | San Francisco | Jun | Joe | Organic |
6388.00 | Los Angeles | Apr | Bill | White |
6326.00 | Los Angeles | Feb | Joe | Organic |
10694.00 | Los Angeles | Jan | Joe | White |
8516.00 | Los Angeles | May | Bill | White |
7177.00 | San Diego | Apr | Bill | White |
6312.00 | Los Angeles | May | Joe | Organic |
5294.00 | San Francisco | Jan | Jane | White |
7879.00 | San Francisco | Mar | Jane | White |
6061.00 | San Diego | Jan | Jane | Organic |
7763.00 | San Francisco | Jun | Jane | White |
9248.00 | San Francisco | Apr | Joe | Organic |
10919.00 | San Francisco | Feb | Joe | White |
11498.00 | Los Angeles | Jan | Bill | White |
8950.00 | San Diego | May | Jane | White |
9133.00 | Los Angeles | Mar | Joe | White |
5437.00 | Los Angeles | Feb | Joe | White |
7914.00 | San Diego | Feb | Joe | White |
8274.00 | San Francisco | Mar | Bill | White |
3877.00 | San Diego | Jan | Jane | Organic |
Mean | 7546.42 | |||
Median | 7570.00 | |||
Mode | 7498 |
2.
Sales | City | Date | Sales Rep | Type Wine | Profit | |||
7451.00 | Los Angeles | Feb | Bill | Red | 894.12 | Row Labels | Sum of Profit | |
11221.00 | Los Angeles | Jun | Joe | Organic | 2356.41 | Organic | 34896.54 | |
9525.00 | San Francisco | Jun | Jane | Red | 1143 | Red | 35923.32 | |
3986.00 | San Diego | May | Jane | Organic | 837.06 | White | 51289.68 | |
11667.00 | San Diego | Apr | Bill | White | 1983.39 | Grand Total | 122109.54 | |
11649.00 | San Francisco | Mar | Jane | Red | 1397.88 | |||
9010.00 | San Diego | Feb | Jane | Red | 1081.2 | |||
5686.00 | Los Angeles | Jan | Joe | Red | 682.32 | |||
9121.00 | San Francisco | Mar | Jane | Organic | 1915.41 | |||
8703.00 | San Francisco | Apr | Jane | Red | 1044.36 | |||
4369.00 | San Diego | Feb | Jane | Organic | 917.49 | |||
5936.00 | Los Angeles | Feb | Bill | White | 1009.12 | |||
9990.00 | San Diego | May | Joe | Organic | 2097.9 | |||
5217.00 | San Diego | Apr | Bill | White | 886.89 | |||
5582.00 | San Francisco | Apr | Joe | Red | 669.84 | |||
7913.00 | Los Angeles | Jun | Jane | White | 1345.21 | |||
8581.00 | San Francisco | Jan | Joe | Red | 1029.72 | |||
7472.00 | San Diego | Feb | Bill | Red | 896.64 | |||
4716.00 | San Francisco | Feb | Joe | White | 801.72 | |||
3020.00 | San Diego | Jan | Jane | Organic | 634.2 | |||
11552.00 | San Francisco | Apr | Jane | Red | 1386.24 | |||
8507.00 | Los Angeles | Mar | Joe | White | 1446.19 | |||
8573.00 | San Francisco | Mar | Bill | White | 1457.41 | |||
4827.00 | San Diego | Jun | Jane | Red | 579.24 | |||
11146.00 | San Diego | Jun | Joe | Organic | 2340.66 | |||
10898.00 | San Diego | Apr | Jane | Red | 1307.76 | |||
10424.00 | San Francisco | Apr | Bill | White | 1772.08 | |||
6077.00 | Los Angeles | Apr | Bill | White | 1033.09 | |||
4908.00 | Los Angeles | Feb | Jane | White | 834.36 | |||
4652.00 | San Francisco | Jan | Jane | White | 790.84 | |||
7498.00 | San Diego | Apr | Joe | Organic | 1574.58 | |||
4641.00 | San Diego | Jun | Bill | White | 788.97 | |||
10440.00 | San Diego | Jan | Jane | White | 1774.8 | |||
4168.00 | San Diego | Mar | Joe | Red | 500.16 | |||
4031.00 | San Francisco | Feb | Bill | White | 685.27 | |||
4031.00 | San Francisco | Feb | Jane | White | 685.27 | |||
7498.00 | San Diego | Apr | Jane | Organic | 1574.58 | |||
8305.00 | San Diego | Feb | Jane | Red | 996.6 | |||
4788.00 | San Francisco | May | Jane | White | 813.96 | |||
11953.00 | San Diego | May | Joe | White | 2032.01 | |||
11482.00 | San Francisco | Jun | Jane | Red | 1377.84 | |||
11959.00 | San Diego | Jan | Jane | White | 2033.03 | |||
8681.00 | San Francisco | Jun | Jane | Organic | 1823.01 | |||
10399.00 | Los Angeles | Mar | Joe | Red | 1247.88 | |||
11310.00 | Los Angeles | Feb | Joe | Red | 1357.2 | |||
6981.00 | San Francisco | Jun | Jane | Organic | 1466.01 | |||
8758.00 | Los Angeles | May | Jane | Organic | 1839.18 | |||
9837.00 | San Diego | May | Bill | Red | 1180.44 | |||
4276.00 | San Francisco | Apr | Joe | White | 726.92 | |||
7515.00 | San Francisco | Apr | Bill | Red | 901.8 | |||
10497.00 | Los Angeles | May | Bill | Red | 1259.64 | |||
8587.00 | San Francisco | Jan | Bill | White | 1459.79 | |||
7373.00 | Los Angeles | Feb | Joe | White | 1253.41 | |||
8722.00 | San Francisco | Jan | Joe | Red | 1046.64 | |||
5607.00 | San Francisco | Jun | Joe | White | 953.19 | |||
11029.00 | San Francisco | Jan | Bill | White | 1874.93 | |||
6262.00 | San Francisco | Mar | Bill | Organic | 1315.02 | |||
9432.00 | San Diego | May | Bill | Organic | 1980.72 | |||
6685.00 | San Diego | Apr | Joe | Organic | 1403.85 | |||
3913.00 | San Diego | Jan | Joe | Red | 469.56 | |||
7642.00 | Los Angeles | Mar | Bill | Organic | 1604.82 | |||
9549.00 | San Diego | Mar | Jane | White | 1623.33 | |||
9014.00 | San Diego | Feb | Bill | White | 1532.38 | |||
7390.00 | San Francisco | May | Jane | Organic | 1551.9 | |||
3390.00 | Los Angeles | Feb | Bill | Red | 406.8 | |||
4670.00 | San Francisco | Jun | Joe | Organic | 980.7 | |||
8535.00 | Los Angeles | Jun | Jane | Red | 1024.2 | |||
9112.00 | San Francisco | May | Bill | Red | 1093.44 | |||
8197.00 | San Francisco | Jan | Joe | Red | 983.64 | |||
3287.00 | Los Angeles | Jan | Jane | Red | 394.44 | |||
6388.00 | Los Angeles | Apr | Bill | White | 1085.96 | |||
9887.00 | San Diego | Jan | Joe | Red | 1186.44 | |||
6326.00 | Los Angeles | Feb | Joe | Organic | 1328.46 | |||
10694.00 | Los Angeles | Jan | Joe | White | 1817.98 | |||
9779.00 | San Francisco | Jun | Bill | Red | 1173.48 | |||
10157.00 | San Francisco | May | Joe | Red | 1218.84 | |||
5286.00 | San Francisco | Jan | Joe | Red | 634.32 | |||
8516.00 | Los Angeles | May | Bill | White | 1447.72 | |||
7177.00 | San Diego | Apr | Bill | White | 1220.09 | |||
6312.00 | Los Angeles | May | Joe | Organic | 1325.52 | |||
5546.00 | San Diego | Feb | Bill | Red | 665.52 | |||
5294.00 | San Francisco | Jan | Jane | White | 899.98 | |||
7879.00 | San Francisco | Mar | Jane | White | 1339.43 | |||
6437.00 | San Francisco | Jun | Bill | Red | 772.44 | |||
6061.00 | San Diego | Jan | Jane | Organic | 1272.81 | |||
7763.00 | San Francisco | Jun | Jane | White | 1319.71 | |||
5761.00 | Los Angeles | Jun | Bill | Red | 691.32 | |||
9248.00 | San Francisco | Apr | Joe | Organic | 1942.08 | |||
10919.00 | San Francisco | Feb | Joe | White | 1856.23 | |||
11498.00 | Los Angeles | Jan | Bill | White | 1954.66 | |||
8950.00 | San Diego | May | Jane | White | 1521.5 | |||
9133.00 | Los Angeles | Mar | Joe | White | 1552.61 | |||
9332.00 | San Francisco | Apr | Bill | Red | 1119.84 | |||
5437.00 | Los Angeles | Feb | Joe | White | 924.29 | |||
7914.00 | San Diego | Feb | Joe | White | 1345.38 | |||
8559.00 | Los Angeles | May | Jane | Red | 1027.08 | |||
8274.00 | San Francisco | Mar | Bill | White | 1406.58 | |||
3877.00 | San Diego | Jan | Jane | Organic | 814.17 | |||
3407.00 | San Francisco | Jan | Bill | Red | 408.84 | |||
5605.00 | San Diego | Jun | Jane | Red | 672.6 |