Question

Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units.

A manager needs to buy raw materials for the production process. For a particular raw material, the manager uses 20,000 units each year. The supplier has quoted a price of $6.50 per unit for an order size of 999 units or less, a price of $6 per unit for orders of 1000 to 1999 units, and a price of $5.75 per unit for an order of 2,000 or more units. There is an annual holding cost of 40 percent of the price of the unit for this inventory. Assume the ordering cost is $80/order. What is the order quantity if the objective is to minimize total annual costs of holding, ordering, and purchasing (be sure to identity total costs of 3 options)?

Answer 1

Please refer the images for the solutions and formulas.

See the formula bar and the active cell to find the formula- Note

The order quantity is 2000 units.

Case 3 1179.5 is not applied here but 2000 unit is the economic order quantity.

Since. the lowest total cost is under case 3.

Please refer the below image for knowing the formula making.

Totcal cost of 1st option is calculated in excel.