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
Enter labels in cells B1E1
5
name the cells for the two decision variables
Preparing the Worksheet for Solver
Highlight the four cells B1C2
Starting from the main menu bar click on Insert
Name Create
6
name the cells for the two decision variables
Preparing the Worksheet for Solver
Check the Top row box and click OK
Cell B2 has the name HB and is currently blank
Cell C2 has the name FF and is currently blank
7
enter the formula for the objective function
Preparing the Worksheet for Solver
Click on cell D3
Type
Click on cell B3
Type
Click on cell B2
Type
Click on cell C3
Type
Click on cell C2
Enter
8
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
Copy the formula in D3 down to cell D6
9
copying down the formulas for totals
Preparing the Worksheet for Solver
Click on cell D4 to check your formula
10
copying down the formulas for totals
Preparing the Worksheet for Solver
Click on cell D5 to check your formula
11
copying down the formulas for totals
Preparing the Worksheet for Solver
Click on cell D6 to check your formula
12
Preparing the Worksheet for Solver
copying down the formulas for totals
13
Using Solver
invoking Solver Add-In from the Tools menu
Starting from the main menu bar click on Tools
Solver
14
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
15
Using Solver
select the value of the objective function as the
Target Cell
Click on the Set Target Cell box and click on
cell D3 which contains the value we want to
optimize
16
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
17
Using Solver
the Changing Cells are the decision variables
Click on the By Changing Cells box and highlight
cells B2C2 containing the decision variables
18
Using Solver
add Constraints
Click on the Subject to the Constraints box and
click on Add
19
Using Solver
add Constraints
20
Using Solver
select the constraints left-hand-sides
Click on the Cell Reference box and highlight
cells D4D6
21
Using Solver
select the constraints types
Click on ? and select gt
22
Using Solver
select the constraints right-hand-sides
Click on the Constraint box and highlight cells
E4E6
NOTE Since the three constraints in the Diet
Problem are of type gt they can be added all at
once.
Click on OK
23
Using Solver
setting the Options
Click on Options
24
Using Solver
setting the Options
Check Assume Linear Model and Assume Non-Negative
boxes (do not modify the other Options for this
problem)
Click OK
25
Using Solver
executing Solver
Click on Solve
26
Using Solver
obtaining solutions from the worksheet
Solver uses a method known as SIMPLEX
27
Using Solver
obtaining an Answer Report
Click on Answer in the Reports area
Click on OK
28
Using Solver
viewing the Answer Report
Click on Answer Report 1 tab
NOTE From this point on, only the spreadsheet
portion of the Excel window will be displayed
29
Using Solver
viewing the Answer Report
Final steps in any solution involve an
interpretation of these computer-generated
results
30
(No Transcript)