Microsoft Office Tutorials and References
In Depth Information
adjusted solutions that they develop. We will see later that one of the reports that
is produced by Solver will provide information on the slack (unused resources) for
each constraint. If there is no slack in a constraint, then the entire RHS (maximum
amount of the resource) is consumed.
Let us now turn to the use of the Solver dialogue boxes to input our formulation.
In Exhibit 9.3 we can see the target (E10) and changing cells (D2:D8) in the Solver
Parame t ers dialogue box. Now we introduce the constraints of the formulation. In
row 23 (Exhibit 9.2) we calculate the use of resource hours. The cell comment in
D23 shows the calculation for the resource D hour usage. Generally, the formula
sums the products of the number of projects selected times the hours consumed by
each project type. These four cells (A23:D23) will be used by Solver to determine
the use of the RHS (800, 900, 700, and 375) and to insure that the individual RHS’s
are not exceeded. We must also account for the maximum available projects for each
type in B2:B8 (Exhibit 9.2).
How do we enter this information into our formulation? We begin by selecting
the Solver in the Analysis group in the Data ribbon. See Exhibit 9.3. Solver should
appear in the group, but if it does not, it is because you have not enabled the Solver
add-in. To enable the add-in, select the Ofﬁce button and Options. One of the options
is Add-ins, as we noted before with the Data Analysis tools.
The Solver Parameters dialogue box is relatively simple, but does require some
forethought to use effectively. The entry of the target cell and the changing cells is
straightforward; the entry of constraints is slightly more complex. You can see in
the Subject to the Constraints area a number of constraints that have been entered
Exhibit 9.3 Solver parameters dialogue box