Solving linear optimization problems using the solver add in
Download
1 / 59

faculty.babson - PowerPoint PPT Presentation


  • 499 Views
  • Updated On :
loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'faculty.babson' - Faraday


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Solving linear optimization problems using the solver add in

Solving Linear Optimization Problems Using the Solver Add-in


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

Fat (g) 1018

Protein (g) 15 3

Carbohydrate (g) 32 32

Calories (kcal) 220 396

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?


objective function

constraints

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>= 45 Protein constraint

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
Preparing the Worksheet for Solver

start with blank sheet & enter labels and constants

Enter labels in cells A2:A6


Preparing the worksheet for solver1
Preparing the Worksheet for Solver

start with blank sheet & enter labels and constants

NOTE: The labels in A4:A6 name the 3 constraints in this problem

Enter labels in cells B1:E1


Preparing the worksheet for solver2

Enter coefficients of objective function in cells B3:C3

10 HB +18 FF

Preparing the Worksheet for Solver

start with blank sheet & enter labels and constants

NOTE: The labels in cells B1:C1 name the 2 variables in this problem


Preparing the worksheet for solver3

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


Preparing the worksheet for solver4

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


Preparing the worksheet for solver5

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


Preparing the worksheet for solver6

15 cells B6:C6HB+ 3 FF>= 45

32 HB+ 32 FF>= 256

Enter right sides of constraint in cells E4:E6

220 HB + 396 FF>= 1980

Preparing the Worksheet for Solver

start with blank sheet & enter labels and constants


Preparing the worksheet for solver7
Preparing the Worksheet for Solver cells B6:C6

start with blank sheet & enter labels and constants


Name the cells for the two decision variables
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Highlight the four cells B1:C2


Name the cells for the two decision variables1
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Starting from the main menu bar click on Insert


Name the cells for the two decision variables2
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Click on Name


Name the cells for the two decision variables3
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Click on Create


Name the cells for the two decision variables4
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Make sure the Top row box is checked


Name the cells for the two decision variables5
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Click OK


Name the cells for the two decision variables6
name the cells for the two decision variables cells B6:C6

Preparing the Worksheet for Solver

Cell B2 has the name HB and is currently blank Cell C2 has the nameFF and is currently blank


Enter the formula for the objective function
enter the formula for the objective function cells B6:C6

Preparing the Worksheet for Solver

Click on cell D3


Enter the formula for the objective function1
enter the formula for the objective function cells B6:C6

Preparing the Worksheet for Solver

Click on cell B3

Type =


Enter the formula for the objective function2
enter the formula for the objective function cells B6:C6

Preparing the Worksheet for Solver

Click on cell B2

Type *


Enter the formula for the objective function3
enter the formula for the objective function cells B6:C6

Preparing the Worksheet for Solver

Click on cell C3

Type +


Enter the formula for the objective function4
enter the formula for the objective function cells B6:C6

Preparing the Worksheet for Solver

Click on cell C2

Type *


Enter the formula for the objective function5
enter the formula for the objective function cells B6:C6

Preparing the Worksheet for Solver

formula entered in D3 objective function (Fat content) 10 HB + 18 FF

current value for the formula entered in D3 NOTE: Blanks in HB and FF (cells B2 and C2) are considered zeros

NOTE: The formula B3*HB+C3*FF could have been typed in cell D3 directly

Enter


Copying down the formulas for totals
copying down the formulas for totals cells B6:C6

Preparing the Worksheet for Solver

Click on cell D3 and grab the fill handle + in the lower right corner


Copying down the formulas for totals1
copying down the formulas for totals cells B6:C6

Preparing the Worksheet for Solver

Copy the formula in D3 down to cell D6


Copying down the formulas for totals2
copying down the formulas for totals cells B6:C6

Preparing the Worksheet for Solver

formula copied in D4 Protein constraint LHS 15 HB + 3 FF

current value for the formula entered in D4

Click on cell D4 to check your formula


Copying down the formulas for totals3
copying down the formulas for totals cells B6:C6

Preparing the Worksheet for Solver

formula copied in D5 Carbohydrate constraint LHS 32 HB +32 FF

current value for the formula entered in D5

Click on cell D5 to check your formula


Copying down the formulas for totals4
copying down the formulas for totals cells B6:C6

Preparing the Worksheet for Solver

formula copied in D6 Calories constraint LHS 220 HB +396 FF

current value for the formula entered in D6

Click on cell D6 to check your formula


Preparing the Worksheet for Solver cells B6:C6

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 cells B6:C6

invoking Solver Add-In from the Tools menu

Starting from the main menu bar click on Tools


Using Solver cells B6:C6

invoking Solver Add-In from the Tools menu

Click on Solver


Using Solver cells B6:C6

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 cells B6:C6

select the value of the objective function as the Target Cell

Click on the Set Target Cell: box


Using Solver cells B6:C6

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 cells B6:C6

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 cells B6:C6

the Changing Cells are the decision variables

Click on the By Changing Cells: box


Using Solver cells B6:C6

the Changing Cells are the decision variables

Highlight cells B2:C2 containing the decision variables


Using Solver cells B6:C6

add Constraints

Click on the Subject to the Constraints: box


Using Solver cells B6:C6

add Constraints

Click on Add


Using Solver cells B6:C6

select the constraints’ left-hand-sides

Click on the Cell Reference: box


Using Solver cells B6:C6

select the constraints’ left-hand-sides

Highlight cells D4:D6


Using Solver cells B6:C6

select the constraints’ left-hand-sides


Using Solver cells B6:C6

select the constraints’ types

Click on 


Using Solver cells B6:C6

select the constraints’ types

Click on >=


Using Solver cells B6:C6

select the constraints’ right-hand-sides

Click on the Constraint: box


Using Solver cells B6:C6

select the constraints’ right-hand-sides

Highlight cells E4:E6


Using Solver cells B6:C6

select the constraints’ right-hand-sides

NOTE: Since the three constraints in the Diet Problem are of type >= they can be added all at once.

Click on OK


Using Solver cells B6:C6

setting the Options

Click on Options


Using Solver cells B6:C6

setting the Options

Check Assume Linear Model and Assume Non-Negative boxes (do not modify the other Options for this problem)


Using Solver cells B6:C6

setting the Options

Click OK


Using Solver cells B6:C6

executing Solver

Click on Solve


Using Solver cells B6:C6

obtaining solutions from the worksheet

Solver finds a solution HB = 6.75 servings of HamburgerFF = 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 cells B6:C6

obtaining an Answer Report

Click on Answer in the Reports box


Using Solver cells B6:C6

obtaining an Answer Report

Click on OK


Using Solver cells B6:C6

viewing the Answer Report

Click on Answer Report 1 tab


Using Solver cells B6:C6

viewing the Answer Report

NOTE: From this point on, only the spreadsheet portion of the Excel window will be displayed


Using Solver cells B6:C6

viewing the Answer Report

with minimum fat content of 90 g

Solution HB = 6.75 servings of HamburgerFF = 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


ad