Page 359 -

EXCEL SOLUTION OF TRANSPORTATION, ASSIGNMENT AND TRANSSHIPMENT PROBLEMS 339

Solver are the decision variables, the objective function, the constraint left-hand
sides and the constraint right-hand sides. These cells are screened in the bottom
portion of the worksheet.
Decision Variables Cells B17:E19 are reserved for the decision variables. The
optimal values are shown to be x 11 ¼ 3500, x 12 ¼ 1500,
x 22 ¼ 2500, x 23 ¼ 2000, x 24 ¼ 1500 and x 41 ¼ 2500. All
other decision variables equal zero indicating nothing will
be shipped over the corresponding routes.
Objective Function The formula ¼ SUMPRODUCT(B5:E7,B17:E19) has been
placed into cell C13 to compute the cost of the solution. The
minimum cost solution is shown to have a value of E39 500.
Left-Hand Sides Cells F17:F19 contain the left-hand sides for the supply
constraints, and cells B20:E20 contain the left-hand sides
for the demand constraints.
Cell F17 ¼ SUM(B17:E17) (Copy to F18:F19)
Cell B20 ¼ SUM(B17:B19) (Copy to C20:E20)
Right-Hand Sides Cells H17:H19 contain the right-hand sides for the supply
constraints, and cells B22:E22 contain the right-hand sides
for the demand constraints.
Cell H17 ¼ F5 (Copy to H18:H19)
Cell B22 ¼ B8 (Copy to C22:E22)

Excel Solution
The solution shown in Figure 7.14 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.15.

Assignment Problem
The first step is to enter the data for the assignment costs in the top portion of the
worksheet. Even though the assignment model is a special case of the transportation
model, it is not necessary to enter values for origin supplies and destination demands
because they are always equal to one.

Figure 7.15 Solver Parameters Dialogue Box for the Foster Electronics 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.