USE EXCEL TEMPLATE

Problem 2: A company has three manufacturing plants (in Atlanta, Tulsa, and Springfield) that produce a product that is then shipped to one of four distribution centers - A, B, C, or D. The three plants can produce 15, 22, and 15 truckloads of product each week, respectively. Distribution centers A & B need 12 truckloads of product each week and Distribution centers C & D need 13 truckloads of product each week. The shipping costs per truckload between the plants and distribution centers are given in the table. The company needs to determine how much to ship from each plant to each distribution center, and would like to minimize total shipping costs. Distribution Center Plant A D Atlanta $ 800 $1300 $400 $ 700 Tulsa $1400 $600 $1000 $1100 $ 600 Springfield $1200 $800 900 a. What are the objective function, decision variables, and constraints for this optimization problem? b. Develop an Excel spreadsheet and use Excel-Solver to find an optimal solution for this problem. c. Describe the optimal solution in words. Transportation Model Objective Function Objective Function: Decision Variables: Constraints: Decision Variables and Constraints Truckloads committed from Plant to DC Distribution Center Plant A B Atlanta Tulsa Springfield Total Out of Plant Capacity 1 15 V V 22 15 b. What is the objective function value? How many truckloads are shipped from each plant to each DC? Are all the constraints satisfied? V Total into DC DC demand 12 1 2 1313 Data D Shipping Costs ($) per Truckload Distribution Center Plant A Atlanta 800 1300 Tulsa 1100 1400 Springfield 600 1200 400 600 800 700 1000 900