Spreadsheets in Chemical Engineering EducationÐA Tool in Process Design and Process Integration* EUGE Â NIO C. FERREIRA Centro de Engenharia Biolo Âgica, Universidade do Minho, Campus de Gualtar, 4710-057 Braga, Portugal. E-mail: ecferreira@deb.uminho.pt RICARDO LIMA and ROMUALDO SALCEDO Departamento de Engenharia Quõ Âmica, Universidade do Porto, Rua Dr. Roberto Frias, 4200-465 Porto, Portugal Recent developments in embedding numerical optimization procedures with linear and nonlinear solvers within a spreadsheet environment have greatly enhanced the use of these tools for teaching chemical process design and process integration. Student skills with respect to these topics are usually gained by complex and expensive modular simulators, e.g. ASPEN Plus 1 or algebraic tools such as GAMS 1 or AMPL 1 . However, modular simulators have a significant learning curve, and algebraic modeling languages are usually ignored once students commence careers. This paper demonstrates how the Solver feature of the Excel 1 spreadsheet is used for the optimization of several chemical engineering systems, including pollution prevention problems and mass-exchange networks. Three nonlinear problems are examined: the (a) recovery of benzene from a gaseous emission; (b) design of a chemical reactor network; and (c) solution of material balances in the production of vinyl chloride from ethylene. Dephenolization of aqueous wastes is presented as a linear case. The ease with which these and similar process problems can be formulated and solved within the Excel 1 environment constitutes a major step towards teaching practical optimization and design concepts for university students. INTRODUCTION UNDERGRADUATE ENGINEERING students are attracted to the powerful `what-if ' spreadsheets with optimization capabilities, such as the EXCEL 1 Solver (Microsoft Co.) and What's Best (Lindo Systems, Inc.). They require a mini- mum amount of effort in building a typical simula- tion/optimization problem, in comparison with standard high level language coding such as GAMS 1 or AMPL 1 . Undergraduate instructors are adopting Excel Solver for introducing students to solving and optimizing process design and integration [1, 2]. In addition, several engineering textbooks now include coverage of the Excel Solver [3±6]. The new edition of the classical text- book Optimization of Chemical Processes [4] dedi- cates several pages to the use of Excel Solver as an optimization tool. The book includes a new co- author, Leon Lasdon, a recognized authority in operations research optimization software and implementation of the Excel Solver [7]. Practicing engineers also use spreadsheets for many tasks, and process optimization is steadily becoming a common task in process synthesis, design and integration. Therefore, it is important to establish to what extent these tools are capable of solving optimization problems. The present authors studied recently an interest- ing problem dealing with the concepts of process synthesis including heat integration and solvent recovery [1, 2, 8]. The Solver feature of the Excel 1 spreadsheet is demonstrated for the opti- mization of several chemical engineering systems, including pollution prevention problems and mass- exchange networks in the current paper. Three nonlinear problems (the recovery of benzene from a gaseous emission; the design of a chemical reactor network; and the solution of material balances in the production of vinyl chloride from ethylene), and one linear problem (the dephenoli- zation of aqueous wastes) are examined. These case studies have been adapted for demonstration purposes in two courses run by the authors. THE EXCEL SOLVER The Microsoft Excel 1 spreadsheet was used as a development framework, coupled with the Solver add-onÐa companion of Excel since 1991 (version 3.0). The Excel Solver has two nonlinear uncon- strained optimizers, a quasi-Newton method and a reduced gradient method. These are used within a * Accepted 14 July 2004. 928 Int. J. Engng Ed. Vol. 20, No. 6, pp. 928±938, 2004 0949-149X/91 $3.00+0.00 Printed in Great Britain. # 2004 TEMPUS Publications.