please answer 10-14 in excel and show steps for explanation, then I will rate up vote. thank you!

800 cases Inventory Supply As part of your examination of the ICE services, you examined how they manage their raw material inventory. On average there are 120 standard food types in 5 freezers. Plus there are seasonal items when appropriate. They have a physical holding cost rate of h = 40% You spoke to the ICE manager about this and pointed out other departments at GDC only have a 8% physical holding cost rate. The ICE manager said that because electricity to keep foods frozen is expensive and frozen foods are perishable if the temperature rises in the freezers. The manager said that other departments are probably under estimating the costs of physically holding their inventory. The cost of placing an order at ICE is $ 10 an order. The ICE manager stated that orders are placed once a week and that the lead time is one week. ICE operates only 5 days a week, so the manager schedules deliveries from suppliers so that suppliers whose food goes into Freezer 1 deliver on Monday. Suppliers whose food is stored in Freezer 2 deliver on Tuesday. The manager requirees that all deliveries be made between 8 AM and 12 Noon Monday to Friday, so deliveries do not interfere with shipments out to the grocery stores. To simplify the ordering process, the manager has set all order quantities at The manager also set the reorder point, ROP = 600 cases for all foods. The manager argues that this simplified ordering and delivery and that the sales volume did not vary significantly between food items. You obtained the following information to test whether the manager's assumptions were correct. Assume ICE works These represent food items with the lowest annual volume, the highest annual volume and the highest standard deviation. Standard Deviation of Annual Sales Sales (Cases/ Unit Cost per Food Item (Cases) Week) Case 10000 100 $300 200 20000 $225 30000 30 $140 22000 $ 205 19000 $203 10 Assume the manager's cost figures are correct. Compare costs with the current Qto the EOQ costs. (insert rows as needed) 250 days annually A B 25 D E 20 80 11 What is the time to zero (tz) for each item? 12 To test the impact of the manager's ROP decision, make a graph of Ending Inventory for 10 weeks for Food item A. Calculate two Ending Inventories. One for a level demand (i.e., Annual Sales / 50 weeks). One level for high demand of Average weekly sales + 1 standard deviation. (Show work and graph below. Have only one graph with two lines on it. Please label it). 13 To test the impact of the manager's ROP decision, make a graph of Ending Inventory for 10 weeks for Food item C. Calculate two Ending Inventories. One for a level demand (i.e., Annual Sales / 50 weeks). One level for high demand of Average weekly sales +1 standard deviation. (Show work and graph below. Have only one graph with two lines on it. Please label it). 14 Based on the two graphs above, state your conclusion about the ordering policies of this manager. Explain your conclusion. 800 cases Inventory Supply As part of your examination of the ICE services, you examined how they manage their raw material inventory. On average there are 120 standard food types in 5 freezers. Plus there are seasonal items when appropriate. They have a physical holding cost rate of h = 40% You spoke to the ICE manager about this and pointed out other departments at GDC only have a 8% physical holding cost rate. The ICE manager said that because electricity to keep foods frozen is expensive and frozen foods are perishable if the temperature rises in the freezers. The manager said that other departments are probably under estimating the costs of physically holding their inventory. The cost of placing an order at ICE is $ 10 an order. The ICE manager stated that orders are placed once a week and that the lead time is one week. ICE operates only 5 days a week, so the manager schedules deliveries from suppliers so that suppliers whose food goes into Freezer 1 deliver on Monday. Suppliers whose food is stored in Freezer 2 deliver on Tuesday. The manager requirees that all deliveries be made between 8 AM and 12 Noon Monday to Friday, so deliveries do not interfere with shipments out to the grocery stores. To simplify the ordering process, the manager has set all order quantities at The manager also set the reorder point, ROP = 600 cases for all foods. The manager argues that this simplified ordering and delivery and that the sales volume did not vary significantly between food items. You obtained the following information to test whether the manager's assumptions were correct. Assume ICE works These represent food items with the lowest annual volume, the highest annual volume and the highest standard deviation. Standard Deviation of Annual Sales Sales (Cases/ Unit Cost per Food Item (Cases) Week) Case 10000 100 $300 200 20000 $225 30000 30 $140 22000 $ 205 19000 $203 10 Assume the manager's cost figures are correct. Compare costs with the current Qto the EOQ costs. (insert rows as needed) 250 days annually A B 25 D E 20 80 11 What is the time to zero (tz) for each item? 12 To test the impact of the manager's ROP decision, make a graph of Ending Inventory for 10 weeks for Food item A. Calculate two Ending Inventories. One for a level demand (i.e., Annual Sales / 50 weeks). One level for high demand of Average weekly sales + 1 standard deviation. (Show work and graph below. Have only one graph with two lines on it. Please label it). 13 To test the impact of the manager's ROP decision, make a graph of Ending Inventory for 10 weeks for Food item C. Calculate two Ending Inventories. One for a level demand (i.e., Annual Sales / 50 weeks). One level for high demand of Average weekly sales +1 standard deviation. (Show work and graph below. Have only one graph with two lines on it. Please label it). 14 Based on the two graphs above, state your conclusion about the ordering policies of this manager. Explain your conclusion