Supply Chain Modeling with linear programming and Excel

Problem 5. (5 points) Seahawk Corp. supplies its four retail outlets from its four plants. The shipping cost per shipment from each plant to each retail outlet is given below. Unit Shipping Cost Plant Retail Outlet 1 Retail Outlet 2 Retail Outlet 3 Retail Outlet 4 1 $500 S600 S400 $200 2 200 900 100 300 3 300 400 200 100 4 200 100 300 200 Plants 1, 2, 3, and 4 make 10, 20, 20, and 10 shipments per month, respectively. Retail Outlets 1, 2, 3, and 4 need to receive 20, 10, 10, and 20 shipments per month, respectively. The distribution manager, Tina Belcher, now wants to determine the best plan for how many shipments to send from each plant to the respective retail outlets each month. Tina's objective is to minimize the total shipping cost. a. Draw the network representation of the above problem. Identify which nodes are the supply nodes and which are the demand nodes. Identify the value for each node. b. Formulate this problem as a LP problem in algebraic form. SCM 478 - Supply Chain Modeling & Analysis c. Formulate this LP model as a transportation problem on a spreadsheet in Excel and then use Solver to obtain an optimal solution