A medical supply store wants to know the number of stethoscopes it should purchase in order to minimize annual costs. Currently, the store is ordering 1,000 stethoscopes per order. Formulate a model in Excel to determine the EOQ for the store. Include all of the variables and calculated examples from Figure 15.1 in order to determine the cost of the store’s current plan. Use a separate worksheet to determine the EOQ, and report on the savings the store would have if it changed its order quantity for stethoscopes to the EOQ. Problem data:

P = purchase cost = $65

D = annual demand = 15,000

H = holding cost = 10 percent per item

K = set cost $250

please show the formulas and explanation

Figure 15.1

A C P Purchase Price $2.00 D Annual Demand 10,000 H. Holding Cost Per Unit 10% 8 S Setup Cost $50.00 9. 10 Order Quantity Q 11 12 APC 13 2,236 Annual Purchase Cost $20,000.00 14 ACC Annual Holding Cost $223.60 15 16 ASC Annual Setup Cost $223.61 17 18 TAC Total Annual Cost $20,447.21 19 1234 56