Managerial Decision Modeling with Spreadsheets

1. Managerial Decision Modeling with Spreadsheets Chapter 6 Integer, Goal, and Nonlinear Programming Models

2. Learning Objectives • Formulate integer programming (IP) models. • Set up and solve IP models using Excel’s Solver. • Understand difference between general integer and binary integer variables • Understand use of binary integer variables in formulating problems involving fixed (or setup) costs. • Formulate goal programming problems and solve them using Excel’s Solver. • Formulate nonlinear programming problems and solve them using Excel’s Solver

3. 6.1 Introduction Other important mathematical models that will allow one to relax each of the basic LP conditions. • integer programming, • goal programming, and • nonlinear programming.

4. Integer Programming Models • Some business problems can be solved only if variables haveinteger values. • Airline decides number of flights to operate on given sector must be an integer or whole number amount. • Two types of integer variables: • general integer variables and • binary variables. • General integer variables can take on any non-negative, integer value that satisfies all constraints in model. • Binary variables can only take on either of two values: 0 or 1.

5. Types of Integer Programming Problems • Pure integer programming problems. • All decision variables must have integer solutions. • Mixed integer programming problems. • Some, but not all, decision variables must have integer solutions. • Non-integer variables can have fractional optimal values. • Pure binary (or Zero - One) integer programming problems. • Alldecision variables are of special type known as binary. • Variables must have solution values of either 0 or 1. • Mixed binary integer programming problems. • Some decision variables are binary, and other decision variables are either general integer or continuous valued.

6. Goal Programming Models • Major limitation of LP is it forces decision maker to state one objective only. • But what if business has several objectives? Management may indeed want: • to maximize profit, • to maximize market share, • to maintain full employment, and • to minimize costs. • Many of these goals can conflict and be difficult to quantify.

7. Nonlinear Programming Models • LP applied only to cases in which constraints and objective function are linear. • Price of various products may be a function of number of units produced. • As more are made, the price per unit decreases. Objective: maximize profit = 25X - 0.4X2 + 30Y - 0.5Y2 • Objective function is nonlinear programming problem.

8. 6.2 Models With General Integer Variables • A model with general integer variables (IP) has objective function and constraints identical to LP models. • No real difference in basic procedure for formulating an IP model and LP model. • Only additional requirement in IP model is one or more of decision variables have to take on integer values in optimal solution. • Actual value of this integer variable is limited by the model constraints. • Values such as 0, 1, 2, 3, etc. are perfectly valid for these variables as long as these values satisfy all model constraints.

9. General Integer Variable (IP) Harrison Electric Company • Produces two expensive products popular with renovators of historic old homes: • Ornate chandeliers (C) and • Old-fashioned ceiling fans (F). • Two-step production process: • Wiring ( 2 hours per chandelier and 3 hours per ceiling fan). • Final assembly time (6 hours per chandelier and 5 hours per fan).

10. General Integer Variable (IP) Harrison Electric Company • Production capability this period: • 12 hours of wiring time available and • 30 hours of final assembly time available. • Profits: • Chandelier profit $600 / unit and • Fan profit $700 / unit.

11. The Integer Programming Model Harrison Electric Company Objective: maximize profit = $600C + $700F subject to 2C + 3F <= 12 (wiring hours) 6C + 5F <= 30 (assembly hours) C, F >= 0   where C = number of chandeliers produced F = number of ceiling fans produced • Two decision variables and two constraints. • Graphical LP approach used to generate optimal solution.

12. Graphical LP Solution Harrison Electric Company

13. Graphical LP Solution Harrison Electric Company • Shaded region 1 shows feasible region for LP problem. • Optimal corner point solution: C = 3.75 chandeliers and F = 1.5 ceiling fans. • Profit of $3,300 during production period.  • Produce and sell integer values of product. • Figure shows all possible integer solutions for this problem.

14. Enumeration of All Integer Solutions Harrison Electric Company

15. Enumeration of All Integer Solutions Harrison Electric Company • Table lists entire set of integer-valued solutions for problem. • By inspecting right-hand column, optimal integer solution is: C= 3 chandeliers, F= 2 ceiling fans. • Total profit = $3,200. • Rounded off solution: C = 4 F = 1 Total profit = $3,100.

16. General Integer Variables Excel Solver Solution

17. Solver Options

18. Premium Solver for Education

19. Solver Options • Maximum Time Allowed. • Max Time option set to 100 seconds default value. • Tolerance of the Optimal Solution. • Toleranceoption set at 5% default value. • (In Premium Solver for Education, select Integer Options in Solver Options window). • Other Options. • Premium Solver for Education has additional options for IP models . • Default value of 5000 for Max Sub-problems and Max Integer Sols options sufficient for most models. • Solve Without Integer Constraints box causes Solver to ignore integer constraints while solving model.

20. 6.3 Models With Binary Variables Binary variables restricted to values of 0 or 1. • Model explicitly specifies that variables are binary. • Typical examples include decisions such as: • Introducing new product (introduce it or not), • Building new facility (build it or not), • Selecting team (select a specific individual or not), and • Investing in projects (invest in specific project or not).

21. Oil Portfolio Selection at Simkin, Simkin, and Steinberg Firm specializes in recommending oil stock portfolios. • At least two Texas oil firms must be in portfolio. • No more than one investment can be made in foreign oil. • Exactly one of two California oil stocks must be purchased. • If British Petroleum stock is included in portfolio, then Texas-Trans Oil stock must also be included in portfolio. • Client has $3 million available for investments and insists on purchasing large blocks of shares of each company for investment. • Objective is to maximize annual return on investment.

22. Oil Stock Investment Opportunities Simkin, Simkin, and Steinberg

23. Formulating the Binary (0, 1) IP Problem Simkin, Simkin, and Steinberg Objective: maximize return on investment = $50XT + $80XB + $90XD + $120XH + $110XL + $40XS + $75XC Binary variable defined as: Xi = 1 if large block of shares in company i is purchased = 0 if large block of shares in company iis not purchased where i= T (for Trans-Texas Oil), B (for British Petroleum), D (for Dutch Shell), H (for Houston Drilling), L (for Lonestar Petroleum), S (for San Diego Oil), or C (for California Petro).

24. Constraints Simkin, Simkin, and Steinberg • Constraint regarding $3 million investment limit expressed as (in thousands of dollars): $480XT + $540XB + $680XD + $1,000XH + $700XL + $510XS + $900XC $3,000 • k Out of n Variables. • Requirement at least two Texas oil firms be in portfolio. • Three (i.e., n = 3) Texas oil firms (XT, XH, and XL) of which at least two (that is, k = 2) must be selected. XT + XH + XL 2

25. Mutually Exclusive Variable Constraints Simkin, Simkin, and Steinberg • Condition no more than one investment be in foreign oil companies (mutually exclusive constraint).   XB + XD 1 • Condition for California oil stock is mutually exclusive variable. • Sign of constraint is an equality rather than inequality. • Simkin mustinclude California oil stock in portfolio. XS + XC = 1

26. If-then (or Linked) Variables Simkin, Simkin, and Steinberg • Condition if British Petroleum stock is included in portfolio, then Texas-Trans Oil stock must also be in portfolio.   XBXT or XB - XT 0 • If XB equals 0, constraint allows XT to equal either 0 or 1. • If XB equals 1, then XT must also equal 1. • If the relationship is two-way (either include both or include neither), rewrite constraint as: XB = XT or XB - XT = 0

27. Solution to the Simkin 0-1 Model Simkin, Simkin, and Steinberg Objective: maximize return = $50XT + $80XB + $90XD + $120XH + $110XL + $40XS + $75XC subject to $480XT + $540XB + $680XD + $1,000XH + $700XL + $510XS + $900XC $3,000 (Investment limit) XT + XH + XL 2 (Texas) XB + XD 1 (Foreign Oil) XS + XC = 1 (California) XB - XT 0 (Trans-Texas and British Petroleum)

28. Excel Solver Setup

29. 6.4 Mixed Integer Models • Fixed costs may include costs to set up machines for production run or construction costs to build new facility. • Fixed costs are independent of volume of production. • Incurred whenever decision to go ahead with project is taken. • Problems involving fixed and variable costs are mixed integer programming models or fixed-charge problems. • Binary variables are used for fixed cost. • Ensure whenever decision variable associated with variable cost is non-zero, binary variable associated with fixed cost takes on a value of 1 (i.e., fixed cost is also incurred).

30. Locating a New Factory Hardgrave Machine Company • Produces computer components at its plants in Cincinnati and Pittsburgh. • Plants not able to keep up with demand for orders at warehouses in Detroit, Houston, New York, and Los Angeles. • Firm to build new plant to expand its productive capacity. • Sites being considered are Seattle, Washington and Birmingham. • Table 6.3 presents - • Production costs and capacities for existing plants demand at each warehouse. • Estimated production costs of new proposed plants. • Transportation costs from plants to warehouses are summarized in Table 6.4.

31. Demand & Supply Data Hardgrave Machine Company

32. Shipping Costs Hardgrave Machine Company

33. Additional Information Hardgrave Machine Company • Monthly fixed costs are $400,000 in Seattle and $325,000 in Birmingham • Which new location will yield lowest cost in combination with existing plants and warehouses? • Unit cost of shipping from each plant to warehouse is found by adding shipping costs (Table 6.4) to production costs (Table 6.3). • Solution consider monthly fixed costs of operating new facility.

34. Decision Variables Hardgrave Machine Company • Use binary variables for each of two locations. YS = 1 if Seattle selected as new plant. = 0 otherwise. YB = 1 if Birmingham is selected as new plant. = 0 otherwise. • Use binary variables for representative quantities. Xij = # of units shipped from plant i to warehouse j where i = C (Cincinnati), K (Kansas City), P ( Pittsburgh), S ( Seattle), or B (Birmingham) j = D (Detroit), H (Houston), N (New York), or L (Los Angeles)

35. Objective Function Hardgrave Machine Company • Objective:  minimize total costs = $73XCD + $103XCH + $88XCN + $108XCL + $85XKD + $80XKH + $100XKN + $90XKL + $88XPD + $97XPH + $78XPN + $118XPL + $84XSD + $79XSH + $90XSN + $99XSL + $113XBD + $91XBH + $118XBN + $80XBL + $400,000YS + $325,000YB • Last two terms in above expression represent fixed costs. • Costs incurred only if plant is built at location that has variable Yi = 1.

36. Constraints Hardgrave Machine Company • Flow balance constraints at plants and warehouses:  Net flow = (Total flow in to node) - (Total flow out of node) • Flow balance constraints at existing plants (Cincinnati, Kansas City, and Pittsburgh) : (0) - (XCD + XCH + XCN + XCL) = -15,000 (Cincinnati supply) (0) - (XKD + XKH + XKN + XKL) = -6,000 (Kansas City supply) (0) - (XPD + XPH + XPN + XPL) = -14,000 (Pittsburgh supply) • Flow balance constraint for new plant - account for the 0,1 (Binary) YS and YB variables: (0) - (XSD + XSH + XSN + XSL) = -11,000YS (Seattle supply) (0) - (XBD + XBH + XBN + XBL) = -11,000YB (Birmingham supply)

37. Constraints Hardgrave Machine Company • Flow balance constraints at existing warehouses (Detroit, Houston, New York, and Los Angeles): XCD + XKD + XPD + XSD + XBD = 10,000 (Detroit demand) XCH + XKH + XPH + XSH + XBH = 12,000 (Houston demand) XCN + XKN + XPN + XSN + XBN = 15,000 (New York demand) XCL + XKL + XPL + XSL + XBL = 9,000 (Los Angeles demand) • Ensure exactly one of two sites is selected for new plant. • Mutually exclusive variable: YS + YB = 1

38. Excel Layout

39. Optimal Solution Hardgrave Machine Company • Cost of shipping was $3,704,000 if new plant built at Seattle. • Cost was $3,741,000 if new plant built at Birmingham. • Including fixed costs, total costs would be:   Seattle: $3,704,000 + $400,000 = $4,104,000 Birmingham: $3,741,000 + $325,000 = $4,066,000 •  Select Birmingham as site for new plant.

40. Excel Layout

41. 6.5 Goal Programming • Profit maximization or cost minimization not always only objectives. • Maximizing total profit is one of several objectives including other contradictory objectives as: • maximizing market share, • maintaining full employment, • providing quality ecological management, • minimizing noise level, and • meeting other non-economic targets or goals. • Important technique developed to supplement LP is goal programming.

42. Goal Programming Harrison Electric Company • Case presented earlier as IP problem: Objective: maximize profit = $600C + $700F subject to 2C + 3F <= 12 (wiring hours) 6C + 5F <= 30 (assembly hours) C, F >= 0   where C = number of chandeliers produced F = number of ceiling fans produced • LP used to find single optimal solution. • Firm to move to new location. • Maximizing profit not realistic goal during move horizon. • Management sets $3,000 profit level as satisfactory during adjustment period.

43. Goal Programming Harrison Electric Company • Find production mix to achieve goal given production time constraints. • Define two deviational variables:

44. Goal Programming Harrison Electric Company • First constraint - • Constraint contains over-achievement and under-achievement variables with respect to $3,000 revised target. • In event target exceeds $3,000, over-achievement variable will state amount over the target. • In event target not met, under-achievement variable will state amount under the target. • In event target amount achieved, the under- and over-achievement variables will equal zero.

45. Multiple Goal Programming Harrison Electric Company

46. New Objective Function and Constraints Harrison Electric Company • Solve multiple goal program using weighted goals approach • or prioritized goals approach.

47. Summary • Integer programming examines three types of programming problems: (1) Pure or all-integer programs. (2) Mixed problems. (3) Binary variable (0 - 1) problems. • Special type of LP problem is multiple goal programming. • Multiple objective functions with individual goals. • Uses weighted goals as well as ranked goals. • NLP was introduced as a special mathematical programming problem. • Excel’s Solver useful in solving simple NLP models.