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 ﬁrst 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 ﬁrst inequality,
Res-A constraint
, above. The

coefﬁcients
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 deﬁned our
decision variables
, constructed

our
objective function
, and identiﬁed 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 deﬁned, 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