DECISION MODELING

1. DECISION MODELING WITH MICROSOFT EXCEL Chapter 3 Linear Optimization Copyright 2001 Prentice Hall Publishers and Ardith E. Baker

2. Linear Optimization A _______________optimization model takes the form of a performance measure to be optimized over a range of __________values of the decision variables. The feasible values of the decision variables are determined by a set of ______________constraints. Values of the decision variables must be chosen such that the inequality constraints are all ____________while either maximizing or minimizing the desired _________________ variable. These models can contain tens, hundreds, or thousands of decision variables and constraints.

3. Linear Optimization Very efficient search techniques exist to optimize ___________________linear models. These models are historically called _________________ (LP). In this chapter we will: 1. Develop techniques for formulating LP models 2. Give some recommended rules for expressing LP models in a spreadsheet that facilitates application of_________________ 3. Use Solver to ___________spreadsheet LP models

4. Formulating LP Models Every linear programming model has two important features: Objective Function Constraints A __________performance measure to be maximized or minimized (e.g., maximize__________, minimize__________) Constraints are _________ or requirements on the set of allowable decisions. Constraints may be further classified into physical, economic, or policy limitations or _______________.

5. Formulating LP Models Investment decisions are restricted by the amount of capital and government regulations. Management decisions are restricted by plant capacity and availability of resources. Staffing and flight plans of an airline are restricted by the maintenance needs of the planes and the number of employees on hand. The use of a certain type of crude oil in producing gasoline is restricted by the characteristics of the gasoline (e.g., octane rating, etc.) The first step in model formulation is the development of the ________________. For example:

6. Formulating LP Models A constrained optimization model represents the problem of allocating scarce ______________in such a way as to ____________an objective of interest. To illustrate this, we will return to the Oak Products model from Chapter 2. However, we will start with a simplified model. Oak Products, Inc. Based on economic _____________for the next week, it has been determined that it will be possible to sell as many Captains or Mates chairs as the firm can produce. So, the question becomes: How many Captains and Mates chairs should be produced given our constraints in order to ___________next week’s profit contribution?

7. Formulating LP Models Consider the following major factors: 1. The unit ____________________(price minus unit variable cost) is $56 for each Captain sold and $40 for each Mate. 2. Long dowels, short dowels, legs and one of two types of seats are needed to _________the chairs. 3. We have a _______inventory of 1280 long dowels and 1600 short dowels for next week’s production. Each _________uses 8 long and 4 short dowels. Each _________uses 4 long and 12 short dowels.

8. Formulating LP Models 4. The _________of legs is 760 units and each chair produced of either type uses 4 legs. 5. The inventory of heavy and light seats is ____and ___, respectively. Each Captain produced uses a heavy seat and each Mate uses a light seat. 6. In order for management to honor an __________ with the union, the total number of chairs produced of both types cannot fall below 100.

9. Let’s summarize the information given so far in a table: Given these considerations, now decide how many Captains and Mates to produce next week. This is called an ______________________problem or an optimal production plan. The first step in solving this is to identify the _________ and __________________.

10. For notation purposes, let C = number of ____________ chairs and M = number of Mates chairs to be produced. Start with the number of long dowels: Both types of chairs require long dowels. So, we can mathematically describe this as: 8(# Captains produced) + 4(# Mates produced) = 1280 or 8C + 4M = 1280

11. Once again, here is the equation for the long dowels: 8C + 4M = 1280 However, since only ______ long dowels are available and we can use at most, only that amount, we must make this equation an __________in order to satisfy this restriction. In this case: 8C + 4M < 1280 We use the __ (less than or equal to) inequality because, we can use all 1280 long dowels, we can use less than that amount, but we will never be able to use more than 1280. This is called an __________________________. Left hand side (LHS) is called a ____________________. Right hand side (RHS) specifies the limitation.

12. Now let’s consider the rest of the constraints: Short Dowels: We need 4 for each Captain and 12 for each mate and we cannot exceed 1600 short dowels. 4C + 12M < 1600 Legs: Each chair requires 4 legs and we have 760 in inventory. 4C + 4M < 760

13. Heavy Seat: These seats are only used for Captains chairs and only 1 per chair is needed. 140 are available. 1C + 0M < 140 or1C < 140 Light Seat: These seats are only used for Mates chairs and only 1 per chair is needed. 120 are available. 1M < 120

14. We need to consider one other constraint not given in the table. Remember that there is a __________agreement that we have to honor: The total number of Captains and Mates chairs cannot fall below 100. This means that we can make exactly 100 chairs, more than 100 chairs, but never less than 100 chairs. Therefore, the _______________that we will use will be > (greater than or equal to). C + M > 100 Note that there are no ______________in front of C or M because we can make any combination of Captains or Mates chairs – as long as the total number does not fall below 100.

15. Nonnegativity Conditions Since it does not make sense to produce a negative number of Captain or Mate chairs, we must include two additional conditions called _______________________: C > 0 and M > 0 Nonnegativity means that the resulting values can be 0 or positive, but not _______________.

16. Summary of Constraints Long Dowels 8C + 4M < 1280 Short Dowels 4C + 12M < 1600 Legs 4C + 4M < 760 Heavy Seat C < 140 Light Seat M < 120 Union Requirement C + M > 100 Nonnegativity C > 0, M > 0

17. Evaluating Various Decisions The choice of values for the pair of variables (C,M) is called a _____________. C and M are called _________________because they are quantities that you can control. In this problem, a decision is a____________________. The mix, or decision, is a combination of Captains and Mates that will not ___________one of the constraints. Of the infinitely many nonnegative pairs of numbers (C,M), including fractional values, some pairs, or decisions, will violate at least one of the constraints, and some will satisfy all the constraints.

18. Evaluating Various Decisions Only _________________decisions that satisfy all the constraints are allowable. These decisions are called ___________decisions. The Objective Function Every linear programming model has a specific _____________as well as constraints. In this case, we would like to _______________next week’s profit. _______contribution comes from the sale of two chairs: Captains and Mates. In fact, we will receive $56 for every Captain and $40 for every Mate sold. We can mathematically state this as: Total profit contribution =56C + 40M

19. Evaluating Various Decisions An Optimal Solution An _____________________is one that gives the largest total profit contribution. An _______________is a decision that will maximize the total weekly profit contribution relative to the set of all possible feasible decisions. Since total profit contribution is a ___________of the variables C,M, we refer to the mathematical expression as the payoff or _____________________. The goal is to find ____________values of C and M that optimize (i.e., maximize) the objective function: Max56C + 40M

20. Symbolic LP Model 8C + 4M < 1280 (Long Dowels Restriction) 4C + 12M < 1600 (Short Dowels Restriction) C + M > 100 (Minimum Production) 4C + 4M < 760 (Legs Restriction) C < 140 (Heavy Seats Restriction) M < 120 (Light Seats Restriction) C > 0 and M > 0 (Nonnegativity Conditions) Max 56C + 40M (objective function) Subject to (s.t.)

21. Symbolic LP Model Linear Functions 8C + 4M < 1280 4C + 12M < 1600 C + M > 100 4C + 4M < 760 C < 140 M < 120 Variable Y C > 0 and M > 0 Variable X Max 56C + 40M (objective function) Subject to (s.t.) Notice that the ________________________ and the objective function are _______________of the two decision variables. The graph of a linear function of the two variables is a straight line.

22. Linear Functions each variable appears only_________ there are no ___________or quotients of variables, no exponents (other than 1), no________________, exponential or trigonometric terms, and no IF() statements in____________. 14C + 12CM 9C2 + 8M 6 C + M IF(), MAX(), MIN(), LN(), ABS() Excel functions 8C + 4M 4C + 12M C + M A linear function is one in which Examples of Linear Equations Examples of Nonlinear Equations

23. Linear Programming Remember, 1. A linear program always has an ______________ function (to be either maximized or minimized) and ___________________. 2. All functions in the problem (objective and constraints) are___________________.

24. Integrality Considerations In LP models where fractional solutions are not directly meaningful, there are four possible recourses: 1. Add an “____________________” to the LP model, which forces one or more decision variables to take on only integer values. This results in an _____________________________________. 2. Solve the model as an ________LP and then round any decision variable for which a fractional answer cannot be __________________.

25. Integrality Considerations 3. Consider the one-week Oak Product model results to be an ______________week’s production for an ongoing multiple week situation. The whole units are produced and the fraction is carried over to the next week as a “_____________________.” 4. Consider the one-week Oak Product model results to be for ________________________only and not operational decisions to be implemented per se. Take into account other information when making the final decision.

26. The Art of LP Model Formulation Create a ___________to translate a managerial situation into a symbolic model: 1. Express the _______________and its performance measure objective function in words. 2. Express each _______________in words. 3. Verbally identify the ____________variables. Ask yourself “What decisions must be made in order to optimize the objective function?” 4. Express each decision variable in the constraint using_________________. 5. Express each decision variable in the __________ function using symbols. Finally, check your work for _______________of units of measurement.

27. Sunk vs. Variable Cost There are often two types of costs: ____________(fixed costs) and _________________. Only the variable costs are relevant in optimization models. Unit contribution margin = per unit revenue – per unit variable cost Sunk costs affect only the _________________report of income or net profit in financial statements. They play no part in future decisions.

28. Oak Product Spreadsheet Model Here is a spreadsheet version of the simplified Oak Product model containing the previously specified objective function and constraints. Note that producing 110 Captains and 90 Mates violates the Legs constraint.

29. ___________are used to clarify the meaning of other entries. The numbers represent the_____________, parameters, and decision values. ___________represent the objective function and constraints. ___________is the difference between the constraint function and the right-hand side computed so that it is non-negative.

30. ______________________is another name for Slack. More on Slack: The purpose of these calculations is to give an indication of how close any constraint is to __________ (evaluated as an equality). _________Slack indicates a binding constraint. To calculate Slack: For a < constraint, slack is the __________side minus the left hand side. For a > constraint, slack is the left-hand side minus the right hand side. Optional but useful, Slack tells us if any ___________are violated, resulting in an infeasible production plan.

31. “What if?” Projections You can use the spreadsheet model to perform “What if?” analyses. Type production values for the Captain and Mate chairs and observe the resulting weekly____________. Also make sure that the slack is not_______________.

32. Optimizing the Spreadsheet Note that you can enter an ________number of decision values for C and M in a “What if?” fashion. However, with Solver, you can transform any spreadsheet LP model into an ____________model with just a few mouse clicks. Here is the optimal model:

33. The LP Model and Spreadsheet Modeling To produce an Excel LP model, follow these steps: 1. Write out the _________on paper as a symbolic LP. Examine your written formulation and look for errors in the logic. 2. Use the ____________model as a guide in creating the Excel representation. Further _______the Excel model by changing the decision variables and checking for obvious errors. 3. Try to optimize the model with_______. If there is a problem, Solver will give an error message.

34. Overview of Solver Solver is an ___________package to Excel. Solver numerically optimizes _____________models using a technique called a mathematical programming algorithm (Simplex Method). To use Solver, you must set up your _____________ model in the appropriate form. Solver can optimize both ___________and nonlinear models. We will focus only on linear models. For LP optimization, every ________________in your model must be linear.

35. Solver Flowchart START EXCEL Solver Flowchart BUILD OR RETRIEVE YOUR OPTIMIZATION MODEL SAVE YOUR WORKBOOK!! CHOOSE “Solver…” IN THE “Tools” MENU • SPECIFY IN SOLVER DIALOG BOX: • CELL TO BE OPTIMIZED • CHANGING CELLS • CONSTRAINTS MODIFY MODEL IN OPTIONS DIALOG, CLICK “ASSUME LINEAR MODEL” & CLICK THE “OK” BUTTON CLICK ON “SOLVE” BUTTON TO BEGIN OPTIMIZATION REVIEW SOLVER COMPLETION MESSAGE

36. SOLVER FOUND OPTIMUM SOLUTION? NO YES CLICK “KEEP SOLVER SOLUTION” & CLICK “OK” BUTTON WANT TO CHANGE MODEL AND RE-OPTIMIZE? YES NO SAVE FINAL MODEL AND EXIT EXCEL

37. Overview of Solver NOTE: if you get a negative decision and it is not meaningful to your model, be sure to specify the ____________________ constraints in your LP model before optimizing with Solver. LP Modeling Terminology Solver Terminology Objective function Decision variables Constraints Constraint functions (LHS) RHS LP Model Target Cell Changing Cells Constraints Constraint Cell Reference Constraint Assume Linear Model

38. Optimizing with Solver To optimize the Oak Products model with Solver, first open the file in Excel and then choose Tools – Solver…

39. The Solver Parameters dialog box will appear. By default, _____is selected (for maximization) and the cursor is in the first edit field: Set Target Cell. Look for the _____________button. If it is not there, then you have not installed Premium Solver for Education (available from the CD). Please install this version.

40. Clicking on Premium allows you to specify the type of ______________________that it will perform. We will use the default Standard Simplex LP optimization.

41. You can also click on the button in the edit field of the dialog to ________ the dialog, click on the cell, and then click on the button to expand the dialog. With your cursor in the Set Target Cell: edit field, specify the cell to be __________(i.e., your model’s performance measure). The easiest way to do this is to move the dialog (drag the title bar) so that cell D4 is ___________and then click on that cell.

42. The Equal to: field allows you to specify the type of optimization. You can either ___________or minimize the _____________variable or cause the Target Cell to be equal to a value of your choosing (select Value of:). Specify the Oak Product model’s ____________variables (cells B4:C4) in the By Changing Cells: edit field.

43. To specify the_______________, click on the Add button to open the Add Constraint dialog. For the LHS of the constraint, specify the ____________for the Total LHS of either one constraint or a group of similar constraints (i.e., constraints with the same__________) in the Cell Reference: edit field of the Add Constraint dialog.

44. For the RHS of the constraint, specify the cell ranges of either one ____________limitation or a group of similar limitations in the Constraint: edit field. Note that when specifying many __________at the same time, the number of cells referenced in the LHS must ______ the number in the RHS. Click Add to add these constraints to Solver.

45. Finally, add the Chair Production constraint. Note that the inequality is > for this constraint.

46. Here is the resulting Solver dialog after adding all of the constraints: Now, in the Solver Parameters dialog, click the ________ button to specify a linear model.

47. In the resulting Solver Options dialog, click on the following options: _____________________ (Specifies an LP model, same as Standard Simplex LP) Assume____________ (Apply nonnegativity constraints) Use _________Scaling (to be discussed later)

48. Click OK to return to the Solver Parameters window and then click __________to start the optimization. Remember that this is an__________ technique and may take a few seconds or a few minutes depending on the size of the model. When completed, the Solver Results dialog will appear. It is important to check to see if Solver found a solution and if all constraints and _____________________were satisfied. This information will be displayed in the first sentence in this dialog. ALWAYS READ THIS SENTENCE!

49. Upon the successful completion of the Solver program, you have the following options: __________the Solver Solution _______the Original Values (throw the solution away) ___________up to three reports on the solution (each formatted as a new worksheet added to the Excel workbook. NOTE: The Premium edition may also list an ______________ Report and a Non-Linear Report if there is a problem.

50. Now, select the ________report, accept the default Keep Solver Solution, and click OK. The resulting _____will be placed in the Excel workbook as a worksheet with the tab name: Answer Report 1 Note: this report has been edited to remove unneeded blank lines.