Example 5.2

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 month’s 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 month’s regular time production, and each month’s 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 month’s 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 Sailco’s problem in this file as a transportation model as shown on the next slide.

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 =C15+UnitHoldCost 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 month’s 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 G26:G30. 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,Flows) 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.