Max 5000E + 4000F ST. 10E + 15F <=150 20E + 10F <=160 30E + 10F >=135 E - 3F <=0 E + F >=5 E, F>=0
2. Defining the problem for the Solver.
You must define the problem for the solver by identifying
three things: 1) The range of cells containing the problem
variables is referred to as adjustable cells.
(In the example this is the range B1 and C1). 2)
Constraint cells: the cells containing all the
constraints of the problem including non-negativity.(In the
example this the range A5 through A11). 3) Optimal cell:
The cell containing the objective function. (In the
example this is cell A3).
To define the problem, first select Range
, then Analyze, and finally
Solver from the menu. This will bring the solver
definition dialog box. Point to the adjustable
cells, constraint range and the
optimal cell. At this point you have defined the model
for the solver.
3. Solving the problem.
Solver generates a number of solutions on the way to the
optimal solution. This means that it actually generates more
solutions than the optimal one (In solver each of these solutions
is referred to as an answer, solutions that do
not satisfy all the constraints are referred to as
attempts ). You can control the number of answers (or
attempts) by specifying a number in the dialog box. Check the
optimal box, select the max or min
solution and click on solve. Optimal
values will appear in the adjustable cells and solver
answer dialog box also appears. This dialog box has
three radio buttons: definition, answer and
reports. The answer button is selected. At
this point you can cycle through all the answers (and attempts).
By choosing definition, you can change the problem definition.
Reports allow you to generate detailed reports of the results.
4. Reports
In addition to the optimal answer in the worksheet itself
Solver provides additional output on the current problem these
are provided as Lotus 123 worksheets for viewing, saving or
printing. From the solver Answer dialog box choose reports radio
button. In the selection list there are three reports. These are
answer table, how solved and what-if
limits. You can choose each of these and then push
table button to get a table of results as a
worksheet that you can save or print using the normal save or
print commands. These tables for the above problem are attached.
5. A better layout
As was mentioned earlier, the above representation of the
problem is rather rudimentary. One can take advantage of the
spreadsheet environment to create a more sophisticated problem
formulation. As an example, consider the following worksheet for
the same problem:
A B C D E F
1 Products E F
2 Contribut 5000 4000
3 Quantity 0 0
4
5
6 Profit 0
7 LHS RHS Constrain
8 Dept A 10 15 0 150 1
9 Dept B 20 10 0 160 1
10 Testing 30 10 0 135 0
11 Mix 1 -3 0 0 1
12 Total Uni 1 1 0 5 0
13 1
14 1
Here B3 and C3 are adjustable cells, D6 is the objective cell and
contains the formula +B2*B3 + C2*C3. LHS (left-hand-side)
contains formulas to calculate the left-hand sides of the
respective constraints, e.g., D8 has the formula +B3*B8+ C3*C8
etc. In fact, if you write the formula in D8 as +$B$3*B8+
$C$3*B8, you can simply copy it to cells D9 through D12. In the
column labeled constraints we have the appropriate logical
expressions, e.g., in F8 enter +D8<=E8 etc. the last two, i.e.,
F13 and F14 contain the non-negativity namely +B3>=0 and +C3>=0.
Now again we can invoke the SOLVER and define the problem by
defining adjustable cells (B3 and C3), optimal cell (D8) and the
constraint cells (F8 through F14) and solve for the optimal
values.