USG Corporation is a major manufacturer of mold-resistant building materials. The company's flagship product is Sheetrockc Brand Humitekc Gypsum Panels, which can be installed on wall or ceiling interiors in order to minimize moisture and mold damage in building areas that are subject to incidental moisture. USG has just won a coveted contract with a major construction company in the growing Glendale, AZ, area. This company is expected to require 24,000 pounds of Sheetrockc Panels per day for the next year in order to satisfy its busy residential and commercial construction schedule. This contract is so lucrative that USG has agreed to operate a dedicated distribution center near Glendale that carries a safety stock of 100,000 pounds of inventory in order to fulfill the construction company's orders quickly. The USG traffic manager wants to determine the mode of transportation to utilize for shipments from the USG production facility in Santa Fe Springs, CA (zip code 90670) to the new distribution center in Glendale, AZ (zip code 85301) in order to minimize the total annual logistics cost. The feasible modes of transportation for these shipments are railcar and truckload.
The lane between Santa Fe Springs and Glendale is served by the Burlington Northern Santa Fe (BNSF) Railroad. The average transit time for rail shipments on this lane is 9.5 days. According to BNSF's website, the cost of a carload of gypsum wallboard weighing less than 167,000 pounds is $2,964.53. The rate for shipments greater than 167,000 pounds is $3,353.53.
The rates you obtained above are ramp-to-ramp rates; thus, they only cover transportation from BNSF's terminal near Santa Fe Springs to its terminal near Glendale. USG has contracted with a third-party logistics provider that coordinates the local delivery and transloading services from USG's facility to the BNSF terminal as well as the final delivery in Glendale. The negotiated rate is a flat $100 per carload (regardless of its size) plus a variable cost of $0.002 per pound of freight in the carload.
1 . Using the following cost data and additional information about USG's production and inventory policies, determine the optimal shipment quantities (in pounds) for small railcar shipments (≤ 167, 000 pounds), large railcar shipments (> 167, 000 pounds), and truckload shipments.
- The value of 1 pound of Sheetrockc Panels held at the plant in Santa Fe Springs is $0.77.
- The value of 1 pound of Sheetrockc Panels held at the warehouse in Glendale is $0.85.
- USG's inventory holding costs are computed as 39% of the inventory's value per year held. (Take a simple average of the two values to determine the value of 1 pound of panels while in transit.)
- Storage space at the plant in Santa Fe Springs costs $0.27 / (pound*year).
- Storage space at the warehouse in Glendale costs $0.33 / (pound*year).
- The average truckload transit time is 2 days.
- The USG plant in Santa Fe Springs produces 24,000 pounds of Sheetrockc Panels per day.
2. Compute the total annual logistics cost for all three methods of transportation using the optimal shipment quantities for each mode. Which mode results in the minimum annual cost?
questions can be solved in excel. step by step process needs to be provided on how to solve in excel