Management science through spreadsheets

profilegilbertojijo
rawdata1.xlsx
Home>Business & Finance homework help>Operations Management homework help>Management science through spreadsheets

Data

Destination Pallets Total Cases Cases red Cases white Cases organic Week of Order Pallets (Bins)
Oakland CA 48 5376 2648.00 2648.00 80 1 72
Portland OR 48 5376 2438.00 2438.00 500 1 48
Portland OR 48 5376 2438.00 2438.00 500 1 24
Riverside CA 48 5376 2673.00 2673.00 30 1 18
Seattle WA 48 5376 2638.00 2638.00 100 1 12
Seattle WA 48 5376 2638.00 2638.00 100 1 6
Oakland CA 28 3136 1518.00 1518.00 100 1 3
Oakland CA 28 3136 1518.00 1518.00 100 1 1
Riverside CA 28 3136 1512.00 1512.00 112 1
Oakland CA 24 2688 1288.00 1288.00 112 1
Oakland CA 24 2688 1324.00 1324.00 40 1
Oakland CA 24 2688 1288.00 1288.00 112 1
Oakland CA 24 2688 1324.00 1324.00 40 1
Oakland CA 24 2688 1294.00 1294.00 100 1
Riverside CA 24 2688 1288.00 1288.00 112 1
Riverside CA 24 2688 1288.00 1288.00 112 1
Riverside CA 24 2688 1329.00 1329.00 30 1
Riverside CA 24 2688 1288.00 1288.00 112 1
Riverside CA 24 2688 1288.00 1288.00 112 1
Riverside CA 24 2688 1329.00 1329.00 30 1
Seattle WA 24 2688 1294.00 1294.00 100 1
Oakland CA 22 2464 1192.00 1192.00 80 1
Riverside CA 22 2464 1176.00 1176.00 112 1
Riverside CA 22 2464 1176.00 1176.00 112 1
Riverside CA 22 2464 1202.00 1202.00 60 1
Seattle WA 22 2464 1120.00 1120.00 224 1
Seattle WA 22 2464 1176.00 1176.00 112 1
Portland OR 12 1344 422.00 422.00 500 1
Portland OR 12 1344 616.00 616.00 112 1
Portland OR 11 1232 560.00 560.00 112 1
Portland OR 11 1232 560.00 560.00 112 1
Portland OR 11 1232 491.00 491.00 250 1
Oakland CA 3 336 112.00 112.00 112 1
Oakland CA 3 336 112.00 112.00 112 1
Oakland CA 3 336 118.00 118.00 100 1
Portland OR 3 336 93.00 93.00 150 1
Portland OR 3 336 168.00 168.00 0 1
Portland OR 3 336 112.00 112.00 112 1
Portland OR 3 336 118.00 118.00 100 1
Riverside CA 3 336 112.00 112.00 112 1
Riverside CA 3 336 112.00 112.00 112 1
Seattle WA 3 336 163.00 163.00 10 1
Seattle WA 3 336 56.00 56.00 224 1
Seattle WA 3 336 112.00 112.00 112 1
Seattle WA 3 336 56.00 56.00 224 1
Oakland CA 1 112 0.00 0.00 112 1
Portland OR 1 112 -69.00 -69.00 250 1
Seattle WA 1 112 53.00 53.00 6 1
Seattle WA 1 112 6.00 6.00 100 1
Seattle WA 1 112 51.00 51.00 10 1
Seattle WA 1 112 0.00 0.00 112 1
Oakland CA 48 5376 2588.00 2588.00 200 2
Oakland CA 48 5376 2638.00 2638.00 100 2
Oakland CA 48 5376 2632.00 2632.00 112 2
Oakland CA 48 5376 2638.00 2638.00 100 2
Oakland CA 48 5376 2632.00 2632.00 112 2
Portland OR 48 5376 2338.00 2338.00 700 2
Seattle WA 48 5376 2632.00 2632.00 112 2
Seattle WA 48 5376 2576.00 2576.00 224 2
Oakland CA 28 3136 1512.00 1512.00 112 2
Riverside CA 28 3136 1512.00 1512.00 112 2
Riverside CA 28 3136 1518.00 1518.00 100 2
Oakland CA 24 2688 1304.00 1304.00 80 2
Oakland CA 24 2688 1294.00 1294.00 100 2
Oakland CA 24 2688 1288.00 1288.00 112 2
Oakland CA 24 2688 1324.00 1324.00 40 2
Oakland CA 24 2688 1304.00 1304.00 80 2
Oakland CA 24 2688 1288.00 1288.00 112 2
Portland OR 24 2688 1294.00 1294.00 100 2
Riverside CA 24 2688 1329.00 1329.00 30 2
Riverside CA 24 2688 1288.00 1288.00 112 2
Riverside CA 24 2688 1329.00 1329.00 30 2
Riverside CA 24 2688 1329.00 1329.00 30 2
Riverside CA 24 2688 1288.00 1288.00 112 2
Riverside CA 24 2688 1288.00 1288.00 112 2
Seattle WA 24 2688 1288.00 1288.00 112 2
Seattle WA 24 2688 1232.00 1232.00 224 2
Seattle WA 24 2688 1294.00 1294.00 100 2
Seattle WA 24 2688 1288.00 1288.00 112 2
Seattle WA 24 2688 1232.00 1232.00 224 2
Seattle WA 24 2688 1232.00 1232.00 224 2
Seattle WA 24 2688 1294.00 1294.00 100 2
Seattle WA 24 2688 1339.00 1339.00 10 2
Seattle WA 24 2688 1304.00 1304.00 80 2
Portland OR 22 2464 1182.00 1182.00 100 2
Riverside CA 22 2464 1192.00 1192.00 80 2
Riverside CA 22 2464 1176.00 1176.00 112 2
Riverside CA 22 2464 1202.00 1202.00 60 2
Portland OR 12 1344 622.00 622.00 100 2
Portland OR 12 1344 616.00 616.00 112 2
Portland OR 11 1232 491.00 491.00 250 2
Portland OR 11 1232 601.00 601.00 30 2
Portland OR 3 336 163.00 163.00 10 2
Portland OR 3 336 43.00 43.00 250 2
Portland OR 3 336 148.00 148.00 40 2
Portland OR 3 336 43.00 43.00 250 2
Riverside CA 3 336 112.00 112.00 112 2
Riverside CA 3 336 153.00 153.00 30 2
Oakland CA 1 112 26.00 26.00 60 2
Oakland CA 1 112 16.00 16.00 80 2
Portland OR 1 112 26.00 26.00 60 2
Portland OR 1 112 51.00 51.00 10 2
Seattle WA 1 112 26.00 26.00 60 2
Portland OR 48 5376 2638.00 2638.00 100 3
Riverside CA 48 5376 2632.00 2632.00 112 3
Riverside CA 48 5376 2632.00 2632.00 112 3
Seattle WA 28 3136 1518.00 1518.00 100 3
Oakland CA 24 2688 1219.00 1219.00 250 3
Riverside CA 24 2688 1219.00 1219.00 250 3
Riverside CA 24 2688 1314.00 1314.00 60 3
Riverside CA 24 2688 1288.00 1288.00 112 3
Riverside CA 22 2464 1182.00 1182.00 100 3
Portland OR 12 1344 616.00 616.00 112 3
Portland OR 12 1344 547.00 547.00 250 3
Portland OR 12 1344 642.00 642.00 60 3
Portland OR 12 1344 547.00 547.00 250 3
Riverside CA 3 336 153.00 153.00 30 3
Riverside CA 1 112 41.00 41.00 30 3
Riverside CA 1 112 16.00 16.00 80 3
Portland OR 72 8064 3732.00 3732.00 600 4
Riverside CA 48 5376 2673.00 2673.00 30 4
Riverside CA 48 5376 2632.00 2632.00 112 4
Riverside CA 28 3136 1538.00 1538.00 60 4
Portland OR 24 2688 1288.00 1288.00 112 4
Riverside CA 24 2688 1288.00 1288.00 112 4
Riverside CA 24 2688 1314.00 1314.00 60 4
Riverside CA 24 2688 1288.00 1288.00 112 4
Oakland CA 22 2464 1171.00 1171.00 122 4
Oakland CA 20 2240 1090.00 1090.00 60 4
Portland OR 12 1344 572.00 572.00 200 4
Portland OR 12 1344 547.00 547.00 250 4
Seattle WA 3 336 118.00 118.00 100 4
Portland OR 1 112 6.00 6.00 100 4
Riverside CA 1 112 51.00 51.00 10 4
Riverside CA 1 112 0.00 0.00 112 4
Portland OR 48 5376 2638.00 2638.00 100 5
Oakland CA 24 2688 1339.00 1339.00 10 5
Riverside CA 24 2688 1229.00 1229.00 230 5
Seattle WA 1 112 31.00 31.00 50 5
Oakland CA 48 5376 2538.00 2538.00 300 6
Seattle WA 24 2688 1219.00 1219.00 250 6
Riverside CA 22 2464 1192.00 1192.00 80 6
Portland OR 3 336 143.00 143.00 50 6
Seattle WA 28 3136 1538.00 1538.00 60 7
Oakland CA 24 2688 1294.00 1294.00 100 7
Riverside CA 24 2688 1294.00 1294.00 100 7
Portland OR 12 1344 547.00 547.00 250 7
Riverside CA 48 5376 2658.00 2658.00 60 7
Seattle WA 48 5376 2576.00 2576.00 224 7
Seattle WA 48 5376 2632.00 2632.00 112 7
Oakland CA 28 3136 1512.00 1512.00 112 7
Oakland CA 28 3136 1512.00 1512.00 112 7
Riverside CA 28 3136 1512.00 1512.00 112 7
Seattle WA 28 3136 1456.00 1456.00 224 7
Seattle WA 28 3136 1518.00 1518.00 100 7
Oakland CA 24 2688 1304.00 1304.00 80 7
Oakland CA 24 2688 1294.00 1294.00 100 7
Oakland CA 24 2688 1304.00 1304.00 80 7
Oakland CA 24 2688 1294.00 1294.00 100 7
Oakland CA 24 2688 1294.00 1294.00 100 8
Oakland CA 24 2688 1324.00 1324.00 40 8
Oakland CA 24 2688 1288.00 1288.00 112 8
Portland OR 24 2688 1294.00 1294.00 100 8
Portland OR 24 2688 1288.00 1288.00 112 8
Portland OR 24 2688 1288.00 1288.00 112 8
Riverside CA 24 2688 1288.00 1288.00 112 8
Riverside CA 24 2688 1288.00 1288.00 112 8
Riverside CA 24 2688 1294.00 1294.00 100 8
Seattle WA 24 2688 1339.00 1339.00 10 8
Seattle WA 24 2688 1294.00 1294.00 100 8
Seattle WA 24 2688 1288.00 1288.00 112 8
Seattle WA 24 2688 1288.00 1288.00 112 9
Oakland CA 22 2464 1125.00 1125.00 214 9
Portland OR 22 2464 1182.00 1182.00 100 9
Seattle WA 22 2464 1197.00 1197.00 70 9
Seattle WA 22 2464 1176.00 1176.00 112 9
Seattle WA 22 2464 1176.00 1176.00 112 9
Seattle WA 22 2464 1176.00 1176.00 112 9
Portland OR 12 1344 547.00 547.00 250 9
Oakland CA 3 336 112.00 112.00 112 9
Riverside CA 3 336 112.00 112.00 112 9
Oakland CA 1 112 16.00 16.00 80 9
Oakland CA 1 112 16.00 16.00 80 9
Portland OR 1 112 26.00 26.00 60 9
Portland OR 1 112 0.00 0.00 112 9
Portland OR 1 112 0.00 0.00 112 9
Riverside CA 1 112 36.00 36.00 40 9
Riverside CA 48 5376 2488.00 2488.00 400 9
Portland OR 24 2688 1319.00 1319.00 50 9
Seattle WA 24 2688 1324.00 1324.00 40 9
Oakland CA 3 336 118.00 118.00 100 9
Portland OR 28 3136 1512.00 1512.00 112 10
Oakland CA 24 2688 1288.00 1288.00 112 10
Riverside CA 24 2688 1194.00 1194.00 300 10
Seattle WA 1 112 36.00 36.00 40 10
Oakland CA 48 5376 2632.00 2632.00 112 11
Seattle WA 24 2688 1288.00 1288.00 112 11
Riverside CA 22 2464 1176.00 1176.00 112 11
Portland OR 3 336 138.00 138.00 60 11
Oakland CA 28 3136 1563.00 1563.00 10 12
Riverside CA 24 2688 1344.00 1344.00 0 12
Seattle WA 22 2464 1182.00 1182.00 100 12
Portland OR 1 112 0.00 0.00 112 12
Riverside CA 48 5376 2563.00 2563.00 250 13
Seattle WA 24 2688 1304.00 1304.00 80 13
Portland OR 12 1344 616.00 616.00 112 13
Oakland CA 3 336 118.00 118.00 100 13
Oakland CA 48 5376 2576.00 2576.00 224 14
Seattle WA 24 2688 1288.00 1288.00 112 14
Riverside CA 22 2464 1182.00 1182.00 100 14
Portland OR 3 336 153.00 153.00 30 14
Seattle WA 28 3136 1548.00 1548.00 40 15
Oakland CA 24 2688 1288.00 1288.00 112 15
Portland OR 24 2688 1144.00 1144.00 400 15
Riverside CA 24 2688 1288.00 1288.00 112 15
Oakland CA 22 2464 1176.00 1176.00 112 16
Portland OR 12 1344 642.00 642.00 60 16
Seattle WA 3 336 112.00 112.00 112 16
Riverside CA 1 112 26.00 26.00 60 16
Portland OR 28 3136 1512.00 1512.00 112 17
Oakland CA 24 2688 1339.00 1339.00 10 17
Riverside CA 24 2688 1288.00 1288.00 112 17
Seattle WA 1 112 31.00 31.00 50 17
Oakland CA 48 5376 2638.00 2638.00 100 18
Seattle WA 24 2688 1304.00 1304.00 80 18
Riverside CA 22 2464 1107.00 1107.00 250 18
Portland OR 3 336 112.00 112.00 112 18
Portland OR 72 8064 3532.00 3532.00 1000 19
Seattle WA 28 3136 1512.00 1512.00 112 19
Oakland CA 24 2688 1232.00 1232.00 224 19
Riverside CA 24 2688 1294.00 1294.00 100 19
Oakland CA 22 2464 1176.00 1176.00 112 20
Seattle WA 22 2464 1212.00 1212.00 40 20
Portland OR 1 112 0.00 0.00 112 20
Riverside CA 1 112 0.00 0.00 112 20
Riverside CA 48 5376 2632.00 2632.00 112 21
Portland OR 24 2688 1314.00 1314.00 60 21
Seattle WA 24 2688 1288.00 1288.00 112 21
Oakland CA 3 336 112.00 112.00 112 21
Oakland CA 24 2688 1339.00 1339.00 10 22
Riverside CA 24 2688 1344.00 1344.00 0 22
Seattle WA 22 2464 1182.00 1182.00 100 22
Portland OR 3 336 112.00 112.00 112 22
Riverside CA 48 5376 2563.00 2563.00 250 23
Seattle WA 24 2688 1304.00 1304.00 80 23
Oakland CA 3 336 118.00 118.00 100 23
Portland OR 3 336 112.00 112.00 112 23
Portland OR 28 3136 1553.00 1553.00 30 24
Oakland CA 24 2688 1232.00 1232.00 224 24
Riverside CA 24 2688 1294.00 1294.00 100 24
Seattle WA 1 112 16.00 16.00 80 24
Oakland CA 48 5376 2638.00 2638.00 100 25
Seattle WA 24 2688 1294.00 1294.00 100 25
Riverside CA 22 2464 1176.00 1176.00 112 25
Portland OR 3 336 43.00 43.00 250 25
Oakland CA 28 3136 1528.00 1528.00 80 26
Riverside CA 24 2688 1288.00 1288.00 112 26
Seattle WA 22 2464 1120.00 1120.00 224 26
Portland OR 1 112 6.00 6.00 100 26
Riverside CA 48 5376 2673.00 2673.00 30 27
Portland OR 24 2688 1288.00 1288.00 112 27
Seattle WA 24 2688 1288.00 1288.00 112 27
Oakland CA 3 336 112.00 112.00 112 27
Oakland CA 24 2688 1324.00 1324.00 40 28
Portland OR 24 2688 1288.00 1288.00 112 28
Riverside CA 24 2688 1288.00 1288.00 112 28
Seattle WA 1 112 0.00 0.00 112 28
Seattle WA 48 5376 2683.00 2683.00 10 29
Portland OR 22 2464 1232.00 1232.00 0 29
Riverside CA 22 2464 1202.00 1202.00 60 29
Oakland CA 1 112 0.00 0.00 112 29
Oakland CA 24 2688 1294.00 1294.00 100 30
Seattle WA 24 2688 1294.00 1294.00 100 30
Portland OR 3 336 43.00 43.00 250 30
Riverside CA 3 336 112.00 112.00 112 30
Oakland CA 28 3136 1528.00 1528.00 80 31
Portland OR 24 2688 1294.00 1294.00 100 31
Riverside CA 24 2688 1288.00 1288.00 112 31
Seattle WA 24 2688 1232.00 1232.00 224 31
Seattle WA 48 5376 2632.00 2632.00 112 32
Riverside CA 28 3136 1553.00 1553.00 30 32
Portland OR 22 2464 1176.00 1176.00 112 32
Oakland CA 1 112 0.00 0.00 112 32
Oakland CA 24 2688 1324.00 1324.00 40 33
Seattle WA 24 2688 1288.00 1288.00 112 33
Portland OR 3 336 112.00 112.00 112 33
Riverside CA 3 336 112.00 112.00 112 33
Oakland CA 28 3136 1512.00 1512.00 112 34
Riverside CA 24 2688 1314.00 1314.00 60 34
Seattle WA 22 2464 1227.00 1227.00 10 34
Portland OR 1 112 56.00 56.00 0 34
Portland OR 48 5376 2563.00 2563.00 250 35
Oakland CA 22 2464 1182.00 1182.00 100 35
Seattle WA 3 336 118.00 118.00 100 35
Riverside CA 1 112 26.00 26.00 60 35
Oakland CA 24 2688 1304.00 1304.00 80 36
Portland OR 24 2688 1294.00 1294.00 100 36
Riverside CA 24 2688 1288.00 1288.00 112 36
Seattle WA 24 2688 1232.00 1232.00 224 36
Portland OR 72 8064 3532.00 3532.00 1000 37
Riverside CA 28 3136 1553.00 1553.00 30 37
Oakland CA 22 2464 1176.00 1176.00 112 37
Seattle WA 3 336 118.00 118.00 100 37
Oakland CA 24 2688 1294.00 1294.00 100 38
Portland OR 24 2688 1288.00 1288.00 112 38
Riverside CA 24 2688 1219.00 1219.00 250 38
Seattle WA 24 2688 1304.00 1304.00 80 38
Seattle WA 48 5376 2632.00 2632.00 112 39
Riverside CA 28 3136 1518.00 1518.00 100 39
Portland OR 12 1344 657.00 657.00 30 39
Oakland CA 1 112 13.00 13.00 86 39
Seattle WA 28 3136 1548.00 1548.00 40 40
Oakland CA 24 2688 1288.00 1288.00 112 40
Portland OR 12 1344 616.00 616.00 112 40
Riverside CA 3 336 112.00 112.00 112 40
Portland OR 72 8064 3732.00 3732.00 600 41
Seattle WA 48 5376 2632.00 2632.00 112 41
Oakland CA 22 2464 1176.00 1176.00 112 41
Riverside CA 1 112 16.00 16.00 80 41
Oakland CA 24 2688 1339.00 1339.00 10 42
Portland OR 24 2688 1288.00 1288.00 112 42
Seattle WA 24 2688 1294.00 1294.00 100 42
Riverside CA 3 336 168.00 168.00 0 42
Oakland CA 28 3136 1518.00 1518.00 100 43
Riverside CA 24 2688 1219.00 1219.00 250 43
Seattle WA 22 2464 1192.00 1192.00 80 43
Portland OR 1 112 0.00 0.00 112 43
Portland OR 72 8064 3832.00 3832.00 400 44
Riverside CA 48 5376 2638.00 2638.00 100 44
Seattle WA 24 2688 1288.00 1288.00 112 44
Oakland CA 3 336 56.00 56.00 224 44
Oakland CA 48 5376 2576.00 2576.00 224 45
Portland OR 48 5376 2632.00 2632.00 112 45
Seattle WA 48 5376 2668.00 2668.00 40 45
Oakland CA 28 3136 1512.00 1512.00 112 45
Oakland CA 28 3136 1548.00 1548.00 40 45
Riverside CA 28 3136 1512.00 1512.00 112 45
Seattle WA 28 3136 1456.00 1456.00 224 45
Seattle WA 28 3136 1518.00 1518.00 100 45
Seattle WA 28 3136 1518.00 1518.00 100 45
Oakland CA 24 2688 1288.00 1288.00 112 45
Portland OR 24 2688 1288.00 1288.00 112 45
Portland OR 24 2688 1288.00 1288.00 112 45
Portland OR 24 2688 1288.00 1288.00 112 45
Riverside CA 24 2688 1288.00 1288.00 112 45
Seattle WA 24 2688 1339.00 1339.00 10 45
Oakland CA 22 2464 1176.00 1176.00 112 45
Oakland CA 22 2464 1192.00 1192.00 80 45
Portland OR 22 2464 1176.00 1176.00 112 45
Seattle WA 22 2464 1176.00 1176.00 112 45
Oakland CA 3 336 148.00 148.00 40 45
Oakland CA 3 336 148.00 148.00 40 45
Portland OR 3 336 153.00 153.00 30 45
Oakland CA 1 112 0.00 0.00 112 45
Portland OR 1 112 6.00 6.00 100 45
Portland OR 1 112 56.00 56.00 0 45
Riverside CA 1 112 41.00 41.00 30 45
Riverside CA 1 112 0.00 0.00 112 45
Riverside CA 1 112 0.00 0.00 112 45
Oakland CA 24 2688 1288.00 1288.00 112 46
Portland OR 24 2688 1314.00 1314.00 60 46
Seattle WA 22 2464 1176.00 1176.00 112 46
Riverside CA 3 336 112.00 112.00 112 46
Portland OR 48 5376 2663.00 2663.00 50 47
Riverside CA 48 5376 2688.00 2688.00 0 47
Portland OR 24 2688 1288.00 1288.00 112 47
Riverside CA 24 2688 1288.00 1288.00 112 47
Seattle WA 24 2688 1294.00 1294.00 100 47
Seattle WA 24 2688 1339.00 1339.00 10 47
Oakland CA 3 336 163.00 163.00 10 47
Portland OR 48 5376 2632.00 2632.00 112 48
Oakland CA 24 2688 1294.00 1294.00 100 48
Riverside CA 24 2688 1219.00 1219.00 250 48
Seattle WA 24 2688 1304.00 1304.00 80 48
Oakland CA 48 5376 2576.00 2576.00 224 49
Seattle WA 48 5376 2632.00 2632.00 112 49
Oakland CA 24 2688 1288.00 1288.00 112 49
Seattle WA 24 2688 1288.00 1288.00 112 49
Riverside CA 22 2464 1182.00 1182.00 100 49
Portland OR 3 336 153.00 153.00 30 49
Riverside CA 3 336 138.00 138.00 60 49
Oakland CA 28 3136 1518.00 1518.00 100 50
Seattle WA 28 3136 1518.00 1518.00 100 50
Oakland CA 24 2688 1294.00 1294.00 100 50
Portland OR 24 2688 1288.00 1288.00 112 50
Riverside CA 24 2688 1288.00 1288.00 112 50
Riverside CA 24 2688 1288.00 1288.00 112 50
Seattle WA 24 2688 1288.00 1288.00 112 50
Seattle WA 24 2688 1288.00 1288.00 112 50
Seattle WA 24 2688 1288.00 1288.00 112 50
Oakland CA 22 2464 1176.00 1176.00 112 50
Oakland CA 22 2464 1192.00 1192.00 80 50
Oakland CA 22 2464 1192.00 1192.00 80 50
Riverside CA 22 2464 1176.00 1176.00 112 50
Seattle WA 22 2464 1182.00 1182.00 100 50
Seattle WA 22 2464 1120.00 1120.00 224 50
Seattle WA 22 2464 1227.00 1227.00 10 50
Seattle WA 3 336 112.00 112.00 112 50
Oakland CA 1 112 36.00 36.00 40 50
Portland OR 1 112 31.00 31.00 50 50
Riverside CA 48 5376 2632.00 2632.00 112 51
Portland OR 24 2688 1294.00 1294.00 100 51
Seattle WA 24 2688 1232.00 1232.00 224 51
Oakland CA 3 336 128.00 128.00 80 51
Portland OR 72 8064 3582.00 3582.00 900 52
Seattle WA 48 5376 2632.00 2632.00 112 52
Oakland CA 24 2688 1288.00 1288.00 112 52
Riverside CA 24 2688 1329.00 1329.00 30 52
8728 988288

Costs&Distances

Destination Miles Truck Cost per mile Fronthaul cost % Deadhead return Return to Lodi costs Total FTL Shipping cost Cost per pallet in a FTL Cost per bottle in a FTL RT time/days
Riverside CA 410 $2.15 $881.50 50% $440.75 $1,322.25 $55.09 $0.08 1.6
Oakland CA 80 $3.00 $240.00 100% $240.00 $480.00 $20.00 $0.03 0.3
Portland OR 620 $2.15 $1,333.00 30% $399.90 $1,732.90 $72.20 $0.11 2.5
Seattle WA 800 $2.15 $1,720.00 30% $516.00 $2,236.00 $93.17 $0.14 3.2
1 pallets = 112 cases
FTL = 24 pallets
1 Case = 6 bottles
Destination Cost to ship one pallet Cost per bottle
Riverside CA $500.00 $0.74
Oakland CA $200.00 $0.30
Portland OR $600.00 $0.89
Seattle WA $800.00 $1.19
bottle Red bottle White bottle Organic case Red case White case Organic
Whole sale price $7.50 $8.00 $12.00 $45.00 $48.00 $72.00
Product cost $2.40 $3.40 $6.30 $14.40 $20.40 $37.80
State taxes Based on Profit
CA 8.8%
OR 6.6%

M1 A-E

Milestone 1
A: Drawing upon the data in the data tab, create a pivot table and a pie chart that shows the total percentage of wine bought by each distributor. The pivot table and pie chart will populate on a new tab; name this tab M1A.
State the rationale you used to create the table and pie chart
Hint:
Create a pivot table using the data spreadsheet as its basis
Make sure you select the select the Pivot table field list options
B: Using a pivot table show wine distribution by variety and distributor and provide a bar chart. The pivot table will populate in a new tab; name this tab M1B
State the rationale you used
C: Create a new tab named M1C. Create a table and bar chart to show revenue by distributor and wine variety
State the rationale you used
Hints:
We are looking at revenue generated and not profit in this problem
Production cost data is provided in the Data tab
Make sure you don't mix your units of measurement (pallets, or cases, or bottles)
D: Show Central Tendency of the shipments to each distributor (use the IF Function to select the data to be used) show in a table
State the rationale you used
Hint: Do not use a pivot table or manually identify each cell to be evaluated
Mean pallet shipment Median pallet shipment Mode pallet shipment
Oakland CA
Portland OR
Riverside CA
Seattle WA
E: Analyze frequency of size of shipment sizes using a histogram with the following Bin sizes (number of pallets). The histogram will generate on a new tab; name this tab M1E
Pallets
72
48
24
18
12
6
3
1
In your response, be sure to use the histogram located in the data analysis tool pack add on.
State the rationale you used
F: Create shipment histograms for Portland and Riverside using the same Bin sizes (as used in E). The histogram will generate on a new tab; name this tab M1E
State the rationale you used
Hint
Use alphabetical sort for destination column select Data Analysis to plot the frequency of pallets shipments using the bin sizes listed for the two destinations separately
G. Provide a summary statement below that describes the inefficiencies in the organizational sales analysis. In your response, explain why this information is important for influencing management decisions.

M2 A-E

Milestone 2
A: Create a spreadsheets that calculate the costs of shipping to Portland and Riverside by pallets based on the frequency distribution used in the Histograms used in Milestone 1 (Link the cost data to the data in the Costs&Distances tab).
State the rationale you used
Size of shipment in pallets 1 3 6 12 18 24 48 72
Tranport cost to Portland
cost per pallet
Frequency
Cost of shipments
Size of shipment in pallets 1 3 6 12 18 24 48 72
Tranport cost to Riverside
cost per pallet
Frequency
Cost of shipments
B: Create a table that calculates the production costs of the wines sold to Portand and Riverside. Hints: Link the cost data to the data in the Costs&Distances tab. Use a pivot table and some additional programing.
State the rationale you used
Red White Organic Total
Portland
Riverside
C: Create a table that calculates Gross Profit and Gross Profit as a percentage of revenue
Gross Profit = Revenue (from Milestone One) minus Transport (from part A) & Production (from part B) from Portland and Riverside
State the rationale you used
Total revenue Transport Production Gross profit %GP/R
Portland
Riverside
Gross Profit Total For Each Variety of Wine by DC
DC Wine Type Revenue Transport COGS Gross profit %GP/R
Portland Red
White
Organic
Gross Profit Total For Each Variety of Wine by DC
DC Wine Type Revenue Transport COGS Gross profit %GP/R
Riverside Red
White
Organic
D: Create a table that calculates Gross Profit (from part C) minus state taxes (from the Costs&Distances tab)
State the rationale you used
State tax rate Gross Profit State Tax Profit After
Portland
Riverside
E. Provide a summary statement that describes the inefficiencies in the organizational cost and profit analysis and explain why this information is important for influencing management decisions.

FS A-B

III. Optimizing Performance
For the Lodi Winery, you have been asked by management to examine the data collected and analyzed in the previous modules. The objective is for you to help management decide on the right mix of wine bottles to sell based on newly derived profit information while considering the limitations of the particular types of grapes available for production. While doing more research on wine production, you realize that it takes 3.5 pounds of grapes to make a bottle of wine. In addition, you already were provided the price per bottle that the distributors are paying for each variety of wine:
Price for Red Wine ($) Price for White Wine ($) Price for Organic Wine ($)
7.5 8 12
After discussing wine production with the operations manager, you also learn that the wineries that supply the grapes to produce the above types of wine can produce up to a total of 200,000 pounds of grapes for a six-month supply of wine bottles for the three markets, with the following expected distribution based on types of grapes:
Red wine ceiling 22,000 bottles
White wine ceiling 24,000 bottles
Organic wine ceiling 12,000 bottles
Note that the production cost per bottle remains the same as before, that is, 32% of sales or revenue for red wine, 42.5% of sales for white wine, and 52.5% for organic wine. With additional information you have gathered, you are now ready to determine the optimum production mix to maximize profit.