Microsoft Office Tutorials and References
In Depth Information
The value of LP is not just the determination of a set of decision variables that
optimize an objective function. As important as the optional solution, are the uses of
the sensitivity analysis associated with the solution. We have seen the power of the
shadow price , reduced cost , and allowable changes of coefficients and RHS’s in our
YRA example. Since LP is a deterministic procedure (all parameters are point esti-
mates), sensitivity analysis permits the consideration of ranges of parameter values,
even though we have represented the ranges with point estimates. Thus, the reality
of the uncertainty and variation associated with parameters can be recognized and
Solver is not restricted to the solution of linear programs. A related class of
problems is known as Non-Linear Programs (NLP) that, broadly defined, con-
tain non-linear relationships in the objective function and/or in constraints. These
can be very difficult problems to solve. Additionally, there are Integer Programs
(IP) where the decision variables are restricted to integer values, Mixed Integer
Programs (MIP) where decision variables can be both continuous (fractional value)
and integer values, and 0–1 Integer Programs where variables are binary (having
two states). Again, these conditions require substantially more complicated solution
algorithms than LP.
Earlier, we ignored the integer nature of the decision variables by assuming that
we could simply round the variables and not worry about the potential violation of
constraints, as long as they were not severe. What if the integer condition is impor-
tant? We can impose integer values on the decision variables by adding constraints
to the problem. Exhibit 9.12 demonstrates how to use the Add Constraint dialogue
box to declare the number of project type 1to be restricted to integer. By selecting the
int designation in the pull-down menu between the Cell Reference and Constraint ,
we can convert the decision variables to integers. The Add Constraint dialogue box
shows the integer constraint for cell D2, project type 1. Exhibit 9.13 shows the new
solution (after imposing integer conditions on all decision variables) where we can
clearly see that all variables result in integer values, and the value of the associated
objective function is $5,477,500.00, not a great deal below the continuous variable
solution. The projects selected are:
X 1 =
7.0; X 2 =
19.0; X 3 =
0.0; X 4 =
1.0; X 5 =
16.0; X 6 =
19.0; X 7 =
Note though, that the Sensitivity Report for integer and binary programs are no
longer valid due to the algorithm used to solve these problems. This of course
is unfortunate and a weakness of the approach since we will no longer have the
sensitivity analysis available to answer questions. Now, let us move on to another
applications tool—Scenarios.
9.4 Scenarios
The Scenarios tool is one of the what-if analysis tools in Excel. It also has been
incorporated into Solver as a button on the Solver Results dialogue box. The basic
function of Scenarios is to simplify the process of management, record keeping, and
Search JabSto ::

Custom Search