Microsoft Office Tutorials and References
In Depth Information
discussion, our models have become ever more prescriptive in nature as we have
progressed through our early chapters. Before we explore Solver, we begin with a
discussion of constrained optimization.
9.2 Solver—Constrained Optimization
Constrained optimization is a subﬁeld of numerical optimization, with the critical
distinction arising from the term constrained . Without a doubt, the most commonly
used type of constrained optimization is Linear Programming (LP). There are
an abundance of software packages to provide solutions to LPs, including Solver.
Additionally, there are numerous important applications of LP ranging from the
distribution of electrical power, to scheduling, to menu planning. Some problems
that can be formulated as LPs occur with such frequency, that they have received
their own special designations; for example, the blending problem, the knapsack
problem, the nurse or shift scheduling problem, and the capital budgeting problem.
These are just a few of the generic formulations that have been developed over time
as standard problem structures.
Linear programming, as a branch of constrained optimization, dates back to the
1940s. Over the years, as new innovations in LP techniques, computer program-
ming, and digital computers have developed, the applications of LP have grown
dramatically. Problems of such immense computational difﬁculty that a timely solu-
tion was once unimaginable can now be solved in fractions of a second by personal
computers. Couple this with the availability of Solver , or other advanced Solvers
that are add-ins to Excel, and now an ordinary personal computer user has a tool
that was once the domain only of ﬁrms that could afford costly LP software and the
experts to conduct LP analysis.
Yet, the adage that “a little knowledge is a dangerous thing” certainly applies to
LP. Before we cavalierly rush into formulating LPs, we need to spend some time
considering the structure of an LP. We could also invest time in understanding the
underlying algorithms that provide a computational platform for solving LPs, but
that is a very sophisticated topic and it will not be our focus. We will leave that for
a text on the mathematical techniques of LP. Our focus will on formulating LPs;
that is, structuring the important elements of a problem and then applying Solver to
obtain a solution. Additionally, we will apply a special form of sensitivity analysis
which is unique to LP solutions.
As we have discussed earlier, LPs consist of three major components:
1. Decision Variables —the continuous, non-negative variables that are selected to
minimize or maximize an objective function which is subject to constraints. As
continuous variables, they can take on fractional values. The objective function
and constraints are comprised of linear , algebraic combinations of the decision
variables. Thus, no powers, other than 1, of the decision variables (no values like
X 2 ,X 1 / 2 , etc) are allowed and only constants can be used as multipliers.