please answer the case questions and put this into excel so that I can see how the formulas are input. *Case Problem 13.3 Pharr Foods Company Pharr Foods Company produces a variety of food products includ- ing a line of candies. One of its most popular candy items is “Far Stars,” a bag of a dozen individually wrapped star-shaped candies made primarily from a blend of dark and milk chocolates, macad amia nuts, and a blend of heavy cream fillings. The item is relatively expensive, so Pharr Foods only produces it for its eastern market encompassing urban areas such as New York, Atlanta, Philadelphia, and Boston. The item is not sold in grocery or discount stores but mainly in specialty shops and specialty groceries, candy stores, and department stores, Pharr Foods supplies the candy to a single food distributor, which has several warehouses on the East Coast. The candy is shipped in cases with 60 bags of the candy per case. Far Stars sell well despite the fact that they are expensive at $9.85 per bag (wholesale). Pharr uses high-quality, fresh ingredients and does not store large stocks of the candy in inventory for very long periods of time. Pharr's distributor believes that demand for the candy follows a seasonal pattern. It has collected demand data (i.e., cases sold) for Far Stars from its warehouses and the stores it supplies for the past three years, as follows. MONTH 192 212 223 DEMAND (CASES) YEAR 1 YEAR 2 YEAR 3 228 210 216 252 293 235 January February March April 231 205 260 May 228 246 172 229 June July August September October November December 160 147 256 209 231 263 370 260 277 342 226 302 410 279 293 261 273 The distributor must hold the candy inventory in climate-con- trolled warehouses and be careful in handling it. The annual carrying cost is $116 per case. The item must be shipped a long distance from the manufacturer to the distributor. In order to keep the candy as fresh as possible, trucks must be air-conditioned and shipments must be direct, and are often less-than-truckload. As a result, ordering cost is $4700. 580 CHAPTER 13 Inventory Management Pharr Foods makes Far Stars from three primary ingredients it orders from different suppliers: dark and milk chocolate, macadamia nuts, and, a special heavy cream filling. Except for its unique star shape, a Far Star is almost like a chocolate truffle. Each Far Star weighs 1.2 ounces and requires 0.70 ounce of blended chocolates, 0.50 ounce of macadamia nuts, and 0.40 ounce of filling to produce (including spillage and waste). Pharr Foods orders chocolate, nuts, and filling from its suppliers by the pound. The annual ordering en is $5700 for chocolate, and the carrying cost is $0.45 per pound. The ordering cost for macadamia nuts is $6300, and the annual carrvi cost is $0.63 per pound. The ordering cost for filling is $4500, and the annual average carrying cost is $0.5 per pound, Each of the suppliers offers the candy manufacturer a quantity- discount price schedule for the ingredients as follows: PRICE $3.05 CHOCOLATE QUANTITY (lb) 0-50,000 50,001-100,000 100,001-150,000 150,001+ MACADAMIA NUTS QUANTITY (lb) 0-30,000 30,001–70,000 70,001+ PRICE $6.50 6.25 5.95 PRICE $1.50 FILLING QUANTITY (lb) 0-40,000 40,001-80.000 80.001+ 1.35 2.90 2.75 2.60 1.25 Determine the inventory order quantity for Pharr's distributor. Com- pare the optimal order quantity with a seasonally adjusted forecast for demand. Does the order quantity seem adequate to meet the seasonal demand pattern for Far Stars? That is, is it likely that shortages or excessive inventories will occur? Can you identify the causes of the seasonal demand pattern for Far Stars? Determine the inventory order quantity for each of the three primary ingredients that Pharr Foods orders from its suppliers. Discuss the possible impact of the order policies of the food distributor and Pharr Foods on quality manage ment and supply chain management
