Solving Linear Optimization Problems Using the Solver Addin

1
Solving Linear Optimization Problems Using
the Solver Add-in
2
Start with a problem to be formulated
How many servings of hamburger and fries would
Julia need to eat to satisfy her daily diet
requirements?
3
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
10 HB 18 FF
subject to the following minimum diet requirements
15 HB 3 FF gt 45 Protein
constraint
32 HB 32 FF gt 256 Carbohydrate
constraint
220 HB 396 FF gt 1980 Calories constraint
nonnegativity constraints
Of course HB gt 0 and FF gt 0
4
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
Enter labels in cells A2A6
5
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
NOTE The labels in A4A6 name the 3 constraints
in this problem
Enter labels in cells B1E1
6
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
NOTE The labels in cells B1C1 name the 2
variables in this problem
7
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
8
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
9
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
10
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
11
Preparing the Worksheet for Solver
start with blank sheet enter labels and
constants
12
name the cells for the two decision variables
Preparing the Worksheet for Solver
Highlight the four cells B1C2
13
name the cells for the two decision variables
Preparing the Worksheet for Solver
Starting from the main menu bar click on Insert
14
name the cells for the two decision variables
Preparing the Worksheet for Solver
Click on Name
15
name the cells for the two decision variables
Preparing the Worksheet for Solver
Click on Create
16
name the cells for the two decision variables
Preparing the Worksheet for Solver
Make sure the Top row box is checked
17
name the cells for the two decision variables
Preparing the Worksheet for Solver
Click OK
18
name the cells for the two decision variables
Preparing the Worksheet for Solver
Cell B2 has the name HB and is currently blank
Cell C2 has the name FF and is currently blank
19
enter the formula for the objective function
Preparing the Worksheet for Solver
Click on cell D3
20
enter the formula for the objective function
Preparing the Worksheet for Solver
Type
Click on cell B3
21
enter the formula for the objective function
Preparing the Worksheet for Solver
Type
Click on cell B2
22
enter the formula for the objective function
Preparing the Worksheet for Solver
Type
Click on cell C3
23
enter the formula for the objective function
Preparing the Worksheet for Solver
Type
Click on cell C2
24
enter the formula for the objective function
Preparing the Worksheet for Solver
formula entered in D3 objective function
(Fat content) 10 HB 18 FF
NOTE The formula B3HBC3FF could have been
typed in cell D3 directly
Enter
current value for the formula entered in D3
NOTE Blanks in HB and FF
(cells B2 and C2) are considered zeros
25
copying down the formulas for totals
Preparing the Worksheet for Solver
Click on cell D3 and grab the fill handle in
the lower right corner
26
copying down the formulas for totals
Preparing the Worksheet for Solver
Copy the formula in D3 down to cell D6
27
copying down the formulas for totals
Preparing the Worksheet for Solver
formula copied in D4 Protein constraint LHS
15 HB 3 FF
Click on cell D4 to check your formula
current value for the formula entered in D4
28
copying down the formulas for totals
Preparing the Worksheet for Solver
formula copied in D5 Carbohydrate constraint LHS
32 HB 32 FF
Click on cell D5 to check your formula
current value for the formula entered in D5
29
copying down the formulas for totals
Preparing the Worksheet for Solver
formula copied in D6 Calories constraint LHS 220
HB 396 FF
Click on cell D6 to check your formula
current value for the formula entered in D6
30
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
31
Using Solver
invoking Solver Add-In from the Tools menu
Starting from the main menu bar click on Tools
32
Using Solver
invoking Solver Add-In from the Tools menu
Click on Solver
33
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
34
Using Solver
select the value of the objective function as the
Target Cell
Click on the Set Target Cell box
35
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
36
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
37
Using Solver
the Changing Cells are the decision variables
Click on the By Changing Cells box
38
Using Solver
the Changing Cells are the decision variables
Highlight cells B2C2 containing the decision
variables
39
Using Solver
add Constraints
Click on the Subject to the Constraints box
40
Using Solver
add Constraints
Click on Add
41
Using Solver
select the constraints left-hand-sides
Click on the Cell Reference box
42
Using Solver
select the constraints left-hand-sides
Highlight cells D4D6
43
Using Solver
select the constraints left-hand-sides
44
Using Solver
select the constraints types
Click on ?
45
Using Solver
select the constraints types
Click on gt
46
Using Solver
select the constraints right-hand-sides
Click on the Constraint box
47
Using Solver
select the constraints right-hand-sides
Highlight cells E4E6
48
Using Solver
select the constraints right-hand-sides
NOTE Since the three constraints in the Diet
Problem are of type gt they can be added all at
once.
Click on OK
49
Using Solver
setting the Options
Click on Options
50
Using Solver
setting the Options
Check Assume Linear Model and Assume Non-Negative
boxes (do not modify the other Options for this
problem)
51
Using Solver
setting the Options
Click OK
52
Using Solver
executing Solver
Click on Solve
53
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
54
Using Solver
obtaining an Answer Report
Click on Answer in the Reports box
55
Using Solver
obtaining an Answer Report
Click on OK
56
Using Solver
viewing the Answer Report
Click on Answer Report 1 tab
57
Using Solver
viewing the Answer Report
NOTE From this point on, only the spreadsheet
portion of the Excel window will be displayed
58
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
59
(No Transcript)