Question

Using the solver function in Excel, how can you find the optimal way to distribute the items in supply zones Alpha-Echo to Demand regions I-IV? The unit cost of relocating an item from each supply zone to the corresponding demand region are displayed in C4:F8, the supply available in each supply zone Alpha-Echo is located at H3:H7, and the demand in each region I-IV is shown B9:E9.

Ali x ✓ fx A B C D E Demand Region F G H I J Supply Available 3 Supply Zone 4 Alpha 5 Beta Charlie Delta Echo Demand

Answers will be upvoted. Thanks.

Answer 1

Answer:

Here, as there is specific information is mentioned in the question, we will solve the given Transportation problem using the MS Excel Solver by following below-mentioned steps:

Step 1: Prepare the following in the excel

(Note: Make Sure to prepare this table in the exact Raws and Columns as mentioned in the below screenshot)

A1 C V fr Supply Zone E F G Row Total Supply 5 A B C D Supply Zone Demand Region NILII IV Alpha Beta Charlie Delta Echo Demand 30 18 24 | 30 Column Total Total Cost Supply Zone Supply IV 42 L 16 30 72 24 Demand Region L I 48 18 40 38 56 62 34 18 24 34 44 Alpha Beta Charlie Delta Echo Demand 18 62 16 28 60 30 30

Step 2: Now, type the following formulas in the column of 'Row Total', row of "Culmn total" and cell of 'Total Cost' as mentioned in the below screenshots. Then, press enter.

Row Total 3 =SUM(B3:E3) 4 =SUM(B4:E4) 5 =SUM(B5:E5) 6 =SUM(B6:E6) 7 =SUM(B7:57)

A Column Total Total Cost =SUM(C3:07) =SUM(D3:D7) =SUM(E3:E7) =SUM(B3:37) =SUMPRODUCT(B3:E7,B15:E19) 10

Hence, we will get the following table:

B E F A Supply Zone D Demand Region L III G Row Total T IV Supply wn- 0 0 16 24 18 16 D L 3 Alpha 4. Beta 5 Charlie 6 Delta 7 Echo 8 Demand 1 9 Column Total 10 Total Cost 28 301 18 24 130 0 10 10 10 13 Supply Zone Supply 14 / IV 42 56 16 Demand Region L III 48 / 18 40 38 L 44 / 56 58 62 15 Alpha 16 Beta 17 Charlie 18 Delta 19 Echo 20 Demand 30 72 34 50 - 82 ISININIONIS 52 34 62 60 30 28 30 18 24

Step 3: Now, open Solver ( Data > Analyze > Solver), and fill the exact data in Solver Pop-Up Window as shown in the below screenshot:

Solver Parameters Set Objective: $B$10 To: O Max O Min O Value of: By Changing Variable Cells: $B$3:$E$7 Subject to the Constraints: $B$9:$E$9 >= $B$8:$E$8 $G$3:$G$7 <= $F$3:$F$ 7 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 Solve Close

Step 4: Once, you press solve button as mentioned in the above step, you will get the following table. Here, the highlighted values are the optimal allocation and the optimal minimum transportation cost:

B E F A Supply Zone C ID Demand Region III O 8 IV Supply 0 16 8 6 G Row Total 16 24 18 16 28 0 24 18 3 Alpha 4 Beta 5 Charlie 6 Delta 7 Echo 8 Demand Column Total 10 Total Cost 18 O 16 28 12 0 0 16 O 30 30 16 30 24 24 30 18 4212 77 Supply Zone IV Supply Demand Region I III 48 40 | 38 ne 18 16 42 56 24 56 82 15 Alpha 16 Beta 17 Charlie 18 Delta 19 Echo 20 Demand 30 72 34 50 44 30 62 62 60 18 16 28 52 34 I 24 30

We were unable to transcribe this image

Row Total 3 =SUM(B3:E3) 4 =SUM(B4:E4) 5 =SUM(B5:E5) 6 =SUM(B6:E6) 7 =SUM(B7:57)

We were unable to transcribe this image

B E F A Supply Zone D Demand Region L III G Row Total T IV Supply wn- 0 0 16 24 18 16 D L 3 Alpha 4. Beta 5 Charlie 6 Delta 7 Echo 8 Demand 1 9 Column Total 10 Total Cost 28 301 18 24 130 0 10 10 10 13 Supply Zone Supply 14 / IV 42 56 16 Demand Region L III 48 / 18 40 38 L 44 / 56 58 62 15 Alpha 16 Beta 17 Charlie 18 Delta 19 Echo 20 Demand 30 72 34 50 - 82 ISININIONIS 52 34 62 60 30 28 30 18 24

Solver Parameters Set Objective: $B$10 To: O Max O Min O Value of: By Changing Variable Cells: $B$3:$E$7 Subject to the Constraints: $B$9:$E$9 >= $B$8:$E$8 $G$3:$G$7 <= $F$3:$F$ 7 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 Solve Close