Question

Subject (Supply Chain Management)

Note :- U will use Microsoft Excel

Problem Statement: The problem is to determine the optimal quantity of coil that should be delivered from company's each warehouse to different distributor's warehouse in order to obtain the minimum transportation cost. The company delivers coils from its three warehouses in warehouse 1 (WH1), warehouse 2 (WH2), and warehouse 3 (WH3) to seven distributor's centres in D, C, R2, B, R1, S, and K without considering the optimal quantity. So if the company applies linear programming to find the optimal quantity of coil to be delivered, it will be able to minimize the transportation cost significantly, which will result in increased profitability In order to determine the optimal quantity of the coil that should be delivered from company's each warehouse to different distributor's canters (B, C, D, R1, R2, S, and K) for obtaining the minimum transportation cost, the following information were collected from the Supply Chain Director of the case company: Table 1: Average shipping costs of per carton coil Distributor's Ceters WH2 WHI WH3 Company warehouse 15 160 154 245 130 125 215 160 100 260 56 190 58 204 160 315 410 290 427 375 R2 Ri *All units are in local currency Storage capacity: Storage capacity of company's different warehouses Table 2 Storage Capacity 3980 1785 4856 All units are in cartons WHI WH2 WH3 Demands: Average demand of different distributor's warehouses Table 3 Average Demand 1168 1560 1439 986 1658 2035 1159 RI All units are in cartons WHI R2 WH2 R1 WH3 Fig.1 Illustration of the transportation network. Using excel solver and answer the following: 1) Define decision variables? 2) Which company warehouse (WH1, WH2, and WH3) will supply how many coils to each distributor's canters (B, C, D, R1, R2, S, and K)? 3) What is the total minimum cost in local currency?

Answer 1

1)

Decision variables

Let,

x11 = Number of units moved from WH1 to D
x12 = Number of units moved from WH1 to C
x13 = Number of units moved from WH1 to R2
x14 = Number of units moved from WH1 to B
x15 = Number of units moved from WH1 to R1
x16 = Number of units moved from WH1 to S
x17 = Number of units moved from WH1 to K

x21 = Number of units moved from WH2 to D
x22 = Number of units moved from WH2 to C
x23 = Number of units moved from WH2 to R2
x24 = Number of units moved from WH2 to B
x25 = Number of units moved from WH2 to R1
x26 = Number of units moved from WH2 to S
x27 = Number of units moved from WH2 to K

x31 = Number of units moved from WH3 to D
x32 = Number of units moved from WH3 to C
x33 = Number of units moved from WH3 to R2
x34 = Number of units moved from WH3 to B
x35 = Number of units moved from WH3 to R1
x36 = Number of units moved from WH3 to S
x37 = Number of units moved from WH3 to K

b)

Objective is to minimize transportation cost = Min 15x11+160x12+154x13+245x14+130x15+125x16+215x17+160x21+12x22+315x23+410x24+290x25+427x26+375x27+100x31+260x32+56x33+190x34+58x35+204x36+160x37

Subject to,
Capacity constraints

x11+x12+x13+x14+x15+x16+x17 <= 3980

x21+x22+x23+x24+x25+x26+x27 <= 1785

x31+x32+x33+x34+x35+x36+x37 <= 4856

Demand Constraints

x11+x21+x31 = 1168

x12+x22+x32 = 1560

x13+x23+x33 = 1439

x14+x24+x34 = 986

x15+x25+x35 = 1658

x16+x26+x36 = 2035

x17+x27+x37 = 1159

all variables >= 0 (Non-negaativity constraint)

Solving in excel we get,

x11 = Number of units moved from WH1 to D = 1168
x12 = Number of units moved from WH1 to C = 0
x13 = Number of units moved from WH1 to R2 = 0
x14 = Number of units moved from WH1 to B = 386
x15 = Number of units moved from WH1 to R1 = 0
x16 = Number of units moved from WH1 to S = 2035
x17 = Number of units moved from WH1 to K =0

x21 = Number of units moved from WH2 to D = 0
x22 = Number of units moved from WH2 to C = 1560
x23 = Number of units moved from WH2 to R2 = 0
x24 = Number of units moved from WH2 to B = 0
x25 = Number of units moved from WH2 to R1 = 0
x26 = Number of units moved from WH2 to S = 0
x27 = Number of units moved from WH2 to K =0

x31 = Number of units moved from WH3 to D = 0
x32 = Number of units moved from WH3 to C = 0
x33 = Number of units moved from WH3 to R2 = 1439
x34 = Number of units moved from WH3 to B = 600
x35 = Number of units moved from WH3 to R1 = 1658
x36 = Number of units moved from WH3 to S = 0
x37 = Number of units moved from WH3 to K =1159

c) Total minimum cost = 861373

Solver screenshot

1 0 386 0 2039 0 1560 0 1439 600 1658 01159 Okjective function851313 3 Constraints 3589く. 1560 4856

Sensitivity report

Final Reduced Objective Allovable Allovable Cell Name alue Cost Coefficient Increase Decrease E+30 3 $B$2 11 10 $C$2 12 11$0$2 13 12 E$2 14 13 $F$2 15 14 $G$2 16 15 H$2 x17 16 $1$2 21 17 J$2 22 18 $K$2 x23 13 $L$2 24 15 E+30 E+30 245 386 E+30 2035 E+30 E+30 E+30 1560 E+30 204 204 E+30 E+30 E+30 E+30 E+30 E+30 E+30 290 427 375 21 $N$2 26 22 $0$2 x27 23 P$2 *31 24 $0$2 x32 25 $R$2 33 26$S$2 *34 $T$2 35 28U$2 *36 23 V$2 *37 302 302 303 260 303 1439 600 1658 E+30 E+30 E+30 204 1159 31 Constraints Final Shado Constraint Allovable Allovable R.H. Side ncrease Decrease Cell Name alue Price 34 W$4 35W$5 36 W$6 3589 1560 4856 1168 1560 1439 986 1658 2035 1159 3980 1785 4856 E+30 386 225 15 225 38 W$8 33 W$9 40$W$10 1560 1439 986 1658 2035 1560 386 386 386 2035 386 245 42 W$1220 43 W$13