Page 358 -
P. 358
338 CHAPTER 7 TRANSPORTATION, ASSIGNMENT AND TRANSSHIPMENT PROBLEMS
Excel Solution of Transportation, Assignment
Appendix 7.1
and Transshipment Problems
In this appendix we show how Excel Solver can be used to solve transportation,
assignment and transshipment problems. We start with the Foster Electronics trans-
portation problem (see Section 7.1).
Transportation Problem
The first step is to enter the data for the transportation costs, the origin supplies
and the destination demands in the top portion of the worksheet. Then the linear
programming model is developed in the bottom portion of the worksheet. As with
all linear programmes the worksheet model has four key elements: the decision
variables, the objective function, the constraint left-hand sides and the constraint
right-hand sides. For a transportation problem, the decision variables are the
amounts shipped from each origin to each destination; the objective function is
the total transportation cost; the left-hand sides are the number of units shipped
from each origin and the number of units shipped into each destination; and the
right-hand sides are the origin supplies and the destination demands.
The formulation and solution of the Foster Electronics problem are shown in
Figure 7.14. The data are in the top portion of the worksheet. The model appears in
the bottom portion of the worksheet; the key elements are screened.
Formulation
The data and descriptive labels are contained in cells A1:F8. The transportation
costs are in cells B5:E7. The origin supplies are in cells F5:F7, and the destination
demands are in cells B8:E8. The key elements of the model required by the Excel
Figure 7.14 Excel Solution of the Foster Electronics Problem
EXCEL file
FOSTER
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.
