Microsoft Excel will be used to perform the financial computations; however, the questions and their corresponding responses should be written up in a Microsoft Word document. IMPORTANT: Make certain that there is a detailed description of how the calculations were performed.

Chapter 15 Questions 5. A medical supply store wants to know the number of stethoscopes it should purchase 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= setup cost= $250 The formulas for determining EOQ and other costs are as follows: EOQ Q* = (2 x A x S/H) Annual Holding Cost AHC = Q*/2 x H x P Annual Ordering Cost AOC = A/Q* x S Total Annual Inventory Cost TIC = AHC + AOC 7. For the given inventory data that follows, determine which items are Type A, Type B and Type C Part line Number Avg Weekly sales Cost ($) Dollar Volume 1 21,000 1 21,000 2 4,000 40 160,000 3 1,600 3 4,800 4 12,000 1 12,000 5 1,000 100 100,000 6 50 50 2,500 7 800 2 1,600 8 10,000 3 30,000 9 4,000 1 4,000 10 5,000 1 5,000 11 250 2 500 12 1,900 55 104,500