Cambridge Foods Company produces a variety of food products, including 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, macadamia nuts, and a blend of heavy cream fillings. The item is relatively expensive, so Cambridge Foods only produces it for large Canadian cities such as Toronto, Montreal, and Vancouver. The item is not sold in grocery or discount stores, but mainly in specialty shops and specialty groceries, candy stores, and department stores. Cambridge Foods supplies the candy to a single food distributor, which has several warehouses throughout Canada. 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). Cambridge uses high quality, fresh ingredients and does not store large stocks of the candy in inventory for very long periods of time.

Cambridge'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.

The distributor must hold the candy inventory in climate-controlled 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. 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.

Cambridge Foods orders the three primary ingredients for Far Stars from different suppliers. Except for its unique star shape, a Far Star is almost like a chocolate truffle. Each Far Star weighs 1.2 grams and requires 0.70 grams of blended chocolates, 0.50 grams of macadamia nuts, and 0.40 grams of filling to produce (including spillage and waste). Cambridge Foods orders chocolate, nuts, and filling from its suppliers by the kilogram. The annual ordering cost is $5700 for chocolate, and the carrying cost is $0.45 per kilogram. The ordering cost for macadamia nuts is $6300, and the annual carrying cost is $0.63 per kilogram. The ordering cost for filling is $4500, and the annual average carrying cost is $0.55 per kilogram.

Each supplier offers the candy manufacturer a quantity discount price schedule for the ingredients as follows:

1. Determine the inventory order quantity for Cambridge's distributor

2. Compare 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?

3. Can you identify the causes of the seasonal demand pattern for Far Stars?

4. Determine the inventory order quantity for each of the three primary ingredients that Cambridge Foods orders from its suppliers.

Please present it in excel with formula text

Chocolate Macadamia Nuts Filling Price Quantity (lb) Price Price Quantity (lb) Quantity (lb) $3.05 0-50,000 $6.50 0-30,000 $1.50 0-40,000 2.90 6.25 1.35 50,001- 100,000 30,001- 70.000 40,001- 80,000 2.75 5.95 70,000+ 1.25 80,000 + 100,001- 150,000 2.60 150,000 + Demand (cases) Month Year 1 Year 2 Year 3 January 192 212 228 February 210 223 231 March 205 216 226 April 260 252 293 May 228 235 246 June 172 220 229 July 160 209 217 August 147 231 226 September 256 263 302 Demand (cases) Month Year 1 Year 2 Year 3 October 342 370 410 November 261 260 279 December 273 277 293 Chocolate Macadamia Nuts Filling Price Quantity (lb) Price Price Quantity (lb) Quantity (lb) $3.05 0-50,000 $6.50 0-30,000 $1.50 0-40,000 2.90 6.25 1.35 50,001- 100,000 30,001- 70.000 40,001- 80,000 2.75 5.95 70,000+ 1.25 80,000 + 100,001- 150,000 2.60 150,000 + Demand (cases) Month Year 1 Year 2 Year 3 January 192 212 228 February 210 223 231 March 205 216 226 April 260 252 293 May 228 235 246 June 172 220 229 July 160 209 217 August 147 231 226 September 256 263 302 Demand (cases) Month Year 1 Year 2 Year 3 October 342 370 410 November 261 260 279 December 273 277 293