In another location, the distributor in question #2 stocks four different items
ID: 370766 • Letter: I
Question
In another location, the distributor in question #2 stocks four different items in common warehouse space. Each item is described by an annual demand rate, a fixed cost per order, a holding cost per year, a unit purchase cost, and a space requirement. The following data describe the four products:
A.Considering each product separately, as if it were independent from the others, what are the respective EOQs? What is the total annual cost of ordering, holding, and purchasing across the four products at these order quantities?
B. What is the minimum total annual cost for the four products if the average space taken up must be no more than 12,000 square feet?
C. What is the minimum total annual cost for the four products if the average space taken up must be no more than 12,000 square feet and the number of order per year must be no more than 65?
PLEASE SHOW IN EXCEL
ITEM 1 2 3 4 Demand 5000 10000 30000 300 Fixed Cost 400 700 100 250 Holding Cost 50 25 8 100 Purchase Cost 500 250 80 1000 Space (Sq. Ft) 12 25 5 10Explanation / Answer
For attempting this question I am assuming unit of measurements of data given . If the assumption is not right kindly let me know I will rework. ITEM Unit of mesaurements 1 2 3 4 Demand Units A 5000 10000 30000 300 Fixed Cost $/Order B 400 700 100 250 Holding Cost $/Unit C 50 25 8 100 Purchase Cost $/Unit D 500 250 80 1000 Space (Sq. Ft) Sq.Ft per unit E 12 25 5 10 EOQ Units F=Square root of (2*A*B/C) 282.84=283 748.33=748 866.02=866 38.72=39 EOQ Units F 283 748 866 39 Number of orders placed per year Numbers G=A/F 17.67 13.37 34.64 7.69 Annual Ordering Cost $ H=B*G 7067.14 9358.29 3464.20 1923.08 Inventory Holding Cost $ I=C*F 14150 18700 6928 3900 Annual Purchase Cost $ J=D*A 2500000 2500000 2400000 300000 Total cost of ordering , holding and purchasing $ K=H+I+J 2521217.14 2528058.29 2410392.20 305823.08 Space required for stocking EOQ quantity Sq.Ft L=E*F 3396 18700 4330 390 Space Constraint for each item Sq.Ft M 12000 12000 12000 12000 Number of units that can be stocked Units N=M/E 1000 480 2400 1200 New EOQs on account of Space Constraint Units O 283 480 866 39 Only for item 2 EOQ has come down on account of space constraint Number of orders placed per year Numbers P=A/O 17.67 20.83 34.64 7.69 Annual Ordering Cost $ Q=P*B 7067.14 14583.33 3464.20 1923.08 Inventory Holding Cost $ R=O*C 14150 12000 6928 3900 Annual Purchase Cost $ S=D*A 2500000 2500000 2400000 300000 Total cost of ordering , holding and purchasing $ T=Q+R+S 2521217.14 2526583.33 2410392.20 305823.08 Now number of orders that can be placed are 65 and in our case the number is 17.67+20.83+34.64+7.69=80.83 So we have to reduce number of orders by 15.83. For reducing this we have to increase EOQ and also have to see space constraint is not an issue. We will reduce orders of item 3 as inventory holding cost is lowest here .Based upon this number of orders that can be placed are : Number of orders placed per year Numbers U 17.67 20.83 34.64-15.83=18.81 7.69 New EOQ on account of space and number of orders constraint. Units V=A/U 283 480 1594.90 39 Annual Ordering Cost $ X=U*B 7067.14 14583.33 1881.00 1923.08 Inventory Holding Cost $ Y=V*C 14150 12000.00 12759.17 3900.00 Annual Purchase Cost $ Z=D*A 2500000 2500000 2400000 300000 Total cost of ordering , holding and purchasing $ T=X+Y+Z 2521217.14 2526583.33 2414640.17 305823.08
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.