Models

Excel Models

 What is Operations Research? Excel Models and OR

Operations research is based on mathematics and uses mathematical notation to described it models. As an example, we write the general unconstrained minimization problem in mathematical terms below.

 means that the variables of the problem are expressed as a vector of n real numbers. Sometimes we show the variables as a vector . means that for every value of x the function computes a unique real number. means that we are seeking the value of x that minimizes the function.

Although not all users of Excel will be aware of this mathematical notation or the principals of optimization, many use functions on a regular basis and optimization is often the goal of spreadsheet analyses. One of the most striking features to the first time user of spreadsheets is that he or she can make the content of one cell depend on the contents of others. Simply entering into cell C1 the expression:

= A1 + B1

causes cell C1 to compute the sum of the contents of A1 and B1. Every time one of the numbers changes C1 changes immediately and automatically. This may seem trivial now, but it was amazing to the first users of spreadsheets. Neither paper and pencil, typewriters, slide rules nor the early calculators could do this. The computer program that runs the spreadsheet has computed the function z = x+y, where x is the contents of A1, y is the contents of B1 and z is the value computed in C1.

Today, of course, users of Excel and other spreadsheet programs are well versed at creating very complicated series of calculations when the contents of a cell can depend on very many other cells, some on different pages of the workbook or perhaps even on different workbooks. The built in functions of Excel expand considerably the variety and complexity of calculations that can be implemented by even the most inexperienced users of Excel. VBA, Visual Basic for Applications, the Microsoft programming language that accompanies Excel allows the creation of user defined functions that perform any imaginable operation on numbers.

Since very complex models can be created on a spreadsheet they are used in almost every field that involves some kind of calculation. This is particularly true in industry where almost everyone with a computer uses spreadsheet models, either created by the user or created by support personnel.

This is a great strength of spreadsheets, but it is also a weakness. With only a few linked expressions, the designer of a spreadsheet can lose track of the exact sequence of computations. It is often impossible to write an explicit formula describing how the contents of one cell relates to many others. It is easy to make mistakes when entering equations and it is hard to locate and correct the errors. The spreadsheet computation is like a black box. Numbers go into the box that represent the variables and parameters of a model and other numbers leave the box representing function values that are important to analyst. Although every individual calculation is immediately and accurately shown as a formula in a cell, the combination of linked calculations is hidden. It is entirely possible, that the value of a function is in error not because of a mistake in the input data, but because of a mistake in the formulas implementing the function.

This contrasts with the usual mathematical statement of an OR model. With most OR models, the functions that describe the model of a situation are closed form expressions. The relation between the decisions described by the vector x and the value of the function f(x) is clear. When the equations are arranged in a formal way it is quite likely that the function values will be correct. This is one of the strengths of the OR, but it is also a weakness. It is a strength because those who view the model can understand it, at least after some study. This allows a situation to be considered with objectivity. Persons interested in the result of a model, can review the assumptions (or abstractions) and argue whether they should be changed. The model then can be varied to see the effects of changing the model. The simplicity of some models also makes possible powerful methods that suggest the best values or optimum values of the input parameters. These methods are generally not available for arbitrarily complex models.

The clarity of the OR model is also a weakness. Clarity requires simplicity to some extent and it may be difficult to create a simple model for a complex situation. Using a specific data structure also takes away some of the creativity possible with the flexible spreadsheet medium.

So we have a conflict when applying OR to spreadsheet analysis. The spreadsheet offers freedom of expression and flexibility to almost everyone that uses a computer. There has been a hugh growth in analytical capabilities in almost every field of application. OR models are relatively simple in form and applicable to restricted sets of situations. OR methods often place restrictions on models that are not easily understood by many users. When overlooked, violations of the restrictions may invalidate results.

The authors of this site believe that the methods of OR can be of great use to spreadsheet users if the models are carefully constructed and the methods are used where they are appropriate. We have tried to provide a broad range of OR models and methods easily available to the spreadsheet user. The OR models and methods are provided through Excel add-ins programmed in VBA. In most cases a click of an item on a menu and entry of a few parameters on a dialog will cause a form to be constructed with designated input and output cells. Many of the complex formulas and algorithms of OR are hidden from the user, but most rest on proven results derived by many experts in the field of OR and explained in textbooks and journal articles. The authors have tried to make the underlying programs as accurate as possible. The models can be stand-alone applications of OR or imbedded in more complex spreadsheet analyses.

Operations Research Models and Methods
Internet
by Paul A. Jensen