I have attached the excel file that you need to use to answer each of the questions posted below. There is a corresponding tab to each question. Please do not reformat the excel sheet.
Do your analysis for each of the following problems in the spreadsheet template provided.
1.TXOil, a refiner in Texas, serves four customers near Huntsville, AL. The inventory at each customer location is owned by TXOil. Currently, TXOil uses a truckload (TL) transportation to deliver separately to each customer. Each truck costs $800 plus $250 per stop. Thus, delivering to each customer separately costs $1,050 per truck. TXOil is considering aggregating deliveries to Huntsville on a single truck. Demands at the four customers are 60 tons a year, 45 tons a year, 30 tons a year, and 15 tons a year, respectively. Product cost for TXOil is $10,000 per ton, and it uses a 25-percent holding cost rate. Truck capacity is 15 tons.
a. What is the average annual transportation and holding cost if TXOil ships a full truckload each time a customer is running out of stock?
b. What is the optimal delivery policy (shipment/order quantity and timing) to each customer if TXOil ships separately to each of them? What is the average annual transportation and holding cost?
c. What is the optimal delivery policy if TXOil aggregates shipments to each of the four customers on every truck that goes to Huntsville? What is the average annual transportation and holding cost?
2. (Postponement) HP produces four printers that have a particular main component that is common to all four products. Currently, each brand manager for each product forecasts the expected demand and then passes onto the production and they produce each item separately. The forecasted demand for each product is given in Excel, under tab “Problem 2.”
a. If each product is produced separately, given a CSL=95-percent, how much safety inventory HP require for each product?
b. It is possible to coordinate the production of these four products by only forecasting the demand for the common product and postponing the individual production. By calculating the combined (aggregated) demand for all products, please determine the safety stock required for a CSL=95-percent.
c. If inventory holding cost is estimated as 20% of the unit cost ($200), how much savings in holding cost can HP expect with postponement?