Question

2. Write the complete linear programming model for the following transportation problem. ABC Building Materials operates two factories and those factories ship supplies to three distribution centers. The cost for shipping is shown in the following table along with Supply and Demand quantities: Distribution Centers Des Moines Kansas City St. Louis Factory 1 $14 1 6 Factory 28105. Supply 30 20 Demand 25 15

Answer 1

Let Assume Xij represent the unit transported from Factory to Distribution Centers where i = 1,2 (Factory) and j = 1,2,3 (DC)

Objective - Min Z = 14X11 + 16X12 + 7X13 + 8X21 + 10X22 + 5X23

s.t.,

X11+X12+X13<= 30

X21+X22+X23<=20

X11+X21 >= 25

X12+X22 >= 15

X13+X23 >= 10

Xij >=0

Below is the screenshot of the LP table, formula applied , solver table and result as required -

File Home Insert Page Layout Formulas Data X Cut Еe Copy Format Painter Clipboard Calibri v11 VA A BIU - DA. = Paste Font A1 - X Fax From/To B C D Des Moines Kansas City St. Louis A 1 From/to 2 Factory 1 3 Factory 2 4 Demand E Supply 30 20 T 14 00 10 5 10 25 15 Supply 7 Decision Table Des Moines Kansas City St. Louis 8 Factory 1 9 Factory 2 10 Demand 0 13 Cost 14

2020 - Excel fx F ? A ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - Used - - * Time Reference Trigº Functions Manager Function Library A1 X fy From/To .l le - Tell me what you want to do Define Name Fi Use in Formula Create from Selection Defined Names - Trace Prec Trace Depe Remove A Des Moines Kansas City St. Louis Supply 1 From/To 2 Factory 1 3 Factory 2 4 Demand ulola 5 Des Moines Kansas City St. Louis 7 Decision Table 8 Factory 1 9 Factory 2 10 Demand Supply =SUM(B8:08) =SUM(B9:09) =SUM(B8:39) =SUM(C8:09) =SUM(D8:09) 11 12 13 Cost =SUMPRODUCT(B2:D3,38:09) 14

Solver Parameters Revie So File Home Insert Page Layout Formulas Data Show Queries Connections Properties Get External New Refresh Data Query Le Recent sources All Edit Links Get & Transform Connections Set Objective: $B$13 From Table To: O Max Min O Value of: By Changing Variable Cells: SB58:5D59 From/To Subject to the Constraints: SBS10:SD$10 >= SB$4:$D$4 SBS8:SD$9 >= 0 SE58:SE$9 <= SE$2:SES3 Ε Add Change 1 From/To 2 Factory 1 3 Factory 2 4 Demand 5 Des Moines Kansas City St. Louis 16 10 5 15 10 Supply 30 20 Delete GO Reset All Des Moines Kansas City St. Louis Supply Load/Save 7 Decision Table 8 Factory 1 9 Factory 2 10 Demand Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 12 13 Cost 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 Solve Close

Review Soes File Home Insert Page Layout Formulas Data Show Queries Connections From Table E Properties Get External New Refresh Data Query - Lo Recent Sources All Edit Links Get & Transform Connections 5 Sto A1 x fc From/To 1 From/To 2 Factory 1 3 Factory 2 4 Demand C D E Des Moines Kansas City St. Louis Supply 16 7 130 10 5 20 10 15 6 7 Decision Table 8 Factory 1 9 Factory 2 10 Demand Des Moines Kansas City St. Louis Supply 15 10 30 2000 20 25 15 10 11 12 13 Cost 540 14