Excel solver

Excel Solver for Linear Programming Optimization

1. Solving Linear programs using excel Arpee Arruejo , MIT

2. Agenda What is Excel Solver? How to add Solver in Excel? How to use Solver in Excel? Excel Solver Examples Excel Solver Algorithms

3. What is Excel Solver? Excel Solver  belongs to a special set of commands often referred to as What-if Analysis Tools. It is primarily purposed for simulation and optimization of various business and engineering models. The Excel Solver add-in is especially useful for solving linear programming problems, aka linear optimization problems, and therefore is sometimes called a  linear programming solver

4. How to add Excel Solver? Excel, perform the following steps: In  Excel 2010 ,  Excel 2013 , and  Excel 2016 , click  File  >  Options . In  Excel 2007 , click the  Microsoft Office  button, and then click  Excel Options . In the  Excel Options  dialog, click  Add-Ins  on the left sidebar, make sure  Excel Add-ins  is selected in the  Manage  box at the bottom of the window, and click  Go .

5. How to use Excel Solver? Problem . Supposing, you are the owner of a beauty salon and you are planning on providing a new service to your clients. For this, you need to buy a new equipment that costs $40,000, which should be paid by instalments within 12 months. Goal : Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.

6. Model:

7. Formulating in Excel Write the LP out on paper, with all constraints and the objective function. Decide on cells to represent variables. Enter coefficients of each variable in each constraint in a block of cells. Compute amount of each constraint being used by current solution.

8. Formulating in Excel 5. Place inequalities in sheet, so you remember <=, >= 6. Enter amount of each constraint 7. Enter objective coefficients 8. Calculate value of objective function 9. Make sure you have plenty of labels. 10. Widen columns for readability.

9. How to use… cont’n …. 1. Run Excel Solver On the  Data  tab, in the  Analysis  group, click the  Solver  button. 2. Define the problem The  Solver Parameters  window will open where you have to set up the 3 primary components: Objective cell Variable cells Constraints

10. How to use… cont’n …. Objective The  Objective  cell ( Target  cell in earlier Excel versions) is the cell  containing a formula  that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.

11. How to use… cont’n …. In this example, the objective cell is B7, which calculates the payment term using the formula =B3/(B4*B5) and the result of the formula should be equal to 12:

12. How to use… cont’n …. Variable cells Variable  cells ( Changing  cells or  Adjustable  cells in earlier versions) are cells that contain variable data that can be changed to achieve the objective. Excel Solver allows specifying up to 200 variable cells.

13. How to use… cont’n …. In this example, we have a couple of cells whose values can be changed: Projected clients per month (B4) that should be less than or equal to 50; and Cost per service (B5) that we want Excel Solver to calculate.

14. How to use… cont’n …. Constraints The Excel Solver  Constrains  are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.

15. How to use… cont’n …. To add a constraint(s), do the following: Click the  Add  button right to the " Subject to the Constraints " box.

16. How to use… cont’n …. In the  Constraint  window, enter a constraint. Click the  Add  button to add the constraint to the list. Continue entering other constraints. After you have entered the final constraint, click  OK  to return to the main  Solver   Parameters window .

17. How to use… cont’n …. Solve the problem After you've configured all the parameters, click the  Solve  button at the bottom of the  Solver Parameters  window (see the screenshot above) and let the Excel Solver add-in find the optimal solution for your problem. Depending on the model complexity, computer memory and processor speed, it may take a few seconds, a few minutes, or even a few hours. When Solver has finished processing, it will display the  Solver Results  dialog window, where you select  Keep the Solver Solution  and click  OK :

18. How to use… cont’n …. Solve the problem In this example, $66.67 appears in cell B5, which is the minimal cost per service that will let you pay for the new equipment in 12 months, provided there are at least 50 clients per month:

19. More Examples Excel Solver example 1 Problem : You want to minimize the cost of shipping goods from 2 different warehouses to 4 different customers. Each warehouse has a limited supply and each customer has a certain demand. Goal : Minimize the total shipping cost, not exceeding the quantity available at each warehouse, and meeting the demand of each customer.

20. More Examples.. Cont’n … Formulating the model To define our linear programming problem for the Excel Solver, let's answer the 3 main questions: What decisions are to be made? We want to calculate the optimal quantity of goods to deliver to each customer from each warehouse. These are  Variable  cells (B7:E8). What are the constraints? The supplies available at each warehouse (I7:I8) cannot be exceeded, and the quantity ordered by each customer (B10:E10) should be delivered. These are  Constrained  cells. What is the goal? The minimal total cost of shipping. And this is our  Objective  cell (C12).

21. More Examples.. Cont’n … The next thing for you to do is to calculate the total quantity shipped from each warehouse (G7:G8), and the total goods received by each customer (B9:E9). You can do this with simple Sum formulas demonstrated in the below screenshot. Also, insert the SUMPRODUCT formula in C12 to calculate the total cost of shipping: our  Objective  cell (C12).

22. More Examples.. Cont’n … The last thing left for you to do is configure the Excel Solver parameters: Objective: Shipping_cost set to  Min Variable cells: Products_shipped Constraints: Total_received = Ordered and Total_shipped <= Available

23. More Examples.. Cont’n … Solution Click the  Solve  button at the bottom of the  Solver Parameters  window, and you will get your answer. In this example, the Excel Solver add-in calculated the optimal quantity of goods to deliver to each customer from each warehouse with the minimal total cost of shipping:

24. Standard Form Max 7x 1 + 5x 2 s.t . 4x 1 + 3x 2 <= 240 2x 1 + 1x 2 <= 100 x 1 >= 0 x 2 >= 0 electronics assembly

25. Constraint coefficients Current value of variables Formulating in Excel

26. Amount of each constraint used by current solution Formulating in Excel

27. RHS of constraints, Inequality signs. Objective Function Coefficients Formulating in Excel Objective Function Value

28. Solving in Excel All we have so far is a big ‘what if” tool. We need to tell the LP Solver that this is an LP that it can solve. Choose ‘Solver’ from ‘Tools’ menu

29. Click “Data” then “Solver”

30. If No Solver, Office2010

31. If No Solver, Office 2007

32. Solving in Excel Choose ‘Solver’ from ‘Data’ tab Tell Solver what is the objective function, and which are variables. Tell Solver to minimize or maximize

33. Solver Dialog Box Set the Target Sell Tell to minimize or maximize Where the variables are

34. Solving in Excel Choose ‘Solver’ from ‘Tools’ menu Tell Solver what is the objective function, and which are variables. Tell Solver to minimize or maximize Add constraints: Click ‘Add’, enter LHS, RHS, choose inequality Click ‘Add’ if you need to do more, or click ‘Ok’ if this is the last one. Add rest of constraints

35. Add Constraint Dialog Box

36. Constraints Added

37. Assuming Linear You have to tell Solver that the model is Linear. Click ‘options,’ and make sure the ‘Assume Linear Model’ box is checked.

38. Assume Linear

39. Assuming Linear You have to tell Solver that the model is Linear. Click ‘options,’ and make sure the ‘Assume Linear Model’ box is checked. On this box, checking “assume non-negative” means you don’t need to actually add the non-negativity constraints manually. Solve the LP: Click ‘Solve.’ Look at Results.

40. Office 2010

41. Office 2010 Options Set maximum time to look for a solution OR maximum # of iterations Our problems should solve quickly

42. Solution is Found When a solution has been found, this box comes up. You can choose between keeping the solution and going back to your original solution. Highlight the reports that you want to look at.

43. Successful Solution

44. Answer Report Gives optimal and initial values of objective function Gives optimal and initial values of variables Tells amount of ‘slack’ between LHS and RHS of each constraint, tells whether constraint is binding.

45. Answer Report

46. Excel Solver algorithms When defining a problem for the Excel Solver, you can choose one of the following methods in the  Select a Solving Method  dropdown box: GRG Nonlinear.  Generalized Reduced Gradient Nonlinear  algorithm is used for problems that are smooth nonlinear, i.e. in which at least one of the constraints is a smooth nonlinear function of the decision variables. More details can be found  here . LP Simplex . The Simplex LP Solving method is based the Simplex algorithm created by an American mathematical scientist George Dantzig . It is used for solving so called  Linear Programming  problems - mathematical models whose requirements are characterized by linear relationships, i.e. consist of a single objective represented by a linear equation that must be maximized or minimized. For more information, please check out  this page . Evolutionary . It is used for non-smooth problems, which are the most difficult type of optimization problems to solve because some of the functions are non-smooth or even discontinuous, and therefore it's difficult to determine the direction in which a function is increasing or decreasing.  For more information, please see  this page .