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 magenta. This color indicates that
the student is to enter Excel formulas in these cells. For
example, the equation in I13 is to be the square of the value
of P1. That value is in I8, so the formula in I13 is.
=I8^2
This describes the nonlinear effect of the variable in P1
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. Initially all nonlinear terms are
set to zero. Both the nonlinear expressions in row 13 and the
nonlinear coefficients in row 14 must be nonzero for the nonlinear
terms to affect the objective function.
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. |