Reference no: EM132296062

problem in excel?

A local beverage company distributes an energy drink that has a constant annual demand rate of 5,000 cases. Ordering costs are $80 per order and inventory holding costs are estimated at $5.00 per case per year. Please answer the following questions by attaching your answers in a Word or Excel document (make sure that your Word/Excel file name includes your last name). Show all work. Round up your answers where relevant.

1. What is the Economic Order Quantity (EOQ)?

2. What is the number of orders per year if the company uses the EOQ order?

3. How long will each order last (in months) if the manager uses the Economic Order Quantity? Assume there are 12 months per year.

4. What is the maximum number of cases that are held in inventory in a given ordering cycle using the EOQ?

5. Assume that the lead time for ordering the energy drink is 10 days. What is the Reorder Point? Verbally, interpret the meaning of this Reorder Point. You may assume there are 250 operating days in a year.

6. A newly hired inventory manager believes that by changing the order quantity from the EOQ to 300 cases per order, the beverage company can save money on its holding/carrying costs and therefore lower its annual total inventory costs. Do you agree with this inventory strategy? Justify your answer. Hint: Compare the ordering and carrying costs using the EOQ amount versus the current total inventory cost using 300 cases.