supply chain help using excel please!

2. Compute the projected annual volume, average daily case volume, and the order quantity in cases for 2021 . Assume 250 business days in a year. 3. Using the data in Exhibit 1 of the case, compute the standard deviation of sales. 4. Using the average transit time from Kentucky to Butte, Montana stated in the case and a standard deviation in transit lead time of 1.5 days, compute safety stock requirements for Tender Most inventories at the Butte, Montana, distribution center that will provide a stock protection level of at least 95%. 5. How much additional inventory would be required and inventory carrying costs incurred if the stock protection level were raised to 99% ? On the other hand, how much could inventories and holding costs be reduced if the stock protection level were lowered to approximately 91% ? 6. What are the reorder points for the three in-stock levels? 7. What is the total annual logistics costs associated with the current practice of shipping product by rail to Butte and then by commercial trucks to the retail outlets? 8. Compute the expected service levels (SL) for the stock protection percentages examined. 9. What will be the total annual logistics costs if the company uses the commoncarriage trucking option from Kentucky to Butte, Montana? 10. Which transportation mode should Walnut Hill use for Tender Most? Why? Background Walnut Hill is a well-established smoked foods company specializing in smoked turkeys and has been selling its products throughout the northwestern part of the continental United States since 1967. Initially owned and operated by a German immigrant family, the key to the company's success lay in a secret smoking process native to Rhineland, Germany. By 1998 , the firm owned 250 retail outlets in addition to 400 franchise arrangements. Though the company sold a variety of turkey products, its competitive strength lay in one particular type of smoked turkeys: Tender Most (2020 sales: $11,475,250 ). The 2021 sales were forecasted to have a 11% growth over the 2020 sales level. The turkeys were supplied by Kentucky Meadow Turkeys, Inc. The supplier cleaned and smoked the turkeys using a patented process, and provided the necessary industrial packaging for safe shipment to the retail outlets. Walnut Hill coordinated the remaining retailing functions. The cured and packaged turkeys were purchased from the supplier on a FOB origin basis, and sold to the retail outlets on a FOB destination basis. Approximately 50% of the sales revenue was attributable to direct variable costs; 75% of the direct variable costs were estimated to be actual turkey costs. These costing percentages were expected to hold for the next few years. Tender Most was sold to retail outlets for $6.85 per pound. Products The smoking process employed by Walnut Hill had some unique characteristics which clearly differentiated the company's products from those of its competitors. The Walnut Hill smoked turkeys could be refrigerated for up to 14 days without spoiling. Moreover, they remained fresh and edible for another seven days even without refrigeration. These features, however, did not permit the company to engage in forward buying since the freezer costs were relatively high. Consequently, the company purchased the turkeys from the Kentucky supplier in simple economic order quantities. It took an estimated six days for the railroad freezer cars to bring the smoked turkeys from the supplier's factory in Kentucky to Butte, Montana (Walnut Hill's centralized distribution point), and another two days on average for the commercial trucks to deliver them to the various retail outlets. Tender Most was shipped in the pack size of 25 pounds. The inventory carrying costs were 18%, and the in-transit inventory carrying costs were 16% per annum. The order processing costs were estimated at $27.50 per order. Walnut Hill paid \$8.25 per hundred pounds shipped for the refrigerated railcars in freight, and another $14.65 per hundred pounds for the commercial trucks (LTL shipments to the stores, on a daily basis). Planning Issues for 2021 From a logistical standpoint, management wanted to examine two issues for the planning year of 2021. In 2020, the company found itself out of stock of Tender Most on several occasions while the fresh goods were in transit. Since Tender Most was one of their largest selling brands, the company wanted to minimize its costs of lost sales by determining the best reorder point and the best order size. It was estimated that the stockout costs approximate $55 per case (in future losses) in addition to the obvious contribution margin losses. The sales department found the following distribution of daily demand from retailers for 2019 on reviewing the last working days: The current reorder policy was to place fresh orders as soon as the on-hand inventory was down to 48,575 pounds of Tender Most (1,943 cases). Management wanted to use this information to objectively determine: 1) the optimal reorder point, and once this was fixed, 2) to compute the optimal quantity of cases to order each time. In addition to the objectives stated above, management wished to evaluate an alternate mode/method of transportation. The company could stop the use of the freezer cars for delivery up to Butte, Montana, and use trucking instead. The delivery to the retail outlets planning year of 2021. In 2020, the company found itself out of stock of Tender Most on several occasions while the fresh goods were in transit. Since Tender Most was one of their largest selling brands, the company wanted to minimize its costs of lost sales by determining the best reorder point and the best order size. It was estimated that the stockout costs approximate $55 per case (in future losses) in addition to the obvious contribution margin losses. The sales department found the following distribution of daily demand from retailers for 2019 on reviewing the last working days: The current reorder policy was to place fresh orders as soon as the on-hand inventory was down to 48,575 pounds of Tender Most (1,943 cases). Management wanted to use this information to objectively determine: 1) the optimal reorder point, and once this was fixed, 2) to compute the optimal quantity of cases to order each time. In addition to the objectives stated above, management wished to evaluate an alternate modelmethod of transportation. The company could stop the use of the freezer cars for delivery up to Butte, Montana, and use trucking instead. The delivery to the retail outlets would also continue to be done by motor carrier. It was decided that if any changeover is economical for the company and the new system (if any at all) works well for Tender Most in 2021, in subsequent years, similar feasibility exercises could be carried out for Golden Best, the next best-selling product for Walnut Hill. The cost for common-carriage TL transportation was estimated to be \$12.60 per cwt. from Kentucky to Butte, MT (load of 30,000lbs.). The lead time for this option is 3 days with a standard deviation of 1/2 day. 2. Compute the projected annual volume, average daily case volume, and the order quantity in cases for 2021 . Assume 250 business days in a year. 3. Using the data in Exhibit 1 of the case, compute the standard deviation of sales. 4. Using the average transit time from Kentucky to Butte, Montana stated in the case and a standard deviation in transit lead time of 1.5 days, compute safety stock requirements for Tender Most inventories at the Butte, Montana, distribution center that will provide a stock protection level of at least 95%. 5. How much additional inventory would be required and inventory carrying costs incurred if the stock protection level were raised to 99% ? On the other hand, how much could inventories and holding costs be reduced if the stock protection level were lowered to approximately 91% ? 6. What are the reorder points for the three in-stock levels? 7. What is the total annual logistics costs associated with the current practice of shipping product by rail to Butte and then by commercial trucks to the retail outlets? 8. Compute the expected service levels (SL) for the stock protection percentages examined. 9. What will be the total annual logistics costs if the company uses the commoncarriage trucking option from Kentucky to Butte, Montana? 10. Which transportation mode should Walnut Hill use for Tender Most? Why? Background Walnut Hill is a well-established smoked foods company specializing in smoked turkeys and has been selling its products throughout the northwestern part of the continental United States since 1967. Initially owned and operated by a German immigrant family, the key to the company's success lay in a secret smoking process native to Rhineland, Germany. By 1998 , the firm owned 250 retail outlets in addition to 400 franchise arrangements. Though the company sold a variety of turkey products, its competitive strength lay in one particular type of smoked turkeys: Tender Most (2020 sales: $11,475,250 ). The 2021 sales were forecasted to have a 11% growth over the 2020 sales level. The turkeys were supplied by Kentucky Meadow Turkeys, Inc. The supplier cleaned and smoked the turkeys using a patented process, and provided the necessary industrial packaging for safe shipment to the retail outlets. Walnut Hill coordinated the remaining retailing functions. The cured and packaged turkeys were purchased from the supplier on a FOB origin basis, and sold to the retail outlets on a FOB destination basis. Approximately 50% of the sales revenue was attributable to direct variable costs; 75% of the direct variable costs were estimated to be actual turkey costs. These costing percentages were expected to hold for the next few years. Tender Most was sold to retail outlets for $6.85 per pound. Products The smoking process employed by Walnut Hill had some unique characteristics which clearly differentiated the company's products from those of its competitors. The Walnut Hill smoked turkeys could be refrigerated for up to 14 days without spoiling. Moreover, they remained fresh and edible for another seven days even without refrigeration. These features, however, did not permit the company to engage in forward buying since the freezer costs were relatively high. Consequently, the company purchased the turkeys from the Kentucky supplier in simple economic order quantities. It took an estimated six days for the railroad freezer cars to bring the smoked turkeys from the supplier's factory in Kentucky to Butte, Montana (Walnut Hill's centralized distribution point), and another two days on average for the commercial trucks to deliver them to the various retail outlets. Tender Most was shipped in the pack size of 25 pounds. The inventory carrying costs were 18%, and the in-transit inventory carrying costs were 16% per annum. The order processing costs were estimated at $27.50 per order. Walnut Hill paid \$8.25 per hundred pounds shipped for the refrigerated railcars in freight, and another $14.65 per hundred pounds for the commercial trucks (LTL shipments to the stores, on a daily basis). Planning Issues for 2021 From a logistical standpoint, management wanted to examine two issues for the planning year of 2021. In 2020, the company found itself out of stock of Tender Most on several occasions while the fresh goods were in transit. Since Tender Most was one of their largest selling brands, the company wanted to minimize its costs of lost sales by determining the best reorder point and the best order size. It was estimated that the stockout costs approximate $55 per case (in future losses) in addition to the obvious contribution margin losses. The sales department found the following distribution of daily demand from retailers for 2019 on reviewing the last working days: The current reorder policy was to place fresh orders as soon as the on-hand inventory was down to 48,575 pounds of Tender Most (1,943 cases). Management wanted to use this information to objectively determine: 1) the optimal reorder point, and once this was fixed, 2) to compute the optimal quantity of cases to order each time. In addition to the objectives stated above, management wished to evaluate an alternate mode/method of transportation. The company could stop the use of the freezer cars for delivery up to Butte, Montana, and use trucking instead. The delivery to the retail outlets planning year of 2021. In 2020, the company found itself out of stock of Tender Most on several occasions while the fresh goods were in transit. Since Tender Most was one of their largest selling brands, the company wanted to minimize its costs of lost sales by determining the best reorder point and the best order size. It was estimated that the stockout costs approximate $55 per case (in future losses) in addition to the obvious contribution margin losses. The sales department found the following distribution of daily demand from retailers for 2019 on reviewing the last working days: The current reorder policy was to place fresh orders as soon as the on-hand inventory was down to 48,575 pounds of Tender Most (1,943 cases). Management wanted to use this information to objectively determine: 1) the optimal reorder point, and once this was fixed, 2) to compute the optimal quantity of cases to order each time. In addition to the objectives stated above, management wished to evaluate an alternate modelmethod of transportation. The company could stop the use of the freezer cars for delivery up to Butte, Montana, and use trucking instead. The delivery to the retail outlets would also continue to be done by motor carrier. It was decided that if any changeover is economical for the company and the new system (if any at all) works well for Tender Most in 2021, in subsequent years, similar feasibility exercises could be carried out for Golden Best, the next best-selling product for Walnut Hill. The cost for common-carriage TL transportation was estimated to be \$12.60 per cwt. from Kentucky to Butte, MT (load of 30,000lbs.). The lead time for this option is 3 days with a standard deviation of 1/2 day