Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your model.


 

Premium Solver Platform - Diagnosing Models with Poor Scaling

Diagnosing Models with Poor Scaling

Problems with Poorly Scaled Models

Problems due to poor scaling in a large, complex optimization model can be difficult to identify and resolve. It can cause Solver engines to return messages such as "Solver could not find a feasible solution" or "Solver could not improve the current solution," with results that are suboptimal or otherwise very different from your expectations.

For example, suppose you decide to make a simple change in your portfolio optimization model: Instead of using percentages for the stock allocations, you'd rather see the actual dollars to be invested, in your $1 billion institutional portfolio. So you change the constraint TotalPortfolio = 1 (or 100%) to be TotalPortfolio = 1000000000.

When you click Solve, you are surprised to find that the Solver reports it cannot find a feasible solution (click the worksheet to see it full size):

Model with Scaling Problem (66279 bytes)

You get this result whether or not you check the Use Automatic Scaling box in the LP/Quadratic Solver Options dialog. 

You've read about the effects of poor scaling, but how do you find the poorly scaled formulas In a large, complex optimization model?  The Polymorphic Spreadsheet Interpreter in the Premium Solver Platform can help -- just select the Scaling Report in the Solver Results dialog above, to obtain a report like the one below, inserted as a new worksheet in your workbook:

The report indicates that there are scaling problems with the formulas at H6, I14 and I16. You can click on the cell references to jump to the actual cells containing these formulas.  You see that the calculation of the Portfolio Variance involves adding a very small value and a very large one (the Stock 5 variance times 1 billion squared) which leads to a loss of significant digits.

Helping you find scaling problems in a large model is just another way that the Premium Solver Platform can save you hours of time, and enable you to find better solutions.

< Back to Premium Solver Platform Product Overview