June 1999 Spreadsheet Add-ins for OR Instruction By Paul A. Jensen This is a regular column sponsored by INFORMED, the INFORMS Forum on Education. Contact Armann Ingolfsson at armann.ingolfsson@ualberta.ca With version 5.0, Microsoft introduced a new macro language for Excel — Visual Basic for Applications. VBA is a powerful object-oriented language with which virtually any operations research technique may be programmed. This article describes a collection of programs created with VBA and stored for distribution as add-ins. When installed, an add-in increases the functionality of Excel by constructing and formatting worksheets, performing computational procedures and interacting with the student or other user. I use the add-ins in an introductory operations research modeling course, courses in manufacturing system analysis and graduate courses in mathematical programming. The Collection The programs of the collection are listed in the table below. The primary interface for this collection is the OR/MS menu on the main Excel menubar. The add-ins place one or more items under this menu. Optimization Probability Models Manufacturing Systems Mathematical Programming Linear Programming Solver Network Flow Solver Dynamic Programming Probability Queueing Markov Chain Markov Process Decision Analysis Process Flow Analysis Facility Layout MRP (Materials Requirements Planning) Say a student is given an assignment to model and solve an optimization problem. When the Math Programming add-in is installed, four different model forms are added to the OR/MS menu: Linear, Nonlinear, Network and Transportation. If the student clicks the Linear option, a dialog box is presented that allows entry of the structural parameters of a linear model. The dialog accepts a problem name, numbers of variables and constraints, number of integer variables and the solver selection. The optimization models use the Solver add-in provided with Excel or the LP and Network Flow solvers provided in the collection. Pressing the OK button initiates a macro that creates a worksheet identified by the problem name. A data form is constructed on the worksheet by the programs of the add-in. Cells are provided for variable and constraint names, linear coefficients of the objective function and constraints, and upper and lower bounds for the variable and constraint values. Shaded cells hold formulas or model data that should not be changed. With the Excel Solver option, the macro automatically constructs the Solver model. Pressing the Solve button on the worksheet initiates the solution algorithm that ultimately places the optimum solution into a row of the worksheet. A Change button allows the number of variables, number of constraints, or the integrality requirements on the variables to be easily changed with minimal disruption of the data already entered. Most of the add-ins operate in a similar manner, constructing a form ready for data entry by the student. Exceptions are the add-ins for probability and queueing that provide built-in functions that can be used anywhere on a worksheet. Using Excel for Teaching OR/MS There are many ways to use Excel to teach OR/MS models and methods. One way is to ask students to build their own spreadsheet models with the powerful capabilities that come with the program. I call this the free-form modeling approach. I used free-form modeling in my first attempt to use Excel in an OR models course, and I found several difficulties. Students have a wide range of skills in spreadsheet use, and constructing a model is much easier for some than others. I spent much of my time trying to teach Excel rather than OR models. It is often difficult to verify (or debug) a model created by a student, since models may include involved functional relationships that are difficult to trace. It is easy for a student to overlook the basic limitations of the OR/MS solution methods and create models that violate their underlying assumptions. An alternative to free-form modeling is to use templates. These are worksheets created by the teacher or author that have the data form already constructed. The Solver model for a mathematical programming model is saved with the template so the student need not invent the data form or create the Solver model. The template transfers the skill of the author in a form the industrious student can emulate or the typical student can use to solve problems. The principal problem with templates is the difficulty of adjusting the problem size. Although rows and columns can be inserted with the formulas automatically adjusted, itıs easy to mess up a template. Both free-form modeling and the template approach are limited to the built-in functionality of Excel. Although no doubt this is extensive, many of the computational procedures used in this collection simply could not be accomplished directly on an Excel worksheet. The Solver add-in is a great feature, but not every problem has a mathematical programming model. Visual Basic for Applications is the most general tool that comes in the Excel package. Complex data forms that could be transferred by a template can alternatively be created by a VBA subroutine, with the added advantage of allowing the problem size to be specified by the student. Computational procedures that are impossible to implement on a spreadsheet can be programmed, allowing the full range of OR/MS techniques to be accessed through the spreadsheet interface. The best application of Excel for OR/MS education will probably involve all of these techniques in a mix appropriate to the purpose of the course. For my OR models course where students attack a variety of problem situations involving a number of model types, the add-ins have been very successful. The entire collection of add-ins including instructions and examples are available on the web at www.me.utexas.edu/~jensen/addins. Paul A. Jensen is a professor in the Department of Mechanical Engineering, University of Texas, and head of the departmentıs Operations Research and Industrial Engineering Group. Table of Contents OR/MS Today Home Page OR/MS Today copyright İ 1999 by the Institute for Operations Research and the Management Sciences. All rights reserved. Lionheart Publishing, Inc. 2555 Cumberland Parkway, Suite 299, Atlanta, GA 30339 USA Phone: 770-431-0867 | Fax: 770-432-6969 E-mail: lpi@lionhrtpub.com URL: http://www.lionhrtpub.com Web Site İ Copyright 1999 by Lionheart Publishing, Inc. All rights reserved.