Return to Index
Operations Research Models and Methods
Computation Section
Subunit Transportation Model
 - Constructing the Worksheet

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

With the data from the dialog, the add-in 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 checkboxes 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 expressions should not be disturbed. Similarly the column labeled Shipped (L9:L12), contains the total flows shipped by the suppliers and the expressions should not be disturbed.

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 formulae. 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. Clicking the Solve button initiates the solution algorithm. The optimum flows are placed in the flow array.


Return to Top

tree roots

Operations Research Models and Methods
by Paul A. Jensen
Copyright 2004 - All rights reserved

Next Page