ASSUMPTIONS:

Annual demand is twice the half year demand. Average inventory for a given product can be calculated as the safety stock plus half of the order quantity (i.e., I a v g = S S + Q 2). Customer service level with the current policy will be equal to the probability of demand over the lead time not being greater than the amount of inventory on hand between placing an order and receiving that order (i.e., reorder point). You can calculate the service level for a given product using Excels NORM.DIST(Reorder point, Mean demand over the lead time, Std. Dev. Of demand over the lead time,1) function. Mean demand over lead time = L x daily demand. Total Annual Inventory Cost = D Q S + I a v g H. In the Fixed Time Period model, assume that the on-hand inventory during the review is always equal to the safety stock. The annual inventory savings due to decreasing inventory include two parts: (1) the decrease in the total annual inventory management cost and (2) the opportunity of having additional cash flow that would otherwise be used to have more inventory (i.e., opportunity cost). The Opportunity cost of money for EMMICO is 5%. For instance, if the annual inventory value decreases by $100, the company saves $5 per year due to the opportunity cost. Use the ABC classification provided in the dataset. Do not round any calculation.

Here's the dataset:

1. What is the total average inventory across 37 parts? (5 points)
2. What is the value of total average inventory? (5 points)
3. What is the average customer service level across 37 parts? (5 points)
4. What is the total annual inventory cost (i.e., holding and ordering costs) to manage the inventory for 37 parts? (5 points)

Working Current safety stock Sw 5,owl 485 5,742 #ml Days in Total demand Stdev May 1st half in 1st half Mean Daily Monthly Stdev Daily Material Material description ABC Jan Demand Feb Demand Mar Demand Apr Demand Demand Jun Demand year year Demand Demand Demand Lead time Average Price 24223,07housing 24,897 21,799 23,916 26,85935,729 40,269 119 1 73,469 1,458 1,367 4. 126765,02 housing 4,8275 ,173 6,742 4,965 7,8747,35311937,534 3161,265285 10 2.62 1801.086asket 257,136 251,961 183,161 245,849 331,020 311,249 119 1,580,376 13,355 52,415 11,77o 5 0.05 121449.0 5ealing body A RE,297 RE,552 79,183 10E,641 60,224 9 8,930 119 5 17,827 4,352 1,149 3,627 5 0.16 j3470.05 5 ealing body A 37,7B9 88,381 83,958 B9,822 63,166 68,577 119 4 31,603 3,628 19,946| 4479 0.17 %D2080.00 quad-ring 188,374 229,268 1 77,93 198,870 124,110 8 8,878 119 1 ,0FD6,593 8,459 51,751 11,621 5 0.04 710625.xDEGRWave 3j 3] d 119 6 155 48 45.58 on2540,02Housing 3,5] 250 250 1,560 250 5 1, 1 19 7,310 621,282 2B8 5 1.38 114433.8Do-ring 41,4x4,2 EPDMc 3,316 4,355 5,016 3,244 11g 15,932 1342,162 13 0.5 id2765.05 Ring 1@o2.2 EPUM 70c 46,083 58,055 51,728 50,55360,451 6 1,118 119 327,988 2,7576,C361.369 .02 112126.00 vawe cap 27,336 29,7x5 28,962 33,858 29,358 3 4,236 119 183,450 1,5422,812 632 5 0.4 002138.03 Rubber ring 28,406 30,125 ,304 8,711 26,884 15,885 119 1 15,315 97 10,745 2,413 5 0.06 120740.025eal 36,8Xj 55,229 123,337 70,925 7 24,310 11 3 12,4x1 2,52E 42,625 9,572 5 0.02 11x26,0 thermostate 250 11 250 3 103 s 23.87 002385.05 sealing body 15,113 27,%D7 15,543 1,028 11,777 26,696 119 103,164 8678,079 1,815 5 0.05 120577.00 thermostate 498 119 498 s 204 10.56 114097.00 thermostate E ] 114 Exp 327 5.61 123ot]8,0 bracket 4,096 1,587 2,8144,2166,511 119 26,595 2241.319 297 5 0. 16 ios309.00 sphere d3,5 1.3541c 180,572 87,353 B8,370 10,947 61,349134,130 11g 592,121 4,981 18,976 4,261 E 0.0 b2c85.000-ring 60x3,5 NBR c 14,511 8,058 22,644 16,4607,615 5,833 75,121 6336,48 1,46) 5 0.05 122826.j thermostate 0 24 24 102 23 001 19738.03 eal 18,0Xj ,4%D 24, 12.2018,3 12,00119 3,9] 735,414 1,216 15 0.04 21268.0 seal 2045 3,302 2,5OR 1,744 12,334 1C411 205 15 . 0.28 003658.02 Gasket C 1,440 4,225 351 3,7772.86 3,555119 18,914 1573 $ 017 12026.01 bwpa5s plate 21,431 27,413 31,872 22,3928,352 5,591 119 117,051 984 10,44E 2,345 5 0.03 114432.0 5eal 3,316 4,35E 4,397 3,18 1011g 1525 1292,031 457 5 ,18 124195.j thermostate 50 119 250 3 103 1 5 10.72 114634.2 0-ring 39,23x1,98 EPDc 5,6] 8,000 9,600 5,607,20 1.2x1 1g 43,20 3641,518 341 5 0.0E 03E49,0 sp\got 3,222 726 1.2x E,141 521,305 24 s 0.39 on254, 0 6asket 491 2,559 2.549119 5,702 48 1,296 0.19 1203g9, 0 asket 585 3.52E 4,324 875 470 1,625 11,405 96 1,639 15 0.0 118938.xj5eal 553 119 653 6 267 5 0,92 118939.] 5eal d 653 11 653 6 267 5 0.66 p3561.020-ring 474 1,28 0 0 2,392 119 4,7 3 1,057 238 5 0.OE p2523.00 Gasket 1,178 683 119 1,861 1650E 114 S 0.11 %D2898.d] 6asket 610 00 119 610 6 250 8 . 15 p3595.00 Gasket 2,4cj 0 1,102 -2,538 1 19 1,629 366 15 0.07 5,372 Current Order Current Quantity ROP (COQ) 7,0x0 14,29) ,x 4,&x17 ,96) 1,716 BOD,0% D 866,825 60.cl 40, 61,760 4@ya 60,00 78,140 60,0xxa &0, D 122,295 20,ooL 1 ] 488 3L 75 1,060 48) 5,000 12,5] 26,285 60,Ocz 6,00 13,710 4,Gol 30,Gyj 3 4,850 75,Dol 48,000 61,130 3D,al 25 15,000 1 9,335 50,yal 475 2sol 770 D5 2.3.4 3,424 4x1 12,xx@ 144,905 40,Gol 13,10] 16,265 20,xxa 1,0 1015 3g 6,0x0 1 7,850 12,5a 2015 3,576 20xo 2,4] 3,195 8,xpL 3,240 8,760 30,Gol 4,xx] 4,545 10,xxa 275 250 16, 17,820 12,xx 750 1010 5,gooL 2.EX] 2_240 6,Oca 3,10x2 4,540 32.5x 70 739 25L 709 73 2sal 2,52 2,575 38 1,00 1, 0 4,Ca[ 20Xj 248 1xx 3,30 3,435 1,Oca | Rg a P| 119 119 2058 576 29 119 292 s 964 Working Current safety stock Sw 5,owl 485 5,742 #ml Days in Total demand Stdev May 1st half in 1st half Mean Daily Monthly Stdev Daily Material Material description ABC Jan Demand Feb Demand Mar Demand Apr Demand Demand Jun Demand year year Demand Demand Demand Lead time Average Price 24223,07housing 24,897 21,799 23,916 26,85935,729 40,269 119 1 73,469 1,458 1,367 4. 126765,02 housing 4,8275 ,173 6,742 4,965 7,8747,35311937,534 3161,265285 10 2.62 1801.086asket 257,136 251,961 183,161 245,849 331,020 311,249 119 1,580,376 13,355 52,415 11,77o 5 0.05 121449.0 5ealing body A RE,297 RE,552 79,183 10E,641 60,224 9 8,930 119 5 17,827 4,352 1,149 3,627 5 0.16 j3470.05 5 ealing body A 37,7B9 88,381 83,958 B9,822 63,166 68,577 119 4 31,603 3,628 19,946| 4479 0.17 %D2080.00 quad-ring 188,374 229,268 1 77,93 198,870 124,110 8 8,878 119 1 ,0FD6,593 8,459 51,751 11,621 5 0.04 710625.xDEGRWave 3j 3] d 119 6 155 48 45.58 on2540,02Housing 3,5] 250 250 1,560 250 5 1, 1 19 7,310 621,282 2B8 5 1.38 114433.8Do-ring 41,4x4,2 EPDMc 3,316 4,355 5,016 3,244 11g 15,932 1342,162 13 0.5 id2765.05 Ring 1@o2.2 EPUM 70c 46,083 58,055 51,728 50,55360,451 6 1,118 119 327,988 2,7576,C361.369 .02 112126.00 vawe cap 27,336 29,7x5 28,962 33,858 29,358 3 4,236 119 183,450 1,5422,812 632 5 0.4 002138.03 Rubber ring 28,406 30,125 ,304 8,711 26,884 15,885 119 1 15,315 97 10,745 2,413 5 0.06 120740.025eal 36,8Xj 55,229 123,337 70,925 7 24,310 11 3 12,4x1 2,52E 42,625 9,572 5 0.02 11x26,0 thermostate 250 11 250 3 103 s 23.87 002385.05 sealing body 15,113 27,%D7 15,543 1,028 11,777 26,696 119 103,164 8678,079 1,815 5 0.05 120577.00 thermostate 498 119 498 s 204 10.56 114097.00 thermostate E ] 114 Exp 327 5.61 123ot]8,0 bracket 4,096 1,587 2,8144,2166,511 119 26,595 2241.319 297 5 0. 16 ios309.00 sphere d3,5 1.3541c 180,572 87,353 B8,370 10,947 61,349134,130 11g 592,121 4,981 18,976 4,261 E 0.0 b2c85.000-ring 60x3,5 NBR c 14,511 8,058 22,644 16,4607,615 5,833 75,121 6336,48 1,46) 5 0.05 122826.j thermostate 0 24 24 102 23 001 19738.03 eal 18,0Xj ,4%D 24, 12.2018,3 12,00119 3,9] 735,414 1,216 15 0.04 21268.0 seal 2045 3,302 2,5OR 1,744 12,334 1C411 205 15 . 0.28 003658.02 Gasket C 1,440 4,225 351 3,7772.86 3,555119 18,914 1573 $ 017 12026.01 bwpa5s plate 21,431 27,413 31,872 22,3928,352 5,591 119 117,051 984 10,44E 2,345 5 0.03 114432.0 5eal 3,316 4,35E 4,397 3,18 1011g 1525 1292,031 457 5 ,18 124195.j thermostate 50 119 250 3 103 1 5 10.72 114634.2 0-ring 39,23x1,98 EPDc 5,6] 8,000 9,600 5,607,20 1.2x1 1g 43,20 3641,518 341 5 0.0E 03E49,0 sp\got 3,222 726 1.2x E,141 521,305 24 s 0.39 on254, 0 6asket 491 2,559 2.549119 5,702 48 1,296 0.19 1203g9, 0 asket 585 3.52E 4,324 875 470 1,625 11,405 96 1,639 15 0.0 118938.xj5eal 553 119 653 6 267 5 0,92 118939.] 5eal d 653 11 653 6 267 5 0.66 p3561.020-ring 474 1,28 0 0 2,392 119 4,7 3 1,057 238 5 0.OE p2523.00 Gasket 1,178 683 119 1,861 1650E 114 S 0.11 %D2898.d] 6asket 610 00 119 610 6 250 8 . 15 p3595.00 Gasket 2,4cj 0 1,102 -2,538 1 19 1,629 366 15 0.07 5,372 Current Order Current Quantity ROP (COQ) 7,0x0 14,29) ,x 4,&x17 ,96) 1,716 BOD,0% D 866,825 60.cl 40, 61,760 4@ya 60,00 78,140 60,0xxa &0, D 122,295 20,ooL 1 ] 488 3L 75 1,060 48) 5,000 12,5] 26,285 60,Ocz 6,00 13,710 4,Gol 30,Gyj 3 4,850 75,Dol 48,000 61,130 3D,al 25 15,000 1 9,335 50,yal 475 2sol 770 D5 2.3.4 3,424 4x1 12,xx@ 144,905 40,Gol 13,10] 16,265 20,xxa 1,0 1015 3g 6,0x0 1 7,850 12,5a 2015 3,576 20xo 2,4] 3,195 8,xpL 3,240 8,760 30,Gol 4,xx] 4,545 10,xxa 275 250 16, 17,820 12,xx 750 1010 5,gooL 2.EX] 2_240 6,Oca 3,10x2 4,540 32.5x 70 739 25L 709 73 2sal 2,52 2,575 38 1,00 1, 0 4,Ca[ 20Xj 248 1xx 3,30 3,435 1,Oca | Rg a P| 119 119 2058 576 29 119 292 s 964