Excel Solver has some limitations for production-ready data reconciliation, despite Excel being used often for process manufacturing production and operational accounting. To overcome these limitations, Resolver is an Excel add-in using the same model configuration as Solver, but using a solver that is optimized for the data reconciliation problem. This results in a fast, robust, and very accurate solver for linear, nonlinear, equality, and inequality data reconciliation problems. The combination of Excel and robust solver could bring ubiquity to process data reconciliation.
Resolver is an Excel add-in that performs data reconciliation using a high performance, accurate, and robust algorithm optimized for the data reconciliation problem. It uses the same problem setup as the Solver Excel-add-in. In fact the problems are interchangeable. You can setup and solve a problem in Resolver and then use the same setup and problem definition to try and solve it with Solver, just in case you want verification of the results! Resolver is not limited to trivial problems: it has been tested with hundreds of variables, and hundreds of constraints, both linear and nonlinear.
Resolver came about as a result of trying to use Solver to solve process data reconciliation problems, as reported in Is Excel Solver suitable for Production Data Reconciliation?. As noted, Excel has a lot of advantages for setting up process data reconciliation problems, and is widely used for operational and production accounting. Along with some advantages, some issues were revealed which have been overcome with Resolver.
Excel Data Reconciliation Problem Setup
Resolver uses a reconciliation model defined via constraint equations expressed as Excel cell formula in the same way as Solver.
Solving with Resolver
The data reconciliation problem is defined in a very similar way to Excel Solver. In fact the model definitions are interchangeable, although Resolver can only solve for the Gaussian objective function, and Solver cannot produce the reconciliation diagnostics.
Resolving will write the results back into the variable cells, and also produces a report to help diagnose the reconciliation problems. This produces the well established gross, constraint, and measurement error diagnostics.
Advantages of Resolver-based Data Reconciliation
- The use of symbolic differentiation of the constraint equations eliminates the need for numeric differentiation and its inherent inaccuracy
- Resolver does not rely on Excel to recalculate the objective function which makes it very fast.
- The constraint-sequential solution technique ensures solution stability even when the problem is poorly formulated, such as zero tolerances, and redundant constraints.
- Covariance factorization improves numeric stability and accuracy.
- Compatible (and interchangeable) with Solver problems
- Data reconciliation can take place within the same spreadsheet environment with which many production and operational accounting systems exist.
- Constraints are defined as Excel cell formula, not strings within Excel cells that use the reconciliation application’s own syntax.
Disadvantages of Resolver-based Data Reconciliation
- There is no visual modeler or designer of the flowsheet or problem.
- Constraint formulae need to be differentiatable.
Advantages of Solver-based Data Reconciliation
- “Very easy to prepare data within Excel and to post process the results for presentation.”
- Resolver uses the same setup and problem definition. In fact they are interchangeable.
- “Solver is not limited to linear-only constraints, thus component balance (bi-linear) reconciliation problems can be handled, although they were not tested here.”
- Resolver can handle the same range of problems which includes the ‘traditional’ linear balances, non-linear balances, and inequality constraints. To achieve its accelerated and robust convergence Resolver uses symbolic differentiation of the constraint formula provided in the Excel cells and their precedents. At present not all Excel functions are supported. Additional functions will be supported in further releases of Resolver.
- “Solver is not limited to equality constraints, thus for example all estimates can be forced to be positive.”
- Resolver supports inequality constraints, as well as the convenient options that forces all variables to be positive (AssumeNonNegative)
Limitations of Solver-based Data Reconciliation
- “Solver uses a generic solver but is not optimized for the characteristics of a data reconciliation problem, such as linear or bi-linear problems, and quadratic cost functions.”
- Resolver uses an algorithm4 optimized for the data reconciliation problem that is fast, accurate and robust.
- “It is easy to define a problem that becomes unstable. In the example, adding the redundant constraint of the ‘world’ causes solution instability.”
- Resolver uses an algorithm that is very efficient and robust. It is optimized to handle some of the quirks of the process data reconciliation problem such as variables that differ significantly in numeric value, unobservable variables, and variables that we wish to ‘fix’.
- “Irrespective of objective function, Solver took 25~75 iterations to converge. This could be an issue for realistically sized (100~1000’s of variables) reconciliation problems.”
- Resolver will solve linear data reconciliation problems with equality and inequality constraints immediately. Nonlinear problems are usually solved within a few (5~15) iterations.
- “There is no access to such intermediate products such as the covariance matrix with which to perform more sophisticated gross error detection.”
- Resolver produces all of the intermediate diagnostics needed to perform gross error detection, measurement error detection, and constraint error detection. These are made available so users can superimpose their own constraint and measurement removal algorithms.