Operations Research Models and Methods / Computation / Mathematical Programming /
Nonlinear/Integer Programming

Constructing the Worksheet


Pressing OK on the Linear Model Dialog calls the Macro that constructs the worksheet below. Cells colored yellow contain formulas or data required by the add-in and should not be changed by the student. In this case these include: the cells holding the problem name and type, F1:F2; the cells defining the goal and objective value, F3:F4; the range defining the constraint values, cells D15:D18; the range defining the Excel Solver model, A2:A7; and the range providing solver instructions, K1:K3. Most of these features can be changed using the Change button. Depending on the problem, the model range will have different numbers of cells, but the range always starts in cell A2. When the Jensen Solver has been selected, the model range does not appear.

The cells colored green, H8:L8, hold the values for the decision variables. They may be set by the student to experiment with various solutions, however, the solver add-ins replace the contents of this range with the optimum solution. Below the variable values we see ranges holding Lower and Upper bounds for the variable values. The ranges will not appear when the associated checkboxes on the LP dialog are not checked. When the lower bound is absent, the assumed lower bound on variables is zero. When the upper bound range is absent the variable values are unbounded from above. Above the variable values appear the variable indices and names. The indices are used to identify when variables are integer, although they are not integer in the example. The names shown here are the default values. Variable names should be assigned to relate to the problem under consideration.

The range H12:L12 holds the linear objective coefficients for the model. Since the model is linear these numerical coefficients are sufficient to determine the objective function value. Cell F4 holds an Excel equation that multiplies the vector of variable values with the vector of linear coefficients.

The constraint information is placed in rows 15 and below. Column B holds indices. Column C holds constraint names. The default values shown here should be replaced with names more representative of the problem. Column D holds constraint values. For the linear model a constraint value is computed by multiplying the linear coefficients for that constraint by the variable values in row 8. Excel equations that compute the values are located in column D and colored yellow to indicate that they should not be changed.

Column E shows the constraint relationship and is only used when the constraint limit option is specified as 1, as for this example. We illustrate the case for the limit of 2 later. The relationship must take on one of three possibilities (<=, >=, or =). The default value is as indicated above, but may be changed by typing in an alternative. The Change Relation button provides an easy method for changing cells in this range. Select one or more cells in the Relationship range and click this button. The cells in the selected range cycle through the three possibilities, thus eliminating the need to type the values. The RHS (right hand side) range in column F places a limit on the value of each constraint that must be satisfied for feasibility.

Starting in column H and in rows 15 and greater we place the coefficients of the linear constraints. For the linear model the values of the constraints are sufficient to determine the constraint values for given variable values.



Adding the Data

Updated 1/16/01
Operations Research Models and Methods

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