Using Excel Solver for Linear Optimization Problems

# Using Excel Solver for Linear Optimization Problems

## Using Excel Solver for Linear Optimization Problems

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Using Excel Solver for Linear Optimization Problems Wendy Pitchko Irene Meglis Shawn Lemko

2. What is Solver? • Solver is an Add-In for Microsoft Excel which can solve optimization problems, including multiple constraint problems. • You can maximize, minimize, or set a target value to achieve.

3. Installing Solver for Use • On Excel Menu, choose • Tools • Add-Ins...

4. Installing Solver for Use • On Excel Menu, choose • Tools • Add-Ins... • Put a Check in the Box Next to ‘Solver Add-in’

5. Using Solver • On Excel Menu, choose • Tools • Solver • This brings up the Solver Parameters box which will be discussed next.

6. Recall the Cargo Problem from the last homework assignment… Our task is to maximize the profit for a shipping company using a combination of cargo weights without violating the given constraints Revenue is \$250 per ton of cargo shipped Limit of 50,000 ft3 space and 100 tons Three types of Cargos with different densities, maximum available amounts Refresher: Setting up a Linear Problem On Paper

7. Setting Up the Problem in Excel • The Objective Function

8. Setting Up the Problem in Excel • The Objective Function • The Decision Variables (optimal values will be calculated by Solver)

9. Setting Up the Problem in Excel • The Objective Function • The Decision Variables • The Constraints • Relationships

10. Setting Up the Problem in Excel • The Objective Function • The Decision Variables • The Constraints • Relationships • Values

11. Running Solver

12. Solver Solution

13. Understanding the Output: the Answer Report

14. Understanding the Output: the Sensitivity Report

15. The End • Thanks for your attention…