Instructions: Use MS Excel to produce the computations to support your answers t
ID: 347761 • Letter: I
Question
Instructions: Use MS Excel to produce the computations to support your answers to Problems I, and II. Problem l: The annual demand for a special type of perfume sold by a department store is 8,300 bottles. The department store buys the perfume bottles from the manufacturing company and each perfume bottle costs $29.00 for the department store The cost of placing a purchase order is $194.00 for the department store. The inventory carrying cost rate for the department store is 26% of the purchase cost for any item There are 240 working days per year and the lead time is 4 days. Answer the following questions 1 through 7. (1) The Economic Order Quantity (Optimal Order Quantity) is (2) Suppose you order every time a quantity equal to the EOQ. Then the number of orders placed in a year is equal to (3) The cycle time under the EOQ policy is (4) The re-order point under the EOQ policy is (5) Suppose you order every time a quantity equal to the EOQ. Then the total cost of placing the orders and holding the inventory per year is the total cost of placing the orders and holding the inventory per year is Then, the increase in total costs (i.e., penalty) per year for adopting this new (6) Suppose you order every time a quantity equal to 400 instead of the EOQ. Then (7) Suppose you order every time a quantity equal to 400 instead of the EOQ policy instead of the EOQ isExplanation / Answer
7. Increase in total cost = 5533.5-4927.7 = $ 605.8
Continuous or Fixed order quantity Model EOQ Days per year Y = 240 Annual Demand D = Y*µD 8,300 Lead Time (Days) L = 4.0 Item Cost C = $29 Annual Holding Cost Rate i = 26% Annual Holding Cost per Unit H = i*C $7.5 Ordering Cost per Order S = $194 1 Lot Size / Optimal Order Qty = EOQ Q = (2SD/H) 653.5 2 Number of Orders per year N = D/Q 12.7 3 Cycle Time under EOQ policy T = Y/N 18.9 days 4 Reorder Point (R) ROP = D/Y*L 138.3 Annual Ordering Cost OC = S*N 2,463.8 Annual Holding Cost HC = Q/2*H 2,463.8 5 Total Annual Inventory Cost TC = OC + HC 4,927.7