Details: Complete problems 11.5, S11.4, S11.9, S11.15, 12.1, 12.4, 12.13, and 12.17 in the textbook. Submit one Excel file. Put each problem result on a separate sheet in your file. 11.5 Baker Mfg. Inc. (see Table 11.9) wishes to compare its inventory turnover to those of industry leaders, who have turnover of about 13 times per year and 8% of their assets invested in inventory. a) What is Baker’s inventory turnover? b) What is Baker’s percent of assets committed to inventory? c) How does Baker’s performance compare to the industry leaders? TABLE 11.9 For Problems 11.5 and 11.6 ARROW DISTRIBUTING CORP. Net revenue $16,500 Cost of sales $13,500 Inventory $ 1,000 Total assets $ 8,600 BAKER MFG. INC. Net revenue $27,500 Cost of sales $21,500 Inventory $ 1,250 Total assets $16,600 S11.4 Johnson Chemicals is considering two options for its supplier portfolio. Option 1 uses two local suppliers. Each has a “unique-event” risk of 5%, and the probability of a “super-event” Problems that would disable both at the same time is estimated to be 1.5%. Option 2 uses two suppliers located in different countries. Each has a “unique-event” risk of 13%, and the probability of a “super-event” that would disable both at the same time is estimated to be 0.2%. a) What is the probability that both suppliers will be disrupted using option 1? b) What is the probability that both suppliers will be disrupted using option 2? c) Which option would provide the lowest risk of a total shutdown? S11.9 Consider a three-firm supply chain consisting of a retailer, manufacturer, and supplier. The retailer’s demand over an 8-week period was 100 units each of the first 2 weeks, 200 units each of the second 2 weeks, 300 units each of the third 2 weeks, and 400 units each of the fourth 2 weeks. The following table presents the orders placed by each firm in the supply chain. Notice, as is often the case in supply chains due to economies of scale, that total units are the same in each case, but firms further up the supply chain (away from the retailer) place larger, less frequent, orders. WEEK RETAILER MANUFACTURER SUPPLIER 1 100 200 600 2 100 3 200 400 4 200 5 300 600 1400 6 300 7 400 800 8 400 Recall that the sample variance of a data set can be found by using the VAR.S function in Excel or by plugging each x value of the data set into the formula: Variance = g(x - x)2 (n - 1) , where x is the mean of the data set and n is the number of values in the set. a) What is the bullwhip measure for the retailer? b) What is the bullwhip measure for the manufacturer? c) What is the bullwhip measure for the supplier? d) What conclusions can you draw regarding the impact that economies of scale may have on the bullwhip effect? S11.15 Monczka-Trent Shipping is the logistics vendor for Handfield Manufacturing Co. in Ohio. Handfield has daily shipments of a power-steering pump from its Ohio plant to an auto assembly line in Alabama. The value of the standard shipment is $250,000. Monczka-Trent has two options: (1) its standard 2-day shipment or (2) a subcontractor who will team drive overnight with an effective delivery of one day. The extra driver costs $175. Handfield’s holding cost is 35% annually for this kind of inventory. a) Which option is more economical? b) What production issues are not included in the data presented? 12.1 L. Houts Plastics is a large manufacturer of injectionmolded plastics in North Carolina. An investigation of the company’s manufacturing facility in Charlotte yields the information presented in the table below. How would the plant classify these items according to an ABC classification system? PX L. Houts Plastics’ Charlotte Inventory Levels ITEM CODE # AVERAGE INVENTORY (UNITS) VALUE ($/UNIT) 1289 400 3.75 2347 300 4.00 2349 120 2.50 2363 75 1.50 2394 60 1.75 2395 30 2.00 6782 20 1.15 7844 12 2.05 8210 8 1.80 8310 7 2.00 9111 6 3.00 12.4 Lindsay Electronics, a small manufacturer of electronic research equipment, has approximately 7,000 items in its inventory and has hired Joan Blasco-Paul to manage its inventory. Joan has determined that 10% of the items in inventory are A items, 35% are B items, and 55% are C items. She would like to set up a system in which all A items are counted monthly (every 20 working days), all B items are counted quarterly (every 60 working days), and all C items are counted semiannually (every 120 working days). How many items need to be counted each day? 12.13 Joe Henry’s machine shop uses 2,500 brackets during the course of a year. These brackets are purchased from a supplier 90 miles away. The following information is known about the brackets: Annual demand: 2,500 Holding cost per bracket per year: $1.50 Order cost per order: $18.75 Lead time: 2 days Working days per year: 250 a) Given the above information, what would be the economic order quantity (EOQ)? b) Given the EOQ, what would be the average inventory? What would be the annual inventory holding cost? c) Given the EOQ, how many orders would be made each year? What would be the annual order cost? d) Given the EOQ, what is the total annual cost of managing the inventory? e) What is the time between orders? f) What is the reorder point (ROP)? 12.17 Radovilsky Manufacturing Company, in Hayward, California, makes flashing lights for toys. The company operates its production facility 300 days per year. It has orders for about 12,000 flashing lights per year and has the capability of producing 100 per day. Setting up the light production costs $50. The cost of each light is $1. The holding cost is $0.10 per light per year. a) What is the optimal size of the production run? b) What is the average holding cost per year? c) What is the average setup cost per year? d) What is the total cost per year, including the cost of the lights?