1 / 67

Solving Linear Optimization Problems with Excel Solver for Microsoft Excel 2000

Solving Linear Optimization Problems with Excel Solver for Microsoft Excel 2000. Hamburger. Fries. Fat (g). 10. 18. Protein (g). 15. 3. Carbohydrate (g). 32. 32. Calories (kcal). 220. 396. A Linear Optimization Problem. a diet problem.

sera
Download Presentation

Solving Linear Optimization Problems with Excel Solver for Microsoft Excel 2000

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

Presentation Transcript

  1. Solving Linear Optimization Problems with Excel Solver for Microsoft Excel 2000

  2. Hamburger • Fries • Fat (g) • 10 • 18 • Protein (g) • 15 • 3 • Carbohydrate (g) • 32 • 32 • Calories (kcal) • 220 • 396 A Linear Optimization Problem • a 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. • She chooses her favorite fast foods: hamburger and french fries. The nutritive values per serving are given below. • How many servings of hamburger and fries would Julia need to eat to satisfy her daily diet requirements?

  3. objective function constraints Formulate the Problem • 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

  4. Preparing the Worksheet for Solver • start with a blank sheet

  5. Preparing the Worksheet for Solver • enter labels Enter row labels in cells A2:A6

  6. Preparing the Worksheet for Solver • enter labels Enter column labels in cells B1:F1

  7. Preparing the Worksheet for Solver • enter labels Enter inequalities in cells E4:E6

  8. Preparing the Worksheet for Solver • enter labels NOTE: These labels are not essential for the use of Solver

  9. Remember… 10HB+18FF Fat Preparing the Worksheet for Solver • enter constants Enter coefficients of objective function in cells B3:C3

  10. 15 HB + 3 FF45 Protein 32 HB+ 32 FF 256 Carbohydrate Remember… 220 HB+ 396 FF1980Calories Preparing the Worksheet for Solver • enter constants Enter coefficients of left side of constraints in cells B4:C6

  11. 15HB + 3 FF 45 Protein 32HB + 32 FF 256 Carbohydrate Remember… 220HB + 396 FF 1980 Calories Preparing the Worksheet for Solver • enter constants Enter right sides of constraints in cells F4:F6

  12. Preparing the Worksheet for Solver • name decision variables cells Highlight cells B1:C2

  13. Preparing the Worksheet for Solver • name decision variables cells Click on Insert

  14. Preparing the Worksheet for Solver • name decision variables cells Click on Name

  15. Preparing the Worksheet for Solver • name decision variables cells Click on Create

  16. Preparing the Worksheet for Solver • name decision variables cells

  17. Make sure the Top row (containing the names HB and FF) box is checked Preparing the Worksheet for Solver • name decision variables cells

  18. Click on OK Preparing the Worksheet for Solver • name decision variables cells

  19. Preparing the Worksheet for Solver • name decision variables cells Cell B2is named HB and is currently blank Cell C2is named FF and is currently blank

  20. Remember… 10 HB + 18 FFFat NOTE: Objective function formula in cell D3 Preparing the Worksheet for Solver • enter formula for objective function Click on cell D3

  21. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Type in = NOTE: Objective function formula in cell D3

  22. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Click on cell B3 NOTE: Objective function formula in cell D3

  23. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Type in * NOTE: Objective function formula in cell D3

  24. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Click on cell B2 NOTE: Objective function formula in cell D3

  25. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Type in + NOTE: Objective function formula in cell D3

  26. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Click on cell C3 NOTE: Objective function formula in cell D3

  27. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Type in * NOTE: Objective function formula in cell D3

  28. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Click on cell C2 NOTE: Objective function formula in cell D3

  29. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Press Enter NOTE: Objective function formula in cell D3

  30. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Click on cell D3to check formula NOTE: This step is not essential for the use of Solver

  31. Remember… 10HB + 18 FFFat Preparing the Worksheet for Solver • enter formula for objective function Formula entered in cell D3 Current value of formula entered in cell D3 NOTE: The formula is equivalent to =B3*$B$2+C3*$C$2 . Blanks in cells HB and FF (cells B2 and C2) are considered zeros

  32. NOTE: Formulas for the left sides of the constraints in cells D4:D6 15 HB + 3 FF 45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF 1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints

  33. 15 HB + 3 FF 45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF 1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Click on cell D3and grab the fill handle + on the lower right corner NOTE: Formulas for the left sides of the constraints in cells D4:D6

  34. 15 HB + 3 FF 45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF 1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Dragthe fill handle +down to cell D6  NOTE: Formulas for the left sides of the constraints in cells D4:D6 NOTE: The formula in cellD3 will be copied down to cell D6

  35. 15 HB + 3 FF 45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF 1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints  NOTE: Formulas for the left sides of the constraints in cells D4:D6

  36. 15 HB + 3 FF 45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF 1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Click on cell D4to check formula NOTE: This step is not essential for the use of Solver

  37. 15 HB + 3 FF 45 Protein 32 HB + 32 FF 256 Carbohydrate 220 HB + 396 FF1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Formula entered in cell D4(Protein) Current value of formula entered in cell D4 NOTE: The rows for column B and C were copied as relative references while HB and FF remained fixed

  38. 15 HB + 3 FF 45 Protein 32 HB + 32 FF 256 Carbohydrate 220 HB + 396 FF1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Click on cell D5to check formula NOTE: This step is not essential for the use of Solver

  39. 15 HB + 3 FF45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Formula entered in cell D5(Carbohydrate) Current value of formula entered in cell D5

  40. 15 HB + 3 FF45 Protein 32 HB + 32 FF  256 Carbohydrate 220 HB + 396 FF1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Click on cell D6to check formula NOTE: This step is not essential for the use of Solver

  41. 15 HB + 3 FF45 Protein 32 HB + 32 FF 256 Carbohydrate 220 HB + 396 FF 1980Calories Remember… Preparing the Worksheet for Solver • enter formulas for constraints Formula entered in cell D6(Calories) Current value of formula entered in cell D6

  42. Preparing the Worksheet for Solver • enter formulas for constraints Decision variable values in cells B2:C2 Objective function formula in cell D3 NOTE: These cells will be colored to indicate that they are essential for Solver

  43. Using Solver • invoke Solver Click on Tools

  44. Using Solver • invoke Solver Click on Solver

  45. Using Solver • invoke Solver

  46. NOTE: The cell displayed in the Set Target Cell: box must contain the objective function formula (cell D3) Using Solver • complete the Solver Parameters dialog box Click on cell D3

  47. Check the Min: circle Using Solver • complete the Solver Parameters dialog box

  48. Click on the By Changing Cells: box Using Solver • complete the Solver Parameters dialog box

  49. Highlight cells B2:C2 Using Solver • complete the Solver Parameters dialog box NOTE: The By Changing Cells: box must display the decision variable cells (cells B2:C2)

  50. Click on Add NOTE: The Subject to the Constraints: box must contain the constraints on the decision variables Using Solver • complete the Solver Parameters dialog box

More Related