Transportation Models

1
Example 5.2

• Transportation Models

2
Background Information

• Sailco manufactures sailboats. During the next
  four months the company must meet (on time) the
  demand for sailboats listed in the table shown
  here.

3
Background Information -- continued

• At the beginning of month 1, Sailco has 10 boats
  in inventory.
• Each month it must determine how many boats to
  produce.
• During any month Sailco can produce up to 40
  boats with regular-time labor and an unlimited
  number of boats with overtime labor.
• Boats produced with regular-time labor cost 400
  to produce, and boats produced with overtime
  labor cost 450 to produce.

4
Background Information -- continued

• It costs 20 to hold a sailboat in inventory at
  the end of the month.
• Sailco wants to find a production and inventory
  schedule that minimizes the cost of meeting the
  next four months demands on time.

5
Solution

• Although this problem can be solved much like the
  Pigskin problem in Chapter 3, an alternative is
  to model it as a transportation problem.
• The key idea is to define the supply and demand
  points appropriately.
• The supply points are the initial inventory, each
  months regular time production, and each months
  overtime production.
• The demand points are the demands for each month.

6
Solution -- continued

• A shipment from a supply point to a demand
  point specifies how much of a given type of
  supply is used to meet a given months demand.
• For example, shipping 5 units from initial
  inventory to month 3 demand means that 5 units of
  the initial inventory are used to meet month 3
  demand.
• We describe the details of this procedure next.

7
SAILCO.XLS

• We setup Sailcos problem in this file as a
  transportation model as shown on the next slide.

8
(No Transcript)
9
Developing the Model

• The steps are
• Inputs. Enter the given inputs in the shaded
  cells.
• Cost matrix. It is useful to set up a matrix of
  unit costs first. To understand the logic,
  consider meeting a demand of one boat in month 4
  from regular-time production in month 2. The
  production cost is 400, and the boat is held in
  inventory at the ends of month 2 and 3, for a
  total holding cost of 2(20) 40. This explains
  the 440 unit cost in cell F16. To generate the
  costs in this matrix quickly, enter 0 in cell
  C13, enter the formula RTUnitCost in cells C14,
  D16, E18, and F20, and enter the formula
  OTUnitCost in cells C15, D17, E19, and F21.

10
Developing the Model -- continued

• Finally, to fill in all of the remaining unit
  costs, enter the formula C15UnitHoldCost in
  cell D15, and copy it to all of the other
  (nonblank) cells in the CostMatrix range. The
  reason is that each of these costs is the same as
  the cost to its left, except that an extra
  months holding cost is incurred.
• Origin and destination indexes. Starting in row
  25, enter indexes for the supply and demand
  points corresponding to each nonblank cell in the
  CostMatrix range. The supply points are indexed 1
  to 9, while the demand points are indexed 1 to 4.
  For example, the indexes 3 and 2 in row 34
  correspond to the arc in the network from the
  third supply point to the second demand point.

11
Developing the Model -- continued

• Costs on arcs. To obtain the corresponding costs
  for these arcs from the CostMatrix, enter the
  formula INDEX(CostMatrix,A25,B25) in cell C25,
  and copy it down.
• Flows on arcs. Enter any trial values in the
  Flows range.
• Node balance constraints. There are two types of
  node balance constraints, capacity and demand.
  For capacity, we cannot allocate more of the
  initial inventory than there is, and we cannot
  use more regular-time capacity than there is. The
  relevant supply points are 1, 2, 4, 6, and 8, so
  enter these in the range G26G30. To get the
  flows out of these points, enter the formula
  SUMIF(Origins,G26,Flows) in cell H26, and copy
  it down. Then enter links to initial inventory
  and regular-time production to fill in the
  Capacities range.

12
Developing the Model -- continued

• For the demands, we need the inflows to the
  demand points, so enter the formula
  SUMIF(Dests,G34,Flows) in cell H34, and copy it
  down.
• Total cost. Enter the formula SUMPRODUCT(Costs,Fl
  ows) to calculate the total of all production and
  holding costs in the TotCost cell.
• Using the Solver The Solver dialog box should
  be filled in as shown on the next slide.

13
Developing the Model -- continued

• We minimize the total cost, with the flows on the
  arcs as the changing cells.
• The constraints are that capacities cannot be
  exceeded, and demand must be met on time.

14
Solution -- continued

• The optimal solution shown shows that Sailco
  meets its demands at the minimum cost of 78,450.
• By examining the flows column, we see that month
  1 demand is met with 40 units of month 1
  regular-time production.
• Month 2 demand is met with 10 units of initial
  inventory, 40 units of month 2 regular-time
  production and 10 units of month 2 overtime
  production.
• Month 3 demand is met with 40 units of month 3
  regular-time production and 35 units of month 3
  overtime production.

15
Solution -- continued

• This solution is represented graphically here.

16
Solution -- continued

• This solution makes intuitive sense.
• Sailco wishes to avoid expensive holding costs
  and overtime costs and to take advantage of
  relatively cheap regular-time production costs
  whenever possible.
• This is exactly what this solution allows Sailco
  to do. Of course, some overtime is required to
  meet demand.