Transportation Problem ( TP )
Case : ABC Ltd.
A product is produced at three plants and shipped to three
warehouses (the transportation costs per unit are shown in the
following table).
Solve TP : Allocate Capacity of each Plant to various Warehouse demand such
that Minimize Total transportation cost
Prepare Data Entry format
Start entering data ….
Start entering data ….
Start entering data ….
Prepare work area format
Keep H3:J5 blank cells
( By changing cell for Solver)
Enter formula at H6
“=SUM(H3:H5) “
Copy H6 formula to I6:J6
Enter formula in K3
“=SUM(H3:J3) “
Copy K3 formula to K4:K5
Enter formula at B8
“=SUMPRODUCT(H3:J5,B3:D5) “
Select B8
Select Min
Select H3:J5
Add constraint : H6:J6=B6:D6
Add constraint : K3:K5 = E3:E5
Select ‘Simplex LP’
Click ‘Solve’
TP solution is ready…
You can write solution as above table
ce TP Case : US City
sider the following network representation of a transportation problem
The supplies,
demands and
transportation
costs per unit ( on
the arrow lines )
are shown on the
network.
Solve TP by using Excel Solver
Transshipment Problem
The transshipment problem is an
extension of the transportation problem in
which
intermediate nodes, referred to as
transshipment nodes, are added to
account for
locations such as warehouses
Transshipment Problem
Case : NHS
Ryan Pharmaceuticals manufactures specialist medical supplies
and has facilities just outside Geneva and also in Stockholm.
It is currently reviewing its supply activities to the UK’s National
Health Service (NHS).
Transshipment Problem
Data entry : Unit
transportation cost & Supply
capacity
Work-area :
H5:I6 Blank cells
H7 formula =SUM(H5:H6) & copy to I7
J5 formula =H5+I5 & copy to J6
B8 formula : =SUMPRODUCT(C5:D6,H5:I6)
Stage-2: Warehouse to Hospital data entry
Work area H12:I15 blank cells
Formula at H16 : =SUM(H12:H15) & copy to I16
Formula at J12 : =SUM(H12:I12) & copy to J13:J15
Formula at B17 :
=SUMPRODUCT(C12:D15,H12:I15)
Formula at 9: =B8+B17
Set objective : Select B19
To : select Min
By changing
variable cells:
select H5:I6
Select H12:I15
Subject to the
constraints:
Add following
constraint:
Received at Warehouse =
Dispatched to Hospitals
Subject to the constraints:
Add following constraint:
Requirements at Hospitals at least satisfied
Subject to the constraints:
Add following constraint:
Sent to warehouse <= Capacity
Select : Simplex LP
Click : Solve
Solution from Solver
Solution from Solver
Practice : Transshipment Problem
1500 1900
1200
900
2500