Question

SHOW ME EXCEL STEPS BY STEPS PLEASE

3. Devos Inc. wants to figure out the most cost efficient shipping routes from 3 plants to 3 different warehouses. Transportation costs are as follows:

                                          Warehouse  

Plant                              1                 2            3         Capacities (supply)

A                               3.21       1.45       2.13          4,000

B                               3.35        5.43       1.60         6,000

C                               4.25     3.27     3.11         3,000

Requirement              8,000        2,000   3,000 (demand)

1. What routes get taken and how much gets shipped over each route?
2. In a separate tab than part a) (label as Q3b), Use the same information in part A, except that plant B now can supply only 5,000 units. Re-solve. Which warehouse (or warehouses) will not have their demand met, and how many units will they be short?
3. In a separate tab than part a) an d b) (label as Q3c), use the same information in part A (note, not part b – use original data in part a), except that now warehouse 2 can ship to warehouse 1 for $0.34. What routes are taken and how much gets shipped over each route?

​

Answer 1

Plant	Warehouse 1	Warehouse 2	Warehouse 3	Capacities
A	3.21	1.45	2.13	4000
B	3.35	5.43	1.6	6000
C	4.25	3.27	3.11	3000
Requirement	8000	2000	3000

Decision Variables:

Let Xij be the units shipped form plant i to warehouse j.

Plant	1	2	3
A	X11	X12	X13
B	X21	X22	X23
C	X31	X32	X33

The objective is to minimize the shipping cost

Zmin = 3.21X11+1.45X12+2.13X13+3.35X21+5.43X22+1.6X23+4.25X31+3.27X32+3.11X33

Subject to Constraints:(Plant can provide only up to its capacity, hence relationship is ≤)

X11+X12+X13 ≤ 4000 (Capacity of Plant A)

X21+X22+X23 ≤ 6000 (Capacity of Plant B)

X31+X32+X33 ≤ 3000 (Capacity of Plant C)

Requirement Constraint: (The total units shipped to each warehouse has to meet the respective demand at least, hence relationship is ≥)

X11+X21+X31 ≥ 8000 (Demand of Warehouse 1)

X12+X22+X32 ≥ 2000 (Demand of Warehouse 2)

X13+X23+X33 ≥ 3000 (Demand of Warehouse 3)

Xij ≥ 0 (Non-Negativity Constraint)

Solver Steps:

1. Parameters:

The objective, changing cells, constraints are added in excel solver. The above screenshot is for the reference. Then click on solve.

2. Solution:

Ans to Question a.

Routes:

Plant A -> Warehouse 1 = 2000

Plant B -> Warehouse 1 = 3000

Plant C -> Warehouse 1 = 3000

Plant A -> Warehouse 2 = 2000

Plant B -> Warehouse 3 = 3000

Minimum Cost = 36920

Ans to Question b:

Plant B only can supply 5000 units instead of 6000. Hence the capacity is changed for plant B.

The solver parameters are revised. The red circled cell shows the capacity is changed to 5000.

Again solving the LP, shows an error that solver cannot find a feasible solution for which all constraints are satisfied. Click ok to see which constraint did not get satisfied.

Solution

The demand constraint of Warehouse 3 is not satisfied. This means the demand for warehouse 3 is not satisfied by 1000 units.

Formulae used in excel

Ans to Question c:

Warehouse 2 can provide to Warehouse 1 for $0.34. There is one supply node included for Warehouse 1 and the demand for warehouse 2 has to be increased from 2000 units.

Reformulating the LP:

Decision Variables are the same as before but we have to add one more variable for the shipping from warehouse 2 to warehouse 1. Let X41 be the units shipped from warehouse 2 to warehouse 1.

Plant	1	2	3
A	X11	X12	X13
B	X21	X22	X23
C	X31	X32	X33
Warehouse 2	X41

Objective Function will change:

Z min = 3.21X11+1.45X12+2.13X13+3.35X21+5.43X22+1.6X23+4.25X31+3.27X32+3.11X33+0.34X41

Constraints:

Constraints are the same as before except:

X11+X21+X31+X41 ≥ 8000 (Demand of Warehouse 1)

X12+X22+X32 ≥ 2000+X41 (Demand for Warehouse 2)

The demand for warehouse 2 has to increase to supply to warehouse 1 because now the warehouse 2 would ship to warehouse 1 as well as meet its demand.

As the capacity details are not mentioned for warehouse 2, it is not considered in the constraint.

Solving the LP in solver:

Parameters: The changing variable cell has been changed by adding the X41 reference as highlighted in the screesnshot.

Formulae used:

Solution:

Routes:

Plant A -> Warehouse 2 = 4000

Plant B -> Warehouse 1 = 3000

Plant B -> Warehouse 3 = 3000

Plant C -> Warehouse 2 = 3000

Warehouse 2 -> Warehouse 1 = 5000