Introduction to
Management Science
with Spreadsheets
Stevenson and Ozgur
First Edition
Part 2 Deterministic Decision Models
Chapter 6
Transportation,
Transshipment, and
Assignment Problems
McGraw-Hill/Irwin
Copyright 2007 by The McGraw-Hill Companies, Inc. All rights
Learning Objectives
After completing this chapter, you should be able to:
1. Describe the nature of transportation, transshipment,
and assignment problems.
2. Formulate a transportation problem as a linear
programming model.
3. Use the transportation method to solve problems
with Excel.
4. Solve maximization transportation problems,
unbalanced problems, and problems with prohibited
routes.
5. Solve aggregate planning problems using the
transportation model.
Copyright 2007 The McGraw-Hill
McGraw-
Learning Objectives (contd)
After completing this chapter, you should be able to:
6. Formulate a transshipment problem as a linear
programming model.
7. Solve transshipment problems with Excel.
8. Formulate an assignment problem as a linear
programming model.
9. Use the assignment method to solve problems with
Excel.
Copyright 2007 The McGraw-Hill
McGraw-
Transportation
Transportation Problems
Problems
Transportation Problem
A distribution-type problem in which supplies of goods
that are held at various locations are to be distributed
to other receiving locations.
The solution of a transportation problem will indicate to
a manager the quantities and costs of various routes
and the resulting minimum cost.
Used to compare location alternatives in deciding
where to locate factories and warehouses to achieve
the minimum cost distribution configuration.
Copyright 2007 The McGraw-Hill
McGraw-
Formulating
Formulating the
the Model
Model
A transportation problem
Typically involves a set of sending locations, which
are referred to as origins, and a set of receiving
locations, which are referred to as destinations.
To develop a model of a transportation problem, it is
necessary to have the following information:
1. Supply quantity (capacity) of each origin.
2. Demand quantity of each destination.
3. Unit transportation cost for each origindestination route.
Copyright 2007 The McGraw-Hill
McGraw-
Transshipment
Transshipment Problems
Problems
Transshipment Problems
A transportation problem in which some locations are
used as intermediate shipping points, thereby serving
both as origins and as destinations.
Involve the distribution of goods from intermediate
nodes in addition to multiple sources and multiple
destinations.
Copyright 2007 The McGraw-Hill
McGraw-
Assignment
Assignment Problems
Problems
The Assignment-type Problems
Involve the matching or pairing of two sets of items
such as jobs and machines, secretaries and reports,
lawyers and cases, and so forth.
Have different cost or time requirements for different
pairings.
Copyright 2007 The McGraw-Hill
McGraw-
Figure
Figure61
61
Schematic
SchematicofofaaTransportation
TransportationProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Table
Table61
61
Transportation
TransportationTable
Tablefor
forHarleys
HarleysSand
Sandand
andGravel
Gravel
Copyright 2007 The McGraw-Hill
McGraw-
Special
Special Cases
Cases of
of Transportation
Transportation Problems
Problems
Maximization
Transportation-type problems that concern profits or
revenues rather than costs with the objective to
maximize profits rather than to minimize costs.
Unacceptable Routes
Certain origin-destination combinations may be
unacceptable due to weather factors, equipment
breakdowns, labor problems, or skill requirements that
either prohibit, or make undesirable, certain
combinations (routes).
Copyright 2007 The McGraw-Hill
McGraw-
Special
Special Cases
Cases of
of Transportation
Transportation Problems
Problems
(contd)
(contd)
Unequal Supply and Demand
Situations in which supply and demand are not equal
such that it is necessary to modify the original problem
so that supply and demand are equalized.
Quantities in dummy routes in the optimal solution are
not shipped and serve to indicate which supplier will
hold the excess supply, and how much, or which
destination will not receive its total demand, and how
much it will be short.
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit6-1
6-1
Input
Inputand
andOutput
OutputWorksheet
Worksheetfor
forthe
theTransportation
Transportation(topsoil)
(topsoil)Problem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit6-2
6-2
Parameter
ParameterSpecification
SpecificationScreen
Screenfor
forthe
theTopsoil
TopsoilTransportation
Transportation
Problem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit63
63
Solver
SolverOptions
OptionsScreen
Screen
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit64
64
Solver
SolverResults
Results
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit65
65
Answer
AnswerReport
Reportfor
forthe
theTopsoil
TopsoilTransportation
TransportationProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit66
66
Sensitivity
SensitivityReport
Reportfor
forthe
theTopsoil
TopsoilTransportation
TransportationProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit67
67
Input
Inputand
andOutput
OutputSheet
Sheetfor
forthe
theRevised
RevisedTransportation
Transportation(topsoil)
(topsoil)
Problem
ProblemWhen
Whenthe
theShipping
ShippingRoute
Routebetween
betweenFarm
FarmBBand
andProject
Project11IsIs
Prohibited
Prohibited
Copyright 2007 The McGraw-Hill
McGraw-
Figure
Figure62
62
AANetwork
NetworkDiagram
Diagramof
ofaaTransshipment
TransshipmentProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Example
Example6-2
6-2
Transshipment Problem
The manager of Harleys Sand and Gravel Pit has decided to utilize two
intermediate nodes as transshipment points for temporary storage of topsoil. The
revised diagram of the transshipment problem is given in Figure 6-3.
Table
Table62
62
Cost
CostofofShipping
ShippingOne
OneUnit
Unitfrom
fromthe
theFarms
Farmsto
toWarehouses
Warehouses
Table
Table62
62
Cost
CostofofShipping
ShippingOne
OneUnit
Unitfrom
fromthe
theWarehouses
Warehousesto
toProjects
Projects
Copyright 2007 The McGraw-Hill
McGraw-
Figure
Figure63
63
AANetwork
NetworkDiagram
Diagramof
ofHarleys
HarleysSand
Sandand
andGravel
GravelPit
Pit
Transshipment
TransshipmentExample
Example
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit68
68
Excel
ExcelInput
Inputand
andOutput
OutputScreen
Screenfor
forthe
theTransshipment
TransshipmentProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit69
69
Parameter
ParameterSpecifications
SpecificationsScreen
Screenfor
forthe
theTransshipment
Transshipment
Problem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Using
Using the
the Transportation
Transportation Problem
Problem to
to Solve
Solve
Aggregate
Aggregate Planning
Planning Problems
Problems
Aggregate Planning
Involves creating a long-term production plan for
achieving a demand-supply balance.
Aggregate planners usually avoid in terms of thinking
of individual products.
Planners are concerned about the quantity and timing
of production to meet the expected demand.
Aggregate planners attempt to minimize the
production cost over the planning horizon.
Copyright 2007 The McGraw-Hill
McGraw-
Table
Table64
64
Transportation
TransportationTable
Tablefor
forAggregate
AggregatePlanning
PlanningPurposes
Purposes
Copyright 2007 The McGraw-Hill
McGraw-
Example
Example6-3
6-3
Copyright 2007 The McGraw-Hill
McGraw-
Table
Table65
65
Transportation
TransportationTable
Tablefor
forthe
theAggregate
AggregatePlanning
PlanningProblem
Problemofof
Example
Example6-3
6-3
Copyright 2007 The McGraw-Hill
McGraw-
Using
Using the
the Transportation
Transportation Problem
Problem to
to Solve
Solve
Location
Location Planning
Planning Problems
Problems
Location Analysis
Comparing transportation costs for alternative
locations for new facilities to minimize total cost.
Provides planners an opportunity to assess the impact
of each warehouse location on the total distribution
costs for the system.
Copyright 2007 The McGraw-Hill
McGraw-
Table
Table66
66
System
Systemwith
withChicago
ChicagoWarehouse
Warehouse
Table
Table67
67
System
Systemwith
withDetroit
DetroitWarehouse
Warehouse
Copyright 2007 The McGraw-Hill
McGraw-
Example
Example6-4
6-4
A manager has prepared a table that shows the cost of performing
each of five jobs by each of five employees (see Table 6-8). According
to this table, job I will cost $15 if done by Al. $20 if it is done by Bill,
and so on. The manager has stated that his goal is to develop a set of
job assignments that will minimize the total cost of getting all four
jobs done. It is further required that the jobs be performed
simultaneously, thus requiring one job being assigned to each
employee.
In the past, to find the minimum-cost set of assignments, the
manager has resorted to listing all of the different possible
assignments (i.e., complete enumeration) for small problems such as
this one. But for larger problems, the manager simply guesses
because there are too many possibilities to try to list them. For
example, with a 5X5 table, there are 5! = 120 different possibilities; but
with, say, a 7X7 table, there are 7! = 5,040 possibilities.
Copyright 2007 The McGraw-Hill
McGraw-
Table
Table68
68
Numerical
NumericalExample
Examplefor
forthe
theAssignment
AssignmentProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit610
610
Excel
ExcelInput
Inputand
andOutput
OutputWorksheet
Worksheetfor
forthe
theAssignment
AssignmentProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit611
611
Parameter
ParameterSpecifications
SpecificationsScreen
Screenfor
forthe
theAssignment
AssignmentProblem
Problem
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit612
612
Excel
ExcelWorksheet
Worksheetfor
forthe
theTransportation
TransportationProblem
ProblemininSolved
SolvedProblem
Problem11
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit613
613
Parameter
ParameterSpecification
SpecificationScreen
Screenfor
forSolved
SolvedProblem
Problem11
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit614
614
Excel
ExcelWorksheet
Worksheetfor
forthe
theAssignment
AssignmentProblem
ProblemininSolved
SolvedProblem
Problem22
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit615
615
Parameter
ParameterSpecification
SpecificationScreen
Screenfor
forSolved
SolvedProblem
Problem22
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit616
616
Excel
ExcelWorksheet
Worksheetfor
forthe
theTransportation
TransportationProblem
ProblemininSolved
SolvedProblem
Problem33
Copyright 2007 The McGraw-Hill
McGraw-
Exhibit
Exhibit617
617
Parameter
ParameterSpecification
SpecificationScreen
Screenfor
forSolved
SolvedProblem
Problem33
Copyright 2007 The McGraw-Hill
McGraw-
