Chapter Fifteen Transportation and Assignment Problems by ezm24188

VIEWS: 894 PAGES: 70

									                                                                                          CD 15-1


Learning objectives
After completing this chapter, you should be able to
1. Describe the characteristics of transportation problems.
2. Formulate a spreadsheet model for a transportation problem from a description of
the problem.
3. Do the same for some variants of transportation problems.
4. Give the name of two algorithms that can solve huge transportation problems that
are well beyond the scope of the Excel Solver.
5. Identify several areas of application of transportation problems and their variants.
6. Describe the characteristics of assignment problems.
7. Identify the relationship between assignment problems and transportation
problems.
8. Formulate a spreadsheet model for an assignment problem from a description of
the problem.
9. Do the same for some variants of assignment problems.
10. Give the name of an algorithm that can solve huge assignment problems that are
well beyond the scope of the Excel Solver.




Chapter Fifteen
Transportation and
Assignment Problems
Transportation problems were introduced in Section 3.5 and Section 3.6 did the same for
assignment problems. Both of these similar types of problems arise quite frequently in a variety
of contexts. Because of their importance, we now will elaborate much further on these kinds of
problems and their applications in this self-contained chapter.
        Transportation problems received this name because many of their applications involve
determining how to transport goods optimally. However, you will see that some of their important
applications have nothing to do with transportation.
        Assignment problems are best known for applications involving assigning people to
tasks. However, they have a variety of other applications as well.
        Following a case study, the initial sections of this chapter describe the characteristics of
transportation problems and their variants, illustrate the formulation of spreadsheet models for
such problems, and survey a variety of applications. The subsequent sections then do the same for
assignment problems.

15.1 A CASE STUDY: THE P & T COMPANY DISTRIBUTION
PROBLEM
Douglas Whitson is concerned. Costs have been escalating and revenues have not been keeping
pace. If this trend continues, shareholders are going to be very unhappy with the next earnings
report. As CEO of the P & T Company, he knows that the buck stops with him. He’s got to find a
way to bring costs under control.
                                                                                         CD 15-2

        Douglas suddenly picks up the telephone and places a call to his distribution manager,
Richard Powers.
Douglas (CEO): Richard. Douglas Whitson here.
Richard (distribution manager): Hello, Douglas.
Douglas:    Say, Richard. I’ve just been looking over some cost data and one number jumped out
            at me.
Richard:    Oh? What’s that?
Douglas:    The shipping costs for our peas. $178,000 last season! I remember it running under
            $100,000 just a few years ago. What’s going on here?
Richard:    Yes, you’re right. Those costs have really been going up. One factor is that our
            shipping volume is up a little. However, the main thing is that the fees charged by the
            truckers we’ve been using have really shot up. We complained. They said something
            about their new contract with the union representing their drivers pushed their costs
            up substantially. And their insurance costs are up.
Douglas:    Have you looked into changing truckers?
Richard:    Yes. In fact, we’ve already selected new truckers for the upcoming growing season.
Douglas:    Good. So your shipping costs should come down quite a bit next season?
Richard:    Well, my projection is that they should run about $165,000.
Douglas:    Ouch. That’s still too high.
Richard:    That seems to be the best we can do.
Douglas:    Well, let’s approach this from another angle. You’re shipping the peas from our three
            canneries to all four of our warehouses?
Richard:    That’s right.
Douglas:    How do you decide how much each cannery will ship to each warehouse?
Richard:    We have a standard strategy that we’ve been using for many years.
Douglas:    Does this strategy minimize your total shipping cost?
Richard:    I think it does a pretty good job of that.
Douglas:    But does it use an algorithm to generate a shipping plan that is guaranteed to
            minimize the total shipping cost?
Richard:    No, I can’t say it does that. Is there a way of doing that?
Douglas:    Yes. I understand there is a management science technique for doing that. This is
            something I learned when I interviewed that new MBA graduate we hired last month,
            Kim Baker. Kim thought this technique could be directly applicable to our company.
            We hired Kim to help us incorporate some of the best techniquesbeing taught in
            business schools these days. I think we should have Kim look at your shipping plan
            and see if she can improve upon it.
Richard:    Sounds reasonable.
Douglas:    OK, good. I would like you to coordinate with Kim and report back to me soon.
                                                                                         CD 15-3

Richard:      Will do.
The conversation ends quickly.

Background
The P & T Company is a small family-owned business. It receives raw vegetables, processes and
cans them at its canneries, and then distributes the canned goods for eventual sale.
        One of the company’s main products is canned peas. The peas are prepared at three
canneries (near Bellingham, Washington; Eugene, Oregon; and Albert Lea, Minnesota) and then
shipped by truck to four distributing warehouses in the western United States (Sacramento,
California; Salt Lake City, Utah; Rapid City, South Dakota; and Albuquerque, New Mexico), as
shown in Figure 15.1.




Figure 15.1              Location of the canneries and warehouses for the P&T Co. problem.




The Company’s Current Approach
For many years, the company has used the following strategy for determining how much output
should be shipped from each of the canneries to meet the needs of each of the warehouses.
                                                                                         CD 15-4


Current Shipping Strategy
1.   Since the cannery in Bellingham is furthest from the warehouses, ship its output to its
     nearest warehouse, namely, the one in Sacramento, with any surplus going to the warehouse
     in Salt Lake City.
2.   Since the warehouse in Albuquerque is furthest from the canneries, have its nearest cannery
     (the one in Albert Lea) ship its output to Albuquerque, with any surplus going to the
     warehouse in Rapid City.
3.   Use the cannery in Eugene to supply the remaining needs of the warehouses.
        For the upcoming harvest season, an estimate has been made of the output from each
cannery, and each warehouse has been allocated a certain amount from the total supply of peas.
This information is given in Table 15.1.
        Applying the current shipping strategy to the data in Table 15.1 gives the shipping plan
shown in Table 15.2. The shipping costs per truckload for the upcoming season are shown in
Table 15.3.


Table 15.1 Shipping Data for the P & T Co.

 Cannery             Output                    Warehouse            Allocation
Bellingham        75 truckloads              Sacramento           80 truckloads
Eugene           125 truckloads              Salt Lake City       65 truckloads
Albert Lea       100 truckloads              Rapid City           70 truckloads
   Total         300 truckloads              Albuquerque          85 truckloads
                                                  Total           300 truckloads


Table 15.2 Current Shipping Plan for the P & T Co.

                                                         Warehouse
                          To
                       From
                             Sacramento Salt Lake City Rapid City Albuquerque
                  Bellingham      75           0            0          0
Cannery           Eugene          5            65           55         0
                  Albert Lea      0            0            15        85
                                                                                        CD 15-5


Table 15.3 Shipping Costs for the P & T Co.

                             Shipping Cost per Truckload

                                                           Warehouse
                          To
                       From
                             Sacramento Salt Lake City            Rapid City Albuquerque
                  Bellingham    $464        $513                    $654        $867
Cannery           Eugene        $352        $416                    $690        $791
                  Albert Lea    $995        $682                    $388        $685



        Combining the data in Tables 15.2 and 15.3 yields the total shipping cost under the
current plan for the upcoming season:
   Total shipping cost = 75($464) + 5($352) + 65($416) + 55($690) + 15($388) + 85($685)
                        = $165,595
Kim Baker now is reexamining the current shipping strategy to see if she can develop a new
shipping plan that would reduce the total shipping cost to an absolute minimum.


The Management Science Approach
Kim immediately recognizes that this problem is just a classic example of a transportation
problem. Formulating the problem in this way is straightforward. Furthermore, software is readily
available for quickly finding an optimal solution on a desktop computer. This enables Kim to
return to management the next day with a new shipping plan that would reduce the total shipping
cost by over $13,000.
        This story will unfold in the next section after we provide more background about
transportation problems.


Review Questions
1. What is the specific concern being raised by the CEO of the P & T Co. in this case study?
2. What is Kim Baker being asked to do?


15.2 CHARACTERISTICS OF TRANSPORTATION PROBLEMS
The Model for Transportation Problems
To describe the model for transportation problems, we need to use terms that are considerably
less specific than for the P & T Co. problem. Transportation problems in general are concerned
(literally or figuratively) with distributing any commodity from any group of supply centers,
called sources, to any group of receiving centers, called destinations, in such a way as to
minimize the total distribution cost. The correspondence in terminology between the specific
                                                                                                     CD 15-6

application to the P & T Co. problem and the general model for any transportation problem is
summarized in Table 15.4.
         As indicated by the fourth and fifth rows of the table, each source has a certain supply of
units to distribute to the destinations, and each destination has a certain demand for units to be
received from the sources. The model for a transportation problem makes the following
assumption about these supplies and demands.


Table 15.4 Terminology for a Transportation Problem

            P & T Co. Problem                                        General Model
Truckloads of canned peas                              Units of a commodity
Canneries                                              Sources
Warehouses                                             Destinations
Output from a cannery                                  Supply from a source
Allocation to a warehouse                              Demand at a destination
Shipping cost per truckload from a                     Cost per unit distributed from a source to a
       cannery to a warehouse                                 destination
.


       The Requirements Assumption: Each source has a fixed supply of units, where this entire supply
       must be distributed to the destinations. Similarly, each destination has a fixed demand for units,
       where this entire demand must be received from the sources.
This assumption that there is no leeway in the amounts to be sent or received means that there
needs to be a balance between the total supply from all sources and the total demand at all
destinations.
       The Feasible Solutions Property: A transportation problem will have feasible solutions if and only
       if the sum of its supplies equals the sum of its demands.
Fortunately, these sums are equal for the P & T Co. since Table 15.1 indicates that the supplies
(outputs) sum to 300 truckloads and so do the demands (allocations).
        In some real problems, the supplies actually represent maximum amounts (rather than
fixed amounts) to be distributed. Similarly, in other cases, the demands represent maximum
amounts (rather than fixed amounts) to be received. Such problems do not fit the model for a
transportation problem because they violate the requirements assumption, so they are variants of
a transportation problem. Fortunately, it is relatively straightforward to formulate a spreadsheet
model for such variants that the Excel Solver can still solve, as will be illustrated in Section 15.3.
        The last row of Table 15.4 refers to a cost per unit distributed. This reference to a unit
cost implies the following basic assumption for any transportation problem.
       The Cost Assumption: The cost of distributing units from any particular source to any particular
       destination is directly proportional to the number of units distributed. Therefore, this cost is just the
       unit cost of distribution times the number of units distributed.
The only data needed for a transportation problem model are the supplies, demands, and unit
costs. These are the parameters of the model. All these parameters for the P & T Co. problem are
shown in Table 15.5. This table (including the description implied by its column and row
headings) summarizes the model for the problem.
                                                                                                     CD 15-7

       The Model: Any problem (whether involving transportation or not) fits the model for a
       transportation problem if it (1) can be described completely in terms of a table like Table 15.5 that
       identifies all the sources, destinations, supplies, demands, and unit costs, and (2) satisfies both the
       requirements assumption and the cost assumption. The objective is to minimize the total cost of
       distributing the units.


Table 15.5 The Data for the P & T Co. Problem Formulated as a Transportation Problem
                                                         Unit Cost

Destination
(Warehouse)                  Sacramento Salt Lake City Rapid City Albuquerque                         Supply
Source (Cannery)
Bellingham                        $464               $513            $654             $867               75
Eugene                            $352               $416            $690             $791              125
Albert Lea                        $995               $682            $388             $685              100

Demand                               80                 65              70               85


         Therefore, formulating a problem as a transportation problem only requires filling out a
table in the format of Table 15.5. It is not necessary to write out a formal mathematical model
(even though we will do this for demonstration purposes later).
         The Big M Company problem presented in Section 3.5 is another example of a
transportation problem. In this example, the company’s two factories need to ship turret lathes to
three customers and the objective is to determine how to do this so as to minimize the total
shipping cost. Table 3.9 presents the data for this problem in the same format as Table 15.5,
where the factories are the sources, their outputs are the supplies, the customers are the
destinations, and their order sizes are the demands.

Using Excel to Formulate and Solve Transportation Problems
Section 3.5 describes the formulation of the spreadsheet model for the Big M Company problem.
We now will do the same for the P & T Co. problem.
         The decisions to be made are the number of truckloads of peas to ship from each cannery
to each warehouse. The constraints on these decisions are that the total amount shipped from each
cannery must equal its output (the supply) and the total amount received at each warehouse must
equal its allocation (the demand). The overall measure of performance is the total shipping cost,
so the objective is to minimize this quantity.
        This information leads to the spreadsheet model shown in Figure 15.2. All the data
provided in Table 15.5 are displayed in the following data cells: UnitCost (D5:G7), Supply
(J12:J14), and Demand (D17:G17). The decisions on shipping quantities are given by the
changing cells, ShippingQuantity (D12:G14). The output cells are TotalShipped (H12:H14) and
Total Received (D15:G15), where the SUM functions entered into these cells are shown near the
bottom of Figure 15.2. The constraints, TotalShipped (H12:H14) = Supply (J12:J14) and
TotalReceived (D15:G15) = Demand (D17:G17), have been specified on the spreadsheet and
entered into the Solver dialogue box. The target cell is TotalCost (J17), where its
SUMPRODUCT function is shown in the lower right-hand corner of Figure 15.2. The Solver
dialogue box specifies that the objective is to minimize this target cell. One of the selected Solver
                                                                                                                              CD 15-8

options (Assume Non-Negative) specifies that all shipment quantities must be nonnegative. The
other one (Assume Linear Model) indicates that this transportation problem is also a linear
programming problem (as described later in this section).


       A       B              C                D              E                 F              G               H          I        J
  1   P&T Co. Distribution Problem
  2
  3        Unit Cost                                        Destination (Warehouse)
  4                                        Sacramento   Salt Lake City      Rapid City     Albuquerque
  5         Source           Bellingham       $464           $513             $654            $867
  6        (Cannery)            Eugene        $352           $416             $690            $791
  7                           Albert Lea      $995           $682             $388            $685
  8
  9
 10        Shipment Quantity                                Destination (Warehouse)
 11        (Truckloads)                    Sacramento   Salt Lake City      Rapid City     Albuquerque    Total Shipped         Supply
 12          Source          Bellingham         0             20                0               55              75        =       75
 13         (Cannery)           Eugene         80             45                0               0              125        =      125
 14                           Albert Lea        0              0               70               30             100        =      100
 15                      Total Received        80             65               70               85
 16                                             =             =                 =               =                              Total Cost
 17                               Demand       80             65               70               85                             $152,535


                                                                                         Range Name                       Cells
                                                                                         Demand                           D17:G17
                                                                                         ShipmentQuantity                 D12:G14
                                                                                         Supply                           J12:J14
                                                                                         TotalCost                        J17
                                                                                         TotalReceived                    D15:G15
                                                                                         TotalShipped                     H12:H14
                                                                                         UnitCost                         D5:G7

                                                                                                                    H
                                                                                                         11   Total Shipped
                                                                                                         12 =SUM(D12:G12)
                                                                                                         13 =SUM(D13:G13)
                                                                                                         14 =SUM(D14:G14)

                   C                      D                      E                             F                      G
  15        Total Received          =SUM(D12:D14)          =SUM(E12:E14)                 =SUM(F12:F14)          =SUM(G12:G14)

                                                                                J
                                                            16              Total Cost
                                                            17 =SUMPRODUCT(UnitCost,ShipmentQuantity)

Figure 15.2                  A spreadsheet formulation of the P & T Co. problem as a transportation
                             problem, including the target cell TotalCost (J17) and the other output cells
                             TotalShipped (H12:H14) and TotalReceived (D15:G15), as well as the
                             specifications needed to set up the model. The changing cells
                             ShipmentQuantity (D12:G14) show the optimal shipping plan obtained by
                             the Solver.


         To begin the process of solving the problem, any value (such as 0) can be entered in each
of the changing cells. After clicking on the Solve button, the Solver will use the simplex method
to solve the transportation problem and determine the best value for each of the decision
                                                                                          CD 15-9

variables. This optimal solution is shown in ShippingQuantity (D12:G14) in Figure 15.2, along
with the resulting value $152,535 in the target cell TotalCost (J17).

The Network Representation of a Transportation Problem
A nice way to visualize a transportation problem graphically is to use its network representation.
This representation ignores the geographical layout of the sources and destinations. Instead, it
simply lines up all the sources in one column on the left (where S1 is the symbol for Source 1,
etc.) and all the destinations in one column on the right (where D1 is the symbol for Destination 1,
etc.). Figure 15.3 shows the network representation of the P & T Co. problem, where the
numbering of the sources (canneries) and destinations (warehouses) is that given in Figure 15.1.
The arrows show the possible routes for the truckloads of canned peas, where the number next to
each arrow is the shipping cost (in dollars) per truckload for that route. Since the figure also
includes the supplies and demands, it includes all the data provided by Table 15.5. Therefore, this
network representation provides an alternative way of summarizing the model for a transportation
problem model.
       Since the Big M Company problem presented in Section 3.5 also is a transportation
problem, it too has a network representation like the one in Figure 15.3, as shown in Figure 3.9.




Figure 15.3         The network representation of the P & T Co. transportation problem shows
                    all the data in Table 15.5 graphically.




        For transportation problems larger than the P & T Co. problem, it is not very convenient
to draw the entire network and display all the data. Consequently, the network representation is
mainly a visualization device.
       Recall that Section 3.5 described transportation problems as a major category of linear
programming problems that often involve the distribution of goods through a distribution
                                                                                             CD 15-10

network. The networks in both Figure 3.5 and Figure15.3 are a simple type of distribution
network where every shipping lane goes directly from a source to a destination.
        Recall that Chapter 6 presents some related kinds of network optimization problems that
sometimes also involve the distribution of goods through a distribution network. In fact, Section
6.1 points out that transportation problems are a special type of minimum-cost flow problem,
which commonly involves the flow of goods through a distribution network.


The Transportation Problem Is a Linear Programming Problem
To demonstrate that the P & T Co. problem (or any other transportation problem) is, in fact, a
linear programming problem, let us formulate its mathematical model in algebraic form.
         Using the numbering of canneries and warehouses given in Figure 15.1, let xij be the
number of truckloads to be shipped from Cannery i to Warehouse j for each i = 1, 2, 3 and j = 1,
2, 3, 4. The objective is to choose the values of these 12 decision variables (the xij) so as to




Minimize Cost = 464x11 + 513 x12 + 654 x13 + 867x14 + 352 x21 + 416 x22
                + 690 x23 + 791 x24 + 995 x31 + 682 x32 + 388 x33 + 685 x34,
subject to the constraints
x11 +x12    +x13   +x14                                                           =     75

                           x21      +x22   +x23 +x24                              =    125

                                                          x31 +x32 +x33 +x34 =         100

x11                       +x21                           +x31                     =     80

      x12                           +x22                        +x32              =     65

             x13                           +x23                        +x33       =     70

                    x14                           +x24                        +x34 =    85

and
                          xij ≥ 0   (i = 1, 2, 3; j = 1, 2, 3, 4).



This is indeed a linear programming problem.
        The P & T Co. always ships full truckloads of canned peas since anything less would be
uneconomical. This implies that each xij should have an integer value (0, 1, 2, . . .). To avoid
obtaining an optimal solution for our model that has fractional values for any of the decision
variables, we could add another set of constraints specifying that each xij must have an integer
value. This would convert our linear programming problem into an integer programming
                                                                                            CD 15-11

problem, which is more difficult to solve. (Recall that we discuss integer programming problems
in Chapters 3 and 7.) Fortunately, this conversion is not necessary because of the following
property of transportation problems.
       Integer Solutions Property: As long as all its supplies and demands have integer values,
       any transportation problem with feasible solutions is guaranteed to have an optimal
       solution with integer values for all its decision variables. Therefore, it is not necessary to
       add constraints to the model that restrict these variables to only have integer values.
         When dealing with transportation problems, practitioners typically do not bother to write
out the complete linear programming model in algebraic form since all the essential information
can be presented much more compactly in a table like Table 15.5 or in the corresponding
spreadsheet model.
         Before leaving this linear programming model though, take a good look at the left-hand
side of the functional constraints. Note that every coefficient is either 0 (so the variable is deleted)
or 1. Also note the distinctive pattern for the locations of the coefficients of 1, including the fact
that each variable has a coefficient of 1 in exactly two constraints. These distinctive features of
the coefficients play a key role in being able to solve transportation problems extremely
efficiently.


Solving Transportation Problems
Because transportation problems are a special type of linear programming problem, they can be
solved by the simplex method (the procedure used by the Excel Solver to solve linear pro-
gramming problems). However, because of the very distinctive pattern of coefficients in its
functional constraints noted above, it is possible to greatly streamline the simplex method to
solve transportation problems far more quickly. This streamlined version of the simplex method
is called the transportation simplex method. It sometimes can solve large transportation
problems more than 100 times faster than the regular simplex method. However, it is only
applicable to transportation problems.
         Just like a transportation problem, other minimum-cost flow problems also have a similar
distinctive pattern of coefficients in their functional constraints. Therefore, the simplex method
can be greatly streamlined in much the same way as for the transportation simplex method to
solve any minimum-cost flow problem (including any transportation problem) very quickly. This
streamlined method is called the network simplex method.
        Linear programming software often includes the network simplex method, and may
include the transportation simplex method as well. When only the network simplex method is
available, it provides an excellent alternative way of solving transportation problems. In fact, the
network simplex method has become quite competitive with the transportation simplex method in
recent years.
        After obtaining an optimal solution, what-if analysis generally is done for transportation
problems in much the same way as described in Chapter 5 for other linear programming
problems. Either the transportation or network simplex method can readily obtain the allowable
range for each coefficient in the objective function. Dealing with changes in right-hand sides
(supplies and demands) is more complicated now because of the requirement that the sum of the
supplies must equal the sum of the demands. Thus, each change in a supply must be accompanied
by a corresponding change in a demand (or demands), and vice versa.
                                                                                          CD 15-12

        Because the Excel Solver is not intended to solve the really large linear programming
problems that often arise in practice, it simply uses the simplex method to solve transportation
problems as well as other minimum-cost flow problems encountered in this book (and
considerably larger ones as well), so we will continue to use the Solver (or Premium Solver) and
thereby forgo any use of the transportation simplex method or network simplex method.


Completing the P & T Co. Case Study
We now can summarize the end of the story of how the P & T Co. was able to substantially
improve on the current shipping plan shown in Table 15.2, which has a total shipping cost of
$165,595.
        You already have seen how Kim Baker was able to formulate this problem as a
transportation problem simply by filling out the table shown in Table 15.5. The corresponding
formulation on a spreadsheet was shown in Figure 15.2. Applying the Solver then gave the
optimal solution shown in ShipmentQuantity (D12:G14).
        Note that this optimal solution is not an intuitive one. Of the 75 truckloads being supplied
by Bellingham, 55 of them are being sent to Albuquerque, even though this is far more expensive
($867 per truckload) than to any other warehouse. However, this sacrifice for Cannery 1 enables
low-cost shipments for both Canneries 2 and 3. Although it would be difficult to find this optimal
solution manually, the simplex method in the Excel Solver finds it readily.
        As given in the target cell TotalCost (J17), the total shipping cost for this optimal
shipping plan is


   Total shipping cost = 20($513) + 55($867) + 80($352) + 45($416) + 70($388) + 30($685)
                         = $152,535


a reduction of $13,060 from the current shipping plan. Richard Powers is pleased to report this
reduction to his CEO, Douglas Whitson, who congratulates him and Kim Baker for achieving this
significant savings.


An Award-Winning Application of a Transportation Problem
Except for its small size, the P & T Co. problem is typical of the problems faced by many
corporations that must ship goods from their manufacturing plants to their customers.
         For example, consider an award-winning management science study conducted at
Procter & Gamble (as described in the January–February 1997 issue of Interfaces). Prior to the
study, the company’s supply chain consisted of hundreds of suppliers, over 50 product categories,
over 60 plants, 15 distribution centers, and over 1,000 customer zones. However, as the company
moved toward global brands, management realized that it needed to consolidate plants to reduce
manufacturing expenses, improve speed to market, and reduce capital investment. Therefore, the
study focused on redesigning the company’s production and distribution system for its North
American operations. The result was a reduction in the number of North American plants by
almost 20 percent, saving over $200 million in pretax costs per year.
                                                                                          CD 15-13

         A major part of the study revolved around formulating and solving transportation
problems for individual product categories. For each option regarding the plants to keep open,
and so forth, solving the corresponding transportation problem for a product category shows what
the distribution cost would be for shipping the product category from those plants to the
distribution centers and customer zones. Numerous such transportation problems were solved in
the process of identifying the best new production and distribution system.

Review Questions
1.   Give a one-sentence description of transportation problems.
2.   What data are needed for the model of a transportation problem?
3.   What needs to be done to formulate a problem as a transportation problem?
4.   What is required for a transportation problem to have feasible solutions?
5.   Under what circumstances will a transportation problem automatically have an optimal
     solution with integer values for all its decision variables?
6.   Name two algorithms that can solve transportation problems much faster than the general
     simplex method.


15.3 MODELING VARIANTS OF TRANSPORTATION PROBLEMS
The P & T Co. problem is an example of a transportation problem where everything fits
immediately. Real life is seldom this easy. Linear programming problems frequently arise that are
almost transportation problems, but one or more features do not quite fit. Here are the features
that we will consider in this section.
1.   The sum of the supplies exceeds the sum of the demands, so each supply represents a
     maximum amount (not a fixed amount) to be distributed from that source.
2.   The sum of the supplies is less than the sum of the demands, so each demand represents a
     maximum amount (not a fixed amount) to be received at that destination.
3.   A destination has both a minimum demand and a maximum demand, so any amount between
     these two values can be received.
4.   Certain source–destination combinations cannot be used for distributing units.
5.   The objective is to maximize the total profit associated with distributing units rather than to
     minimize the total cost.
        For each of these features, it is possible to reformulate the problem in a clever way to
make it fit the format for transportation problems. When this is done with a really big problem
(say, one with many hundreds or thousands of sources and destinations), it is extremely helpful
because either the transportation simplex method or network simplex method can solve the
problem in this format much faster (perhaps more than 100 times faster) than the simplex method
can solve the general linear programming formulation.
        However, when the problem is not really big, the simplex method still is capable of
solving the general linear programming formulation in a reasonable period of time. Therefore, a
basic software package (such as the Excel Solver) that includes the simplex method but not the
transportation simplex method or network simplex method can be applied to such problems
without trying to force them into the format for a transportation problem. This is the approach we
                                                                                           CD 15-14

will use. In particular, this section illustrates the formulation of spreadsheet models for variants
of transportation problems that have some of the features listed above.
         Our first example focuses on features 1 and 4. A second example will illustrate the other
features.


Example 1: Assigning Plants to Products
The Better Products Company has decided to initiate the production of four new products, using
three plants that currently have excess production capacity. The products require a comparable
production effort per unit, so the available production capacity of the plants is measured by the
number of units of any product that can be produced per day, as given in the rightmost column of
Table 15.6. The bottom row gives the required production rate (number of units produced per
day) to meet projected sales. Each plant can produce any of these products, except that Plant 2
cannot produce Product 3. However, the variable costs per unit of each product differ from plant
to plant, as shown in the main body of the table.
        Management now needs to make a decision about which plants should produce which
products. Product splitting, where the same product is produced in more than one plant, is
permitted. (We shall return to this same example in Section 15.7 to consider the option where
product splitting is prohibited, which requires a different kind of formulation.)
Formulation of a Spreadsheet Model
This problem is almost a transportation problem. In fact, after substituting conventional
terminology (supply, demand, etc.) for the column and row headings in Table 15.6, this table
basically fits the formulation for a transportation problem, as shown in Table 15.7. But there are
two ways in which this problem deviates from a transportation problem.


Table 15.6 Data for the Better Products Co. Problem


                                           Unit Cost
                                                                           Capacity
Product:                         1         2         3         4           Available
Plant
1                              $41       $27       $28       $24               75
2                              $40       $29         –       $23               75
3                              $37       $30       $27       $21               45
Required production             20        30        30        40
                                                                                         CD 15-15


Table 15.7 The Data for the Better Products Co. Problem Formulated as a Variant
of a Transportation Problem

                                          Unit Cost
Destination (Product)             1       2      3          4             Supply
Source (Plant)
          1                      $41     $27      $28     $24                75
          2                      $40     $29       —      $23                75
          3                      $37     $30      $27     $21                45
Demand                            20      30       30      40

         One (minor) deviation is that a transportation problem requires a unit cost for every
source–destination combination, but Plant 2 cannot produce Product 3, so no unit cost is available
for this particular combination. The other deviation is that the sum of the supplies (75 + 75 + 45+
195) exceeds the sum of the demands (20 + 30 + 30 + 40 + 120) in Table 15.7. Thus, as the
feasible solutions property (Section 15.2) indicates, the transportation problem represented by
Table 15.7 would have no feasible solutions. The requirements assumption (Section 15.2)
specifies that the entire supply from each source must be used.
         In reality, these supplies in Table 15.7 represent production capacities that will not need
to be fully used to meet the sales demand for the products. Thus, these supplies are upper bounds
on the amounts to be used.
        The spreadsheet model for this problem, shown in Figure 15.4, has the same format as the
one in Figure 15.2 for the P & T Co. transportation problem with two key differences. First,
because Plant 2 cannot produce Product 3, a dash is inserted into cell E5 and the constraint that
E12 = 0 is included in the Solver dialogue box. Second, because the supplies are upper bounds,
cells H11:H13 have ≤ signs instead of = signs and the corresponding constraints in the Solver
dialogue box are ProducedAtPlant (G11:G13) ≤ Capacity (I11:I13).
                                                                                                        CD 15-16



      A               B                   C           D           E           F            G        H        I
 1    Better Products Co. Production Planning Problem
 2
 3        Unit Cost                    Product 1   Product 2   Product 3   Product 4
 4                           Plant 1      $41         $27         $28         $24
 5                           Plant 2      $40         $29          -          $23
 6                           Plant 3      $37         $30         $27         $21
 7
 8
 9                                                                                      Produced
 10       Daily Production             Product 1   Product 2   Product 3   Product 4     At Plant        Capacity
 11                       Plant 1          0          30          30           0            60      <=     75
 12                       Plant 2          0           0           0          15            15      <=     75
 13                       Plant 3         20           0           0          25            45      <=     45
 14            Products Produced          20          30          30          40
 15                                        =          =           =            =                         Total Cost
 16           Required Production         20          30          30          40                          $3,260

                                                                   Range Name                       Cells
                                                                   Capacity                         I11:I13
                                                                   DailyProduction                  C11:F13
                                                                   ProducedAtPlant                  G11:G13
                                                                   ProductsProduced                 C14:F14
                                                                   RequiredProduction               C16:F16
                                                                   TotalCost                        I16
                                                                   UnitCost                         C4:F6

                                                                                                 G
                                                                                       9     Produced
                                                                                       10     At Plant
                                                                                       11 =SUM(C11:F11)
                                                                                       12 =SUM(C12:F12)
                                                                                       13 =SUM(C13:F13)

                 B                 C             D             E             F
 14        Products Produced =SUM(C11:C13) =SUM(D11:D13) =SUM(E11:E13) =SUM(F11:F13)

                                                                           I
                                                       15             Total Cost
                                                       16 =SUMPRODUCT(UnitCost,DailyProduction)

Figure 15.4 A spreadsheet formulation of the Better Products Co. problem as a variant of a
            transportation problem, including the target cell TotalCost (I16) and the other
            output cells ProducedAtPlant (G11:G13) and ProductsProduced (C14:F14), as well
            as the specifications needed to set up the model. The changing cells
            DailyProduction (C11:F13) show the optimal production plan obtained by the
            Solver.




       Using the Excel Solver then gives the optimal solution shown in the changing cells
DailyProduction (C11:F13) for the production rate of each product at each plant. This solution
minimizes the cost of distributing 120 units of production from the total supply of 195 to meet the
                                                                                          CD 15-17

total demand of 120 at the four destinations (products). The total cost given in the target cell
TotalCost (I16) is $3,260 per day.


Example 2: Choosing Customers
The Nifty Company specializes in the production of a single product, which it produces in three
plants. The product is doing very well, so the company currently is receiving more purchase
requests than it can fill. Plans have been made to open an additional plant, but it will not be ready
until next year.
         For the coming month, four potential customers (wholesalers) in different parts of the
country would like to make major purchases. Customer 1 is the company’s best customer, so his
full order will be met. Customers 2 and 3 also are valued customers, so the marketing manager
has decided that, at a minimum, at least a third of their order quantities should be met. However,
she does not feel that Customer 4 warrants special consideration, and so is unwilling to guarantee
any minimum amount for this customer. There will be enough units produced to go somewhat
above these minimum amounts.
        Due largely to substantial variations in shipping costs, the net profit that would be earned
on each unit sold varies greatly, depending on which plant is supplying which customer.
Therefore, the final decision on how much to send to each customer (above the minimum
amounts established by the marketing manager) will be based on maximizing profit.
        The unit profit for each combination of a plant supplying a customer is shown in Table
15.8. The rightmost column gives the number of units that each plant will produce for the coming
month (a total of 20,000). The bottom row shows the order quantities that have been requested by
the customers (a total of 30,000). The next-to-last row gives the minimum amounts that will be
provided (a total of 12,000), based on the marketing manager’s decisions described above.
        The marketing manager needs to determine how many units to sell to each customer
(observing these minimum amounts) and how many units to ship from each plant to each
customer to maximize profit.


Formulation of a Spreadsheet Model
This problem is almost a transportation problem, since the plants can be viewed as sources and
the customers as destinations, where the production quantities are the supplies from the sources.
         If this were fully a transportation problem, the purchase quantities would be the demands
for the destinations. However, this does not work here because the requirements assumption
(Section 15.2) says that the demand must be a fixed quantity to be received from the sources.
Except for Customer 1, all we have here are ranges for the purchase quantities between the
minimum and the maximum given in the last two rows of Table 15.8. In fact, one objective is to
solve for the most desirable values of these purchase quantities.
                                                                                          CD 15-18


Table 15.8 Data for the Nifty Co. Problem

                                         Unit Profit                    Production
        Customer                  1        2         3            4      Quantity
       Plant
         1                     $55        $42        $46       $53         8,000
         2                     $37        $18        $32       $48         5,000
         3                     $29        $59        $51       $35         7,000
Minimum purchase             7,000      3,000      2,000         0
Requested purchase           7,000      9,000      6,000     8,000


         Figure 15.5 shows the spreadsheet model for this variant of a transportation problem.
Instead of a demand row below the changing cells, we instead have both a minimum row and a
maximum row. The corresponding constraints in the Solver dialogue box are TotalShipped
(C17:F17) ≤ MaxPurchase (C19:F19) and TotalShipped (C17:F17) ≥ MinPurchase (C15:F15),
along with the usual supply constraints. Since the objective is to maximize the total profit rather
than minimize the total cost, the Solver dialogue box specifies that the target cell TotalProfit (I17)
is to be maximized.
                                                                                                         CD 15-19



       A           B                 C            D            E              F            G        H         I
  1    Nifty Co. Product-Distribution Problem
  2
  3        Unit Profit           Customer 1   Customer 2   Customer 3     Customer 4
  4                   Plant 1       $55          $42          $46            $53
  5                   Plant 2       $37          $18          $32            $48
  6                   Plant 3       $29          $59          $51            $35
  7
  8
  9                                                                                       Total          Production
 10        Shipment              Customer 1   Customer 2   Customer 3     Customer 4   Production         Quantity
 11                    Plant 1     7,000          0          1,000            0          8,000      =      8,000
 12                    Plant 2       0            0            0            5,000        5,000      =      5,000
 13                    Plant 3       0          6,000        1,000            0          7,000      =      7,000
 14
 15            Min Purchase        7,000        3,000        2,000            0
 16                                 <=           <=           <=             <=                          Total Profit
 17            Total Shipped       7,000        6,000        2,000          5,000                        $1,076,000
 18                                 <=           <=           <=             <=
 19           Max Purchase         7,000        9,000        6,000          8,000

                                                                        Range Name                      Cells
                                                                        MaxPurchase                     C19:F19
                                                                        MinPurchase                     C15:F15
                                                                        ProductionQuantity              I11:I13
                                                                        Shipment                        C11:F13
                                                                        TotalProduction                 G11:G13
                                                                        TotalProfit                     I17
                                                                        TotalShipped                    C17:F17
                                                                        UnitProfit                      C4:F6

                                                                                                  G
                                                                                        9        Total
                                                                                        10    Production
                                                                                        11 =SUM(C11:F11)
                                                                                        12 =SUM(C12:F12)
                                                                                        13 =SUM(C13:F13)
                  B               C             D             E             F
  17          Total Shipped =SUM(C11:C13) =SUM(D11:D13) =SUM(E11:E13) =SUM(F11:F13)

                                                                               I
                                                              16          Total Profit
                                                              17 =SUMPRODUCT(UnitProfit,Shipment)

Figure 15.5                A spreadsheet formulation of the Nifty Co. problem as a variant of a
                           transportation problem, including the target cell TotalProfit (I17) and the
                           other output cells TotalProduction (G11:G13) and TotalShipped (C17:F17),
                           as well as the specifications needed to set up the model. The changing cells
                           Shipment (C11:F13) show the optimal shipping plan obtained by the Solver.




        After clicking on the Solve button, the optimal solution shown in Figure 15.5 is obtained.
Cells TotalShipped (C17:F17) indicate how many units to sell to the respective customers. The
                                                                                           CD 15-20

changing cells Shipment (C11:F13) show how many units to ship from each plant to each
customer. The resulting total profit of $1.076 million is given in the target cell TotalProfit (I17).


Review Questions
1.   What needs to be done to formulate the spreadsheet model for a variant of a
     transportationproblem where each supply from a source represents a maximum amount
     rather than a fixed amount to be distributed from that source?
2.    What needs to be done to formulate the spreadsheet model for a variant of a transportation
     problem where the demand for a destination can be anything between a specified minimum
     amount and a specified maximum amount?


15.4 SOME OTHER APPLICATIONS OF VARIANTS OF
TRANSPORTATION PROBLEMS
You now have seen examples illustrating three areas of application of transportation problems
and their variants:
1. Shipping goods (the P & T Co. problem).
2. Assigning plants to products (the Better Products Co. problem).
3. Choosing customers (the Nifty Co. problem).
You will further broaden your horizons in this section by seeing examples illustrating some (but
far from all) other areas of application.
Distributing Natural Resources
Metro Water District is an agency that administers water distribution in a large geographic region.
The region is fairly arid, so the district must purchase and bring in water from outside the region.
The sources of this imported water are the Colombo, Sacron, and Calorie rivers. The district then
resells the water to users in its region. Its main customers are the water departments of the cities
of Berdoo, Los Devils, San Go, and Hollyglass.
         It is possible to supply any of these cities with water brought in from any of the three
rivers, with the exception that no provision has been made to supply Hollyglass with Calorie
River water. However, because of the geographic layouts of the aqueducts and the cities in the
region, the cost to the district of supplying water depends upon both the source of the water and
the city being supplied. The variable cost per acre foot of water for each combination of river and
city is given in Table 15.9.
        Using units of 1 million acre feet, the bottom row of the table shows the amount of water
needed by each city in the coming year (a total of 12.5). The rightmost column shows the amount
available from each river (a total of 16).
        Since the total amount available exceeds the total amount needed, management wants to
determine how much water to take from each river, and then how much to send from each river to
each city. The objective is to minimize the total cost of meeting the needs of the four cities.
Formulation and Solution
                                                                                        CD 15-21

Figure 15.6 shows a spreadsheet model for this variant of a transportation problem. Because
Hollyglass cannot be supplied with Calorie River water, the Solver dialogue box includes the
constraint that F13 = 0. The amounts available in column I represent maximum amounts rather
than fixed amounts, so ≤ signs are used for the corresponding constraints, TotalFromRiver
(G11:G13) ≤ Available (I11:I13).
         The Excel Solver then gives the optimal solution shown in Figure 15.6. The cells Total-
FromRiver (G11:G13) indicate that the entire available supply from the Colombo and Sacron
rivers should be used whereas only 1.5 million acre feet of the 5 million acre feet available from
the Calorie River should be used. The changing cells WaterDistribution (C11:F13) provide the
plan for how much to send from each river to each city. The total cost is given in the target cell
TotalCost (I17) as $1.975 billion.


Table 15.9 Water Resources Data for Metro Water District
                                                                                                      CD 15-22



       A               B                  C          D          E          F            G        H         I
   1   Metro Water District Distribution Problem
   2
   3       Unit Cost ($millions)        Berdoo   Los Devils   San Go   Hollyglass
   4                Colombo River        160        130        220        170
   5                  Sacron River       140        130        190        150
   6                  Calorie River      190        200        230          -
   7
   8
   9       Water Distribution                                                          Total
  10       (million acre-feet)          Berdoo   Los Devils   San Go   Hollyglass   From River         Available
  11                 Colombo River        0          5           0          0            5       <=       5
  12                   Sacron River       2          0          2.5       1.5            6       <=       6
  13                   Calorie River      0          0          1.5         0           1.5      <=       5
  14                    Total To City     2          5           4        1.5
  15                                      =          =           =          =                         Total Cost
  16                        Needed        2          5           4        1.5                         ($million)
  17                                                                                                    1,975

                                                                       Range Name                    Cells
                                                                       Available                     I11:I13
                                                                       Needed                        C16:F16
                                                                       TotalCost                     I17
                                                                       TotalFromRiver                G11:G13
                                                                       TotalToCity                   C14:F14
                                                                       UnitCost                      C4:F6
                                                                       WaterDistribution             C11:F13

                                                                                              G
                                                                                     9       Total
                                                                                     10   From River
                                                                                     11 =SUM(C11:F11)
                                                                                     12 =SUM(C12:F12)
                                                                                     13 =SUM(C13:F13)

                 B                 C             D             E             F
  14           Total To City =SUM(C11:C13) =SUM(D11:D13) =SUM(E11:E13) =SUM(F11:F13)

                                                                          I
                                                      15             Total Cost
                                                      16              ($million)
                                                      17 =SUMPRODUCT(UnitCost,WaterDistribution)

Figure 15.6                A spreadsheet formulation of the Metro Water District problem as a variant
                           of a transportation problem, including the target cell TotalCost I17) and the
                           other output cells TotalFromRiver (G11:G13) and TotalToCity (C14:F14), as
                           well as the specifications needed to set up the model. The changing cells
                           WaterDistribution (C11:F13) show the optimal solution obtained by the
                           Solver.


Production Scheduling
The Northern Airplane Company builds commercial airplanes for various airline companies
around the world. The last stage in the production process is to produce the jet engines and then
to install them (a very fast operation) in the completed airplane frame. The company has been
                                                                                                  CD 15-23

working under some contracts to deliver a considerable number of airplanes in the near future,
and the production of the jet engines for these planes must now be scheduled for the next four
months.
         To meet the contracted dates for delivery, the company must supply engines for
installation in the quantities indicated in the second column of Table 15.10. Thus, the cumulative
number of engines produced by the end of months 1, 2, 3, and 4 must be at least 10, 25, 50, and
70, respectively.


Table 15.10 Production Scheduling Data for the Northern Airplane Company Problem




         The facilities that will be available for producing the engines vary according to other
production, maintenance, and renovation work scheduled during this period. The resulting
monthly differences in the maximum number of engines that can be produced during regular time
hours (no overtime) are shown in the third column of Table 15.10, and the additional numbers
that can be produced during overtime hours are shown in the fourth column. The cost of
producing each one on either regular time or overtime is given in the fifth and sixth columns.
         Because of the variations in production costs, it may well be worthwhile to produce some
of the engines a month or more before they are scheduled for installation, and this possibility is
being considered. The drawback is that such engines must be stored until the scheduled
installation (the airplane frames will not be ready early) at a storage cost of $15,000 per month
(including interest on expended capital) for each engine1, as shown in the rightmost column of
Table 15.10.
       The production manager wants a schedule developed for the number of engines to be
produced in each of the four months so that the total of the production and storage costs will be
minimized.
Formulation and Solution
Figure 15.7 shows the formulation of this problem as a variant of a transportation problem. The
sources of the jet engines are their production on regular time (RT) and on overtime (OT) in each
of the four months. Their supplies are obtained from the third and fourth columns of Table 15.10.
The destinations for these engines are their installation in each of the four months, so their
demands are given in the second column of Table 15.10.




1
  For modeling purposes, it is being assumed that the storage cost is incurred at the end of the month to just
those engines that are being held over into the next month. Thus, engines that are produced in a given
month for installation in the same month are assumed to incur no storage cost.
                                                                                               CD 15-24



     A       B            C              D          E           F       G          H      I        J
1    Northern Airplane Co. Production-Scheduling Problem
2
3        Production Cost               Regular                     Storage Cost
4        ($millions)                    Time     Overtime      ($millions per month)
5                     Month 1           1.08       1.10                 0.015
6                     Month 2           1.11       1.12
7                     Month 3           1.10       1.11
8                     Month 4           1.13       1.15
9
10
11       Unit Cost                                Month Installed
12       ($millions)                     1         2            3       4
13                            1 (RT)    1.08      1.10         1.11    1.13
14                            1 (OT)    1.10      1.12         1.13    1.15
15                            2 (RT)      -       1.11         1.13    1.14
16        Month               2 (OT)      -       1.12         1.14    1.15
17       Produced             3 (RT)      -         -          1.10    1.12
18                            3 (OT)      -         -          1.11    1.13
19                            4 (RT)      -         -            -     1.13
20                            4 (OT)      -         -            -     1.15
21
22
23                                                Month Installed                              Maximum
24       Units Produced                   1         2            3       4     Produced        Production
25                          1 (RT)       10         5            0       5        20      <=       20
26                          1 (OT)        0         0            0       0         0      <=       10
27                          2 (RT)        0        10            0       0        10      <=       30
28        Month             2 (OT)        0         0            0       0         0      <=       15
29       Produced           3 (RT)        0         0           25       0        25      <=       25
30                          3 (OT)        0         0            0      10        10      <=       10
31                          4 (RT)        0         0            0       5         5      <=       5
32                          4 (OT)        0         0            0       0         0      <=       10
33                        Installed      10        15           25      20
34                                       =         =            =       =                      Total Cost
35          Scheduled Installations      10        15           25      20                     ($millions)
36                                                                                                77.4
                                                                                          CD 15-25

(Figure 15.7 continued)
          B            C      D             E                   F                        G
  11 Unit Cost                                          Month Installed
  12 ($millions)           1       2                   3                      4
  13                1 (RT) =D5     =D5+StorageCost     =D5+2*StorageCost      =D5+3*StorageCost
  14                1 (OT) =E5     =E5+StorageCost     =E5+2*StorageCost      =E5+3*StorageCost
  15                2 (RT)   -     =D6                 =D6+StorageCost        =D6+2*StorageCost
  16    Month       2 (OT)   -     =E6                 =E6+StorageCost        =E6+2*StorageCost
  17 Produced       3 (RT)   -             -           =D7                    =D7+StorageCost
  18                3 (OT)   -             -           =E7                    =E7+StorageCost
  19                4 (RT)   -             -                     -            =D8
  20                4 (OT)   -             -                     -            =E8

                                                                                       H
                                                                            24      Produced
                                                                            25   =SUM(D25:G25)
                                                                            26   =SUM(D26:G26)
                                                                            27   =SUM(D27:G27)
                                                                            28   =SUM(D28:G28)
                                                                            29   =SUM(D29:G29)
                                                                            30   =SUM(D30:G30)
                                                                            31   =SUM(D31:G31)
                                                                            32   =SUM(D32:G32)




                 C            D             E             F                             G
      33      Installed =SUM(D25:D32) =SUM(E25:E32) =SUM(F25:F32)                 =SUM(G25:G32)
 Range Name                       Cells                            J
 Installed                        D33:G33        34            Total Cost
 MaxProduction                    J25:J32        35            ($millions)
 Produced                         H25:H32        36 =SUMPRODUCT(UnitCost,UnitsProduced)
 ProductionCost                   D5:E8
 ScheduledInstallations           D35:G35
 StorageCost                      G5
 TotalCost                        J36
 UnitCost                         D13:G20
 UnitsProduced                    D25:G32

Figure 15.7          A spreadsheet formulation of the Northern Airplane Co. problem as a variant
                     of a transportation problem, including the target cell TotalCost (J36) and the
                     othr output cells UnitCost (D13:G20), Produced (H25:H32), and Installed
                     (D33:G33), as well as the specifications needed to set up the model. The
                     changing cells UnitsProduced (D25:G32) display the optimal production
                     schedule obtained by the Solver.


        It is not possible to install an engine in some month prior to its production, so the Solver
dialogue box includes constraints that the number installed must be zero in each of these cases.
Similarly, dashes are inserted into the UnitCost table for these cases. Otherwise, the unit costs
given in this table (in units of $1 million) are obtained by combining the unit cost of production
                                                                                      CD 15-26

from the fifth or sixth column of Table 15.10 with any storage costs ($0.015 million per unit per
month stored). (The equations entered into UnitCost (D13:G20) are shown after the spreadsheet
in Figure 15.7.) Since the quantities in MaxProduction (J25:J32) represent the maximum amounts
that can be produced, they are preceded by ≤ signs in column I. The corresponding supply
constraints, Produced (H25:H32) ≤ MaxProduction (J25:J32), are included in the Solver dialogue
box along with the usual demand constraints.
                                                                                          CD 15-27

Table 15.11 Optimal Production Schedule for the Northern Airplane Co.




         The changing cells UnitsProduced (D25:G32) show an optimal solution for this problem.
Table 15.11 summarizes the key features of this solution. Overtime is used only once (in month
3). Despite the hefty costs incurred by storing engines, extra engines are produced in the first and
third months to be stored for installation later. Even month 2 produces enough engines that five
will remain in storage for installation in month 3, despite the fact that production costs are higher
in month 2 than in month 3. Thus, a human scheduler would have difficulty in finding this
schedule. However, the Excel Solver has no difficulty in balancing all the factors involved to
reduce the total cost to an absolute minimum, which turns out to be $77.4 million (as shown in
the target cell TotalCost [J36]) in this case.
Designing School Attendance Zones
The Middletown School District is opening a third high school and thus needs to redraw the
boundaries for the areas of the city that will be assigned to the respective schools.
         For preliminary planning, the city has been divided into nine tracts with approximately
equal populations. (Subsequent detailed planning will divide the city further into over 100 smaller
tracts.) The main body of Table 15.12 shows the approximate distance between each tract and
school. The rightmost column gives the number of high school students in each tract next year.
(These numbers are expected to grow slowly over the next several years.) The last two rows show
the minimum and maximum number of students each school should be assigned.


Table 15.12 Data for the Middletown School District Problem
                                                                                           CD 15-28



        The school district management has decided that the appropriate objective in setting
school attendance zone boundaries is to minimize the average distance that students must travel
to school. At this preliminary stage, they want to determine how many students from each tract
should be assigned to each school to achieve this objective, while also satisfying the enrollment
constraints at each school indicated by the bottom two rows of Table 15.12.


Formulation and Solution
Minimizing the average distance that students must travel is equivalent to minimizing the sum of
the distances that individual students must travel. Therefore, adopting the latter objective, this is
just a variant of a transportation problem where the unit costs are distances.
         Because each school has both a minimum and maximum enrollment, we proceed just as
in the Nifty Co. example (Section 15.3) to provide two rows of data cells below the changing
cells that specify these minimum and maximum amounts in the spreadsheet model shown in
Figure 15.8. The corresponding constraints are included in the Solver dialogue box along with the
usual supply constraints. Clicking on the Solve button then gives the optimal solution shown in
the changing cells NumberOfStudents (C17:E25).
        This optimal solution gives the following plan:
        Assign tracts 2 and 3 to school 1.
        Assign tracts 1, 4, and 7 to school 2.
        Assign tracts 6, 8, and 9 to school 3.
        Split tract 5, with 350 students assigned to school 1 and 150 students assigned to school 2.
         As indicated in the target cell TotalDistance (H30), the total distance traveled to school
by all the students is 3,530 miles (an average of 0.872 mile per student).
                                                                                             CD 15-29

      A            B                C          D          E           F        G         H
 1    Middletown School District Zoning Problem
 2
 3        Distance (Miles)       School 1   School 2   School 3
 4                     Tract 1     2.2        1.9        2.5
 5                     Tract 2     1.4        1.3        1.7
 6                     Tract 3     0.5        1.8        1.1
 7                     Tract 4     1.2        0.3         2
 8                     Tract 5     0.9        0.7         1
 9                     Tract 6     1.1        1.6        0.6
 10                    Tract 7     2.7        0.7        1.5
 11                    Tract 8     1.8        1.2        0.8
 12                    Tract 9     1.5        1.7        0.7
 13
 14
 15           Number of                                              Total              Total
 16           Students           School 1   School 2   School 3   From Tract          In Tract
 17                  Tract 1        0         500         0          500       =        500
 18                  Tract 2       400         0          0          400       =        400
 19                  Tract 3       450         0          0          450       =        450
 20                  Tract 4        0         400         0          400       =        400
 21                  Tract 5       350        150         0          500       =        500
 22                  Tract 6        0          0         450         450       =        450
 23                  Tract 7        0         450         0          450       =        450
 24                  Tract 8        0          0         400         400       =        400
 25                  Tract 9        0          0         500         500       =        500
 26
 27            Min Enrollment     1,200      1,500      1,350
 28                                <=         <=         <=                        Total Distance
 29            Total At School    1,200      1,500      1,350                         (miles)
 30                                <=         <=         <=                            3,530
 31           Max Enrollment      1,800      1,700      1,500

                                                              Range Name             Cells
                                                              MaxEnrollment          C31:E31
                                                              Miles                  C4:E12
                                                              MinEnrollment          C27:E27
                                                              NumberOfStudents       C17:E25
                                                              TotalAtSchool          C29:E29
                                                              TotalDistance          H30
                                                              TotalFromTract         F17:F25
                                                              TotalInTract           H17:H25
                                                                                     F
                                                                          15        Total
                                                                          16     From Tract
                                                                          17   =SUM(C17:E17)
              B               C             D             E               18   =SUM(C18:E18)
 29     Total At School =SUM(C17:C25) =SUM(D17:D25) =SUM(E17:E25)         19   =SUM(C19:E19)
                                                                          20   =SUM(C20:E20)
                                     H                                    21   =SUM(C21:E21)
                  28           Total Distance                             22   =SUM(C22:E22)
                  29              (miles)                                 23   =SUM(C23:E23)
                  30 =SUMPRODUCT(Miles,NumberOfStudents)                  24   =SUM(C24:E24)
                                                                          25   =SUM(C25:E25)

Figure 15.8 A spreadsheet formulation of the Middletown School District problem as a variant of
            a transportation problem, including the target cell TotalDistance (H30) and the other
            output cells TotalFromTract (F17:F25) and the TotalAtSchool (C29:E29), as well as
            the specifications needed to set up the model. The changing cells NumberOfStudents
            (C17:E25) show the optimal zoning plan obtained by the Solver.
                                                                                           CD 15-30




Meeting Energy Needs Economically
The Energetic Company needs to make plans for the energy systems for a new building.
        The energy needs in the building fall into three categories: (1) electricity, (2) heating
water, and (3) heating space in the building. The daily requirements for these three categories (all
measured in the same units) are 20 units, 10 units, and 30 units, respectively.
        The three possible sources of energy to meet these needs are electricity, natural gas, and a
solar heating unit that can be installed on the roof. The size of the roof limits the largest possible
solar heater to providing 30 units per day. However, there is no limit to the amount of electricity
and natural gas available.
        Electricity needs can be met only by purchasing electricity. Both other energy needs
(water heating and space heating) can be met by any of the three sources of energy or a
combination thereof.
        The unit costs for meeting these energy needs from these sources of energy are shown in
Table 15.13. The objective of management is to minimize the total cost of meeting all the energy
needs.


Table 15.13 Cost Data for the Energetic Co. Problem




Formulation and Solution
Figure 15.9 shows the formulation of this problem as a variant of a transportation problem. The
changing cells DailyEnergyUse (D12:F14) show the resulting optimal solution for how many
units of each energy source should be used to meet each energy need. The target cell TotalCost
(I18) gives the total cost as $24,000 per day.
                                                                                                          CD 15-31

       A        B                C                D              E               F            G      H         I
 1     Energetic Co. Energy-Sourcing Problem
 2
 3                                                          Energy Need
 4         Unit Cost ($/day)                  Electricity   Water Heating   Space Heating
 5           Source             Electricity      400            500             600
 6              of           Natural Gas          -             600             500
 7           Energy          Solar Heater         -             300             400
 8
 9
 10                                                         Energy Need                      Total
 11        Daily Energy Use                   Electricity   Water Heating   Space Heating    Used
 12          Source            Electricity       20               0               0           20
 13             of          Natural Gas           0               0              10           10           Max Solar
 14          Energy         Solar Heater          0              10              20           30     <=       30
 15                       Total Supplied         20              10              30
 16                                               =               =              =                         Total Cost
 17                               Demand         20              10              30                         ($/day)
 18                                                                                                         24,000

                                                                            Range Name                Cells
                                                                            DailyEnergyUse            D12:F14
                                                                            Demand                    D17:F17
                                                                            MaxSolar                  I14
                                                                            TotalCost                 I18
                                                                            TotalSolar                G14
                                                                            TotalSupplied             D15:F15
                                                                            TotalUsed                 G12:G14
                                                                            UnitCost                  D5:F7

                                                                                                     G
                                                                                            10     Total
                                                                                            11     Used
                                                                                            12 =SUM(D12:F12)
                                                                                            13 =SUM(D13:F13)
                                                                                            14 =SUM(D14:F14)
                 C               D             E             F
  15        Total Supplied =SUM(D12:D14) =SUM(E12:E14) =SUM(F12:F14)
                                                                                I
                                                            16             Total Cost
                                                            17              ($/day)
                                                            18 =SUMPRODUCT(UnitCost,DailyEnergyUse)

Figure 15.9                A spreadsheet formulation of the Energetic Co. problem as a variant of a
                           transportation problem, including the target cell TotalCost (I18) and the other
                           output cells TotalUsed (G12:G14) and TotalSupplied (D15:F15), as well as
                           the specifications needed to set up the model. The changing cells
                           DailyEnergyUse (D12:F14) give the optimal energy-sourcing plan obtained
                           by the Solver.




Choosing a New Site Location
One of the most important decisions that the management of many companies must face is where
to locate a major new facility. The facility might be a new factory, a new distribution center, a
                                                                                             CD 15-32

new administrative center, or some other building. The new facility might be needed because of
expansion. In other cases, the company may be abandoning an unsatisfactory location.
        There generally are several attractive potential sites from which to choose. Increasingly,
in today’s global economy, the potential sites may extend across national borders.
         There are a number of important factors that go into management’s decision. One of them
is shipping costs. For example, when evaluating a potential site for a new factory, management
needs to consider the impact of choosing this site on the cost of shipping goods from all the
factories (including the new factory at this site) to the distribution centers. By locating the new
factory near some distribution centers that are far from all the current factories, the company can
obtain low shipping costs for the new factory and, at the same time, substantially reduce the
shipping costs from the current factories as well. Management needs to know what the total
shipping cost would be, following an optimal shipping plan, for each potential site for the new
factory.
         A similar question may arise regarding the total cost of shipping some raw material from
its various sources to all the factories (including the new one) for each potential site for the new
factory.
       A transportation problem (or a variant) often provides the appropriate way of formulating
such questions. Solving this formulation for each potential site then provides key input to
management, who must evaluate both this information and other relevant considerations in
making its final selection of the site.
        The case study presented in the next section illustrates this kind of application.


Review Questions
1. What are the areas of application illustrated in this section for variants of transportation
   problems?
2. What is the objective of management for the Metro Water District problem?
3. What are the sources and destinations in the formulation of the Northern Airplane Co.
   production scheduling problem?
4. What plays the role of unit costs in the Middletown School District problem?
5. What is the objective of management for the Energetic Co. problem?


15.5 A CASE STUDY: THE TEXAGO CORP. SITE SELECTION
PROBLEM
The Texago Corporation is a large, fully integrated petroleum company based in the United
States. The company produces most of its oil in its own oil fields and then imports the rest of
what it needs from the Middle East. An extensive distribution network is used to transport the oil
to the company’s refineries and then to transport the petroleum products from the refineries to
Texago’s distribution centers. The locations of these various facilities are given in Table 15.14.
                                                                                             CD 15-33


Table 15.14 Location of Texago’s Current Facilities




         Texago is continuing to increase its market share for several of its major products.
Therefore, management has made the decision to expand its output by building an additional
refinery and increasing its imports of crude oil from the Middle East. The crucial remaining
decision is where to locate the new refinery.
         The addition of the new refinery will have a great impact on the operation of the entire
distribution system, including decisions on how much crude oil to transport from each of its
sources to each refinery (including the new one) and how much finished product to ship from
each refinery to each distribution center. Therefore, the three key factors for management’s
decision on the location of the new refinery are
1. The cost of transporting the oil from its sources to all the refineries, including the new one.
2. The cost of transporting finished product from all the refineries, including the new one, to
the distribution centers.
3. Operating costs for the new refinery, including labor costs, taxes, the cost of needed supplies
(other than crude oil), energy costs, the cost of insurance, and so on. (Capital costs are
not a factor since they would be essentially the same at any of the potential sites.)
        Management has set up a task force to study the issue of where to locate the new refinery.
After considerable investigation, the task force has determined that there are three attractive
potential sites. These sites and the main advantages of each are spelled out in Table 15.15.
                                                                                          CD 15-34


Table 15.15 Potential Sites for Texago’s New Refinery and Their Main Advantages




Gathering the Necessary Data
The task force needs to gather a large amount of data, some of which requires considerable
digging, in order to perform the analysis requested by management.
         Management wants all the refineries, including the new one, to operate at full capacity.
Therefore, the task force begins by determining how much crude oil each refinery would need
brought in annually under these conditions. Using units of 1 million barrels, these needed
amounts are shown on the left side of Table 15.16. The right side of the table shows the current
annual output of crude oil from the various oil fields. These quantities are expected to remain
stable for some years to come. Since the refineries need a total of 360 million barrels of crude oil,
and the oil fields will produce a total of 240 million barrels, the difference of 120 million barrels
will need to be imported from the Middle East.


Table 15.16 Production Data for Texago Corp.




         Since the amounts of crude oil produced or purchased will be the same regardless of
which location is chosen for the new refinery, the task force concludes that the associated
production or purchase costs (exclusive of shipping costs) are not relevant to the site selection
decision. On the other hand, the costs for transporting the crude oil from its source to a refinery
are very relevant. These costs are shown in Table 15.17 for both the three current refineries and
the three potential sites for the new refinery.
                                                                                         CD 15-35


Table 15.17 Cost Data for Shipping Crude Oil to a Texago Refinery




         Also very relevant are the costs of shipping the finished product from a refinery to a
distribution center. Letting one unit of finished product correspond to a refinery’s production
from 1 million barrels of crude oil, these costs are given in Table 15.18. The bottom row of the
table shows the number of units of finished product needed by each distribution center.


Table 15.18 Cost Data for Shipping Finished Product to a Distribution Center




         The final key body of data involves the operating costs for a refinery at each potential
site. Estimating these costs requires site visits by several members of the task force to collect
detailed information about local labor costs, taxes, and so forth. Comparisons then are made with
the operating costs of the current refineries to help refine these data. In addition, the task force
gathers information on one-time site costs for land, construction, and other expenses and
amortizes these costs on an equivalent uniform annual cost basis. This process leads to the
estimates shown in Table 15.19.
                                                                                           CD 15-36

Table 15.19 Estimated Operating Costs for a Texago Refinery at Each Potential Site




Analysis (Six Applications of a Transportation Problem)
Armed with these data, the task force now needs to develop the following key financial
information for management:
1.   Total shipping cost for crude oil with each potential choice of a site for the new refinery.
2.   Total shipping cost for finished product with each potential choice of a site for the new
     refinery.
For both types of costs, once a site is selected, an optimal shipping plan will be determined and
then followed. Therefore, to find either type of cost with a potential choice of a site, it is
necessary to solve for the optimal shipping plan given that choice and then calculate the
corresponding cost.
        The task force recognizes that the problem of finding an optimal shipping plan for a
given choice of a site is just a transportation problem. In particular, for shipping crude oil, Figure
15.10 shows the spreadsheet model for this transportation problem, where the entries in the data
cells come directly from Tables 15.16 and 15.17. The entries for the New Site column (cells
G5:G8) will come from one of the last three columns of Table 15.17, depending on which
potential site currently is being evaluated. At this point, before entering this column and clicking
on the Solve button, a trial solution of 0 for each of the shipment quantities has been entered into
the changing cells ShipmentQuantity (D13:G16).
         These same changing cells in Figures 15.11, 15.12, and 15.13 show the optimal shipping
plan for each of the three possible choices of a site. The target cell TotalCost (J20) gives the
resulting total annual shipping cost in millions of dollars. In particular, if Los Angeles were to be
chosen as the site for the new refinery (Figure 15.11), the total annual cost of shipping crude oil
in the optimal manner would be $880 million. If Galveston were chosen instead (Figure 15.12),
this cost would be $920 million, whereas it would be $960 million if St. Louis were chosen
(Figure 15.13).
                                                                                                                        CD 15-37



      A       B               C                  D             E                 F             G             H          I       J
  1   Texago Corp. Site-Selection Problem (Shipping to Refineries)
  2
  3                                                                 Refineries
  4       Unit Cost ($millions)              New Orleans   Charleston          Seattle      New Site
  5                                Texas         2             4                 5
  6         Oil                 California       5             5                 3
  7        Fields                 Alaska         5             7                 3
  8                           Middle East        2             3                 5
  9
 10
 11       Shipment Quantity                                         Refineries
 12       (millions of barrels)              New Orleans   Charleston          Seattle      New Site    Total Shipped        Supply
 13                                 Texas        0             0                 0             0              0         =      80
 14         Oil                 California       0             0                 0             0              0         =      60
 15        Fields                  Alaska        0             0                 0             0              0         =     100
 16                           Middle East        0             0                 0             0              0         =     120
 17                        Total Received        0             0                 0             0
 18                                              =             =                 =             =                            Total Cost
 19                                Demand       100           60                 80           120                           ($millions)
 20                                                                                                                             0


                                                                                         Range Name                     Cells
                                                                                         Demand                         D19:G19
                                                                                         ShipmentQuantity               D13:G16
                                                                                         Supply                         J13:J16
                                                                                         TotalCost                      J20
                                                                                         TotalReceived                  D17:G17
                                                                                         TotalShipped                   H13:H16
                                                                                         UnitCost                       D5:G8
                                                                                                                    H
                                                                                                       12     Total Shipped
                                                                                                       13   =SUM(D13:G13)
                                                                                                       14   =SUM(D14:G14)
                                                                                                       15   =SUM(D15:G15)
                                                                                                       16   =SUM(D16:G16)

                  C              D             E             F             G
 17         Total Received =SUM(D13:D16) =SUM(E13:E16) =SUM(F13:F16) =SUM(G13:G16)

                                                                                 J
                                                             18              Total Cost
                                                             19              ($millions)
                                                             20 =SUMPRODUCT(UnitCost,ShipmentQuantity)

Figure 15.10                      The basic spreadsheet formulation for the Texago transportation problem for
                                  shipping crude oil from oil fields to the refineries, including the new refinery
                                  at a site still to be selected. The target cell is TotalCost (J20) and the other
                                  ouput cells are TotalShipped (H13:H16) and TotalReceived (D17:G17).
                                  Before entering the data for a new site and then clicking on the Solve button,
                                  a trial solution of 0 has been entered into each of the changing cells
                                  ShipmentQuantity (D13:G16).
                                                                                                                             CD 15-38



      A       B               C                      D              E               F          G               H         I         J
 1    Texago Corp. Site-Selection Problem (Shipping to Refineries, Including Los Angeles)
 2
 3                                                                    Refineries
 4        Unit Cost ($millions)               New Orleans    Charleston          Seattle   Los Angeles
 5                                  Texas         2              4                 5            3
 6          Oil                 California        5              5                 3            1
 7         Fields                 Alaska          5              7                 3            4
 8                            Middle East         2              3                 5            4
 9
 10
 11       Shipment Quantity                                           Refineries
 12       (millions of barrels)               New Orleans    Charleston          Seattle   Los Angeles   Total Shipped          Supply
 13                                 Texas         40             0                 0            40             80        =        80
 14         Oil                 California        0              0                 0            60             60        =        60
 15        Fields                  Alaska         0              0                 80           20            100        =       100
 16                           Middle East         60            60                 0            0             120        =       120
 17                        Total Received        100            60                 80          120
 18                                               =              =                 =            =                             Total Cost
 19                                Demand        100            60                 80          120                            ($millions)
 20                                                                                                                              880



Figure 15.11                      The changing cells ShipmentQuantity (D13:G16) give Texago management
                                  an optimal plan for shipping crude oil if Los Angeles is selected as the new
                                  site for the refinery in column G of Figure 15.10.




      A       B              C                   D              E                 F           G               H          I        J
 1    Texago Corp. Site-Selection Problem (Shipping to Refineries, Including Galveston)
 2
 3                                                                   Refineries
 4        Unit Cost ($millions)              New Orleans    Charleston          Seattle    Galveston
 5                                Texas          2              4                 5            1
 6           Oil              California         5              5                 3            3
 7          Fields              Alaska           5              7                 3            5
 8                          Middle East          2              3                 5            3
 9
 10
 11       Shipment Quantity                                          Refineries
 12       (millions of barrels)              New Orleans    Charleston          Seattle    Galveston     Total Shipped         Supply
 13                               Texas          20             0                 0           60               80        =       80
 14          Oil              California         0              0                 0           60               60        =       60
 15         Fields               Alaska          20             0                 80           0              100        =      100
 16                         Middle East          60            60                 0            0              120        =      120
 17                      Total Received         100            60                 80         120
 18                                              =              =                 =            =                              Total Cost
 19                               Demand        100            60                 80         120                              ($millions)
 20                                                                                                                              920



Figure 15.12                      The changing cells ShipmentQuantity (D13:G16) give Texago management
                                  an optimal plan for shipping crude oil if Galveston is selected as the new site
                                  for a refinery in column G of Figure 15.10.
                                                                                                                   CD 15-39

      A       B              C                 D             E                 F          G             H          I       J
 1    Texago Corp. Site-Selection Problem (Shipping to Refineries, Including St. Louis)
 2
 3                                                                Refineries
 4        Unit Cost ($millions)            New Orleans   Charleston          Seattle   St. Louis
 5                               Texas         2             4                 5           1
 6          Oil              California        5             5                 3           4
 7         Fields              Alaska          5             7                 3           7
 8                         Middle East         2             3                 5           4
 9
 10
 11       Shipment Quantity                                       Refineries
 12       (millions of barrels)            New Orleans   Charleston          Seattle   St. Louis   Total Shipped        Supply
 13                              Texas         0             0                 0           80            80        =      80
 14         Oil              California        0            20                 0           40            60        =      60
 15        Fields               Alaska         20            0                 80          0            100        =     100
 16                        Middle East         80           40                 0           0            120        =     120
 17                     Total Received        100           60                 80         120
 18                                            =             =                 =           =                           Total Cost
 19                               Demand      100           60                 80         120                          ($millions)
 20                                                                                                                       960



Figure 15.13                  The changing cells ShipmentQuantity (D13:G16) give Texago management
                              an optimal plan for shipping crude oil if St. Louis is selected as the new site
                              for a refinery in column G of Figure 15.10.




        The analysis of the cost of shipping finished product is similar. Figure 15.14 shows the
spreadsheet model for this transportation problem, where rows 5–7 come directly from the first
three rows of Table 15.18. The New Site row would be filled in from one of the next three rows of
Table 15.18, depending on which potential site for the new refinery is currently under evaluation.
Since the units for finished product leaving a refinery are equivalent to the units for crude oil
coming in, the data in Supply (J13:J16) come from the left side of Table 15.16.
        The changing cells ShipmentQuantity (D13:G16) in Figures 15.15, 15.16, and 15.17
show the optimal plan for shipping finished product for each of the sites being considered for the
new refinery. The target cell TotalCost (J20) in Figure 15.15 indicates that the resulting total
annual cost for shipping finished product if the new refinery were in Los Angeles is $1.57 billion.
Similarly, this total cost would be $1.63 billion if Galveston were the chosen site (Figure 15.16)
and $1.43 billion if St. Louis were chosen (Figure 15.17).
                                                                                                                            CD 15-40

      A         B               C                 D          E                 F                G                H          I       J
 1    Texago Corp. Site-Selection Problem (Shipping to D.C.'s)
 2
 3                                                            Distribution Center
 4        Unit Cost ($millions)               Pittsburgh   Atlanta         Kansas City     San Francisco
 5                            New Orleans         6.5        5.5                6                8
 6         Refineries           Charleston         7          5                 4                7
 7                                 Seattle         7          8                 4                3
 8                               New Site
 9
 10
 11       Shipment Quantity                                   Distribution Center
 12       (millions of barrels)               Pittsburgh   Atlanta         Kansas City     San Francisco    Total Shipped        Supply
 13                            New Orleans         0          0                 0                0                0         =     100
 14         Refineries          Charleston         0          0                 0                0                0         =      60
 15                                 Seattle        0          0                 0                0                0         =      80
 16                                New Site        0          0                 0                0                0         =     120
 17                          Total Received        0          0                 0                0
 18                                                =          =                 =                =                              Total Cost
 19                                 Demand        100        80                80               100                             ($millions)
 20                                                                                                                                 0


                                                                                         Range Name                         Cells
                                                                                         Demand                             D19:G19
                                                                                         ShipmentQuantity                   D13:G16
                                                                                         Supply                             J13:J16
                                                                                         TotalCost                          J20
                                                                                         TotalReceived                      D17:G17
                                                                                         TotalShipped                       H13:H16
                                                                                         UnitCost                           D5:G8
                                                                                                                        H
                                                                                                           12     Total Shipped
                                                                                                           13   =SUM(D13:G13)
                                                                                                           14   =SUM(D14:G14)
                                                                                                           15   =SUM(D15:G15)
                                                                                                           16   =SUM(D16:G16)

                  C               D                               E             F             G
 17         Total Received =SUM(D13:D16)                    =SUM(E13:E16) =SUM(F13:F16) =SUM(G13:G16)

                                                                               J
                                                           18              Total Cost
                                                           19              ($millions)
                                                           20 =SUMPRODUCT(UnitCost,ShipmentQuantity)

Figure 15.14                  The basic spreadsheet formulation for the Texago transportation problem for
                              shipping finished product from the refineries (including the new one at a site
                              still to be selected) to the distribution centers. The target cell is TotalCost
                              (J20) and the other output cells are TotalShipped (H13:H16) and
                              TotalReceived (D17:G17). Before entering the data for a new site and then
                              clicking on the Solve button, a trial solution of 0 has been entered into each
                              of the changing cells ShipmentQuantity (D13:G16).
                                                                                                                           CD 15-41

      A         B                C                 D          E                  F              G               H          I       J
 1    Texago Corp. Site-Selection Problem (Shipping to D.C.'s When Choose Los Angeles)
 2
 3                                                              Distribution Center
 4        Unit Cost ($millions)                Pittsburgh   Atlanta          Kansas City   San Francisco
 5                            New Orleans          6.5        5.5                 6              8
 6         Refineries           Charleston          7          5                  4              7
 7                                 Seattle          7          8                  4              3
 8                            Los Angeles           8          6                  3              2
 9
 10
 11       Shipment Quantity                                     Distribution Center
 12       (millions of barrels)                Pittsburgh   Atlanta          Kansas City   San Francisco   Total Shipped        Supply
 13                            New Orleans          80        20                  0              0              100        =     100
 14         Refineries           Charleston         0         60                  0              0               60        =      60
 15                                  Seattle        20         0                  0             60               80        =      80
 16                             Los Angeles         0          0                 80             40              120        =     120
 17                          Total Received        100        80                 80            100
 18                                                 =          =                  =              =                             Total Cost
 19                                  Demand        100        80                 80            100                             ($millions)
 20                                                                                                                              1,570



Figure 15.15                   The changing cells ShipmentQuantity (D13:G16) give Texago management
                               an optimal plan for shipping finished product if Los Angeles is selected as
                               the new site for a refinery in rows 8 and 16 of Figure 15.14.


      A         B                C                 D          E                  F              G               H          I       J
 1    Texago Corp. Site-Selection Problem (Shipping to D.C.'s When Choose Galveston)
 2
 3                                                              Distribution Center
 4        Unit Cost ($millions)                Pittsburgh   Atlanta          Kansas City   San Francisco
 5                            New Orleans          6.5        5.5                 6              8
 6         Refineries           Charleston          7          5                  4              7
 7                                 Seattle          7          8                  4              3
 8                              Galveston           5          4                  3              6
 9
 10
 11       Shipment Quantity                                     Distribution Center
 12       (millions of barrels)                Pittsburgh   Atlanta          Kansas City   San Francisco   Total Shipped        Supply
 13                            New Orleans         100         0                  0              0              100        =     100
 14         Refineries          Charleston          0         60                  0              0               60        =      60
 15                                 Seattle         0          0                  0             80               80        =      80
 16                              Galveston          0         20                 80             20              120        =     120
 17                          Total Received        100        80                 80            100
 18                                                 =          =                  =              =                             Total Cost
 19                                  Demand        100        80                 80            100                             ($millions)
 20                                                                                                                              1,630



Figure 15.16                   The changing cells ShipmentQuantity (D13:G16) give Texago management
                               an optimal plan for shippig finished product if Galveston is selected as the
                               new site for a refinery in rows 8 and 16 of Figure 15.14.
                                                                                                                            CD 15-42

       A         B                C                 D          E                  F              G               H          I       J
  1    Texago Corp. Site-Selection Problem (Shipping to D.C.'s When Choose St. Louis)
  2
  3                                                              Distribution Center
  4        Unit Cost ($millions)                Pittsburgh   Atlanta          Kansas City   San Francisco
  5                            New Orleans          6.5        5.5                 6              8
  6         Refineries           Charleston          7          5                  4              7
  7                                 Seattle          7          8                  4              3
  8                               St. Louis          4          3                  1              5
  9
  10
  11       Shipment Quantity                                     Distribution Center
  12       (millions of barrels)                Pittsburgh   Atlanta          Kansas City   San Francisco   Total Shipped        Supply
  13                            New Orleans         100         0                  0              0              100        =     100
  14         Refineries          Charleston          0         60                  0              0               60        =      60
  15                                 Seattle         0          0                  0             80               80        =      80
  16                                St. Louis        0         20                 80             20              120        =     120
  17                          Total Received        100        80                 80            100
  18                                                 =          =                  =              =                             Total Cost
  19                                  Demand        100        80                 80            100                             ($millions)
  20                                                                                                                              1,430



Figure 15.17                    The changing cells ShipmentQuantity (D13:G16) give Texago management
                                an optimal plan for shipping product if St. Louis is selected as the new site
                                for a refinery in rows 8 and 16 of Figure 15.14.




         For each of the three alternative sites, two separate spreadsheet models have been used
 for planning the shipping of crude oil and the shipping of finished product. However, another
 option would have been to combine all this planning into a single spreadsheet model for each site
 and then to simultaneously optimize the plans for the two types of shipments. This would
 essentially involve combining Figure 15.11 with Figure 15.15, Figure 15.12 with Figure 15.16,
 and Figure 15.13 with Figure 15.17, and then using the sum of the shipping costs for the pair of
 transportation problems as the target cell to be minimized. This would have the advantage of
 showing all the shipment planning for a given site on a single spreadsheet. At the end of the
 chapter, Case 15-1 will continue this Texago case study by considering a situation where this kind
 of combined spreadsheet model is needed to find the best overall shipping plan for each possible
 choice of a site.
 The Message to Management
 The task force now has completed its financial analysis of the three alternative sites for the new
 refinery. Table 15.20 shows all the major variable costs (costs that vary with the decision) on an
 annual basis that would result from each of the three possible choices for the new site. The second
 column summarizes what the total annual cost of shipping crude oil to all refineries (including the
 new one) would be for each alternative (as already given in Figures 15.11, 15.12, and 15.13). The
 third column repeats the data in Figures 15.15, 15.16, and 15.17 on the total annual cost of
 shipping finished product from the refineries to the distribution centers. The fourth column shows
 the estimated operating costs for a refinery at each potential site, as first given in Table 15.19.
                                                                                                          CD 15-43



Table 15.20 Annual Variable Costs Resulting from the Choice of Each Site for the New
Texago Refinery




        Adding across these three columns gives the total variable cost for each alternative.
        Conclusion: From a purely financial viewpoint, St. Louis is the best site for the new refinery. This site would
        save the company about $200 million annually as compared to the Galveston alternative and about $150
        million as compared to the Los Angeles alternative.

          However, as with any site selection decision, management must consider a wide variety
of factors, including some nonfinancial ones. (For example, remember that one important
advantage of the Galveston site is that it is close to corporate headquarters.) Furthermore, if ways
can be found to reduce some of the costs in Table 15.20 for either the Los Angeles or Galveston
sites, this might change the financial evaluation substantially. Management also must consider
whether there are any cost trends or trends in the marketplace that might alter the picture in the
future.
         After careful consideration, Texago management tentatively chooses the St. Louis site.
(This story continues in Case 15-1, where the task force is asked to analyze the option of
enlarging the capacity of the new refinery before the final decision is made on its site.)


Review Questions
1.   What are the three key factors for management’s decision on the location of the new
     refinery?
2.   Why do shipping costs to and from the current refineries need to be considered along with
     those for the new refinery?
3.   Why did the Texago task force find it necessary to solve six transportation problems instead
     of just one?
4.   What else must Texago management consider in addition to the financial analysis based on
     solving?


15.6 CHARACTERISTICS OF ASSIGNMENT PROBLEMS
We now turn to another special type of linear programming problem (first introduced in Section
3.6) called assignment problems. As the name suggests, this kind of problem involves making
assignments. Frequently,these are assignments of people to jobs. Thus, many applications of the
assignment problem involve aiding managers in matching up their personnel with tasks to be
                                                                                        CD 15-44

performed. Other applications might instead involve assigning machines, vehicles, or plants to
tasks.
       The Sellmore Company problem presented in Section 3.6 is a prototype example of an
assignment problem. For completeness, we begin with this same example.
An Example: The Sellmore Company Problem
The marketing manager of the Sellmore Company will be holding the company’s annual sales
conference soon for sales regional managers and personnel. To assist in the administration of the
conference, he is hiring four temporary employees (Ann, Ian, Joan, and Sean), where each will
handle one of the following four tasks:
1. Word processing of written presentations.
2. Computer graphics for both oral and written presentations.
3. Preparation of conference packets, including copying and organizing written materials.
4. Handling of advance and on-site registrations for the conference.
He now needs to decide which person to assign to each task.
        Although each temporary employee has at least the minimal background necessary to
perform any of the four tasks, they differ considerably in how efficiently they can handle the
different types of work. Table 15.21 shows how many hours each would need for each task. The
rightmost column gives the hourly wage based on the background of each employee.
Formulation of a Spreadsheet Model
Figure 15.18 shows a spreadsheet model for this problem. Table 15.21 is entered at the top.
Combining these required times and wages gives the cost (cells D15:G18) for each possible
assignment of a temporary employee to a task, using equations shown at the bottom of Figure
15.18. This cost table is just the way that any assignment problem is displayed. The objective is
to determine which assignments should be made to minimize the sum of the associated costs.


Table 15.21 Data for the Sellmore Co. Problem
                                                                                                                 CD 15-45

       A          B        C           D           E              F            G              H           I           J
  1    Sellmore Co. Assignment Problem
  2
  3                                                        Task
  4        Required Time             Word                                                               Hourly
  5        (Hours)                 Processing   Graphics      Packets     Registrations                 Wage
  6                         Ann        35          41           27             40                        $14
  7         Assignee         Ian       47          45           32             51                        $12
  8                        Joan        39          56           36             43                        $13
  9                        Sean        32          51           25             46                        $15
 10
 11
 12                                                        Task
 13                                  Word
 14        Cost                    Processing   Graphics      Packets     Registrations
 15                         Ann       $490       $574          $378          $560
 16         Assignee         Ian      $564       $540          $384          $612
 17                        Joan       $507       $728          $468          $559
 18                        Sean       $480       $765          $375          $690
 19
 20
 21                                                        Task
 22                                 Word                                                      Total
 23        Assignment             Processing    Graphics      Packets     Registrations   Assignments              Supply
 24                          Ann      0            0             1             0               1          =          1
 25         Assignee          Ian     0            1             0             0               1          =          1
 26                        Joan       0            0             0             1               1          =          1
 27                        Sean       1            0             0             0               1          =          1
 28               Total Assigned      1            1             1             1
 29                                   =            =            =              =                                  Total Cost
 30                     Demand        1            1             1             1                                   $1,957


                                                                              Range Name                      Cells
                                                                              Assignment                      D24:G27
                                                                              Cost                            D15:G18
                                                                              Demand                          D30:G30
                                                                              HourlyWage                      I6:I9
                                                                              RequiredTime                    D6:G9
                                                                              Supply                          J24:J27
                                                                              TotalAssigned                   D28:G28
                                                                              TotalAssignments                H24:H27
                                                                              TotalCost                       J30
                                                                                                          H
                                                                                             22          Total
                                                                                             23      Assignments
                                                                                             24    =SUM(D24:G24)
                                                                                             25    =SUM(D25:G25)
                                                                                             26    =SUM(D26:G26)
                                                                                             27    =SUM(D27:G27)
                 C              D                            E                     F                      G
  28       Total Assigned =SUM(D24:D27)                =SUM(E24:E27)         =SUM(F24:F27)          =SUM(G24:G27)

                                                                                       J
                                                                        29         Total Cost
                                                                        30 =SUMPRODUCT(Cost,Assignment)

Figure 15.18 A spreadsheet formulation of the Sellmore Co. problem as an assignment problem, including the target
             cell TotalCost (J30) and the other output cells Cost (D15:G18), TotalAssignments (H24:H27), and Total
             Assigned (D28:G28), as well as the specifications needed to set up the model. The values of 1 in the
             changing cells Assignment (D24:G27) show the optimal plan obtained by the Solver for assigning the
             people to the tasks.
                                                                                          CD 15-46

        The values of 1 in Supply (J24:J27) indicate that each person (assignee) listed in column
C must perform exactly one task. The values of 1 in Demand (D30:G30) indicate that each task
must be performed by exactly one person. These requirements then are specified in the
constraints given in the Solver dialogue box.
         Each of the changing cells Assignment (D24:G27) is given a value of 1 when the
corresponding assignment is being made, and a value of 0 otherwise. Therefore, the Excel
equation for the target cell, TotalCost = SUMPRODUCT(Cost, Assignment), gives the total cost
for the assignments being made. The Solver dialogue box specifies that the objective is to
minimize this target cell.
        The changing cells in Figure 15.18 show the optimal solution obtained after clicking on
the Solve button. This solution is
        Assign Ann to prepare conference packets.
        Assign Ian to do the computer graphics.
        Assign Joan to handle registrations.
        Assign Sean to do the word processing.
The total cost given in cell J30 is $1,957.


The Model for Assignment Problems
Any assignment problem can be described in the following general terms. Given a set of tasks to
be performed and a set of assignees who are available to perform these tasks, the problem is to
determine which assignee should be assigned to each task.
         To fit the model for an assignment problem, the following assumptions need to be
satisfied:
1.   The number of assignees and the number of tasks are the same.
2.   Each assignee is to be assigned to exactly one task.
3.   Each task is to be performed by exactly one assignee.
4.   There is a cost associated with each combination of an assignee performing a task.
5.   The objective is to determine how all the assignments should be made to minimize the total
     cost.
         The first three assumptions are fairly restrictive. Many potential applications do not quite
fit these assumptions. However, these variants of assignment problems still can be solved by the
Excel Solver, as we will describe in Section 15.7.
         When the assumptions are satisfied, all that needs to be done to formulate a problem as
an assignment problem is to (1) identify the assignees and tasks and (2) construct a cost table that
gives the cost associated with each combination of an assignee performing a task. Figure 15.18
illustrates how to display this formulation on a spreadsheet. The spreadsheet model for any
assignment problem will include constraints to enforce assumptions 2 and 3. In Figure 15.18,
these constraints are TotalAssignments (H24:H27) = Supply (J24:J27) and TotalAssigned
(D28:G28) = Demand (D30:G30), where values of 1 are entered in the data cells Supply
(J24:J27) and Demand (D30:G30).
                                                                                         CD 15-47


The Network Representation of an Assignment Problem
In addition to a cost table, the network representation provides an alternative way of displaying
an assignment problem. Figure 15.19 shows the network representation of the Sellmore Co.
assignment problem, where all the assignees are lined up in order on the left and all the tasks are
lined up in order on the right. The arrows show the possible assignments, where exactly four
arrows are to be chosen—one emanating from each assignee and one leading to each task. The
number next to each arrow gives the cost if that particular assignment is chosen.




Figure 15.19         The network representation of the Sellmore Co. assignment problem shows
                     all the possible assignments and their costs graphically.


        This network representation provides a way of visualizing an assignment problem
graphically. This representation also can be used to clarify the relationship between assignment
problems and the other network optimization problems considered in Chapter 6.
The Assignment Problem Is a Special Type of Transportation Problem
Did you happen to notice that the network representation in Figure 15.19 is strikingly similar to
the network representation for a transportation problem shown in Figure 15.3? Look and see.
         This similarity is no coincidence. The assignment problem is, in fact, just a special type
of transportation problem where the sources now are assignees and the destinations now are
tasks. Furthermore, as illustrated by the Sellmore Co. assignment problem in Figure 15.18, every
source has a supply of 1 (since each assignee is to be assigned to exactly one task) and every
destination has a demand of 1 (since each task is to be performed by exactly one assignee).
        Therefore, all the characteristics of transportation problems described in Section 15.2 also
apply to assignment problems.
                                                                                          CD 15-48


Solving Assignment Problems
The Excel Solver uses the simplex method to solve any kind of linear programming problem,
including both transportation problems and assignment problems and their variants. This works
fine for problems of the size considered in this book (or even considerably larger).
        However, as discussed in Section 15.2, either the transportation simplex method or the
network simplex method provides a far more efficient way of solving big transportation problems.
Consequently, since the assignment problem is a special type of transportation problem, these
same algorithms can be used to solve big assignment problems quickly.
         Nevertheless, even these special algorithms do not provide the fastest way of solving
assignment problems. There are much faster algorithms available that have been designed
specifically to solve assignment problems. The most famous of these is called the Hungarian
method. In practice, one of these special algorithms normally would be used to solve large
assignment problems. Although the Excel Solver does not have special-purpose algorithms such
as the Hungarian method for efficiently solving special types of linear programming problems,
other linear programming software packages are available that do.


Review Questions
1.   Give a one-sentence description of assignment problems.
2.   What assumptions about assignees and tasks need to hold for a problem to be an assignment
      problem?
3.   What needs to be done to formulate a problem as an assignment problem?
4.   What are the sources, destinations, supplies, and demands when an assignment problem is
     described as a special kind of transportation problem?
5.   Name an algorithm that has been designed specifically just to solve assignment problems
     very quickly.


15.7 MODELING VARIANTS OF ASSIGNMENT PROBLEMS
Variants of assignment problems frequently arise because they have one or more features that do
not quite fit all the assumptions enumerated in the preceding section for the model of an
assignment problem. The features we will consider are the following:
1.   Certain assignees are unable to perform certain tasks.
2.   Although each assignee will perform exactly one task, there are more tasks than assignees,
     so some tasks will not be done.
3.   Although each task will be performed by exactly one assignee, there are more assignees
     than tasks, so some assignees will not perform any task.
4.      Each assignee can be assigned to perform more than one task simultaneously.
5.   Each task can be performed jointly by more than one assignee.
          For each of these features, there is a clever way of reformulating the problem to make it
fit the format for an assignment problem, which then enables using an extremely efficient
specialpurpose algorithm (such as the Hungarian method). However, this isn’t necessary except
                                                                                        CD 15-49

on problems that are much larger than any considered in this book. Therefore, we instead will
formulate a spreadsheet model in the most straightforward way and solve it with the Excel Solver.
        Three examples are presented below to illustrate the above features. The first example
focuses on features 1 and 2. The second combines feature 4 with a variation of feature 3. The
third deals with feature 5.
        To illuminate the close relationships between transportation problems and assignment
problems, the second and third examples are based on earlier examples of variants of
transportation problems.
Example 1: Assigning Machines to Locations
The Job Shop Company has purchased three new machines of different types. There are five
available locations in the shop where a machine could be installed. Some of these locations are
more desirable than others for particular machines because of their proximity to work centers that
will have a heavy work flow to and from these machines. (There will be no work flow between
the new machines.) Therefore, the objective is to assign the new machines to the available
locations to minimize the total cost of materials handling. The estimated cost per hour of
materials handling involving each of the machines is given in Table 15.22 for the respective
locations. Location 2 is not considered suitable for machine 2, so no cost is given for this case.


Table 15.22 Materials-Handling Cost Data for the Job Shop Co. Problem




Formulation of a Spreadsheet Model
As it stands, this is almost an assignment problem, since the machines can be viewed as assignees
to be assigned to locations as the tasks. However, it does not quite qualify because assumption 1
for the assignment problem model is violated (we have two more locations than machines), as are
assumption 3 (two locations will not be filled by a machine) and assumption 4 (we do not have a
cost associated with assigning machine 2 to location 2).
        Figure 15.20 shows a spreadsheet model for this variant of an assignment problem.
Because location 2 cannot be used for machine 2, the Solver dialogue box includes the constraint
that D12 = 0. The usual supply constraints, TotalAssignments (H11:H13) = Supply (J11:J13),
ensure that each machine will be assigned to exactly one location. The fact that two locations will
not be used is taken into account by using a ≤ sign in the demand constraints, TotalAssigned
(C14:G14) ≤ Demand (C16:G16).
                                                                                                                  CD 15-50



      A          B                C            D            E            F             G            H         I        J
  1   Job Shop Co. Machine-Location Problem
  2
  3       Cost ($/hour)       Location 1   Location 2   Location 3   Location 4    Location 5
  4               Machine 1      13           16           12           14            15
  5               Machine 2      15            -           13           20            16
  6               Machine 3       4            7           10            6             7
  7
  8
  9                                                                                                 Total
 10       Assignment        Location 1     Location 2   Location 3   Location 4    Location 5   Assignments         Supply
 11              Machine 1      0              0            0            1             0             1        =       1
 12              Machine 2      0              0            1            0             0             1        =       1
 13              Machine 3      1              0            0            0             0             1        =       1
 14          Total Assigned     1              0            1            1             0
 15                            <=             <=           <=           <=            <=                           Total Cost
 16                Demand       1              1            1            1             1                            ($/hour)
 17                                                                                                                    31


                                                                                  Range Name                  Cells
                                                                                  Assignment                  C11:G13
                                                                                  Cost                        C4:G6
                                                                                  Demand                      C16:G16
                                                                                  Supply                      J11:J13
                                                                                  TotalAssigned               C14:G14
                                                                                  TotalAssignments            H11:H13
                                                                                  TotalCost                   J17
                                                                                                          H
                                                                                                9        Total
                                                                                                10   Assignments
                                                                                                11 =SUM(C11:G11)
                                                                                                12 =SUM(C12:G12)
                                                                                                13 =SUM(C13:G13)
               B               C             D             E             F             G
 14       Total Assigned =SUM(C11:C13) =SUM(D11:D13) =SUM(E11:E13) =SUM(F11:F13) =SUM(G11:G13)

                                                                                       J
                                                                       15         Total Cost
                                                                       16          ($/hour)
                                                                       17 =SUMPRODUCT(Cost,Assignment)

Figure 15.20             A spreadsheet formulation of the Job Shop Co. problem as a variant of an
                         assignment problem, including the target cell TotalCost (J17) and the other
                         output cells TotalAssignments (H11:H13) and TotalAssigned (C14:G14), as
                         well as the specifications needed to set up the model. The values of 1 in the
                         changing cells Assignment (C11:G13) show the optimal plan obtained by the
                         Solver for assigning the machines to the locations.
                                                                                              CD 15-51

         The changing cells Assignment (C11:G13) with a value of 1 show the assignments being
made in the optimal solution after clicking on the Solve button. Since none of these cells for
locations 2 and 5 have a value of 1, a machine will not be placed in either of these locations. The
target cell TotalCost (J17) indicates that the total cost for this optimal solution is $31 per hour.


Example 2: Assigning Plants to Products
Reconsider Example 1 in Section 15.3, where the Better Products Co. needs to assign three plants
to produce four new products. The relevant data are given in Table 15.6.
         As described in Section 15.3, management had permitted product splitting (where the
same product is produced in more than one plant). However, there are some hidden costs
associated with product splitting that are not reflected in Table 15.6, including extra setup,
distribution, and administration costs. Therefore, management now has decided to have the
problem analyzed again under the additional restriction that product splitting is prohibited.
        New Problem Statement: Given the data in Table 15.6, minimize the total cost of assigning each
        plant to at least one new product where each product is to be produced in only one plant (no
        product splitting). Since there are three plants and four new products, two plants will produce.

Formulation of a Spreadsheet Model
Since we want to assign plants to products, the plants can be viewed as assignees and the
products as the tasks to be performed for this variant of an assignment problem. Figure 15.21
shows the resulting spreadsheet model.
                                                                                                           CD 15-52

       A               B                   C           D           E           F            G         H         I
 1     Better Products Co. Production Planning Problem (Revised)
 2
 3         Unit Cost                    Product 1   Product 2   Product 3   Product 4
 4                            Plant 1      $41         $27         $28         $24
 5                            Plant 2      $40         $29          -          $23
 6                            Plant 3      $37         $30         $27         $21
 7
 8             Required Production         20          30          30          40
 9
 10
 11        Cost ($/day)                 Product 1   Product 2   Product 3   Product 4
 12                           Plant 1     $820        $810        $840        $960
 13                           Plant 2     $800        $870          -         $920
 14                           Plant 3     $740        $900        $810        $840
 15
 16
 17                                                                                         Total
 18        Assignment                   Product 1   Product 2   Product 3   Product 4   Assignments          Supply
 19                           Plant 1      0            1           1           0            2        <=       2
 20                           Plant 2      1            0           0           0            1        <=       2
 21                           Plant 3      0            0           0           1            1         =       1
 22                    Total Assigned      1            1           1           1
 23                                        =            =           =           =                           Total Cost
 24                Demand                  1            1           1           1                            $3,290

                                                                        Range Name                    Cells
                                                                        Assignment                    C19:F21
                                                                        Cost                          C12:F14
                                                                        Demand                        C24:F24
                                                                        RequiredProduction            C8:F8
                                                                        Supply                        I19:I21
                                                                        TotalAssigned                 C22:F22
                                                                        TotalAssignments              G19:G21
                                                                        TotalCost                     I24
                                                                        UnitCost                      C4:F6

                                                                                                   G
                                                                                         17       Total
                                                                                         18   Assignments
                                                                                         19 =SUM(C19:F19)
                                                                                         20 =SUM(C20:F20)
                                                                                         21 =SUM(C21:F21)

                  B                C             D             E             F
  22          Total Assigned =SUM(C19:C21) =SUM(D19:D21) =SUM(E19:E21) =SUM(F19:F21)

                                                                                    I
                                                                    23         Total Cost
                                                                    24 =SUMPRODUCT(Cost,Assignment)

Figure 15.21                In contrast to Figure 15.4, product splitting is not allowed, so the Better Products Co.
                            problem becomes a variant of an assignment problem. The target cell is TotalCost
                            (I24) and the other output cells are Cost (C12:F14), TotalAssignments (G19:G21),
                            and TotalAssigned(C22:F22), where the equations entered into these cells are shown
                            below the spreadsheet. The values of 1 in the changing cells Assignment (C19:F21)
                            display the optimal production plan obtained by the Solver.
                                                                                          CD 15-53

         The data from Table 15.6 are given at the top. However, the unit costs given in cells
C4:F6 are not the appropriate costs for the cost table for a variant of an assignment problem. To
construct the appropriate cost table, we must determine each cost associated with assigning a
plant to all the required production of a product. The corresponding unit cost shown in rows 4–6
is the cost of producing only one unit rather than the entire required (daily) production given in
row 8. Therefore, we must multiply this unit cost by the required (daily) production to obtain the
total (daily) cost of the assignment. For example, consider the assignment of Plant 1 to product 1.
     Cost of Plant 1 producing one unit of product 1       = $41
     Required (daily) production of product 1              = 20 units
     Total (daily) cost of assigning Plant 1 to product 1 = 20 ($41)
                                                           = $820
Cost(C12:F14) gives the total (daily) assignment costs, calculated in this way (see the equations
at the bottom of the figure), for each combination of assigning a plant to a product.
        Since Plant 2 cannot produce product 3, the Solver dialogue box includes the constraint
that E20 = 0. Either Plant 1 or Plant 2 (but not both) needs to be chosen to produce a second
product, so these two plants are given a supply of 2 in cells I19:I20. A ≤ sign is then used for the
corresponding supply constraints, G19:G20 ≤ I19:I20. However, the supply constraint for Plant 3
and the demand constraints are the usual ones for an assignment problem.
         After clicking on the Solve button, the optimal solution shown in the changing cells
Assignment (C19:F21) is obtained, namely, Plant 1 produces products 2 and 3, Plant 2 produces
product 1, and Plant 3 produces product 4. The target cell TotalCost (I24) gives the total daily
cost of $3,290 for this production plan.
        It is interesting to compare this solution with the one given in DailyProduction (C11:F13)
of Figure 15.4 when product splitting was permitted. Note that the assignments for plants 2 and 3
in Figure 15.4 are quite different than here. The total cost calculated for the production plan
shown in that figure is $3,260 per day, or $30 per day less than for the plan in Figure 15.21.
         However, the formulation of the original problem (product splitting permitted) as a
variant of a transportation problem does not take into account hidden costs of product splitting
(extra setup, distribution, and administration costs), which probably are considerably more than
$30 per day. Therefore, management adopted the production plan based on this new formulation
(product splitting prohibited) as a variant of an assignment problem.


Example 3: Designing School Attendance Zones
Now refer back to Section 15.4 for the problem faced by the management of the Middletown
School District in designing school attendance zones. Table 15.12 gives the data for the problem
and Figure 15.8 shows its formulation as a variant of a transportation problem.
          The optimal solution obtained from this formulation has two problems that concern
management. One is that this solution splits tract 5 between two schools (schools 1 and 2). Each
tract is a cohesive neighborhood that has always stayed together in attending the same school
prior to high school. The school district superintendent and the school board are in agreement that
it would be much better to continue to keep each neighborhood (including tract 5) together in
assigning it to a single school. The second problem with the solution is that it assigns the smallest
number of students (1,200) to the school with the largest capacity (school 1, with a capacity of
                                                                                                                CD 15-54

1,800 students). Although this is marginally acceptable (the school board has chosen 1,200 as the
minimum number of students it would allow to be assigned to school 1), a more even allocation
of students to the schools would be preferable.
        Therefore, the school district management has decided to prohibit splitting any tract
between schools. To provide a relatively even allocation of students to schools, management also
will require that exactly three tracts be assigned to each school.
          New Problem Statement: Given the data in Table 15.12, minimize the total distance that all students must
          travel to school when each tract is assigned entirely to one school (no tract splitting) and each school is
          assigned exactly three tracts.

Formulation of a Spreadsheet Model
Since tracts are being assigned to schools, this problem can be interpreted as a variant of an
assignment problem where the tracts are the assignees and the schools are the tasks. It is only a
variant because each school is to be assigned exactly three tracts, whereas assumption 3 for the
assignment problem model specifies that each task is to be performed by exactly one assignee.
Therefore, in the spreadsheet model shown in Figure 15.22, each task (school) is given a demand
of 3 rather than 1. Otherwise, the constraints for this model are the same as for an assignment
problem.
      A         B             C          D          E           F         G         H            I          J          K
 1    Middletown School District Zoning Problem (Revised)
 2
 3          Distance                                        Number of             Cost
 4           (Miles)       School 1   School 2   School 3    Students            (Miles)      School 1   School 2   School 3
 5                 Tract 1   2.2        1.9        2.5         500                    Tract 1  1100        950       1250
 6                 Tract 2   1.4        1.3        1.7         400                    Tract 2   560        520        680
 7                 Tract 3   0.5        1.8        1.1         450                    Tract 3   225        810        495
 8                 Tract 4   1.2        0.3         2          400                    Tract 4   480        120        800
 9                 Tract 5   0.9        0.7         1          500                    Tract 5   450        350        500
 10                Tract 6   1.1        1.6        0.6         450                    Tract 6   495        720        270
 11                Tract 7   2.7        0.7        1.5         450                    Tract 7  1215        315        675
 12                Tract 8   1.8        1.2        0.8         400                    Tract 8   720        480        320
 13                Tract 9   1.5        1.7        0.7         500                    Tract 9   750        850        350
 14
 15
 16                                                             Total
 17        Assignment      School 1   School 2   School 3   Assignments          Supply
 18                Tract 1    0          1          0            1        =        1
 19                Tract 2    1          0          0            1        =        1
 20                Tract 3    1          0          0            1        =        1
 21                Tract 4    0          1          0            1        =        1
 22                Tract 5    1          0          0            1        =        1
 23                Tract 6    0          0          1            1        =        1
 24                Tract 7    0          1          0            1        =        1
 25                Tract 8    0          0          1            1        =        1
 26                Tract 9    0          0          1            1        =        1
 27         Total Assigned    3          3          3
 28                           =          =          =                         Total Distance
 29               Demand      3          3          3                            (Miles)
 30                                                                               3560
                                                                                               CD 15-55



Figure 15.22 continued
                                                                Range Name                   Cells
                                                                Assignment                   C18:E26
                                                                Cost                         I5:K13
                                                                Demand                       C29:E29
                                                                Distance                     C5:E13
                                                                NumberOfStudents             F5:F13
                                                                Supply                       H18:H26
                                                                TotalAssigned                C27:E27
                                                                TotalAssignments             F18:F26
                                                                TotalDistance                H30
                                                      H                I             J             K
                                             3       Cost
                                             4      (Miles)      School 1      School 2       School 3
                                             5        Tract 1   =C5*F5        =D5*F5         =E5*F5
                                             6        Tract 2   =C6*F6        =D6*F6         =E6*F6
                           F                 7        Tract 3   =C7*F7        =D7*F7         =E7*F7
               16         Total              8        Tract 4   =C8*F8        =D8*F8         =E8*F8
               17     Assignments            9        Tract 5   =C9*F9        =D9*F9         =E9*F9
               18   =SUM(C18:E18)           10        Tract 6   =C10*F10      =D10*F10       =E10*F10
               19   =SUM(C19:E19)           11        Tract 7   =C11*F11      =D11*F11       =E11*F11
               20   =SUM(C20:E20)           12        Tract 8   =C12*F12      =D12*F12       =E12*F12
               21   =SUM(C21:E21)           13        Tract 9   =C13*F13      =D13*F13       =E13*F13
               22   =SUM(C22:E22)
               23   =SUM(C23:E23)
               24   =SUM(C24:E24)
               25   =SUM(C25:E25)
               26   =SUM(C26:E26)

               B                C                      D                    E
  27       Total Assigned =SUM(C18:C26)          =SUM(D18:D26)        =SUM(E18:E26)
                                                                           H
                                                           28        Total Distance
                                                           29           (Miles)
                                                           30 =SUMPRODUCT(Cost,Assignment)

Figure 15.22         In contrast to Figure 15.8, tract splitting is no longer allowed, so the Middletown
                     School District problem becomes a variant of an assignment problem. The target cell
                     is TotalDistance (H30) and the other output cells are TotalAssignments (F18:26),
                     TotalAssigned (C27: E27), and (in units of miles) Cost (I5:K13), where the
                     equations entered into these cells are shown after the spreadsheet. The values of 1 in
                     the changing cells Assignment (C18:E26) show the optimal zoning plan found by the
                     Solver.




         The objective for an assignment problem is to minimize the total cost of all the
assignments made, but now cost is being measured in terms of the total distance that students
travel. Therefore, the cost of assigning any tract to a particular school is the number of students in
                                                                                           CD 15-56

that tract times the distance to that school per student, where both of these quantities are given in
the table called Distance (C5:E13) in Figure 15.22. To illustrate, consider the cost of assigning
tract 1 to school 1.
     Distance from tract 1 to school 1    = 2.2 miles
     Number of students in tract 1        = 500
     Cost of assigning tract 1 to school 1 = 500(2.2 miles)
                                          = 1,100 miles
The table called Cost (I5:K13) shows the costs calculated in this way for all the combinations of
tracts and schools, using the equations given for these cells.
        The changing cells Assignment (C18:E26) show the optimal assignments of tracts to
schools obtained by clicking on the Solve button. As indicated in the target cell TotalDistance
(H30), the resulting total distance traveled to school by all the students is 3,560 miles. This
amounts to an average of 0.879 mile per student.
         This plan is very similar to the one obtained in Section 15.4 (see Figure 15.8) when tract
splitting was permitted. The only difference is that the earlier plan splits tract 5, with 150 of its
500 students assigned to school 2 rather than to school 1, thereby reducing the distance traveled
to school for each of these 150 students from 0.9 mile to 0.7 mile. However, the school district
management feels that this small saving in distance traveled does not justify separating these 150
students from their neighbors who had always gone to school with them. Therefore, management
adopted the new plan.
       As this example and the preceding one illustrate, management often needs to have
modifications made in the original model of the problem to better consider managerial concerns.


Review Questions
1.    When formulating a spreadsheet model for a variant of an assignment problem where
      certain assignees are unable to perform certain tasks, how is this feature formulated in the
      model?
2.    If an assignee will perform more than one task, how is this feature formulated in the
      spreadsheet model?
3.    If a task will be performed jointly by more than one assignee, how is this feature formulated
      in the spreadsheet model?


15.8 Summary
Transportation problems and assignment problems (and their variants) are special types of linear
programming problems that have a variety of important applications.
         A transportation problem is concerned (literally or figuratively) with distributing a
commodity from its sources to some destinations. Each source has a fixed supply and each
destination has a fixed demand for the commodity. A basic assumption is that the cost of
distribution from each source to each destination is directly proportional to the amount
distributed. Formulating a transportation problem requires identifying the unit costs of
distribution, the supplies, and the demands.
                                                                                         CD 15-57

         Given a set of tasks to be performed and a set of assignees who are available to perform
the tasks (one assignee per task), an assignment problem deals with the question of which
assignee should be assigned to each task so as to minimize the total cost of performing all the
tasks. The assignees can be people, machines, vehicles, plants, and so on, so there are many
applications. The formulation of the problem requires constructing a cost table that gives the cost
for each possible assignment of an assignee to a task.
       A variety of features that do not quite fit either the transportation problem format or the
assignment problem format also can be readily formulated in a spreadsheet model.
       The overriding goal of this chapter has been to enable you to recognize when a problem
you might face as a future manager can be formulated and analyzed as a transportation or
assignment problem, or as a variant of one of these problem types.


Glossary
assignees The entities (people, machines, vehicles, plants, etc.) that are to perform the tasks
when formulating a problem as an assignment problem. (Section 15.6)
cost table The table that summarizes the formulation of an assignment problem by giving
the cost for each possible assignment of an assignee to a task. (Section 15.6)
demand at a destination The number of units that need to be received by this destination from
the sources. (Section 15.2)
destinations The receiving centers for a transportation problem. (Section 15.2)
Hungarian method An algorithm designed specifically to solve assignment problems very
efficiently. (Section 15.6)
network simplex method A streamlined version of the simplex method for solving distribution-
network problems, including transportation and assignment problems, very efficiently. (Section
15.2)
sources The supply centers for a transportation problem. (Section 15.2)
supply from a source The number of units to be distributed from this source to the destinations.
(Section 15.2)
tasks The jobs to be performed by the assignees when formulating a problem as an assignment
problem. (Section 15.6)
transportation simplex method A streamlined version of the simplex method for solving
transportation problems very efficiently. (Section 15.2)
                                                                                      CD 15-58


Learning Aids for This Chapter in Your MS Courseware

Chapter 15 Excel Files:
P & T Case Study                        Energetic Example
Better Products Example                 Texago Case Study (6 spreadsheets)
Nifty Example                           Sellmore Example
Metro Example                           Job Shop Example
Northern Airplane Example               Revised Better Products Example
Middletown Example                      Revised Middletown Example


An Excel Add-in:
Premium Solver for Education


Problems
We have inserted the symbol E* to the left of each problem (or its parts) where Excel should be
used (unless your instructor gives you contrary instructions). An asterisk on the problem number
indicates that at least a partial answer is given at the end of the problems.
15.1. Consider the transportation problem having the following data:




15.2. Consider the transportation problem having the following data:
CD 15-59
                                                                                          CD 15-60

15.3. The Cost-Less Corp. supplies its four retail outlets from its four plants. The shipping cost
per shipment from each plant to each retail outlet is given below.




15.4. The Childfair Company has three plants producing child push chairs that are to be shipped
to four distribution centers. Plants 1, 2, and 3 produce 12, 17, and 11 shipments per month,
respectively. Each distribution center needs to receive 10 shipments per month. The distance from
each plant to the respective distribution centers is given below:




E* 15.5.* Tom would like 3 pints of home brew today and an additional 4 pints of home brew
tomorrow. Dick is willing to sell a maximum of 5 pints total at a price of $3.00/pint today and
$2.70/pint tomorrow. Harry is willing to sell a maximum of 4 pints total at a price of $2.90/pint
today and $2.80/pint tomorrow.
                                                                                         CD 15-61

         Tom wishes to know what his purchases should be to minimize his cost while satisfying
his thirst requirements. Formulate and solve a spreadsheet model for this problem.
E* 15.6. The Versatech Corporation has decided to produce three new products. Five branch
plants now have excess product capacity. The unit manufacturing cost of the first product would
be $31, $29, $32, $28, and $29 in plants 1, 2, 3, 4, and 5, respectively. The unit manufacturing
cost of the second product would be $45, $41, $46, $42, and $43 in plants 1, 2, 3, 4, and 5,
respectively. The unit manufacturing cost of the third product would be $38, $35, and $40 in
plants 1, 2, and 3, respectively, whereas plants 4 and 5 do not have the capability for producing
this product. Sales forecasts indicate that 600, 1,000, and 800 units of products 1, 2, and 3,
respectively, should be produced per day. Plants 1, 2, 3, 4, and 5 have the capacity to produce
400, 600, 400, 600, and 1,000 units daily, respectively, regardless of the product or combinations
of products involved. Assume that any plant having the capability and capacity to produce them
can produce any combination of the products in any quantity.
        Management wishes to know how to allocate the new products to the plants to minimize
total manufacturing cost. Formulate and solve a spreadsheet model for this problem.


E* 15.7. Suppose that England, France, and Spain produce all the wheat, barley, and oats in the
world. The world demand for wheat requires 125 million acres of land devoted to wheat
production. Similarly, 60 million acres of land are required for barley and 75 million acres of land
are required for oats. The total amount of land available for these purposes in England, France,
and Spain is 70 million acres, 110 million acres, and 80 million acres, respectively. The number
of hours of labor needed in England, France, and Spain, respectively, to produce an acre of wheat
is 18, 13, and 16; to produce an acre of barley is 15, 12, and 12; and to produce an acre of oats is
12, 10, and 16. The labor cost per hour in England, France, and Spain, respectively, for producing
wheat is $9.00, $7.20, and $9.90; for producing barley is $8.10, $9.00, and $8.40; and for
producing oats is $6.90, $7.50, and $6.30. The problem is to allocate land use in each country so
as to meet the world food requirements and minimize the total labor cost. Formulate and solve a
spreadsheet model for this problem.


E* 15.8. A contractor, Susan Meyer, has to haul gravel to three building sites. She can purchase
as much as 18 tons at a gravel pit in the north of the city and 14 tons at one in the south. She
needs 10, 5, and 10 tons at sites 1, 2, and 3, respectively. The purchase price per ton at each
gravel pit and the hauling cost per ton are given in the following table.




         Susan wishes to determine how much to haul from each pit to each site to minimize the
total cost for purchasing and hauling gravel. Formulate and solve a spreadsheet model for this
problem.


E* 15.9. Reconsider the P & T Co. case study presented in Sections 15.1 and 15.2. Refer to the
spreadsheet in Figure 15.2, which shows the formulation as a transportation problem and displays
                                                                                         CD 15-62

an optimal solution. You now learn that one or more of the unit costs in the data cells UnitCost
(D5:G7) may change slightly before shipments begin.
         Use the Excel Solver to generate the sensitivity report for this problem. Use this
report to determine the allowable range for each of the unit costs. What do these allowable
ranges tell P & T management?


E* 15.10. Reconsider the Metro Water District problem presented in Section 15.4. Refer to the
spreadsheet in Figure 15.6, which shows the formulation as a variant of a transportation problem
and displays an optimal solution.
         The numbers given in the data cells are only estimates that may be somewhat inaccurate,
so management now wishes to do some what-if analysis. Use the Excel Solver to generate the
sensitivity report. Then use this report to address the following questions. (In each case, assume
that the indicated change is the only change in the model.)
a.   Would the optimal solution in Figure 15.6 remain optimal if the cost per acre foot of
     shipping Calorie River water to San Go were actually $200 rather than $230?
b.   Would this solution remain optimal if the cost per acre foot of shipping Sacron River water
     to Los Devils were actually $160 rather than $130?
c.   Must this solution remain optimal if the costs considered in parts a and b were
     simultaneously changed from their original values to $215 and $145, respectively?
d.   Suppose that the supply from the Sacron River and the demand at Hollyglass are decreased
     simultaneously by the same amount. Must the shadow prices for evaluating these changes
     remain valid if the decrease were 0.5 million acre feet?


E* 15.11. Reconsider the Metro Water District problem presented in Section 15.4, including the
data given in Table 15.9.
        The numbers in this table for the amount of water needed by the respective cities actually
represent the absolute minimum that each city must have. Each city would like to have as much
as 2 million additional acre feet beyond this minimum amount.
        Since the amount of water available exceeds the sum of these minimum amounts by 3.5
million acre feet, Metro management has decided to distribute this additional water to the cities as
well. The decisions on how much additional water the respective cities will receive beyond
meeting their minimum needs will be based on minimizing Metro’s total cost. Management wants
to know which plan for distributing water from the rivers to the cities will achieve this objective.
        Formulate and solve a spreadsheet model for this problem.


E* 15.12. The Onenote Co. produces a single product at three plants for four customers. The
three plants will produce 60, 80, and 40 units, respectively, during the next week. The firm has
made a commitment to sell 40 units to customer 1, 60 units to customer 2, and at least 20 units to
customer 3. Both customers 3 and 4 also want to buy as many of the remaining units as possible.
The net profit associated with shipping a unit from plant i for sale to customer j is given by the
following table:
                                                                                       CD 15-63




         Management wishes to know how many units to sell to customers 3 and 4 and how many
units to ship from each of the plants to each of the customers to maximize profit. Formulate and
solve a spreadsheet model for this problem.


E* 15.13. The Move-It Company has two plants building forklift trucks that then are shipped to
three distribution centers. The production costs are the same at the two plants, and the cost of
shipping each truck is shown below for each combination of plant and distribution center:




A total of 60 forklift trucks are produced and shipped per week. Each plant can produce and ship
any amount up to a maximum of 50 trucks per week, so there is considerable flexibility on how to
divide the total production between the two plants so as to reduce shipping costs. However, each
distribution center must receive exactly 20 trucks per week.
        Management’s objective is to determine how many forklift trucks should be produced at
each plant, and then what the overall shipping pattern should be to minimize total shipping cost.
Formulate and solve a spreadsheet model for this problem.


E* 15.14. Redo Problem 15.13 when any distribution center may receive any quantity between
10 and 30 forklift trucks per week in order to further reduce total shipping cost, provided only
that the total shipped to all three distribution centers must still equal 60 trucks per week.


E* 15.15. The Build-Em-Fast Company has agreed to supply its best customer with three
widgits during each of the next three weeks, even though producing them will require some
overtime work. The relevant production data are as follows:
                                                                                          CD 15-64




The cost per unit produced with overtime for each week is $100 more than for regular time. The
cost of storage is $50 per unit for each week it is stored. There is already an inventory of two
widgets on hand currently, but the company does not want to retain any widgets in inventory after
the three weeks.
       Management wants to know how many units should be produced in each week to
minimize the total cost of meeting the delivery schedule. Formulate and solve a spreadsheet
model for this problem.


E* 15.16. The MJK Manufacturing Company must produce two products in sufficient quantity to
meet contracted sales in each of the next three months. The two products share the same
production facilities, and each unit of both products requires the same amount of production
capacity. The available production and storage facilities are changing month by month, so the
production capacities, unit production costs, and unit storage costs vary by month. Therefore, it
may be worthwhile to overproduce one or both products in some months and store them until
needed.
         For each of the three months, the initialed columns of the following table give the
maximum number of units of the two products combined that can be produced on regular time
(RT) and on overtime (OT). For each of the two products, the subsequent columns give (1) the
number of units needed for the contracted sales, (2) the cost (in thousands of dollars) per unit
produced on regular time, (3) the cost (in thousands of dollars) per unit produced on overtime,
and (4) the cost (in thousands of dollars) of storing each extra unit that is held over into the next
month. In each case, the numbers for the two products are separated by a slash /, with the number
for product 1 on the left and the number for product 2 on the right.




The production manager wants a schedule developed for the number of units of each of the two
products to be produced on regular time and, if regular time production capacity is used up, on
overtime in each of the three months. The objective is to minimize the total of the production and
storage costs while meeting the contracted sales for each month. There is no initial inventory, and
no final inventory is desired after the three months.
                                                                          CD 15-65

        Formulate and solve a spreadsheet model for this problem.


15.17. Consider the transportation problem having the following data:




15.18. Consider the assignment problem having the following cost table:




15.19. Consider the assignment problem having the following cost table:
                                                                                       CD 15-66




15.20. Four cargo ships will be used for shipping goods from one port to four other ports (labeled
1, 2, 3, 4). Any ship can be used for making any one of these four trips. However, because of
differences in the ships and cargoes, the total cost of loading, transporting, and unloading the
goods for the different ship–port combinations varies considerably, as shown in the following
table:




E* 15.21. Reconsider Problem 15.6. Suppose that the sales forecasts have been revised
downward to 240, 400, and 320 units per day of products 1, 2, and 3, respectively. Thus, each
plant now has the capacity to produce all that is required of any one product. Therefore,
management has decided that each new product should be assigned to only one plant and that no
plant should be assigned more than one product (so that three plants are each to be assigned one
product, and two plants are to be assigned none). The objective is to make these assignments so
as to minimize the total cost of producing these amounts of the three products. Formulate and
solve a spreadsheet model for this problem.


15.22.* The coach of an age group swim team needs to assign swimmers to a 200-yard medley
relay team to send to the Junior Olympics. Since most of his best swimmers are very fast in more
than one stroke, it is not clear which swimmer should be assigned to each of the four strokes. The
                                                                                         CD 15-67

five fastest swimmers and the best times (in seconds) they have achieved in each of the strokes
(for 50 yards) are




The coach wishes to determine how to assign four swimmers to the four different strokes to
minimize the sum of the corresponding best times.




E* 15.23. Reconsider Problem 15.8. Now suppose that trucks (and their drivers) need to be hired
to do the hauling, where each truck can only be used once to haul gravel from a single pit to a
single site. Enough trucks are available to haul all the gravel that can be purchased at each site.
Each truck can haul five tons, and the cost per truck is five times the hauling cost per ton given
earlier. Only full trucks are to supply each site.
        Formulate and solve a spreadsheet model for this problem.


E* 15.24. Reconsider Problem 15.13. Now distribution centers 1, 2, and 3 must receive exactly
10, 20, and 30 units per week, respectively. For administrative convenience, management has
decided that each distribution center will be supplied totally by a single plant, so that one plant
will supply one distribution center and the other plant will supply the other two distribution
centers. The choice of these assignments of plants to distribution centers is to be made solely on
the basis of minimizing total shipping cost.
        Formulate and solve a spreadsheet model for this problem.




Partial Answers to Selected Problems
15.5.   3 pints from Harry today, 4 pints from Dick tomorrow. Total cost = $19.50.

15.19. b. A–2, B–4, C–3, D–1. Total cost = $20.

15.22. b. David–backstroke, Tony–breaststroke, Chris–butterfly, Carl–freestyle. Total time =
           126.20 seconds.
                                                                                          CD 15-68


Case 15-1
Continuation of the Texago Case Study
Reconsider the case study presented in Section 15.5 involving the Texago Corp. site selection
problem.
        Texago management has tentatively chosen St. Louis as the site of the new refinery.
However, management now is addressing the question of what the capacity of the new refinery
should be.
          While analyzing the site selection problem, the task force was told to assume that the new
refinery would have the capacity to process 120 million barrels of crude oil per year. As indicated
in Table 15.16, this then would increase the total capacity of all the corporation’s refineries from
240 million barrels to 360 million barrels. According to marketing forecasts, Texago will be able
to sell all its finished product once this new capacity becomes available, but no more. Therefore,
the choice of 120 million barrels as the capacity of the new refinery would enable all the
corporation’s refineries to operate at full capacity while also fully meeting the forecasted demand
for Texago’s products.
         However, to prepare for possible future increases in demand beyond the current forecasts,
management now wants to also consider the option of enlarging the plans for the new refinery so
that it would have the capacity to process 150 million barrels of crude oil annually. Although this
would force the corporation’s refineries collectively to operate below full capacity by 30 million
barrels for awhile, the extra capacity then would be available later if Texago were to continue to
increase its market share. This might be well worthwhile since the capital and operating costs
incurred by enlarging the plans for the new refinery would be far less (perhaps 40 percent less)
than constructing and operating another refinery later to process only 30 million barrels of crude
oil per year. Furthermore, management feels that this extra capacity might be needed within a few
years.
         The extra capital costs needed to increase the capacity of the new refinery by 30 million
barrels is estimated to be $1.2 billion. The cost of carrying this extra capital would be about $100
million per year, depending on future interest rates. If some of this extra capacity were used at the
new refinery, the total operating cost for the refinery would be somewhat larger than the amount
shown in Table 15.19, but decreasing the production rate by the same amount at another refinery
would decrease its total operating cost by a comparable amount. Since the operating cost per
million barrels of crude oil processed is roughly the same at all the refineries, including the new
one, the total operating cost for processing 360 million barrels should not be substantially
affected by the allocation of this work to the refineries. However, management feels that having
some flexibility for where to allocate this work might permit a substantial reduction in the cost of
shipping crude oil and finished product. Since Table 15.20 indicates that the total annual shipping
cost for crude oil and finished product would be $2.92 billion with St. Louis as the site for the
refinery, management hopes that substantial reductions can be achieved in this way.
         Figures 15.13 and 15.17 show the optimal shipping plans for crude oil and finished
product, respectively, when the new refinery is in St. Louis and has a capacity of processing 120
million barrels of crude oil per year. Management now is asking the task force to analyze the
situation under the option of increasing this capacity to 150 million barrels. In particular,
management wants the following questions addressed. Under the new option, how should the
shipping plan for crude oil in Figure 15.13 change and how much reduction in the total shipping
cost would be achieved? How should the shipping plan for finished product in Figure 15.17
change and how much reduction in the total shipping cost would be achieved? Finally, assuming
                                                                                           CD 15-69

that the differences in operating costs shown in Table 15.19 would continue to apply under the
new option, would the financial comparison of the three sites given in Table 15.20 be altered
substantially if this option were to be adopted?
        As the head of the task force, you have decided to lead the way in executing the
following steps with the new option.
a.   Formulate and solve a spreadsheet model to find an optimal plan for shipping 360 million
     barrels of crude oil per year from the oil fields to the refineries, including the new one in St.
     Louis, where the amount of crude oil each refinery will receive (up to its capacity) is based
     on minimizing the total annual cost for these shipments. (Hint: You can save some time in
     this and subsequent parts by using the live spreadsheets for the Texago case study in this
     chapter’s Excel files as a starting point.) Compare the resulting total annual cost for these
     shipments with the results obtained in Figure 15.13 under the original assumption of a
     smaller refinery in St. Louis.
b.    Assume that the plan found in part a (including its specification of how much crude oil
     each refinery will receive) will be used. On this basis, formulate and solve a spreadsheet
     model to find an optimal plan for shipping finished product from the refineries to the
     distribution centers. Compare the resulting total annual cost for these shipments with the
     results obtained in Figure 15.17. Also calculate the total annual cost of shipping both crude
     oil and finished product under this plan and compare it with the corresponding total of $2.39
     billion obtained from Table 15.20.
c.   You realize that the cost of shipping final product tends to be somewhat larger than the cost
     of shipping crude oil. Therefore, rather than having the decisions regarding the amount of
     crude oil each refinery will receive and process be dictated by minimizing the total annual
     cost of shipping crude oil (as in parts a and b), you decide to check what would happen if
     these decisions were based on minimizing the total annual cost of shipping final product
     instead. Formulate and solve a spreadsheet model to find an optimal plan for shipping final
     product from the refineries (including the new one in St. Louis) to the distribution centers,
     where the allocation of the 360 million barrels of crude oil per year to the refineries is based
     on minimizing the total annual cost for these shipments. Compare the resulting total annual
     cost for these shipments with the results obtained in part b and in Figure 15.17.
d.   Assume that the plan found in part c (including its specification regarding how much crude
     oil each refinery will receive and process) will be used. On this basis, formulate and solve a
     spreadsheet model to find an optimal plan for shipping crude oil from the oil fields to the
     refineries. Compare the resulting total annual cost for these shipments with the results
     obtained in part a and in Figure 15.13. Also calculate the total annual cost of shipping both
     crude oil and finished product under this plan and compare it with the corresponding total
     obtained in part b and in Table 15.20.
e.   You realize that, so far, you have been suboptimizing the overall problem by optimizing
     only one part of the problem at a time, so now it is time to get down to serious business.
     Formulate a single spreadsheet model that simultaneously considers the shipping of 360
     million barrels of crude oil per year from the oil fields to the refineries (including the new
     one in St. Louis) and the shipping of final product from the refineries to the distribution
     centers. Use the objective of minimizing the grand total of all these shipping costs. Since the
     refineries collectively have a capacity of processing 390 million barrels of crude oil per
     year, the decisions regarding the amount of crude oil each refinery will receive and process
     (up to each refinery’s capacity) also are to be based on this same objective. Solve the model
                                                                                        CD 15-70

     and compare the resulting total of all the shipping costs with the corresponding total
     calculated in parts b and d and in Table 15.20.
f.   Repeat part e if the new refinery (with a capacity of processing 150 million barrels of crude
     oil per year) were to be placed in Los Angeles instead of St. Louis. Then repeat it again if
     Galveston were to be selected as the site instead of St. Louis. Using the operating costs
     given in Table 15.19 for the three sites, construct a table like Table 15.20 to show the new
     financial comparison between the sites. (Although the operating costs will be larger than
     given in Table 15.19 if the new refinery processes more than 120 million barrels of crude oil
     per year, management has instructed the task force to assume that the differences in
     operating costs shown in Table 15.19 would continue to apply, so the differences in the total
     variable costs in the table being constructed would still be valid.)
g.   You now are ready to submit all your results (including your spreadsheets) to management.
     Write an accompanying memorandum that presents your recommendations.

								
To top