3 Components for a Spreadsheet Optimization Problem

1. 3 Components for a Spreadsheet Optimization Problem • There is one cell which can be identified as the Target or Set Cell, the single objective of the problem that is to be maximized or minimized. • There is at least one cell that is limited to assume values over specified ranges. These cells are referred to as the Constraint Cells of the problem. • There is at least one Changing or Variable Cell (decision variable). The set of changing cells must influence the target cell and all of the constraint cells. The changing cells should not have formulas entered in the cells.

2. Linear Programming (LP) • A mathematical programming problem is one that seeks to maximize or minimize an objective function subject to constraints. • If both the objective function and the constraints are linear, the problem is referred to as a linear programming problem.

3. Linearity • Linear functions are functions in which each variable appears in a separate term raised to the first power and is multiplied by a constant (which could be 0). • Linear constraints are linear functions that are restricted to be "less than or equal to", "equal to", or "greater than or equal to" a constant.

4. CPCC Algebraic Formulation Maximize 900 X1 + 600 X2 Subject to 2 X1 + X2 <= 20,000 drives 4 X1 + X2 <= 32,000 hours 2 X1 + 7 X2 <= 88,000 tests X1 >= 0, X2 >= 0 Where X1 = # desktops to produce X2 = # laptops to produce

5. Linear Programming Applications • Production Planning: • several products • multiperiod demand • limited period resources • want minimal production costs or maximum profitability • Blending Problems: • different ingredients • product requirements • want minimal production costs

6. Linear Programming Applications • Investment Planning: • several investment alternatives • risk and capital restrictions • want maximum expected return • Labor Scheduling: • full-time and part-time workforce • multi-period staffing requirements • workforce staffing restrictions • want minimum total labor cost

7. LP Terminology • The maximization or minimization of some quantity is the objective in all linear programming problems. • A feasible solution is an assignment of values to the decision variables that satisfies all the problem's constraints. • An optimal solution is a feasible solution that results in the largest possible objective function value, z, when maximizing or smallest z when minimizing. • A graphical solution method can be used to solve a linear program with two variables.

8. Graphical Solutions • 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

9. Properties of LP Solutions • The feasible region for a two-variable linear programming problem can be nonexistent, a single point, a line, a polygon, or an unbounded area. • Assuming that a feasible region exists, an optimal solution will always occur at one or more of the extreme points, where an extreme point is a corner point of the feasible region. The constraints that determine that extreme point are binding constraints. • In the graphical method, if the objective function line is parallel to a boundary constraint in the direction of optimization, there are alternate optimal solutions, with all points on this line segment being optimal.

10. Binding Constraints • The slack of a resource constraint is the amount of the corresponding resource that will not be used if the optimal solution is implemented. • The surplus of a minimum requirement constraint is the amount by which the requirement will be exceeded if the optimal solution is implemented. • A binding constraint implies that the corresponding resource or requirement is restricting the optimal solution and objective value. A binding constraint has slack or surplus of zero. • A nonbinding constraint is one in which there is positive slack or surplus when evaluated at the optimal solution.

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

12. 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 Assume Linear Model option or Simplex LP solving method 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.

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

14. 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.

15. Solver Infeasible Problem Message • 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), a cell formula in the spreadsheet may be missing or you may not have enough changing cells.

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

17. 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.

18. Solver Unbounded Result Message • Set Cell values do not converge: Your model as formulated is unbounded. One or more constraints are missing from the problem or entered wrong. Often times the modeler has forgotten to check the Assume Nonnegativity option in Solver.

19. Solver Result Messages • The conditions for Assume Linear Model 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). Use the option in Solver called Use Automatic Scaling. Solver will attempt to rescale your data. If that doesn’t solve the problem, you can try rescaling the data yourself.