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

Constructing the Worksheet

The worksheet obtained after data entry and solution is below. The solution was found using the Solver program.

As for linear models, the linear objective coefficient row (row 12) models the linear terms of the objective. These coefficients are multiplied by the vector of variable values by the Excel formula in cell I2. The cell is labeled Linear to indicate that this value is the linear part of the objective function.

The worksheet has two additional rows in the portion of the model describing the variables, one row for nonlinear objective coefficients and the other for nonlinear terms. The space for nonlinear objective coefficients in row 14 is useful for nonlinear terms that have only a single coefficient. In this case it holds the values of -d. The equations for the quadratic terms are entered into row 13. We color the cells in this row from column H through M pink. This color indicates that the student is to enter Excel formulas in these cells. The program automatically enters the square of the variable value in cells I through M. For example, if we look at cell J13 we find:

=J8^2

This describes the nonlinear effect of the variable in J8 on profit. The nonlinear coefficients multiply the nonlinear terms and are summed to obtain the entry called the Nonlinear 1 component of the objective function. The formula for this contribution is in cell I3. In general, row 13 may hold any nonlinear, differentiable functions of the variable values. It is important that the functions be well defined for all values of the variables. We provide the default squared function because quadratic separable problems are common. They have no effect on the objective function if the nonlinear coefficients are all zero.

A column (H) has been created to hold other nonlinear terms that may appear in the objective function and constraints. Although not used for this example, these cells can hold any continuous, differentiable functions of the decision variables. Where problems have several identifiable sources of nonlinearities, it may be convenient to have more than one column for nonlinear terms. These nonlinear terms are summed and are shown as the Nonlinear 2 component of the objective function in cell I4. The Linear, Nonlinear 1 and Nonlinear 2 values are summed in cell F4 to obtain the objective function. This quantity is optimized by the Solver.

Nonlinear terms in the constraints can be entered as explicit functions of the decision variable in the range H17:H20. The nonlinear function values are accumulated with the linear constraint functions in the Value column of the constraints.

The sensitivity analysis provided by the Excel Solver for nonlinear programming problems has a slightly different format than for linear programming problems as illustrated below.

Solving the Problem

Updated 1/16/01
Operations Research Models and Methods

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