Nonlogistics Network Models

1
Example 5.5

• Non-logistics Network Models

2
Background Information

• The city of Spring View is taking bids from six
  companies on the right routes that must be driven
  in the surrounding school district.
• Each company enter a bid on how much it will
  charge to drive selected routes, although not all
  companies bid on all routes.
• The data appears in the table on the next slide.

3
Background Information -- continued

• Blank cells indicates routes on which a company
  does not bid.

4
Background Information -- continued

• The city needs to select which companies to
  assign to which routes with the specifications
  that
• If a company does not bid on a route, it cannot
  be assigned to that route.
• Exactly one company must be assigned to each
  route.
• A company can be assigned to at most two routes.
• The objective is to minimize the total cost of
  covering all routes.

5
Solution

• We formulate this model in the network way.
• You can imagine nodes for the bus companies on
  the left, nodes for the routes on the right, and
  all arrows going from left to right.
• All flows are 0 or 1 a company is either
  assigned to a route or it isnt.
• The constraint that a bus company can be assigned
  to at most two routes is handled by constraining
  the outflow from any company node to be at most
  2.
• To ensure that each route is covered by some
  company, we constrain the inflow to each route
  node to be at least 1.

6
BUSROUTES.XLS

• The completed model and corresponding Solver
  dialog box are shown on the next slides.
• This file can be used to create the model.
• Because this model is so similar to the MidWest
  Electric transportation model, we will not repeat
  all of the details here.

7
(No Transcript)
8
(No Transcript)
9
Developing the Model

• The key steps are as follows
• Arc lists. The list of arcs (company route
  pairs) in rows 16-46 correspond to nonblank cells
  only in the CostMatrix range. There is no point
  in including arcs that are infeasible.
• Costs and flows. The costs in the Costs range are
  found with the usual method. The formula in cell
  C16 is INDEX(CostMatrix,A16,B16) which is then
  copied down column C. Then enter any values in
  the Flows range.
• Inflows and Outflows. Because no arcs point into
  company nodes and no arcs point out of route
  nodes, we require only outflows for company nodes
  and inflows for route nodes. To calculate these,
  enter the formulas SUMIF(Origins,H16,Flows) and
  SUMIF(Destinations,H24,Flows) in cells I16 and
  I24, and copy them down their respective ranges.

10
Developing the Model -- continued

• Total cost. Calculate the total cost to the city
  in the TotalCost cell with the formula
  SUMPRODUCT(Costs,Flows).

11
Solution -- continued

• The optimal solution shown on the next slide
  indicates the following assignments company 1
  covers route 3, company 2 covers routes 6 and 7,
  company 3 covers route 2, company 5 covers routes
  4 and 8, and company 6 covers routes 1 and 5.
• The total cost to the city of this assignment is
  40,300.
• After all, there is no constraint that every
  company must be assigned to at least one route,
  and company 4 is evidently underbid by at least
  one company for all routes.

12
Solution -- continued

• If we wanted to require that all companies be
  assigned to at least one route, we would simply
  put a lower bound of 1 on all of the outflows
  from the company nodes (in rows 16-21).
• Of course, this would increase the total cost to
  the city.

13
Sensitivity Analysis

• One interesting sensitivity analysis is to see
  what effect the maximum routes upper bound
  constraints has on the total cost.
• Presumably, if we allow more routes per bus
  company (assuming this is physically possible for
  the companies), the companies who tends to bid
  lowest will be assigned to the bulk of the
  routes, and the total cost will decrease.
• The analysis itself is straightforward, with no
  modifications of the model necessary.

14
Sensitivity Analysis -- continued

• We specify the MaxRoutes cell as the single input
  cell, allow it to vary from 1 to 7, say, in
  increments of 1, and keep track of total cost.
• The resulting output appears here.

15
Sensitivity Analysis -- continued

• We see first that if each company can be assigned
  to only one route, there is no feasible solution.
  But this is clear, there are eight routes to
  cover and only six companies!
• For larger values of the MaxRoutes value, the
  total cost begins to decrease, but only until
  MaxRoutes reaches 3.
• From that point, the city achieves no added
  flexibility by allowing companies to travel more
  routes.

16
Sensitivity Analysis -- continued

• Evidently, there is no single company or pair of
  companies who are consistently underbidding all
  others.
• The following model is considerably more
  challenging, but we believe it is also much more
  interesting.
• As with many problems that can be formulated as
  assignment models, the trick here is to recognize
  that it indeed involves appropriate assignments.