Problem 2 Basic EOQ Analysis Consider a medical practice that performs dermatological surgeries. The practice uses 15,000 scrub kits per year and wants to decide how many to order at a time and when to place orders. The cost of placing an order is $30 per order and the annual carrying cost for a single scrub kit is $2.50. They operate 300 days per year and the lead time to receive an order is 4 days. a) Create a new worksheet named Problem 2. Enter all of the given information in clearly labeled cells. See sample layout below. b) Enter formulas to compute each of the items list below. Within the spreadsheet make sure the given information is clearly distinguished from the computed results. Avoid placing values in formulas. Instead formulas should refer to cells which contain values (either inputs or previously computed values). the optimal order quantity Q* (in MS Excel, you can compute the square root by using the SQRT function). Assuming that the practice places orders with quantity (Q) equal to the optimal economic order quantity (Q*), compute the following: the number of orders that will be placed each year, the total annual ordering cost, average annual inventory (in units), the total annual carrying cost and total annual inventory costs (ordering and carrying costs) the reorder point units c) In a textbox, answer the questions below. If the order quantity used by the company was greater than the computed economic order quantity (Q*), indicate how annual carrying costs, annual ordering costs and total annual costs would change (e.g. whether they will increase or decrease) relative to those using the Q*. If the order quantity used by the company was less than the computed economic order quantity (Q*), indicate how annual carrying costs, annual ordering costs and total annual costs would change (e.g. whether they will increase or decrease) relative to those using the Q*. (Studying slide 28 in the Inventory notes will help you answer this question. You may also make a play copy of your worksheet to do a what-if analysis to explore effects of using different Q values.) d) If the store wanted to use a periodic inventory system, what order interval (days) would approximate the economic order quantity computed in part b. Enter a formula in a cell labeled order interval for P system. See slide 44 in the inventory notes for an example computation.