Solving
Linear Optimization Problems
Using the Solver Add-in
Start with a problem to be formulated
The Diet Problem
Julia wonders if she can keep the amount of fat in her diet down and still
get all the protein (45 g), carbohydrate (256 g), and calories (1,980 kcal)
that she needs every day by eating fast food only. For other nutrients,
such as vitamins, iron, and calcium, she will depend on pills (nutritionists
would disapprove, but this example ought to be simple). She chooses
her favorite fast foods: hamburger and French fries. The nutritive values
per serving are given below.
Hamburger Fries How many servings of
Fat (g) 10 18 hamburger and fries
Protein (g) 15 3 would Julia need to
Carbohydrate (g) 32 32 eat to satisfy her daily
Calories (kcal) 220 396 diet requirements?
Formulate the Model
as a Linear Programming Problem
Julia is looking for
HB = the number of servings of Hamburger
decision variables
FF = the number of servings of French Fries
that minimizes the total amount of fat
objective function 10 HB + 18 FF
subject to the following minimum diet requirements
15 HB + 3 FF >= 45 Protein constraint
constraints 32 HB + 32 FF >= 256 Carbohydrate constraint
220 HB + 396 FF >= 1980 Calories constraint
nonnegativity
constraints Of course HB >= 0 and FF >= 0
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Enter labels in cells A2:A6
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
NOTE:
EnterThe
labels
labels
in cells
in A4:A6
B1:E1name
the 3 constraints in this problem
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Enter coefficients
NOTE: of The
objective
labels infunction
cells B1:C1
in cells B3:C3
name the 2 variables in this problem
10 HB + 18 FF
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Enter coefficients of left side of Protein constraint in cells B4:C4
15 HB + 3 FF
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Enter coefficients of left side of Carbohydrate constraint in cells B5:C5
32 HB + 32 FF
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Enter coefficients of left side of Calories constraint in cells B6:C6
220 HB + 396 FF
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Enter right sides of constraint in cells E4:E6 15 HB + 3 FF >= 45
32 HB + 32 FF >= 256
220 HB + 396 FF >= 1980
Preparing the Worksheet for Solver
start with blank sheet & enter labels and constants
Preparing the Worksheet for Solver
name the cells for the two decision variables
Highlight the four cells B1:C2
Preparing the Worksheet for Solver
name the cells for the two decision variables
Starting from the main menu bar click on Insert
Preparing the Worksheet for Solver
name the cells for the two decision variables
Click on Name
Preparing the Worksheet for Solver
name the cells for the two decision variables
Click on Create
Preparing the Worksheet for Solver
name the cells for the two decision variables
Make sure the Top row box is checked
Preparing the Worksheet for Solver
name the cells for the two decision variables
Click OK
Preparing the Worksheet for Solver
name the cells for the two decision variables
Cell B2 has the name HB and is currently blank
Cell C2 has the name FF and is currently blank
Preparing the Worksheet for Solver
enter the formula for the objective function
Click on cell D3
Preparing the Worksheet for Solver
enter the formula for the objective function
ClickType
on cell
= B3
Preparing the Worksheet for Solver
enter the formula for the objective function
Click Type
on cell
* B2
Preparing the Worksheet for Solver
enter the formula for the objective function
ClickType
on cell
+ C3
Preparing the Worksheet for Solver
enter the formula for the objective function
ClickType
on cell
* C2
Preparing the Worksheet for Solver
enter the formula for the objective function
formula entered in D3
objective function
(Fat content)
10 HB + 18 FF
current
NOTE:value
Thefor
formula
the
Enter
formula
B3*HB+C3*FF
entered incould
D3
NOTE: Blanks in HB
have
and
been
FF typed
(cells B2
in cell
andD3C2)directly
are considered zeros
Preparing the Worksheet for Solver
copying down the formulas for totals
Click on cell D3 and grab the fill handle + in the lower right corner
Preparing the Worksheet for Solver
copying down the formulas for totals
Copy the formula in D3 down to cell D6
Preparing the Worksheet for Solver
copying down the formulas for totals
formula copied in D4
Protein constraint LHS
15 HB + 3 FF
current
Click on
value
cell for
D4 the
to check
formula
your
entered
formula
in D4
Preparing the Worksheet for Solver
copying down the formulas for totals
formula copied in D5
Carbohydrate constraint LHS
32 HB + 32 FF
current
Click on
value
cell for
D5 the
to check
formula
your
entered
formula
in D5
Preparing the Worksheet for Solver
copying down the formulas for totals
formula copied in D6
Calories constraint LHS
220 HB + 396 FF
current
Click on
value
cell for
D6 the
to check
formula
your
entered
formula
in D6
Preparing the Worksheet for Solver
copying down the formulas for totals
NOTE: In the formula, the rows for column B and C have
been copied as relative references and the references to the
decision variables HB (cell B2) and FF (cell C2), are fixed
Using Solver
invoking Solver Add-In from the Tools menu
Starting from the main menu bar click on Tools
Using Solver
invoking Solver Add-In from the Tools menu
Click on Solver
Using Solver
invoking Solver Add-In from the Tools menu
NOTE: From this point on, only the spreadsheet
portion of the Excel window will be displayed
Using Solver
select the value of the objective function as the Target Cell
Click on the Set Target Cell: box
Using Solver
select the value of the objective function as the Target Cell
Click on cell D3 which contains the function we want to optimize
Using Solver
indicate if the Target Cell is to be minimized/maximized
Check the Min radio button to indicate that we
want to minimize the value in the Target Cell
Using Solver
the Changing Cells are the decision variables
Click on the By Changing Cells: box
Using Solver
the Changing Cells are the decision variables
Highlight cells B2:C2 containing the decision variables
Using Solver
add Constraints
Click on the Subject to the Constraints: box
Using Solver
add Constraints
Click on Add
Using Solver
select the constraints’ left-hand-sides
Click on the Cell Reference: box
Using Solver
select the constraints’ left-hand-sides
Highlight cells
D4:D6
Using Solver
select the constraints’ left-hand-sides
Using Solver
select the constraints’ types
Click on
Using Solver
select the constraints’ types
Click on
>=
Using Solver
select the constraints’ right-hand-sides
Click on the Constraint: box
Using Solver
select the constraints’ right-hand-sides
Highlight cells E4:E6
Using Solver
select the constraints’ right-hand-sides
NOTE: Since the three
Clickconstraints
on OK in the Diet Problem
are of type >= they can be added all at once.
Using Solver
setting the Options
Click on Options
Using Solver
setting the Options
Check Assume Linear Model and Assume Non-Negative boxes
(do not modify the other Options for this problem)
Using Solver
setting the Options
Click OK
Using Solver
executing Solver
Click on Solve
Using Solver
obtaining solutions from the worksheet
Solver finds a solution
HB = 6.75 servings of Hamburger
FF = 1.25 servings of French fries
with minimum fat content of 90 g
This solution contains
Protein: 105 g
Carbohydrate: 256 g
Calories: 1980 kcal
satisfying the constraints
Solver uses a method known as SIMPLEX
Using Solver
obtaining an Answer Report
Click on Answer in the Reports box
Using Solver
obtaining an Answer Report
Click on OK
Using Solver
viewing the Answer Report
Click on Answer Report 1 tab
Using Solver
viewing the Answer Report
NOTE: From this point on, only the spreadsheet
portion of the Excel window will be displayed
Using Solver
viewing the Answer Report
with minimum fat content of 90 g
Solution
HB = 6.75 servings of Hamburger
FF = 1.25 servings of French fries
and with
Protein: 105 g
Carbohydrate: 256 g
Calories: 1980 kcal
satisfying the constraints
Final steps in any solution involve an interpretation
of these computer-generated results
