Page 361 -

EXCEL SOLUTION OF TRANSPORTATION, ASSIGNMENT AND TRANSSHIPMENT PROBLEMS 341

Left-Hand Sides Cells E16:E18 contain the left-hand sides of the constraints
for the number of clients each project leader can handle.
Cells B19:D19 contain the left-hand sides of the
constraints requiring that each client must be assigned a
project leader.
Cell E16 ¼ SUM(B16:D16) (Copy to E17:E18)
Cell B19 ¼ SUM(B16:B18) (Copy to C19:D19)
Right-Hand Sides Cells G16:G18 contain the right-hand sides for the project
leader constraints and cells B21:D21 contain the right-hand
sides for the client constraints. All right-hand side cell
values are 1.

Excel Solution
The solution shown in Figure 7.16 can be obtained by selecting Solver from the
Tools menu, entering the proper values into the Solver Parameters dialogue box,
selecting Standard Simplex LP and specifying the option Assume Non-Negative.
Then click Solve. The information entered into the Solver Parameters dialogue box
is shown in Figure 7.17.

Transshipment Problem
The worksheet model we present for the transshipment problem can be used for all
the network flow problems (transportation, assignment and transshipment) in this
chapter. We organize the worksheet into two sections: an arc section and a node
section. Let us illustrate by showing the worksheet formulation and solution of the
Ryan transshipment problem (see Section 7.5). Refer to Figure 7.18 as we describe
the steps involved. The key elements are screened.

Formulation

The arc section uses cells A3:D16. For each arc, the start node and end node are
identified in cells A5:B16. The arc costs are identified in cells C5:C16, and cells
D5:D16 are reserved for the values of the decision variables (the amount shipped
over the arcs).

Figure 7.17 Solver Parameters Dialogue Box For The Fowle Marketing Research Problem

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.