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
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
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.