Question

PLEASE SHOW ALL WORK IN EXCEL!!!!!!!

SHOW CALCULATIONS / FORMULAS IN ANSWER!!!!!!!!

You need to buy raw materials for your production process. You use 2,000 units of the raw material each year. The supplier quotes you a price of $9.50 per unit for an order size of 499 units or less, a price of $9 per unit for orders of 500 to 1499 units, and a price of $8.50 per unit for an order of 1,500 or more units. You assign an annual holding cost of 30 percent of the price of the unit to this inventory. Assume ordering cost is $60/order. What order quantity would you use if the objective is to minimize total annual costs of holding, ordering, and purchasing (be sure to identity total costs of 3 options)?

PLEASE SHOW ALL WORK IN EXCEL!!!!!!!

SHOW CALCULATIONS / FORMULAS IN ANSWER!!!!!!!!

Answer 1

Following is the solution in excel

In condition 500 - 1499 and 1500 or more, the EOQ is 298.14 and 306.79 which do not fulfil the condition. Hence minimum order is consider and calculated based on that.

Answer - The optimal order quantity is 290.19 at the price of $9.50