Please do in excel. A manager of an inventory system believes that inventory mod
ID: 376047 • Letter: P
Question
Please do in excel.
A manager of an inventory system believes that inventory models are important decision- making aids. The manager has experience with the EOQ policy, but has never considered a backorder model because of the assumption that backorders were “bad” and should be avoided. However, with upper management’s continued pressure for cost reduction, you have been asked to analyze the economics of a backorder policy for some products that can possibly be backordered. For a specific product with D = 800 units per year, Co = $150, Ch = $3, and Cb = $20, what is the difference in total annual cost between the EOQ model and the planned shortage or backorder model? If the manager adds constraints that no more than 25% of the units can be backordered and that no customer will have to wait more than 15 days for an order, should the backorder inventory policy be adopted? Assume 250 working days per year. Now, if the lead time for new orders changed to 20 days for the inventory system, find the reorder point for both the EOQ and the backorder models.
Explanation / Answer
We have following information
Annual demand D = 800 units/year
Order cost Co = $150
Storage cost Ch = $3/unit/year
Backorder cost Cb = $20/unit/year
As per planned shortage model
Q = {(2D*Co/Ch)*(Ch+Cb)/Cb}
={(2*800*150/3)*(3+20)/20}
= 303.315 or 303 units
Back order B = Q *{Ch/ (Ch +Cb)}
= 303 *{3/ (3+20)}
= 39.56 or 40 units
As per EOQ model
Q =(2D*Co/Ch)
= (2*800*150/3)
= 282.84 or 283 units
Total cost planned shortage model:
Annual carrying cost = {(Q-B) ^2/2Q} * Ch = {(303 -40) ^2 /2*303} *$3 = $342.42
Annual ordering cost = (D/Q) *Co = (800/303) * $150 = $396.04
Annual backordering cost = (B^2/2*Q) *Cb = (40^2/2*303) * $20 = $52.81
Total Cost = $342.42 + $396.04 +$52.81 = $791.27
Total cost regular EOQ model:
Annual carrying cost = (Q/2) * Ch = (283/2) * $3 =$424.50
Annual ordering cost = (D/Q) *Co = (800/283)* $150 = $424.03
Total Cost = $424.50 +$424.03 =$ 848.53
Therefore using the planned shortage model will result in annual savings of =$ 848.53 - $791.27 = $57.26
Number of orders = D/Q = 800/303 = 2.64 orders
Expected annual number of units short = B * (D/Q) = 40 * (800/303) = 105.61 units
d = D/250 days = 800/250 = 3.2 units per day demand
t = B / d = 40 / 3.2 = 12.5 days
Because 12.5 days is less than 20 days and 105.61 units/ 800 = 0.1320 or 13.20% is less than 35% therefore the backorder inventory policy should be adopted.