Return to Index
Operations Research Models and Methods
 
Computation Section
Subunit Linear/Integer Programming
- Constructing the Worksheet

Pressing OK initiates the Macro that constructs the worksheet below. Generally the outlined cells may be changed on the worksheet to enter data describing the model. Cells colored yellow should not be changed. They hold formulae and data required by the program. Cells outside the region defining the model may be used for any purpose. The cells defining the variable values, H8:L8, may be set by the user to experiment with various solutions, however, the Solver Add-in replaces the contents of this range with the optimum solution. Generally, green cells hold numbers filled in by algorithms implemented with VBA subroutines.

The range A2:A7 holds the Excel Solver model. That range is left blank when the Jensen Solver is used. When using the Excel Solver it is important that the user establish contact with that program before a model is created. To do this, select the Solver item from the Tools menu. After the Solver dialog opens, simply close the dialog. This establishes the link to the Excel Solver. The Math-Programming add-in automatically loads and calls the Solver as necessary.
  The user adds data describing the coefficients for his or her problem. Variable names are placed in row 7, the objective coefficients are in row 12, and the lower and upper bounds for variables in rows 9 and 10. The upper bounds for the constraints are placed in column F starting at row 15. Constraint relations are in column E. Initially the relations are <=, however, they may changed to >= or = by placing the cursor on a cell and clicking on the Change Relation button. Several relations can be changed simultaneously by selecting a range of cells before clicking the button. The constraint coefficients start in cell H15 and continue downward and to the right. After entering the data for the example problem the worksheet appears as below.
 

The worksheet shows only the coefficients that describe the objective and constraints of the model. The algebraic model is below.
 

For the example only the relations on the right of the algebraic constraints are relevant, so we used single bound constraints. For some problems it might be more convenient to specify two bounds for each constraint. In that case the constraint lower bounds would have been 0.

The large numbers used for the upper bounds on the variables indicate that there are no effective upper limits to the variables. Constraint and variable bounds may be either positive or negative.

 


  
Return to Top
tree roots

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