Page 101 -

SOLVING LINEAR PROGRAMMES WITH EXCEL 81

Step 3. When the Solver Parameters dialogue box appears (see Figure 2.23):
Enter B18 into the Set Cell box
Select the Equal To: Max option
Enter B16:C16 into the By Changing Variable Cells box
Select Add
Step 4. When the Add Constraint dialogue box appears:
Enter B21:B24 in the Cell Reference box
Select <¼
Enter D21:D24 into the Constraint box
Click OK
Step 5. When the Solver Parameters dialogue box reappears:
Choose Options
Step 6. When the Solver Options dialogue box appears:
Select Assume Non-Negative
Click OK
Step 7. When the Solver Parameters dialogue box appears:
Choose Solve
Step 8. When the Solver Results dialogue box appears:
Select Keep Solver Solution
Click OK
If the Standard button Figure 2.23 shows the completed Solver Parameters dialogue box, and Figure 2.24
and Standard Simplex LP
option do not appear, shows the optimal solution in the worksheet. Note that after rounding, the optimal
click the Premium button solution of 540 standard bags and 252 deluxe bags is the same as we obtained using
and select the Standard the graphical solution procedure. In addition to the output information shown in
Simplex LP option. Figure 2.24, Solver has an option to provide sensitivity analysis information. We
discuss sensitivity analysis in Chapter 3.
In Step 6 we selected the Assume Non-Negative option in the Solver Options
dialogue box to avoid having to enter nonnegativity constraints for the decision
variables. In general, whenever we want to solve a linear programming model in
which the decision variables are all restricted to be nonnegative, we will select this
option. In addition, in Step 4 we entered all four less-than-or-equal-to constraints
simultaneously by entering B21:B24 into the Cell Reference box, selecting <¼, and
entering D21:D24 into the Constraint box. Alternatively, we could have entered the
four constraints one at a time.

Figure 2.23 Solver Parameters Dialogue Box for the GulfGolf Problem

Copyright 2014 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has
deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.