USE EXCEL SOLVER

EXAMPLE

Evansville is now also included as a destination. These 300 desks are shipped from the Fort Lauderdale factory to the Evansville factory Evansville now has nonzero values for flow in and flow out. 0.0 = = B CD F G H K L M 1 Executive Furniture (Transshipment) 2 3 Shipments: Flow balance equations 4 From Albuquerque Boston Cleveland Evansville Flow out Location Flow in Flow out Net flow 5 Des Moines 0.0 100.0 0.00 100.0 Des Moines 100 - 100 -100 6 Evansville 300.0 200.0 1000 0.01 600.0 Evansville 300 600 -300 -300 7 Fort Lauderdale 0.0 0.0 0.0 300.00 300.00 Fort Lauderdale 3001 -300 = 300 8 Flow in 300.0 200.0 200.0 300.01 Albuquerque 300 300 300 9 Boston 200 200 200 10 Unit costs: To Cleveland 200 200 200 11 From Albuquerque Boston Cleveland Evansville LHS Sign RHS 12 Des Moines $5 $4 $3 $2 13 Evansville Solver Parameters $3 $2 $1 $0 14 Fort Lauderdale 59 57 $5 $3 15 Set Objective: $8$16 16 Total cost = $2,600.00 17 To: Value of: 18 19 SUMPRODUCT(B5:E7,B12:E14) By Changing Variable Cels: 58$5:$E$7 21 22 Subject to the Constraints: 23 24 SK$5:$K$10 = $M$5:$M$10 Max 20 Problem 1: Using Analytical Model A supply chain consists of three plants (A, B, and C), three distributors (J, K, and L), and three stores (X, Y, and 2). The relevant supply, demand, and unit shipping cost information are given in the table for this problem. Set up and solve the transshipment model to minimize total shipping costs. To To Plant Supply Store Demand From J KL From Y Z J K L A X 400 A 400 500 350 B J K Y B $4 $7 $5 $8 $5 $4 $5 $6 $7 325 400 $6 $4 $8 $6 $5 $3 $6 $7 $6 $7 $2 $4 $5 $5 $7 C Z C L