Operations Research Models and Methods / Computation / Mathematical Programming /
Transportation Model

Constructing the Worksheet

Selecting Transportation from the OR_MS menu presents the Transportation Model Dialog.

The data shown on the dialog constructs a model with the number of suppliers and demanders specified by the example. The dialog provides an opportunity to select the worksheet name, numbers of suppliers and demanders and direction of optimization. The check boxes allow several nonstandard features of the model that are illustrated later. The worksheet for the example problem after parameters have been entered and after solution by the Solver program is shown below.

The Add-in provides ranges for names of suppliers (C9:C11) and Demanders (D8:H8). The table contained in the range D9:H11 holds the transportation flows. These are variables of the problem and will be determined by the Solver. The row labeled Received (D15:H15) contains a sum formula that computes the total flows received by the demanders. These numbers are used in the constraints and the formulas and should not be disturbed. Similarly the column labeled Shipped (L9:L12), contains the total flows shipped by the suppliers and the formulas should not be changed.

The rows labeled Min Received, Max Received and Revenue hold parameters associated with the demanders. The first two rows place lower and upper bounds on the amounts received by each demander. Since in the present case these numbers are the same, the model is forcing all demands to be met. The row labeled Revenue specifies the unit revenue for each demander. This number is irrelevant for the example, and we have set the row to zero. The Columns labeled Min Ship, Max Ship and Unit Cost hold parameters for the suppliers. We have specified the maximum shipments as the amounts available at each supplier. We have not constrained the minimum shipments. The unit cost column is provided for cases in which different suppliers may have different costs for the products.

The shipping costs are given in the second table of the model in the range D19:H21. The names heading this table are provided by formulas. Any names defined for the flow table will be automatically transferred to this and other tables.

Problems may be solved with either the Jensen Network Solver or the Excel Solver. Sensitivity analysis is only provided by the Excel Solver.

Model Variations

Updated 1/16/01
Operations Research Models and Methods

by Paul A. Jensen and Jon Bard, University of Texas, Copyright by the Authors