Question

FlexSteal, marketed as a miracle brush-on sealant and baldness cure, is manufactured at three plants that are struggling to keep up with the demand at the four regional centers. The cost to ship a truckload from each of the existing plants to the regional centers is shown in the table. Note that the capacity and the total demand are both measured in truckloads. Philadelphia Atlanta Dallas Capacity Plant 1 89 75 93 400 Los Angeles 115 93 98 Plant 2 120 88 103 400 Plant 3 95 82 112 400 Total Demand 350 350 350 350 The supply chain manager has explored plant construction costs in two other cities as well as the cost to ship a truckload from each of the plants to the regional centers. The current plan is to build another plant with a capacity of 400 to allow room for sales growth. The construction cost for the new plants and cost to ship to each of the regional centers is shown here. Philadelphia | Atlanta Dallas Los Angeles Cost to Build Plant A 94 78 95 95 $23,500 Plant B 95 88 - 88 94 $26,000

What is the constraint associated with Plant 2 for the existing set of plants and regional centers (without adding the new plant)? Hint: you need to use all the capacity.

	A.	120x2P + 88x2A + 103x2D + 93x2L = 400
	B.	x2P + x2A + x2D + x2L ≤ 400
	C.	x2P+ x2A + x2D + x2L = 400
	D.	120x2P + 88x2A + 103x2D + 93x2L ≤ 400

Please give me excel solution

Answer 1

Correct answer is (This constraint describes capacity constraint of plan B)

B.	x2P + x2A + x2D + x2L ≤ 400

Let,In this case, Decision variables have been identified as,

xij = number of units shipped from plant i to regional center j where i = {1,2,3,A=4} and j = {Philadelphia = P,Atlanta=A,Dallas=D,Los Angeles=L}

------------------------------------------------------------------------------------------------------------------------------------------------------

For your reference please find the solution below

With Plant A,

Let,

xij = number of units shipped from plant i to regional center j where i = {1,2,3,A=4} and j = {Philadelphia = 1,Atlanta=2,Dallas=3,Los Angeles=4}

Spreadsheet model

A B C D E F G H I J K L M N O P Q R S T U v Objective function 148350 0 24x31x32x33x34x41x42x43x44 350 250 0 0 0 0 50 350 0 x11 x12x13x14x21 22 23 100 300 0 0 0 0 1 1 1 1 1 1 400 <= 350 1 250 1 3 4 Plant 1 5 Plant 2 6 Plant 3 7 Plant A 8 Philadelphia 9 Atlanta 10 Dallas 11 Capacity 12 400 400 400 400 350 350 350 350 1 350 = 350 = 350 = 1 350 =

Formula used

4 A B C D E F G H I J K L M N O P Q R S T U Objectiv x11 x12 x13x14x21 x22x23x24 x31 x32 33 34 x41 42 43 44 function=896B3+75*C393 D3+115 E3+120*F3+88*G3+103*H3+93*13+9513+82 K3+112 L3+98*M3+94*N3+780395*P3+95 Q3+23500 100 300 0 0 0 0 0 350 250 0 0 0 0 50 350 0 4 Plant 1 1 1 1 1 =SUMPRODUCT($B$3:5Q$3,B4:04) <= 400 5 Plant 2 1 1 1 1 =SUMPRODUCT($B$3:5Q$3,85:05) <= 400 6 Plant 3 1 1 1 1 =SUMPRODUCT($B$3:5Q$3,06:06) <= 400 7 Plant A 1 1 1 1 =SUMPRODUCT($B$3:$Q$3,17:07) <= 400 PhilaD 8 elphia 1 =SUMPRODUCT($B$3:5Q$3,38:08) = 350 9 Atlanta =SUMPRODUCT($B$3:5Q$3,19:09) = 350 10 Dallas =SUMPRODUCT($B$3:5Q$3,B10:010 = 350 11 Capacity 1 =SUMPRODUCT($B$3:5Q$3,B11:01: = 350 12

Solver window input

Solving in solver we get following optimal schedule

Solver Parameters Set Objective: sv52 Value Of: To: O Max O Min By Changing Variable Cells: $B$3:5053 Subject to the Constraints: SR$4:$R$7 <= $T$4:$T$7 SR$8:SRS11 = $T$8:$T$11 Add Change Delete Reset All Load/Save ✓ Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help I solve [ close

	Philadelphia	Atlanta	Dallas	Capacity
Plant 1	100	300	0	0
Plant 2	0	0	0	350
Plant 3	250	0	0	0
Plant A	0	50	350	0

Minimized cost = 148350

With Plant B,

Spreadsheet model

B C D E F G H I J K L M N O P Q R S Objective function 148550 x11x12x13x14x21x22 50 350 0 0 0 1 1 1 1 x23x24 x31x32 0 0 350 300 233 0 234 0 241 x42x43x44 0 0 0 350 1 0 1 4 Plant 1 5 Plant 2 6 Plant 3 7 Plant B 400 <= 350 <= 300 <= 350 <= 1 400 350 8 Philadelphia 9 Atlanta 10 Dallas 11 Capacity 350 = 350 = 350 = 350 = 350 350

Formula used

A B C D E F G H I J K L M N O P Q T U Objectiv function = 89'B3+75 C3+93 D3+115'E3+120"F3+88"G3+103'H3+93'13+95 J3+82 K3+112'L3+98 M3+95 N3+88'O3+88*P3+94'Q3+26000 4 5 6 7 211 212 213 214 221 222 223 224 231 50 350 0 0 0 0 0 350 300 Plant 1 1 1 1 1 Plant 2 1 1 1 1 Plant 3 1 Plant B PhilaDelphi 232 233 234 x41x42 243 244 0 0 0 0 0 350 0 =SUMPRODUCT($B$3:$Q$3,B4:04) =SUMPRODUCT($B$3:$Q$3,B5:05) 1 1 1 =SUMPRODUCT($B$3: $Q$3,B6:06) = SUMPRODUCT($B$3:$0$3,B7:07) <= 400 <= 400 <= 400 <= 400 9 10 11 Atlanta Dallas Capacity = SUMPRODUCT($B$3:$0$3,38:08) = 350 =SUMPRODUCT($B$3:$Q$3,69:Q9) = 350 =SUMPRODUCT($B$3:$Q$3,B10:Q10) = 350 =SUMPRODUCT($B$3:$Q$3,B11:Q11) = 350

Solver window input

Solver Parameters Set Objective: sv52 Value Of: To: O Max O Min By Changing Variable Cells: $B$3:5053 Subject to the Constraints: SR$4:$R$7 <= $T$4:$T$7 SR$8:SRS11 = $T$8:$T$11 Add Change Delete Reset All Load/Save ✓ Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help I solve [ close

Solving in solver we get following optimal schedule

	PhilaDelphia	Atlanta	Dallas	Capacity
Plant 1	50	350	0	0
Plant 2	0	0	0	350
Plant 3	300	0	0	0
Plant B	0	0	350	0

Minimized cost = 148550