Table of Contents Chapter 10 Nonlinear Programming The

  • Slides: 78
Download presentation
Table of Contents Chapter 10 (Nonlinear Programming) The Challenges of Nonlinear Programming (Section 10.

Table of Contents Chapter 10 (Nonlinear Programming) The Challenges of Nonlinear Programming (Section 10. 1) NLP with Decreasing Marginal Returns: Wyndor (Section 10. 2) NLP with Decreasing Marginal Returns: Portfolio Selection (Section 10. 2) Separable Programming (Section 10. 3) Difficult Nonlinear Programming Problems (Section 10. 4) Evolutionary Solver and Genetic Algorithms (Section 10. 5) 10. 2– 10. 16 10. 17– 10. 21 10. 22– 10. 26 10. 27– 10. 39 10. 40– 10. 41 10. 42– 10. 50 Nonlinear and Separable Programming (UW Lecture) 10. 51– 10. 66 These slides are based upon a lecture from the MBA elective course “Modeling with Spreadsheets” at the University of Washington (as taught by one of the authors). Evolutionary Solver (UW Lecture) 10. 67– 10. 78 These slides are based upon a lecture from the MBA elective course “Modeling with Spreadsheets” at the University of Washington (as taught by one of the authors). Mc. Graw-Hill/Irwin 1 © The Mc. Graw-Hill Companies, Inc. , 2003

Examples of Linear and Nonlinear Formulas Linear Formulas Nonlinear Formulas SUMPRODUCT(D 4: D 6,

Examples of Linear and Nonlinear Formulas Linear Formulas Nonlinear Formulas SUMPRODUCT(D 4: D 6, C 4: C 6) [(D 1 + D 2) / D 3] * C 4 IF(D 2 >= 2, 2*C 3, 3*C 4) SUMIF(D 1: D 6, 4, C 1: C 6) SUM(D 4: D 6) 2*C 1 + 3*C 4 + C 6 C 1 + C 2 + C 3 SUMPRODUCT(C 4: C 6, C 1: C 3) [(C 1 + C 2) / C 3] * D 4 IF(C 2 >= 2, 2*C 3, 3*C 4) SUMIF(C 1: C 6, 4, D 1: D 6) ROUND(C 1) MAX(C 1, 0) MIN(C 1, C 2) ABS(C 1) SQRT(C 1) C 1 * C 2 C 1 / C 2 C 1 ^2 Data cells are located in D 1: D 6 and changing cells are in C 1: C 6. Mc. Graw-Hill/Irwin 2 © The Mc. Graw-Hill Companies, Inc. , 2003

The Challenges of Nonlinear Programming • Nonlinear programming is used to model nonproportional relationships

The Challenges of Nonlinear Programming • Nonlinear programming is used to model nonproportional relationships between activity levels and the overall measure of performance, whereas linear programming assumes a proportional relationship. • Constructing the nonlinear formula(s) needed for a nonlinear programming model is considerably more difficult than developing the linear formulas used in linear programming. • Solving a nonlinear programming model is often much more difficult (if it is possible at all) than solving a linear programming model. Mc. Graw-Hill/Irwin 3 © The Mc. Graw-Hill Companies, Inc. , 2003

The Challenge of Nonproportional Relationships • Proportionality Assumption of Linear Programming: The contribution of

The Challenge of Nonproportional Relationships • Proportionality Assumption of Linear Programming: The contribution of each activity to the value of the objective function is proportional to the level of the activity. In other words, the term in the objective function involving this activity consists of a coefficient times the decision variable. • Nonlinear programming problems arise when any activity has a nonproportional relationship where the contribution of the activity to the measure of performance is not proportional to the level of the activity. Mc. Graw-Hill/Irwin 4 © The Mc. Graw-Hill Companies, Inc. , 2003

Profit Graphs for Wyndor Glass Co. (Proportional Relationship) Mc. Graw-Hill/Irwin 5 © The Mc.

Profit Graphs for Wyndor Glass Co. (Proportional Relationship) Mc. Graw-Hill/Irwin 5 © The Mc. Graw-Hill Companies, Inc. , 2003

Profit Graphs with Nonproportional Relationships Piecewise Linear with Decreasing Marginal Returns Mc. Graw-Hill/Irwin 6

Profit Graphs with Nonproportional Relationships Piecewise Linear with Decreasing Marginal Returns Mc. Graw-Hill/Irwin 6 © The Mc. Graw-Hill Companies, Inc. , 2003

Profit Graphs with Nonproportional Relationships Decreasing Marginal Returns Except for Discontinuities Mc. Graw-Hill/Irwin Increasing

Profit Graphs with Nonproportional Relationships Decreasing Marginal Returns Except for Discontinuities Mc. Graw-Hill/Irwin Increasing Marginal Returns 7 © The Mc. Graw-Hill Companies, Inc. , 2003

Constructing a Nonlinear Formula Mc. Graw-Hill/Irwin 8 © The Mc. Graw-Hill Companies, Inc. ,

Constructing a Nonlinear Formula Mc. Graw-Hill/Irwin 8 © The Mc. Graw-Hill Companies, Inc. , 2003

Add Trendline Dialogue Box Mc. Graw-Hill/Irwin 9 © The Mc. Graw-Hill Companies, Inc. ,

Add Trendline Dialogue Box Mc. Graw-Hill/Irwin 9 © The Mc. Graw-Hill Companies, Inc. , 2003

Add Trendline Options Mc. Graw-Hill/Irwin 10 © The Mc. Graw-Hill Companies, Inc. , 2003

Add Trendline Options Mc. Graw-Hill/Irwin 10 © The Mc. Graw-Hill Companies, Inc. , 2003

The Trendline (Quadratic Equation) Mc. Graw-Hill/Irwin 11 © The Mc. Graw-Hill Companies, Inc. ,

The Trendline (Quadratic Equation) Mc. Graw-Hill/Irwin 11 © The Mc. Graw-Hill Companies, Inc. , 2003

Solving Nonlinear Programming Models Consider the following model in algebraic form: Maximize Profit =

Solving Nonlinear Programming Models Consider the following model in algebraic form: Maximize Profit = 0. 5 x 5 – 6 x 4 + 24. 5 x 3 – 39 x 2 + 20 x subject to x ≤ 5 x ≥ 0 Mc. Graw-Hill/Irwin 12 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Solution Starting with x = 0 Mc. Graw-Hill/Irwin 13 © The Mc. Graw-Hill

Solver Solution Starting with x = 0 Mc. Graw-Hill/Irwin 13 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Solution Starting with x = 3 Mc. Graw-Hill/Irwin 14 © The Mc. Graw-Hill

Solver Solution Starting with x = 3 Mc. Graw-Hill/Irwin 14 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Solution Starting with x = 4. 7 Mc. Graw-Hill/Irwin 15 © The Mc.

Solver Solution Starting with x = 4. 7 Mc. Graw-Hill/Irwin 15 © The Mc. Graw-Hill Companies, Inc. , 2003

The Profit Graph Mc. Graw-Hill/Irwin 16 © The Mc. Graw-Hill Companies, Inc. , 2003

The Profit Graph Mc. Graw-Hill/Irwin 16 © The Mc. Graw-Hill Companies, Inc. , 2003

Original Wyndor Glass Co. Spreadsheet Mc. Graw-Hill/Irwin 17 © The Mc. Graw-Hill Companies, Inc.

Original Wyndor Glass Co. Spreadsheet Mc. Graw-Hill/Irwin 17 © The Mc. Graw-Hill Companies, Inc. , 2003

Wyndor Glass with Marketing Costs • Market research indicates that Wyndor could sell small

Wyndor Glass with Marketing Costs • Market research indicates that Wyndor could sell small numbers of doors and windows with no advertising. However, extensive advertising would be required to sell all that could be produced. • A curve-fitting procedure was used to estimate the weekly marketing costs required to sustain a production rate of D doors and W windows: – Marketing cost for doors = $25 D 2 – Marketing costs for windows = ($662/3)W 2 • The gross profit per door sold is about $375, and the gross profit per window is about $700. Therefore, the net profits are as follows: – Net profit for doors = $375 D – $25 D 2 Net profit for windows = $700 W – ($662/3)W 2 • Thus, the revised objective function is Maximize Profit = $375 D – 25 D 2 + $700 W –($662/3)W 2 Question: Considering the nonlinear marketing costs, how many doors and windows should Wyndor produce? Mc. Graw-Hill/Irwin 18 © The Mc. Graw-Hill Companies, Inc. , 2003

Profit Graphs for Doors and Windows Mc. Graw-Hill/Irwin 19 © The Mc. Graw-Hill Companies,

Profit Graphs for Doors and Windows Mc. Graw-Hill/Irwin 19 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Formulation Mc. Graw-Hill/Irwin 20 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Formulation Mc. Graw-Hill/Irwin 20 © The Mc. Graw-Hill Companies, Inc. , 2003

Graphical Display of Nonlinear Formulation Mc. Graw-Hill/Irwin 21 © The Mc. Graw-Hill Companies, Inc.

Graphical Display of Nonlinear Formulation Mc. Graw-Hill/Irwin 21 © The Mc. Graw-Hill Companies, Inc. , 2003

Portfolio Selection • It is now common practice for professional managers of large stock

Portfolio Selection • It is now common practice for professional managers of large stock portfolios to use computer models based on nonlinear programming to guide them. • Investors are concerned about both the expected return and the risk. • One way of formulating their approach is as a nonlinear version of a costbenefit trade-off problem: – Minimize Risk subject to Expected return ≥ Minimum acceptable level • Consider a portfolio with 3 stocks. Question: What is the portfolio that will minimize the risk subject to achieving at least an 18% expected return? Mc. Graw-Hill/Irwin 22 © The Mc. Graw-Hill Companies, Inc. , 2003

Data for Stocks Stock Expected Return Risk (Standard Deviation) Pair of Stocks Joint Risk

Data for Stocks Stock Expected Return Risk (Standard Deviation) Pair of Stocks Joint Risk per Stock (Covariance) 1 21% 25% 1 and 2 0. 040 2 30 45 1 and 3 – 0. 005 3 8 5 2 and 3 – 0. 010 Mc. Graw-Hill/Irwin 23 © The Mc. Graw-Hill Companies, Inc. , 2003

Algebraic Formulation Minimize Risk = (0. 25 S 1)2+(0. 45 S 2)2+(0. 05 S

Algebraic Formulation Minimize Risk = (0. 25 S 1)2+(0. 45 S 2)2+(0. 05 S 3)2+2(0. 04)S 1 S 2+2(– 0. 005)S 1 S 3+2(– 0. 01)S 2 S 3 subject to (21%)S 1 + (30%)S 2 + (8%)S 3 ≥ 18% S 1 + S 2 + S 3 = 100% and S 1 ≥ 0, S 2 ≥ 0, S 3 ≥ 0. Mc. Graw-Hill/Irwin 24 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Model Mc. Graw-Hill/Irwin 25 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Model Mc. Graw-Hill/Irwin 25 © The Mc. Graw-Hill Companies, Inc. , 2003

Using Solver Table to Examine Trade-Offs Between Expected Return and Risk Mc. Graw-Hill/Irwin 26

Using Solver Table to Examine Trade-Offs Between Expected Return and Risk Mc. Graw-Hill/Irwin 26 © The Mc. Graw-Hill Companies, Inc. , 2003

Wyndor Glass When Overtime is Needed • Wyndor Glass has accepted a special order

Wyndor Glass When Overtime is Needed • Wyndor Glass has accepted a special order for hand-crafted goods to be made in plants 1 and 2 throughout the next four months. • Filling this order will require borrowing certain employees from the work crews of regular products. • The remaining workers will need to work overtime to utilize the full production capacity of each plant’s machinery for the regular products. • The original constraints of Hours Used ≤ Hours Available are still valid. However, the objective function will need to be modified because of the additional cost of using overtime work. • In particular, because of the additional cost, the profit per unit will be reduced for those units that require overtime. Question: Considering overtime costs, how many doors and windows should Wyndor produce? Mc. Graw-Hill/Irwin 27 © The Mc. Graw-Hill Companies, Inc. , 2003

Data for Wyndor When Overtime is Needed Maximum Weekly Production Product Regular Time Overtime

Data for Wyndor When Overtime is Needed Maximum Weekly Production Product Regular Time Overtime Doors 3 Windows 3 Profit per Unit Produced Total Regular Time Overtime 1 4 $300 $200 3 6 500 100 (and 3 D + 2 W ≤ 18) Mc. Graw-Hill/Irwin 28 © The Mc. Graw-Hill Companies, Inc. , 2003

Profit Graphs for Doors and Windows Mc. Graw-Hill/Irwin 29 © The Mc. Graw-Hill Companies,

Profit Graphs for Doors and Windows Mc. Graw-Hill/Irwin 29 © The Mc. Graw-Hill Companies, Inc. , 2003

The Separable Programming Technique • For each activity that violates the proportionality assumption, separate

The Separable Programming Technique • For each activity that violates the proportionality assumption, separate its profit graph into parts, with a line segment in each part. • Then, instead of using a single decision variable to represent the level of each such activity, introduce a separate new decision variable for each line segment on that activity’s profit graph. • Since the proportionality assumption holds for these new decision variables, formulate a linear programming model in terms of these variables. • For the Wyndor problem, these new decision variables are – DR = Number of doors produced per week on regular time – DO = Number of doors produced per week on overtime – WR = Number of windows produced per week on regular time WO = Number of windows produced per week on overtime Mc. Graw-Hill/Irwin 30 © The Mc. Graw-Hill Companies, Inc. , 2003

Separable Programming Spreadsheet Model Mc. Graw-Hill/Irwin 31 © The Mc. Graw-Hill Companies, Inc. ,

Separable Programming Spreadsheet Model Mc. Graw-Hill/Irwin 31 © The Mc. Graw-Hill Companies, Inc. , 2003

Separable Programming with Smooth Profit Graphs Mc. Graw-Hill/Irwin 32 © The Mc. Graw-Hill Companies,

Separable Programming with Smooth Profit Graphs Mc. Graw-Hill/Irwin 32 © The Mc. Graw-Hill Companies, Inc. , 2003

Advantages of Separable Programming • The Excel Solver can readily solve nonlinear problems that

Advantages of Separable Programming • The Excel Solver can readily solve nonlinear problems that have decreasing marginal returns, with the advantage that no approximation is needed. • However, the separable programming approach also has certain advantages: – Converting the problem into a linear programming problem tends to make it quicker to solve, which can be very helpful for large problems. – A linear programming formulation makes available Solver’s Sensitivity Report. – Separable programming only requires estimating the profit from each activity at a few points. Therefore, it is not necessary to use a curve fitting method to estimate the formula for the profit graph. Mc. Graw-Hill/Irwin 33 © The Mc. Graw-Hill Companies, Inc. , 2003

Wyndor Problem with Both Overtime Costs and Nonlinear Marketing Costs • The previous spreadsheet

Wyndor Problem with Both Overtime Costs and Nonlinear Marketing Costs • The previous spreadsheet model does not include nonlinear marketing costs. • Recall that the curve-fitting procedure was used to estimate the weekly marketing costs required to sustain a production rate of D doors and W windows: – Marketing cost for doors = $25 D 2 – Marketing costs for windows = ($662/3)W 2 Question: Considering both overtime costs and nonlinear marketing costs, how many doors and windows should Wyndor produce? Mc. Graw-Hill/Irwin 34 © The Mc. Graw-Hill Companies, Inc. , 2003

Data for Wyndor with Overtime Costs and Nonlinear Marketing Costs Maximum Weekly Production Regular

Data for Wyndor with Overtime Costs and Nonlinear Marketing Costs Maximum Weekly Production Regular Time Overtime Doors 3 Windows 3 Product Mc. Graw-Hill/Irwin Gross Unit Profit Total Regular Time Overtime Marketing Costs 1 4 $375 $25 D 2 3 6 700 300 662/3 W 2 35 © The Mc. Graw-Hill Companies, Inc. , 2003

Weekly Profit from Producing Doors D Gross Profit Marketing Costs Profit Incremental Profit 0

Weekly Profit from Producing Doors D Gross Profit Marketing Costs Profit Incremental Profit 0 $0 $0 $0 — 1 375 25 350 2 750 100 650 300 3 1, 125 225 900 250 4 1, 400 1, 000 100 Mc. Graw-Hill/Irwin 36 © The Mc. Graw-Hill Companies, Inc. , 2003

Weekly Profit from Producing Windows W Gross Profit Marketing Costs Profit Incremental Profit 0

Weekly Profit from Producing Windows W Gross Profit Marketing Costs Profit Incremental Profit 0 $0 $0 $0 — 1 700 662/3 6331/3 2 1, 400 2662/3 1, 1331/3 500 3 2, 100 600 1, 500 3662/3 4 2, 400 1, 0662/3 1, 3331/3 – 1662/3 5 2, 700 1, 6662/3 1, 0331/3 – 300 6 3, 000 2, 400 600 – 4331/3 Mc. Graw-Hill/Irwin 37 © The Mc. Graw-Hill Companies, Inc. , 2003

Separable Programming Spreadsheet Model Mc. Graw-Hill/Irwin 38 © The Mc. Graw-Hill Companies, Inc. ,

Separable Programming Spreadsheet Model Mc. Graw-Hill/Irwin 38 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonlinear Programming Spreadsheet Model Mc. Graw-Hill/Irwin 39 © The Mc. Graw-Hill Companies, Inc. ,

Nonlinear Programming Spreadsheet Model Mc. Graw-Hill/Irwin 39 © The Mc. Graw-Hill Companies, Inc. , 2003

Difficult Nonlinear Programming Problems • Even if a model has a nonlinear objective function,

Difficult Nonlinear Programming Problems • Even if a model has a nonlinear objective function, so long as the model has certain properties (e. g. , linear constraints, decreasing marginal returns), the Solver can easily find an optimal solution. • In some cases separable programming can be used to model a nonlinear problem in such a way that linear programming can be used. • However, if a problem has increasing marginal returns, or nonlinear functions in the constraints, or disconnected profit graphs, finding a solution is often much more difficult. – such problems may have many local optima – Solver can get stuck at local optima, rather than finding the global optimum • One approach with such problems is to solve the problem many times, each time starting with a different initial solution. – Solver Table can be used to do this process more systematically when there are only one or two variables. Mc. Graw-Hill/Irwin 40 © The Mc. Graw-Hill Companies, Inc. , 2003

Using Solver Table to Try Different Starting Points Mc. Graw-Hill/Irwin 41 © The Mc.

Using Solver Table to Try Different Starting Points Mc. Graw-Hill/Irwin 41 © The Mc. Graw-Hill Companies, Inc. , 2003

Evolutionary Solver and Genetic Algorithms • Evolutionary Solver uses an entirely different approach than

Evolutionary Solver and Genetic Algorithms • Evolutionary Solver uses an entirely different approach than the standard Solver to search for an optimal solution for a model. • The philosophy of Evolutionary Solver is based on genetics, evolution and the survival of the fittest. Hence, this type of algorithm is sometimes called a genetic algorithm. • The standard Solver starts with a single solution, and then moves in directions that will improve this solution. Evolutionary Solver begins by randomly generating a whole population of solutions. • After generating the population, Evolutionary Solver creates a new generation by pairing off solutions in the population to create “offspring”, combining some elements from each parent. Mc. Graw-Hill/Irwin 42 © The Mc. Graw-Hill Companies, Inc. , 2003

Evolutionary Solver and Genetic Algorithms • Among solutions in the population, some will be

Evolutionary Solver and Genetic Algorithms • Among solutions in the population, some will be good (or “fit”) and some will be bad (or “unfit”), as measured by evaluating the objective function. Borrowing from the principles of evolution and survival of the fittest, the “fit” members are allowed to reproduce more frequently than the unfit members. • Another key feature is mutation. Like gene mutation in biology, Evolutionary Solver will occasionally make a random change in a member of the population. This helps the algorithm get unstuck if it is getting trapped near a local optimum. • Evolutionary Solver keeps creating new generations of solutions until there have been no improvements for several consecutive generations. Mc. Graw-Hill/Irwin 43 © The Mc. Graw-Hill Companies, Inc. , 2003

Selecting a Portfolio to Beat the Market • A common goal of portfolio managers

Selecting a Portfolio to Beat the Market • A common goal of portfolio managers is to beat the market. • If we assume that past performance is somewhat of an indicator of the future, then picking a portfolio that beat the market most often in the past might yield a portfolio that will more than likely beat the market in the future. • Consider a portfolio of five large stocks traded on the New York Stock Exchange (NYSE): – – – America Online (AOL) Boeing (BA) Ford (F) Procter & Gamble (PG) Mc. Donald’s (MCD) Question: What mix of these five stocks will yield a portfolio that is likely to beat the market in the future? Mc. Graw-Hill/Irwin 44 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Model Mc. Graw-Hill/Irwin 45 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Model Mc. Graw-Hill/Irwin 45 © The Mc. Graw-Hill Companies, Inc. , 2003

Premium Solver Dialogue Box Mc. Graw-Hill/Irwin 46 © The Mc. Graw-Hill Companies, Inc. ,

Premium Solver Dialogue Box Mc. Graw-Hill/Irwin 46 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Options Dialogue Box Mc. Graw-Hill/Irwin 47 © The Mc. Graw-Hill Companies, Inc. ,

Solver Options Dialogue Box Mc. Graw-Hill/Irwin 47 © The Mc. Graw-Hill Companies, Inc. , 2003

Limit Options Dialogue Box Mc. Graw-Hill/Irwin 48 © The Mc. Graw-Hill Companies, Inc. ,

Limit Options Dialogue Box Mc. Graw-Hill/Irwin 48 © The Mc. Graw-Hill Companies, Inc. , 2003

Evolutionary Solver Spreadsheet Solution Mc. Graw-Hill/Irwin 49 © The Mc. Graw-Hill Companies, Inc. ,

Evolutionary Solver Spreadsheet Solution Mc. Graw-Hill/Irwin 49 © The Mc. Graw-Hill Companies, Inc. , 2003

Advantages and Disadvantages of Evolutionary Solver • Evolutionary Solver has two significant advantages over

Advantages and Disadvantages of Evolutionary Solver • Evolutionary Solver has two significant advantages over the standard Solver for solving difficult nonlinear programming problems: – The complexity of the objective function does not matter. As long as the function can be evaluated for a given candidate solution (to determine the level of fitness), it does not matter if the function has kinks, discontinuities, or many local optima. – By evaluating whole populations of candidate solutions, Evolutionary Solver keeps from getting trapped at a local optimum. Even if the whole population evolves toward a locally optimal solution, mutation allows the possibility of getting unstuck. • However, Evolutionary Solver is not a panacea. – It can take much longer that standard Solver to find a final solution. – Evolutionary Solver does not perform well on models that have many constraints. – Evolutionary Solver is a random process. Running it again on the same model usually will yield a different solution. – The best solution found is typically not optimal (although it may be very close). Mc. Graw-Hill/Irwin 50 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonlinear Programming Consider the following model for a nonlinear programming problem: Maximize Profit =

Nonlinear Programming Consider the following model for a nonlinear programming problem: Maximize Profit = 0. 5 x 5 – 6 x 4 + 24. 5 x 3 – 39 x 2 + 20 x subject to 0 ≤ x ≤ 5 Mc. Graw-Hill/Irwin 51 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Solution Starting with x = 0 Mc. Graw-Hill/Irwin 52 © The Mc. Graw-Hill

Solver Solution Starting with x = 0 Mc. Graw-Hill/Irwin 52 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Solution Starting with x = 3 Mc. Graw-Hill/Irwin 53 © The Mc. Graw-Hill

Solver Solution Starting with x = 3 Mc. Graw-Hill/Irwin 53 © The Mc. Graw-Hill Companies, Inc. , 2003

Solver Solution Starting with x = 4. 7 Mc. Graw-Hill/Irwin 54 © The Mc.

Solver Solution Starting with x = 4. 7 Mc. Graw-Hill/Irwin 54 © The Mc. Graw-Hill Companies, Inc. , 2003

The Profit Graph Mc. Graw-Hill/Irwin 55 © The Mc. Graw-Hill Companies, Inc. , 2003

The Profit Graph Mc. Graw-Hill/Irwin 55 © The Mc. Graw-Hill Companies, Inc. , 2003

Problems That Solver will Solver Correctly • A maximization problem with linear constraints and

Problems That Solver will Solver Correctly • A maximization problem with linear constraints and a concave objective function. A Concave Function Mc. Graw-Hill/Irwin 56 © The Mc. Graw-Hill Companies, Inc. , 2003

Problems That Solver will Solver Correctly • A minimization problem with linear constraints and

Problems That Solver will Solver Correctly • A minimization problem with linear constraints and a convex objective function. A Convex Function Mc. Graw-Hill/Irwin 57 © The Mc. Graw-Hill Companies, Inc. , 2003

Quality Furniture Corporation • The Quality Furniture Corporation manufactures two products: benches and tables.

Quality Furniture Corporation • The Quality Furniture Corporation manufactures two products: benches and tables. • They employ three carpenters. During the next week, 120 hours of labor are available at regular wages ($8 per hour). • Up to 30 hours of overtime can be used at a wage rate of $12 per hour. • Up to 30 hours of weekend time can be utilized at a wage rate of $16 per hour. • 540 pounds of wood is available at a cost of $2 per pound. • Each bench requires 3 labor hours and 12 pounds of wood. Each table requires 6 labor hours and 38 pounds of wood. • Completed benches sell for $80 each, and tables sell for $200 each. Question: How many benches and how many tables should be produced? Mc. Graw-Hill/Irwin 58 © The Mc. Graw-Hill Companies, Inc. , 2003

Outdoor Furniture Labor Costs Mc. Graw-Hill/Irwin 59 © The Mc. Graw-Hill Companies, Inc. ,

Outdoor Furniture Labor Costs Mc. Graw-Hill/Irwin 59 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonlinear Programming Spreadsheet Mc. Graw-Hill/Irwin 60 © The Mc. Graw-Hill Companies, Inc. , 2003

Nonlinear Programming Spreadsheet Mc. Graw-Hill/Irwin 60 © The Mc. Graw-Hill Companies, Inc. , 2003

Outdoor Furniture Labor Costs Mc. Graw-Hill/Irwin 61 © The Mc. Graw-Hill Companies, Inc. ,

Outdoor Furniture Labor Costs Mc. Graw-Hill/Irwin 61 © The Mc. Graw-Hill Companies, Inc. , 2003

Separable Programming Spreadsheet Mc. Graw-Hill/Irwin 62 © The Mc. Graw-Hill Companies, Inc. , 2003

Separable Programming Spreadsheet Mc. Graw-Hill/Irwin 62 © The Mc. Graw-Hill Companies, Inc. , 2003

Advertising Example Mc. Graw-Hill/Irwin 63 © The Mc. Graw-Hill Companies, Inc. , 2003

Advertising Example Mc. Graw-Hill/Irwin 63 © The Mc. Graw-Hill Companies, Inc. , 2003

The Sales Function Mc. Graw-Hill/Irwin 64 © The Mc. Graw-Hill Companies, Inc. , 2003

The Sales Function Mc. Graw-Hill/Irwin 64 © The Mc. Graw-Hill Companies, Inc. , 2003

Approximating a Nonlinear Function Mc. Graw-Hill/Irwin 65 © The Mc. Graw-Hill Companies, Inc. ,

Approximating a Nonlinear Function Mc. Graw-Hill/Irwin 65 © The Mc. Graw-Hill Companies, Inc. , 2003

Advertising Example Using Separable Programming Mc. Graw-Hill/Irwin 66 © The Mc. Graw-Hill Companies, Inc.

Advertising Example Using Separable Programming Mc. Graw-Hill/Irwin 66 © The Mc. Graw-Hill Companies, Inc. , 2003

Evolutionary Solver • The standard Solver has difficulty with problems that are – –

Evolutionary Solver • The standard Solver has difficulty with problems that are – – • highly nonlinear are not smooth (have “kinks” in the objective) have discontinuities (the objective jumps in value) have many local optima (many hills and valleys) Excel functions like IF, MAX, ABS, ROUND, etc. , tend to cause one or more of these problems. Mc. Graw-Hill/Irwin 67 © The Mc. Graw-Hill Companies, Inc. , 2003

Premium Solver • Included on the textbook CD is the “Premium Solver”. After installing,

Premium Solver • Included on the textbook CD is the “Premium Solver”. After installing, a new button (“Premium”) is added to Solver. Mc. Graw-Hill/Irwin 68 © The Mc. Graw-Hill Companies, Inc. , 2003

Premium Solver • Clicking on the “Premium” button switches to Premium Solver, which gives

Premium Solver • Clicking on the “Premium” button switches to Premium Solver, which gives the option of three different solvers. – Standard GRG Nonlinear is equivalent to the regular Solver without choosing “Assume Linear Model”. – Standard Simplex LP is equivalent to the regular Solver with choosing “Assume Linear Model”. – Standard Evolutionary uses a genetic evolutionary algorithm that is only available with Premium Solver. Mc. Graw-Hill/Irwin 69 © The Mc. Graw-Hill Companies, Inc. , 2003

How Genetic Algorithms (Evolutionary Solver) Work Genetic algorithms (such as Evolutionary Solver) use principles

How Genetic Algorithms (Evolutionary Solver) Work Genetic algorithms (such as Evolutionary Solver) use principles from theory of evolution. • The Population: a large set of random solutions is generated. • Level of fitness: each member of the population (solution) is evaluated to determine its level of “fitness” (value of objective). • Evolution: a new generation (set of solutions) is created as follows: – Reproduction: pairs reproduce and create new solutions that share some properties of each. – Survival of the Fittest: more “fit” solutions reproduce more frequently, less “fit” solutions are allowed to die out. – Mutation: occasionaly random “mutations” are introduced. Mc. Graw-Hill/Irwin 70 © The Mc. Graw-Hill Companies, Inc. , 2003

Inventory Ordering Policy with Quantity Discounts • Consider a manufacturer that orders a given

Inventory Ordering Policy with Quantity Discounts • Consider a manufacturer that orders a given part from a supplier. • They require 10, 000 parts per year. • There is a cost associated with each order (due to processing, receiving costs, fixed shipping costs, etc. ) of $20. • The cost of holding a part in inventory is estimated at $4 per year. • The supplier of this part offers quantity discounts on the purchasing cost of this part according to the following schedule. Mc. Graw-Hill/Irwin Order Quantity Purchase Price (per unit) 1– 99 $10. 00 100– 499 9. 80 500– 999 9. 70 1, 000– 9, 999 9. 60 10, 000+ 9. 50 71 © The Mc. Graw-Hill Companies, Inc. , 2003

Total Annual Cost • Recall from Operations Management class that the total annual cost

Total Annual Cost • Recall from Operations Management class that the total annual cost (including puchasing, ordering, and holding costs) is Total Annual Cost = Dp + (Q / 2)H + (D / Q)S where D = Annual demand p = purchase cost Q = order size H = annual holding cost per unit S = ordering cost Mc. Graw-Hill/Irwin 72 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Model Mc. Graw-Hill/Irwin 73 © The Mc. Graw-Hill Companies, Inc. , 2003

Spreadsheet Model Mc. Graw-Hill/Irwin 73 © The Mc. Graw-Hill Companies, Inc. , 2003

Attempts with Standard Solver Various “solutions” provided by the standard Solver, depending on the

Attempts with Standard Solver Various “solutions” provided by the standard Solver, depending on the starting point: Starting Point (Q) Solution (Q*) Cost 1 1, 000 $194, 400 200 500 195, 800 447 197, 789 600 500 195, 800 1, 200 1, 000 194, 400 11, 000 10, 000 210, 040 12, 000 194, 400 Mc. Graw-Hill/Irwin 74 © The Mc. Graw-Hill Companies, Inc. , 2003

Cost Function with Quantity Discounts Mc. Graw-Hill/Irwin 75 © The Mc. Graw-Hill Companies, Inc.

Cost Function with Quantity Discounts Mc. Graw-Hill/Irwin 75 © The Mc. Graw-Hill Companies, Inc. , 2003

Solving with the Evolutionary Solver Mc. Graw-Hill/Irwin 76 © The Mc. Graw-Hill Companies, Inc.

Solving with the Evolutionary Solver Mc. Graw-Hill/Irwin 76 © The Mc. Graw-Hill Companies, Inc. , 2003

Tips on Using Evolutionary Solver • Bounding all of the variables greatly aids the

Tips on Using Evolutionary Solver • Bounding all of the variables greatly aids the Evolutionary Solver by decreasing the search space. • The limit options should be increased (Max Time, Max Subproblems, and Max Feasible Sols) for challenging problems. Setting Tolerance to 0. 0005 and Max Time Without Improvements to 30 will ensure the algorithm will stop if the Target Cell value has improved less than 0. 05% in the last 30 seconds. • Experiment with different populations sizes and mutation rates to see what works well. I have found that higher than default mutation rates can be helpful in problems with lots of local optima. • The Evolutionary Solver can take a very long time, but it will usually find a good solution. Mc. Graw-Hill/Irwin 77 © The Mc. Graw-Hill Companies, Inc. , 2003

Tips on Using Evolutionary Solver • There is no guarantee that Evolutionary Solver will

Tips on Using Evolutionary Solver • There is no guarantee that Evolutionary Solver will find the best solution. • The Evolutionary Solver performs well even with nasty objective functions, but is not very efficient at handling constraints. • Much of the solution process is driven by random numbers that direct the search. Thus, two people running Evolutionary Solver on the same model may get different results. • Once Evolutionary Solver has found a good solution, you can use GRG Nonlinear Solver (the nonlinear algorithm that is included with the Premium Solver software) to try to find a slightly better solution. Mc. Graw-Hill/Irwin 78 © The Mc. Graw-Hill Companies, Inc. , 2003