A poorly scaled model is one that computes values of the objective, constraints, or intermediate results that differ by several orders of magnitude. A classic example is a financial model that computes a currency amount in millions or billions and a return or risk measure in fractions of a percent. Because of the finite precision of computer arithmetic, when these values of very different magnitudes (or others derived from them) are added, subtracted, or compared – in the user’s model or in Solver’s own calculations – the result will be accurate to only a few significant digits. After many such steps, Solver may detect or suffer from “numerical instability.”
The effects of poor scaling in a large, complex optimization model can be among the most difficult problems to identify and resolve. It can cause Solver to return messages such as “Solver could not find a feasible solution,” “Solver could not improve the current solution,” or even “The linearity conditions required by this LP Solver are not satisfied,” with results that are suboptimal or otherwise very different from your expectations. The effects may not be apparent to you, given the initial values of the variables, but when Solver explores Trial Solutions with very large or small values for the variables, the effects will be greatly magnified.
Dealing with Poor Scaling
The Solver Options dialog includes a Use Automatic Scaling check box. When this box is selected (it is selected by default), Solver rescales the values of the objective and constraint functions internally in order to minimize the effects of poor scaling. But this can only help with Solver’s own calculations – it cannot help with poorly scaled results that arise in the middle of your Excel formulas. Further, in some poorly scaled models, automatic scaling can make things worse rather than better – so you may wish to try clearing this check box and re-solving.
The best way to avoid scaling problems is to carefully choose the “units” implicitly used in your model so that all computed results are within a few orders of magnitude of each other. For example, instead of expressing currency amounts in single units, you could express them in units of (say) millions, if appropriate for your model, so the actual numbers computed on your worksheet may range from perhaps 1 to 1,000.