Please answer the 5 questions below in excel.
8% 800 cases 160 Inventory Supply As part of your examination of the ICE services, you examined how they manage their raw material inventory. 161 On average there are 120 standard food types in 5 freezers. Plus there are seasonal items when appropriate. 162 They have a physical holding cost rate of h = 40% You spoke to the ICE manager about this and pointed out 163 other departments at GDC only have a physical holding cost rate. The ICE manager said that is because 164 electricity to keep foods frozen is expensive and frozen foods are perishable if the temperature rises in the freezers. The manager 165 said that other departments are probably under estimating the costs of physically holding their inventory. 166 The cost of placing an order at ICE is $ 10 an order. The ICE manager stated that orders are placed once a week 167 and that the lead time is one week. ICE operates only 5 days a week, so the manager schedules deliveries from suppliers 168 so that suppliers whose food goes into Freezer 1 deliver on Monday. Suppliers whose food is stored in Freezer 2 deliver on Tuesday. 169 The manager requirees that all deliveries be made between 8 AM and 12 Noon Monday to Friday, so deliveries do not interfere 170 with shipments out to the grocery stores. To simplify the ordering process, the manager has set all order quantities at 171 The manager also set the reorder point, ROP = 600 cases for all foods. The manager argues that this simplified 172 ordering and delivery and that the sales volume did not vary significantly between food items. 173 You obtained the following information to test whether the manager's assumptions were correct. Assume ICE works 174 These represent food items with the lowest annual volume, the highest annual volume and the highest standard deviation. Standard Deviation Annual of Sales Sales (Cases/ Unit Cost 175 Food Item (Cases) Week) per Case A 10000 100 $300 200 177 B B 20000 25 $225 178 30000 30 $140 179 D 22000 20 $205 180 E 19000 80 $203 181 1 Assume the manager's cost figures are correct. Compare costs with the current Q to the EOQ costs. 182 (insert rows as needed) 250 days annually 176 183 2 What is the time to zero (tz) for each item? 3 184 185 186 187 188 189 190 191 1921 193 194 195 196 197 198 199 200 201 202 203 204 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). 4 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). 5 Based on the two graphs above, state your conclusion about the ordering policies of this manager. Explain your conclusion. 8% 800 cases 160 Inventory Supply As part of your examination of the ICE services, you examined how they manage their raw material inventory. 161 On average there are 120 standard food types in 5 freezers. Plus there are seasonal items when appropriate. 162 They have a physical holding cost rate of h = 40% You spoke to the ICE manager about this and pointed out 163 other departments at GDC only have a physical holding cost rate. The ICE manager said that is because 164 electricity to keep foods frozen is expensive and frozen foods are perishable if the temperature rises in the freezers. The manager 165 said that other departments are probably under estimating the costs of physically holding their inventory. 166 The cost of placing an order at ICE is $ 10 an order. The ICE manager stated that orders are placed once a week 167 and that the lead time is one week. ICE operates only 5 days a week, so the manager schedules deliveries from suppliers 168 so that suppliers whose food goes into Freezer 1 deliver on Monday. Suppliers whose food is stored in Freezer 2 deliver on Tuesday. 169 The manager requirees that all deliveries be made between 8 AM and 12 Noon Monday to Friday, so deliveries do not interfere 170 with shipments out to the grocery stores. To simplify the ordering process, the manager has set all order quantities at 171 The manager also set the reorder point, ROP = 600 cases for all foods. The manager argues that this simplified 172 ordering and delivery and that the sales volume did not vary significantly between food items. 173 You obtained the following information to test whether the manager's assumptions were correct. Assume ICE works 174 These represent food items with the lowest annual volume, the highest annual volume and the highest standard deviation. Standard Deviation Annual of Sales Sales (Cases/ Unit Cost 175 Food Item (Cases) Week) per Case A 10000 100 $300 200 177 B B 20000 25 $225 178 30000 30 $140 179 D 22000 20 $205 180 E 19000 80 $203 181 1 Assume the manager's cost figures are correct. Compare costs with the current Q to the EOQ costs. 182 (insert rows as needed) 250 days annually 176 183 2 What is the time to zero (tz) for each item? 3 184 185 186 187 188 189 190 191 1921 193 194 195 196 197 198 199 200 201 202 203 204 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). 4 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). 5 Based on the two graphs above, state your conclusion about the ordering policies of this manager. Explain your conclusion
