Solver & Optimization Problems

1. Solver & Optimization Problems • An optimization problem is a problem in which we wish to determine the best values for decision variables that will maximize or minimize a performance measure subject to a set of constraints • A feasible solution is set of values for the decision variables which satisfy all of the constraints • An optimal solution is a feasible solution which achieves the maximization/minimization objective for the performance measure • Solver is an Excel Add-in which can identify the optimal solutions for a correctly defined spreadsheet model

2. Premium Solver for Excel (pg.54-55)

3. Components of an Optimization Problem • Decision Variables: Changing cells, the exogenous cells users experiment with to try to improve the situation and which are under the user’s control • Constraint Cells: The endogenous cells that users watch to make sure that cell values remain in an appropriate range • Objective: Target cell, the endogenous performance measure cell that the user wants to maximize or minimize

4. Influence Chart Notation • Changing Cells: No arrows are directed into these points. They are parameters that are under the manager’s control. (Denoted with squares) • Constraint Cells: Arrows must point into the cell. Changing cells must directly or indirectly influence constraint cells, so an attempt to attain feasibility can be made. (Denote with circles) • Target Cell: Cell that started the influence chart. Arrows must point into the target cell and changing cells must directly or indirectly influence it, so an attempt to optimize the target can be made. (Denote with polygon)

5. Overview of Mathematical Programming Optimization Techniques • Linear Programming: • Continuous values for decision variables • Linear constraints • Single linear objective • Nonlinear Programming: • Continuous values for decision variables • Linear or nonlinear constraints • Single linear or nonlinear objective

6. Overview of Mathematical Programming Optimization Techniques (continued) • Integer Programming: • Integer values for decision variables • Linear constraints • Single linear objective • Goal Programming: • Continuous values for decision variables • Linear or nonlinear constraints • Several linear objectives

7. Linearity • A linear function is where each variable appears in a separate term together with its constant coefficient. • The graph of a linear function of two variables is a straight line • An optimization problem is linear if: • the objective is a linear function of the decision variables • Each constraint cell is a linear function of the decision variables

8. Integrality Considerations • In linear programming, the decision variables are not required to assume only integer values. Therefore often fractional solutions are identified as the optimal solution. • If one or more decision variables need to consider only integer values, the model becomes an integer programming problem. • If possible, fractional solutions can be rounded, interpreted as the average number or work-in-progress or ignored if the model is for planning purposes only

9. Graphical Solutions (for 2 decision variable problems) • Plot all constraints including nonnegativity ones • Determine the feasible region. (The feasible region is the set of feasible solution points) • Identify the optimal solution using either • the isoprofit or isocost line method • the extreme point method which is based on the property that an optimal solution will always exist on at least one of the corner points of the feasible region

10. Types of LP Solutions • Any linear program falls in one of three categories: • is infeasible (the problem is overconstrained so that no solution satisfies all the constraints) • has a unique optimal solution or alternate optimal solutions • has an objective function that can be increased without bound

11. Example: Feasible Problem with Unique Solution • Solve graphically for the optimal solution: Max z = x1 + x2 s.t. 4x1 + 3x2> 12 2x1 + x2< 8 x1, x2> 0

12. x2 2x1 + x2< 8 8 4x1 + 3x2> 12 4 x1 3 4 Example: Unique Optimal Solution • There is one point that satisfies all four constraints, and maximizes the objective. (0,8) is the optimal solution.

13. Solver Result Messages • Solver found a solution. All constraints and optimality conditions are satisfied: Solver has correctly identified an optimal solution for the problem you have formulated. Note that there may be alternative optimal solutions possible however. • Solver has converged to the current solution. All constraints are satisfied: You have not selected the linear programming option in the Solver options. Thus nonlinear programming is being performed and this is the best solution Solver has found so far. It is not guaranteed to be the optimal one however.

14. Example: Infeasible Problem • Solve graphically for the optimal solution: Max z = x1 + x2 s.t. 4x1 + 3x2< 12 2x1 + x2> 8 x1, x2> 0

15. x2 2x1 + x2> 8 8 4x1 + 3x2< 12 4 x1 3 4 Example: Infeasible Problem • There are no points that satisfy both constraints, hence this problem has no feasible region, and no optimal solution.

16. Solver Result Messages • Solver could not find a feasible solution:You may have too many constraints, one of the constraints may be entered wrong (e.g. the inequality sign might be going the wrong way) or you may not have enough changing cells. • Set Cell values do not converge:Your model as formulated is unbounded. One or more constraint is missing from the problem or entered wrong. Often times the modeler has forgotten to check the Assume Nonnegativity option in Solver.

17. Example: Unbounded Problem • Solve graphically for the optimal solution: Max z = 3x1 + 4x2 s.t. x1 + x2> 5 3x1 + x2> 8 x1, x2> 0

18. x2 3x1 + x2> 8 8 x1 + x2> 5 5 Max 3x1 + 4x2 x1 2.67 5 Example: Unbounded Problem • The feasible region is unbounded and the objective function line can be moved parallel to itself without bound so that z can be increased infinitely.

19. Solver Result Messages • The Linearity Conditions required by this Solver Engine are not satisfied: Solver’s preliminary tests indicate that your model is not linear. This may be the case. However sometimes the test fails not due to nonlinearity, but due to poor scaling (e.g. some #s are in % and others in millions). If you think your model is linear, try resolving the model again. Some times Solver can find the solution the second time. If not, use the option in Solver called Use Automatic Scaling. Solver will attempt to rescale your data. If that doesn’t solve the problem, you will need to rescale the data yourself.

20. Solver Result Messages • Solver encountered an error value in a target or constraint cell: Using the optimization technique selected, a cell formula resulted in an error message and the algorithm cannot continue solving the problem. • This can occur if you have a nonlinear formula in a target or constraint cell and you try to solve the problem using the Standard simplex LP technique. Make the formula linear or switch to the Nonlinear solution technique. • This also happens when your formula results in a number that is not real (for instance, when you divide by zero). You will need to fix the logic and then close down and reopen Excel to clear the registry of this error message.

21. Solver Modeling Requirements • All components of the optimization problem must be on the same worksheet. Solver’s settings are saved with the sheet. • To speed up computation time, keep reports, data sets used to calculate parameter values, and other intermediate calculations on a different worksheet. • Solver’s constraint dialog box will not let you enter formulas. All formulas and calculations must be done on the worksheet. The constraint dialog box just compares cells to determine feasibility.

22. Ragsdale’s Chpt 3.9 Investment Problem • Problem: how to design a retirement income portfolio for retirees using corporate bonds? • Client has $750,000 in liquid assets to invest • A list of upcoming bond issues from 6 companies has been identified and is shown in the Investment Problem worksheet of the LPFInancialPlanning.xls spreadsheet. • Restrictions exist regarding how money should be allocated across the 6 bonds in order to control the risk of the portfolio.

23. Retirement Planning Example • You are planning for retirement. At the beginning of each year for the next 40 years, you will contribute some money to your retirement fund. You expect your investment to earn 8% on average per year. • When you retire in 40 years, you plan to withdraw $100,000 per year for 20 years. • How much money should you plan to contribute to your retirement each year? • Refer to the Retirement Planning model in the LPFinancialPlanning.xls spreadsheet