Microsoft Office Tutorials and References

In Depth Information

9.3.2 Formulation of YRA Problem

Maximize:

Z

=

45,000
X
1
+

63,000
X
2
+

27,500
X
3
+

19,500
X
4
+

71,000
X
5
+

56,000
X
6
+

48,500
X
7

Subject to:

X
1
≤

25;
X
2
≤

30;
X
3
≤

47;
X
4
≤

53;
X
5
≤

16;
X
6
≤

19;
X
7
≤

36

6
X
1
+

9
X
2
+

4
X
3
+

4
X
4
+

7
X
5
+

10
X
6
+

6
X
7
≤

800

12
X
1
+

16
X
2
+

10
X
3
+

5
X
4
+

10
X
5
+

5
X
6
+

7
X
7
≤

900

0
X
1
+

4
X
2
+

4
X
3
+

0
X
4
+

8
X
5
+

7
X
6
+

10
X
7
≤

900

5
X
1
+

8
X
2
+

5
X
3
+

7
X
4
+

4
X
5
+

0
X
6
+

3
X
7
≤

375

All
X
i
, where
i

=

1 to 7, are non-negative
.

9.3.3 Preparing a Solver Worksheet

Let us begin the process of using Solver to solve our LP. First, we create a worksheet

that enables Solver to perform its analysis. Exhibit 9.2 appears to be quite similar to

Exhibit 9.1 in terms of the data contained, but there are some important differences.

The differences are essential to the calculations that will be performed, and the

worksheet must be prepared in a manner that Solver understands.

There are two areas in the worksheet that must be
set
in order to execute Solver:

a
target cell
and
changing cells
.The
target cell
is the cell in which we provide

Solver with algebraic instructions on how to calculate the value of the objective

function. This is the value of
Z
in our formulation, and as you recall, it is the sum

of the number of projects of each type multiplied by the revenue that each project

returns (coefﬁcients). In Exhibit 9.2 it is cell E10, and it is the summation of E2

through E8, the individual contributions by each project type. The values shown in

the
target
and
changing
cells are calculated by Solver and will not contain these

values until you have executed Solver. Once a solution is obtained, you can perform

sensitivity analysis by changing the parameters currently in the problem. The values

in the
changing cells
and the
target cell
will be recalculated after the next execution

of Solver.

Although we have yet to perform the procedure, a solution is shown in Exhibit

9.2, and we can see that Solver uses the
changing cells
as the cell locations for

storing the values of the decision variables, in this case the optimal. Similarly, the

optimal value of
Z
is stored in the
target cell
. This is quite convenient and makes

reading the solution simple.

Note below that Solver has produced a solution that selected non-zero, positive

values for project types 1, 2, 4, 5, 6, and 7, and a value of 0 for project type 3:

X
1
=

5.3;
X
2
=

19.7;
X
3
=

0;
X
4
=

2.6;
X
5
=

16.0;
X
6
=

19.0;
X
7
=

36.0

Search JabSto ::

Custom Search