Can spreadsheet solvers solve demanding optimization problems?

Abstract

Practicing engineers resort to modular simulators or to algebraic tools such as GAMS or AMPL for performing complex process optimizations. These tools, however, have a significant learning curve unless they have been introduced at the undergraduate level beforehand. In this work we show how the Solver feature of the Excel spreadsheet can be used for the optimization of a fairly complex system, i.e., a classic solvent extraction/pollution prevention with heat integration process. The specific goal was the design optimization for continuous recovery of organic solvents (VOCs) using a gas absorption tower with solvent recovery in a stripper. It is shown that the Solver feature of the Excel spreadsheet can be used to converge on local optima for these complex systems, provided proper care is taken in the solution procedure. The complexities of optimization can also be demonstrated with this tool, as can several common pitfalls encountered during optimization.