Problem 1-A 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. The table below shows the production capacities and estimated demands 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 given their available space for storage. Plan the shipping quantities on each shipping lane so that the total shipping cost is lowest between the plants and warehouses. (Solver is required) Plants (From) Warehouses (To) Decatur Minneapolis Carbondale E. St. Louis Demand Blue Earth $20 $17 $21 $29 250 Ciro $25 $27 $20 $30 200 Des Moines $22 $25 $22 $30 350 Capacity 300 200 150 150 blue earth 0 200 0 50 ciro 0 0 100 100 des moines 300 0 50 0 cost of production plan = 62250 62250 (Continued from Problem 1-A) Dana Johnson Corp was just notified that -- the estimated demands should be revised to: 250 units, 220 units, and 375 units for Blue Earth, Ciro and DesMoines, respectively (according to the new data from market research); -- The production capacities at four plants provided previously are for regular-hour production. Production using overtime (OT) hours will be available only at the Decatur plant, where the unit OT production cost = $65 per unit and the OT capacity = 100 units; -- 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 in Problem 1-A, 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 and production costs when designing the plan. The table below shows unit production costs at each plant and the unit shipping costs for each shipping lane between a plant and a warehouse are provided already in Problem 1-A. , 1 2 Provide an optimal plan where the total shipping and production cost is the lowest. (You need to create the template and Solver is required) B + TABLE 5 Production cost per unit 5 Decatur 7 B $65 Decatur Minneapolis Carbondale E. St. Louis Regular hours Overtime $50 $60 $70 $40 9 , 1 2 B + 5 5 7 B