Question

In a small workshop three items are purchased, the administration of that workshop has established the requirement that there are never more than $ 45,000 invested, in inventories, at any given time. They use a 25% rate to maintain inventory. The cost and demand parameters are shown in the table. What lot size should the workshop have in order not to exceed the authorized budget?

ARTICLES 2 3 Demand 2 000 1 500 950 Cost to order 100 125 75 Variable cost 50 350 85

Answer 1

Let’s solve the problem with Economic Order Quantity (EOQ) model

EOQ = sqrt (2 * Annual Demand * Cost to order/ Inventory Holding cost)

Here, inventory holding cost is 25% of variable cost i.e. product cost.

Excel Formulation:

A B D E 1 D 2000 1500 Co 100 2 Demand 3 Cost to order 4 Variable Cost 5 Handling cost 6 950 75 85 =E4*25% V 125 350 =D4*25% 50 Ch =C4*25% 7 EOQ =sqrt(2*D*Co/ Ch) EOQ =ROUNDUP(SQRT(2*C2*C3/C5),0) =ROUNDUP(SQRT(2*D2*D3/D5),0) =ROUNDUP(SQRT(2*E2*E3/E5),0) 8 9 Order Qty 10 11 12 13 =E9*E4 Purchased Cost =C9*C4 Total Cost =C11+C3 =D9*D4 =D11+D3 =E11+E3 14 Grand Total =SUM(C12:E12) 15 16

Solution:

A B C D E LL 1 D Co 2 Demand 3 Cost to order 4 Variable Cost 5 Handling cost 6 7 2000 100 50 12.5 1500 950 125 75 350 85 87.5 21.25 V Ch EOQ sqrt(2*D*Co/ Ch) EOQ 179 66 82 8 9 Order Qty 10 11 12 13 Purchased Cost Total Cost 8950 9050 23100 6970 23225 7045 14 Grand Total 39320

Therefore, order quantities for articles 1, 2, & 3 are 179, 66, and 82 units resp. The total ordering cost is 39320 units

If you like the answer please give an up-vote, this will be quite encouraging for me, thank you.

If you have further suggestions in answer, please write below on the comment section, I will certainly look into it.