Assume Linear Model Option

In the built-in Microsoft Excel Solver, this check box is used to select the Solver "engine" to be used to solve the problem. If the box is checked, the Simplex method solver is used, and the model must be a linear programming problem. If the box is not checked, the standard GRG nonlinear solver is used. As discussed in Algorithmic Methods Used by the Solver, you can use the GRG solver for linear programming models, but it will be slower and more susceptible to problems such as degeneracy and scaling, and it will produce only dual values (but not range information) on the Sensitivity Report. (For more information on sensitivity analysis, see Understanding the Solver Reports.)

In the standard Microsoft Excel Solver, when you choose the Assume Linear Model option, the Solver initially assumes that the objective and constraints in the model are linear functions of the variables (see Linear and Nonlinear Functions), and proceeds to compute the coefficients of these functions through "finite differencing" (see Estimates, Derivatives and Search for details). When it finds an optimal solution, the Solver performs a test in which the values of the objective function and the constraints, as determined by its internal model, are compared to the values calculated by the spreadsheet when the decision variable values are "plugged in." If the final values determined in both ways do not agree to within the Precision setting, an alert like the one below is displayed:

Problems with Poorly Scaled Models

Because the standard Microsoft Excel Solver does not automatically re-scale the objective function and constraints (even when you choose the Use Automatic Scaling option), a model which is completely linear but which is poorly scaled may cause sufficient roundoff error to make the Solver's internal model inaccurate, compared to the values computed when the decision variable values are "plugged in" to the worksheet. The test for linearity referred to above may very well detect this inaccuracy and produce the error message in the dialog box above. The solution to this problem is to re-scale the model so that the typical values of the objective function and constraints do not differ from each other, or from the values of the decision variables, by more than three or four orders of magnitude. As a short-term fix, you may also "loosen" the Precision setting, as described in Precision and Tolerance.

All of Frontline's enhanced solvers include both the ability to automatically re-scale linear models internally, and a more robust test for linearity, part of which is performed at the beginning of the solution process, rather than at the end. These methods will eliminate almost all cases where a poorly scaled linear model could yield the error message above. Since no automatic scaling method will work in all situations, we recommend that you take steps to ensure that the model on your worksheet is reasonably well scaled -- even if you do take advantage of the Use Automatic Scaling option.

Problems with Discontinuous Functions

If you try to solve a model containing certain discontinuous functions (which are neither linear nor smooth nonlinear) with the Assume Linear Model box checked, it is possible -- though unlikely -- that the linearity test described above will not detect the discontinuities and will proceed to try to solve the problem. This probably means that the functions were linear over the range considered by the linearity test. You can avoid this problem with proper model design (since any linear function can be written using the SUM or SUMPRODUCT function), and by using the Microsoft Excel Find operation to check for any occurrences of such functions.

Copyright © 1999 Frontline Systems Inc.
Last modified: June 24, 2004.