Microsoft Office Tutorials and References
In Depth Information
Finally, we consider the constraints that are relevant to YRA. There is a number
of constraints that must be met, and the first relates to project availability:
X 1
25; X 2
30; X 3
47; X 4
53; X 5
16; X 6
19; X 7
36
Note that these seven constraints restrict the number of project types that are
selected to not exceed the maximum available. For example, X 1
25 insures
that the number of type 1 projects selected cannot exceed 25, while permitting
values less than or equal to 25. Although we also want to restrict variables to
non-negative values, this can be easily and universally handled with an option avail-
able in Solver— Assume Non-Negative . This condition is particularly important in
minimization problems, since values for decision variables that are negative can
contribute to the minimization of an objective function. Thus, in such a case, unless
we set the non-negative condition, the LP will attempt to make the values of decision
variables more and more negative to achieve a lower and lower Z value.
But we are not done with the constraints yet; we still have a set of constraints to
consider that relate to the consumption of resource hours. For example, there is a
maximum of 800 Res-A hours available in the quarter. Similarly, there are 900, 700,
and 375 available hours of Res-B, Res-C and Res-D, respectively. The consumption
of Res-A occurs when the various projects are selected. Thus, if we multiply each
of the decision variables by the number of hours consumed, the resulting linear
constraint relationships are:
Res-A constraint
...
. 6X 1 +9X 2 +4X 3 +4X 4 +7X 5 + 10X 6 +6X 7
800
Res-B constraint
...
. 12X 1 + 16X 2 + 10X 3 +5X 4 + 10X 5 +5X 6 +7X 7
900
Res-C constraint
...
. 0X 1 +4X 2 +4X 3 +0X 4 +8X 5 +7X 6 + 10X 7
700
Res-D constraint
...
. 5X 1 +8X 2 +5X 3 +7X 4 +4X 5 +0X 6 +3X 7
375
Let’s take a close look at the first inequality, Res-A constraint , above. The
coefficients of the decision variables represent the technology of how the maximum
available hours, 800, are consumed. Each unit of project type 1, X 1 , that is selected
results in the consumption of 6 hours of resource A; each unit of project type 2, X 2 ,
consumes 9 hours, etc. Also recall that since LP’s permit continuous variables, it is
possible that a fraction of a project will be selected. As we mentioned above, later
we will deal with the issue of continuous variables by imposing integer restrictions
on the decision variables.
Our LP is now complete. We have defined our decision variables , constructed
our objective function , and identified our constraint s. Below you can see the LP
formulation , as it is called, of the problem. This format is often used to provide the
complete structure of the LP. The problem can be read as follows: Given the decision
variables which we have defined, maximize the objective function Z , subject to the
constraint set. Next we will see how to use the Solver to select the optimal values
of the decision variables. As usual, dialogue boxes will prompt the user to input
data and designate particular cells and ranges on a spreadsheet for data input and
calculations.
Search JabSto ::




Custom Search