Question

For the given transportation problem, formulate a linear program with objective function and constraints. Solve using the excel sleeve, provide the optimal transport cost.

Imagine that we have three bakeries and three stores. the three stores require 23 dozen, 17 dozen, and 20 dozen loaves of bread, respectively, while the three bakeries can supply 18 dozen, 15 dozen, and 22 dozen loaves, respectively. The unit transportation costs are provided in the table below:

	Store 1	Store 2	Store 3
Bakery 1	8	9	3
Bakery 2	15	2	12
Bakery 3	4	7	8

Provide the LP formulation (only the objective function and one supply and one demand constraint) and solve to optimal using Excel.

Answer 1

Total demand>Total supply.Hence,we add a dummy bakery with cost=0 and supply=5.

LP formulation:

Let Cij be the cost from bakery  i to store  j .

Let Xij be the number of units transported from bakery i to store j .

Objective function =Z= Min i j (Xij * Cij ) ,where i=1,2 ,3,4 and j=1,2,3

(i=4 for dummy bakery)

Supply constraint:

X11+X12+X13<=18

Demand constraint:

X11+X21+X31+X41=23

Using excel solver,we find the optimal solution as shown below:

Min cost=Min SUMPRODUCT(B3:D6,B9:D12)=$ 172

In excel,

B17=SUM(B9:D9)

B18=SUM(B10:D10)

B19=SUM(B11:D11)

B20=SUM(B12:D12)

B21=SUM(B9:B12)

B22=SUM(C9:C12)

B23=SUM(D9:D12)

The optimal shipping pattern is highlighted in yellow: