The Transportation Problem

1
The Transportation Problem
2

• In many distribution/logistics problems, we are
  concerned with finding the minimum cost way to
  get products from a variety of plants/suppliers
  to their final markets.
• Typically, different suppliers have different
  costs and capacities transportation costs are
  specific to a supplier / market pair and
  different markets have different requirements and
  possibly profitability.
• Realistic problems of this type can involve large
  numbers of suppliers, products, and markets and
  can be difficult to figure out by intuition or
  gut feel.

3
Basic Planning Question

• Warehouses
• How many warehouses should there be in the
  logistics network?
• How large should they be, and where should they
  be located?
• Customers
• Which customers should be assigned to which
  warehouses?
• Which warehouses should be assigned to which
  plants, vendors, and ports?
• Distribution
• Which products should be stocked in which
  warehouses?
• Which products should be shipped directly from
  plants/vendors/ports to customers?

4
The Transportation Problem

• How to satisfy demands at a given number of
  destinations with supplies from given set of
  origins.
• Structure of the system is known
• Location and characteristics of facilities
• Location and profile/demand of customers
• Transportation means and costs
• Distribution strategy to satisfy demand at least
  cost.

5
Illustration

• The Hottest Mexican Restaurant has restaurants in
  5 Midwestern cities. They order their tortillas
  from the Laredo Tortilla Factory, which has
  warehouses in 6 cities. The shipping costs (in
  dollars per dozen tortillas) are given below

6

• The demand for each restaurant and the tortillas
  available at each warehouse are

7
Excel Spreadsheet

• Step 1 Set up the EXCEL spreadsheet as shown
  below

8

• Notice that there are two sections. The first
  section shows the unit shipping costs. The cells
  have been formatted as currency with 2 decimal
  places (Select by highlighting the cells, then
  click on Format- Cell- Currency ).
• The second section shows the allocation and
  shipping costs. The optimal allocations have been
  assigned to cells B20F25. (at this time, these
  cells are all blanks). These are the decision
  variables.
• The demand and supply have been entered in cells
  B27F27 and cells H20H25, respectively. Also,
  row 28 has been formatted as currency with 2
  decimal places, and all other cells formatted as
  number with 2 decimal places.

9
Sums of Cells

• Step 2 Enter the formulae for the sum of demand
  (cells B26F26) and the sum of supply (cells
  G20G25), respectively.
• For example, B26SUM(B20B25) copy and paste the
  formula from C26F26 .
• G20SUM(B20F20) copy and paste the formula from
  G21G25 .
• To find out if supply is sufficient, enter the
  formulae of the total system demand and the total
  system supply.
• Total system supply H26SUM(H20H25)
• Total system demand G27SUM(B27F27)
• The sum of supply is H26423. Similarly, compute
  the sum of demand. The sum is G27370. In this
  case, there will be excess supply.

10
Shipments from ... Shipments to

• Step 3 Enter the formula for cell
  G20SUM(B20F20), the total shipment from Tulsa,
  as shown. Note that cells B20F20 the
  allocations from Tulsa to Minneapolis, Salina,
  Kansas, Lincoln, and Wichita, respectively. Copy
  this formula and paste it onto cells G21G25.
• Step 4 Likewise, enter the formula for cell
  B26SUM(B20B25), the shipments to Minneapolis
  copy and paste the formula onto cells C26F26.

11
Shipping Costs

• Step 5 Enter the formula for cell
  B28SUMPRODUCT(B3B8,B20B25), the total shipping
  cost to Minneapolis. Copy and paste the formula
  onto cells C28F28.
• Step 6 Enter the formula for cell
  G28SUM(B28F28), the total system cost.

12
(No Transcript)
13

• What we have just modeled is a linear programming
  problem.
• The objective function is the total
  transportation cost (to be minimized),
• subject to the demand-supply constraints.
• We are now ready to solve the problem using an
  Excel tool called Solver.

14
The Northwest Corner Solution

• Note This step is NOT necessary in solving the
  transportation problem. It is shown here to
  illustrate how a feasible solution can be derived
  manually.

15

• Starting from cell B20 (the northwest corner),
  let us find out how many units we can allocate
  from Tulsa to Minneapolis.
• Tulsa has 77 units available and Minneapolis
  needs 52 units. Suppose we allocated 52 from
  Tulsa, to satisfy the demand of Minneapolis.
• The leaves Tulsa with a remaining capacity of
  77-5225 units. Allocate the remaining 25 units
  from Tulsa to Salina (cell C20).
• Salina has a demand of 99 units. With 25 units
  from Tulsa, Salina still needs 74 units. Allocate
  45 units from the next origin Oklahoma. This will
  exhaust the supply of Oklahoma. The remaining 29
  units will come from Denver.
• Etc., etc.

16
Solver

• Step 8 In the Tool-Solver menu, enter the
  following (the Set Target Cell is G28, the
  grand total cost)
• By changing cells B20F25 (the cells highlighted
  in light green is our allocation table).
• Select the Min button to minimize the grand total
  cost.

• Step 7 Click on Tool, and choose Solver in
  the pull-down menu. You should see this

17
Adding Constraints

• Step 9 Add the following constraints (one at a
  time)
• Since total capacity exceeds demand, the shipment
  from each source should be less than or equal to
  its capacity G20G25 ? H20H25, i.e.
• Since total demand is less than total capacity,
  the total shipment to each destination should be
  equal to its demand, B26F26 B27F27

18
Options Linear, Non-negative, Auto-Scale

• Step 10After entering all constraints, set the
  option as shown

19

• Step 11 Click the Solve button!

20
The Transportation Problem with Lost Sales
21

• Suppose, the warehouse in Omaha becomes
  unavailable.
• Originally, the sum of supply was 423.
• With Omaha gone, the total supply is now 351
  units.
• Since total demand is 370 units, 19 (370-351)
  units of demand will not be satisfied.
• Replace Omaha by Lost Sales, with capacity
  equal to the demand not satisfied, i.e., 19
  units.
• Suppose the unit cost of unsatisfied demand is
  30 for the restaurants in Salina and Kansas, and
  20 for the other locations.

22
The Northwest Corner Solution

• Row 8 has been changed to Lost Sales.
• Cell H25 and cell B16 equals the demand not
  satisfied 19 units.

23

• Solver reduced total cost by 40 (from 2,277
  down to 1,369).
• Lincoln Wichita will have shortages (4 15
  units, respectively).