1 / 59

630 likes | 1.24k Views

Solving Linear Optimization Problems Using the Solver Add-in. The Diet Problem

Download Presentation
## Solving Linear Optimization Problems Using the Solver Add-in

**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.
Content is provided to you AS IS for your information and personal use only.
Download presentation by click this link.
While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

**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**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: The labels in A4:A6 name the 3 constraints in this problem Enter labels in cells B1:E1**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**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**15 HB+ 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 Solver**start with blank sheet & enter labels and constants**name the cells for the two decision variables**Preparing the Worksheet for Solver Highlight the four cells B1:C2**name the cells for the two decision variables**Preparing the Worksheet for Solver Starting from the main menu bar click on Insert**name the cells for the two decision variables**Preparing the Worksheet for Solver Click on Name**name the cells for the two decision variables**Preparing the Worksheet for Solver Click on Create**name the cells for the two decision variables**Preparing the Worksheet for Solver Make sure the Top row box is checked**name the cells for the two decision variables**Preparing the Worksheet for Solver Click OK**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 nameFF and is currently blank**enter the formula for the objective function**Preparing the Worksheet for Solver Click on cell D3**enter the formula for the objective function**Preparing the Worksheet for Solver Click on cell B3 Type =**enter the formula for the objective function**Preparing the Worksheet for Solver Click on cell B2 Type ***enter the formula for the objective function**Preparing the Worksheet for Solver Click on cell C3 Type +**enter the formula for the objective function**Preparing the Worksheet for Solver Click on cell C2 Type ***enter the formula for the objective function**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**Preparing the Worksheet for Solver Click on cell D3 and grab the fill handle + in the lower right corner**copying down the formulas for totals**Preparing the Worksheet for Solver Copy the formula in D3 down to cell D6**copying down the formulas for totals**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 totals**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 totals**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**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 constraints in the Diet Problem are of type >= they can be added all at once. Click on OK**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)

More Related