Constructing the Worksheet Solving the Problem

 Nonlinear Programming - Constructing the Worksheet
 To model this problem with the Mathematical Programming Add-in, select Nonlinear from the OR_MM menu. The Nonlinear Model Dialog presents the same options as the Linear Model Dialog except that it also asks for the number of extra columns. Although not necessary for this problem, we enter 1 as the number of extra columns to illustrate the effect. The remainder of the options on this dialog should be clear from the discussion of the Linear Model Dialog. There are no solver options because only the Excel Solver can handle nonlinear models. Although the Excel Solver does solve problems with integer variables, great care must be exercised when interpretting these answers. The Solver may deliver non-optimal results for integer-nonlinear problems. The worksheet obtained after data entry and solution is below. The solution was found using the Excel 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 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.

Operations Research Models and Methods
Internet
by Paul A. Jensen