Integer Optimization/Optimization. Use Excel to build an LP model, then use the solver to solve questions. In your Excel file, besides your data sheets, you need to have a designated sheet only for your answers.

A manufacturer has three plants and four warehouses. Each plant has a potential capacity, expressed in cartons of product, and each warehouse has a demand requirement for the week that must be met. There are 12 possible shipment routes, and for every plant-warehouse combination, the unit shipping cost is known. The following table provides the given information. The product is subject to evaporation in the tanker trucks used for distribution purposes. The average amount of evaporation depends the distance traveled and the average temperature. The following table shows the corresponding yield loss as it has been observed to occur on each of its shipping routes. (a) [4pts] What is the minimal shipping cost and the associated optimal shipping schedule? (b) [3pts] A new type of tanker trucks can completely eliminate the yield loss. If the manufacturer can replace its fleet with the new trucks, how much can it save on shipping cost (with possibly new schedule)? How should it change the optimal shipping schedule? A manufacturer has three plants and four warehouses. Each plant has a potential capacity, expressed in cartons of product, and each warehouse has a demand requirement for the week that must be met. There are 12 possible shipment routes, and for every plant-warehouse combination, the unit shipping cost is known. The following table provides the given information. The product is subject to evaporation in the tanker trucks used for distribution purposes. The average amount of evaporation depends the distance traveled and the average temperature. The following table shows the corresponding yield loss as it has been observed to occur on each of its shipping routes. (a) [4pts] What is the minimal shipping cost and the associated optimal shipping schedule? (b) [3pts] A new type of tanker trucks can completely eliminate the yield loss. If the manufacturer can replace its fleet with the new trucks, how much can it save on shipping cost (with possibly new schedule)? How should it change the optimal shipping schedule