Dana Johnson Corp has four plants in Decatur, Minneapolis, Carbondale, and E. St. Louis with different production capacities. Note that production capacity is the maximum amount the plant can produce, but the plant does not have to produce that many if not needed. The finished goods are shipped from four plants to three warehouses in Blue Earth, Ciro, and Des Moines according to the demands expected at these warehouses. TABLE 1 shows the production costs per unit and production capacities at each plant. Note that production at each plant has used regular time (RT) hours; production using overtime (OT) hours with a higher unit production cost is available only at the Minneapolis plant, when needed.

TABLE 2 shows the estimated demands at each warehouse for next quarter, along with the shipping costs per unit for each shipping lane. The warehouse demand should be met, but the warehouse can receive more from the plants given their available space for storage. The management indicated that the shipment from Decatur to Des Moines will not be available next quarter due to disagreement over the contract between the management and the carrier.

The manager considers that how you plan the shipping schedule has an impact on not only the shipping quantities but also next quarter's production quantities at each plant. So, he would like to evaluate shipping costs together with production costs for those units on the shipping schedule (i.e., those units scheduled to be shipped on all shipping lanes). Plan the shipping quantities on each shipping lane so that the total cost under consideration is lowest. (Solver is required)

PLEASE INCLUDE THE PROPER EXCEL FORMULAS FOR THE CHART

with a higher unit production cost is available only at the Minneapolis plant, when needed. between the management and the carrier. total cost under consideration is lowest. (Solver is required)