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 (coefficients). 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