Question: Can somebody please provide an answer along with excel formulas to this scenario....
D L M N 0 137 139 141 800 cases 250 days annually 146 B 25 A G . K 131 Inventory Supply As part of your examination of the ICE services, you examined how they manage their raw material inventory. 132 On average there are 120 standard food types in 5 freezers. Plus there are seasonal items when appropriate. 133 They have a physical holding cost rate of h = 40% You spoke to the ICE manager about this and pointed out 134 other departments at GDC only have a 8% physical holding cost rate. The ICE manager said that is because 135 electricity to keep foods frozen is expensive and frozen foods are perishable if the temperature rises in the freezers. The manager 136 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 138 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. 140 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 142 The manager also set the reorder point, ROP = 600 cases for all foods. The manager argues that this simplified 143 ordering and delivery and that the sales volume did not vary significantly between food items. 144 You obtained the following information to test whether the manager's assumptions were correct. Assume ICE works 145 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 147 . 10000 100 $300 200 148 20000 $225 149 C 30000 30 $140 150 D 22000 20 $205 151 E 19000 80 $203 152 10 10 Assume the manager's cost figures are correct. Compare costs with the current Q to the EOQ costs. 153 (insert rows as needed) 154 155 11 5 What is the time to zero (tz) for each item? 156 157 158 159 12 10 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 160 Ending Inventories. One for a level demand (i.e., Annual Sales / 50 weeks). One level for high demand of Average weekly sales +1 standard 161 deviation. (Show work and graph below. Have only one graph with two lines on it. Please label it). 162 163 164 13 10 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 165 Ending Inventories. One for a level demand (i.e., Annual Sales / 50 weeks). One level for high demand of Average weekly sales +1 standard 166 deviation. (Show work and graph below. Have only one graph with two lines on it. Please label it). 167 168 169 14 5 Based on the two graphs above, state your conclusion about the ordering policies of this manager. Explain your conclusion. 170 171 172 173 174 175
