Page 100 -

80 CHAPTER 2 AN INTRODUCTION TO LINEAR PROGRAMMING

Step 1. Enter the problem data in the top part of the worksheet.
Cells B5:C8 show the production requirements per unit for each product.
Cells B9:C9 show the profit contribution per unit for the two products.
Cells D5:D8 show the number of hours available in each department.
Step 2. Specify cell locations for the decision variables.
Cell B16 will contain the number of standard bags produced, and cell C16
will contain the number of deluxe bags produced.
Step 3. Select a cell and enter a formula for computing the value of the objective
function.
Cell B18: ¼B9*B16 + C9*C16
Step 4. Select a cell and enter a formula for computing the left-hand side of each
constraint.
With four constraints, we have:
Cell B21:¼B5*B16 + C5*C16
Cell B22:¼B6*B16 + C6*C16
Cell B23:¼B7*B16 + C7*C16
Cell B24:¼B8*B16 + C8*C16
Step 5. Select a cell and enter a formula for computing the right-hand side of each
constraint.
With four constraints, we have:
Cell D21:¼D5
Cell D22:¼D6
Cell D23:¼D7
Cell D24:¼D8
Note that descriptive labels make the model section of the worksheet easier to read and
understand. For example, we added ‘Standard’, ‘Deluxe’ and ‘Bags Produced’ in rows
15 and 16 so that the values of the decision variables appearing in cells B16 and C16 can
be easily interpreted. In addition, we entered ‘Maximize Total Profit’ in cell A18 to
indicate that the value of the objective function appearing in cell B18 is the maximum
profit contribution. In the constraint section of the worksheet we added the constraint
names as well as the ‘<¼’ symbols to show the relationship that exists between the left-
hand side and the right-hand side of each constraint. Although these descriptive labels
are not necessary to use Excel Solver to find a solution to the GulfGolf problem, the
labels make it easier for the user to understand and interpret the optimal solution.
Excel Solution

The standard Excel Solver developed by Frontline Systems can be used to solve all
of the linear programming problems presented in this text. However, a more power-
ful version referred to as Premium Solver for Education is available. When first
started, Premium Solver looks and behaves exactly like the standard Excel Solver.
But, when the ‘Premium’ button in the main Solver Parameters dialogue box is
selected, this version provides a variety of new features, including an online user’s
guide. The Premium Solver for Education has the same problem size limits as the
standard Excel Solver: 200 decision variables and 100 constraints. We recommend
that you install the new version and use the ‘Premium’ mode option when develop-
ing and solving spreadsheet models of linear programmes.
The following steps describe how Frontline Systems’ Premium Solver for Educa-
tion can be used to obtain the optimal solution to the GulfGolf problem.

Step 1. Select the Tools menu.
Step 2. Select the Solver option.

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.