Please answer the following question in excel. Here is the link to the file for easy access: https://drive.google.com/file/d/1nv-08EI5_k6oF8Xto8J-KgCXhB47GJb8/view?usp=sharing
You may edit this file directly.
Upload pictures with Formula text for each question & answer
A l E Background ICE C D E | F l G l H l | l J l K | L l M l N l O l P Gordon's Distribution Company [GDC} has been a family owned business for the last 80 years. It started as a wholesale distributor to small grocery stores. It then expanded into delivering foods to restaurants. When warehouse stores developed, it adjusted its business model to create smaller box warehouse stores for retail customers, similar to Sam's Club and Costco. While doing that it maintained its other two business lines of distribution to grocery stores and delivering food to restaurants. It found synergies between these three business lines, in that as it developed a deli-service to attract retail customers to its Gordon Warehouse stores some products from the deli could be delivered through its food distribution to small grocery stores and to restaurants. I IlMarysa Gordon is the current President of GDC and is evaluating how GDC should evolve now. In her view GDC isthreatened by competition from Amazon Go Grocery Stores, Aldi grocery stores and Walmart Neighborhood Markets. At this point the competition is limited, but Marysa thinks it isgrowing rapidly and is starting to take market share from the existingthree lines of GDC's business. But, before just opening a small store modeled on Aldi or Amazon Go, Marysa wants to ensure that there is enough flexibility and capacity in the regional warehouses that support the existing lines of business to support a fourth line of business. She has asked you to examine the flow of work in the regional warehouse. Your investigation of the flow of work at the regional warehouse has shown the following: 1 About 20% ofthe ordersfilled bythe regional warehouse are for distribution to small grocery stores. These stores are clustered in the core urban areas or in the rural areas. Their primary competitors are Dollar General Stores etc. Delivery lead times are important to them because they sell at a higher markup than Dollar General and they try to attract customers by having fresher perishables and small deli offerings. GDC has routes set upthat deliver 2 timesaweek. 2 About 40% of the orders filled by the regional warehouse are for distribution to small restaurants. The characteristic that the restaurants preferfrom GDC isthatthe lead time is 1 day. If the restaurant places an order by 4PM, it will receive delivery by 10:00 AM the next day. The average daily orderfrom these restaurants is $500 in frozen meats and $300 in prepared foods. 3 About 40% of the orders filled by the regional warehouse are for GDC's \"Gord on Warehouse\" stores forthe retail customer. These are filled daily. About 20% ofthe orders are for partially prepared foods from the GDC food prep center attached to the regional warehouse. This food's preparation isfinished once it arrives atthe Gordon Warehouse store. Another 60% ofthe orders is for dry goods [e.g., cereal, sugar, flour, cooking oils etc.}. The remaining 20% ofthe orders is for seasonal specialsto attractthe retail customer. For example, a special on lawn chairs and ice chests and picnic supplies was conducted during June of this year. After an initial evaluation ofthe existing lines of business supported bythe GDCwarehouse, you determined thatthe most expensive portion of the storage capacity was the frozen foods. You gathered the folowing information about the GDC frozen foods [referred to as ICE within GDC}. To simplify the analysis you decided to examine the ICE processusing a \"case" asthe flow unit. ICE has a dailyvolume of 1000 cases. On average 30% ofthese are for 35 "case" as the flow unit. ICE has a daily volume of 1000 cases. On average 30% of these are for 36 distribution to small grocery stores. To simplify filling these orders, ICE has a crate designated for each grocery store on its route. 37 ICE begins picking the foods for these groceries at 2 AM so it can have trucks leaving the warehouse at 7AM. 38 To fill an order, a GDC team member goes to the Order Board and takes the next order. On average each order 39 requires loading 20 cases into a store's crate. To do this the team member takes their pallet jack (i.e., a hand pulled 40 vehicle designed to hold cases) into each frezer. This ORDER START activity (i.e., take order off of Order Board, walk to pallet jack 41 place empty pallet on pallet jack and walk to freezer door) takes 3 minutes. 42 The picker goes through the freezers in sequence. There are 5 freezers arranged in a row. The picker starts 43 at freezer 1 and goes through them from front to back. On average it takes a picker 1 minute to walk through a freezer and 44 another 1 minute to pick each case found in a freezer. On average the picker selects 3 cases from 45 freezers 1 & 2 , 4 cases from freezer 3 and 5 cases from freezers 4 & 5. After leaving freezer 5, the picker 46 moves cases on the pallet jack to the crate, taking 1 minute. The picker takes 2 min to return to the Order Board. 47 1 2 During the 2AM to 7AM shift the workers take one 30 minute break. Calculate the TAKT time required for ICE to meet 48 customer demand measured as orders. 49 Takt = minutes 50 #N/A 51 2 2 What is the unit load required for each activity? Activity Unit Load Order Start #N/A Freezer 1 #N/A Freezer 2 #N/A Freezer 3 #N/A Freezer 4 #N/A Freezer 5 #N/A Load Crate #N/A Return to Order Board #N/A 3 2 If all of the orders are at the Order Board at the beginning of the shift, on average how many orders are at the order board over the time of the shift? (i.e., what is the cycle inventory of orders at the Order Board) order board minutes 66 #N/A 67 4 6 Draw a process flow diagram that shows each individual freezer as a separate activity. The flow unit should be Order.6T 4 6 Draw a process flow diagram that shows each individual freezer as a separate activity. The flow unit should be Order. 68 Do not consider the inventory of frozen foods in the freezers. Consider only the inventory of orders at the Order Board 69 m n H 1% ?4 "F5 "F6 T! 2'8 1'9 8!] 81 5 2 What is the average tim e to fill an average order for a grocery store from th e ICE process? 82 Tm: minutes 53 ' mm 84 85 5 3 What is the capacity of each picker, measured as orders per shift? 86 Capacity of one picker: ordersp'shift 8? ' min. 88 E9 90 7 3 ICE currently has 5 pickers working during the 2 AM to '3' AM shift. If they are only picking orders for the grocery 91 stores, what is th eir utilization, considering only tim e available and ti me per order? 92 utilization \"13:, minutes 93 NHL 94 95 96 3 3 As a way of measuring the performance of each picker, ICE keeps a separate order queue for each picker. The computer 9? at the order board gives each picker their next order. The computer places 1 new order into each picker's 98 queue at the order board every 20 minutes. There is no variance in theorder arrival rate. 99 Th ere a re differences though in th e picker's ability to process ord ers. The coefficient of variation of ord er processing 100 for each picker is given below. Calculate the number in their personal order queue if all have the same utilization. lCoeicient 100 101 for each picker is given below. Calculate the number in their personal order queue if all have the same utilization. Coefficient of Variation of Procsin Number of Orders 3 in Picker's Qu eu e 10.2 103 104 105 106 10'.Ir 108 109 You did a survey of small restaurant customers who are served by the ICE team. Yo 110 111 112 comments a bout th ei r service. Create a Pareto diagram below. State your conclusion. [Insert rows below as needed} Category Number 113 114 Supplies received do not match supplies ordered Frozen foods received above critical temperature 115 Delivery was more than 1 hour after specified time 116 11'.Ir Minimum order size too large Outside of case containers dama naged 118 Invoices received are often not accurate 119 120 Delivery drivers will not take cash for payment 1Z1 6 4 Discount given only if bank transfer done within 24 hours 2 44 Higher volume foods were often stocked out or rationed
