Robert Zimmer

1
Lecture 3

• Robert Zimmer
• Room 6, 25 St James

2

• Introduction to Optimization Modeling

3
3.2 Introduction to Optimization

• Common elements of all optimization problems
• Decision Variables - the variables whose values
  the decision maker is allowed to choose.
• Objective Function - value that is to be
  optimized maximized or minimized
• Constraints that must be satisfied
• Excel terminology for optimization
• Decision variables changing cells
• Objective target cell
• Constraints impose restrictions on the values in
  the changing cells.

4

• A common form for a constraint is nonnegativity
• Nonnegativity constraints imply that changing
  cells must contain nonnegative values.
• Two steps in solving an optimization problem.
• Model development decide what the decision
  variables are, what the objective is, which
  constraints are required and how everything fits
  together
• Optimize systematically choose the values of
  the decision variables that make the objective as
  large or small as possible and cause all of the
  constraints to be satisfied.

5

• A feasible solution is any set of values of the
  decision variables that satisfies all of the
  constraints.
• The set of all feasible solutions is called the
  feasible region.
• An infeasible solution is a solution where at
  least one constraint is not satisfied.
• The optimal solution is the feasible solution
  that optimizes the objective.

6

• An algorithm is basically a plan of attack. It
  is a prescription for carrying out the steps
  required to achieve some goal.
• The simplex method is an algorithm that is
  suitable for linear models.
• Excels Solver tool finds the best feasible
  solution with the most suitable algorithm.
• There is really a third step in the optimization
  process sensitivity analysis. This step allows
  us to ask a number of what-if questions about the
  completed model.

7
Example 3.1 Two Variable Model

• Maggie decided she must plan her desserts
  carefully. Maggie will allow herself no more then
  450 calories and 25 grams of fat in her daily
  desserts. She requires at least 120 grams of
  desserts a day. Each dessert also has a taste
  index.
• What should her daily dessert plan be to stay
  within her constraints and maximizes the total
  taste index of her dessert?
• First step is to identify appropriate decision
  variables, the appropriate objective, the
  constraints and the relationships between them.

8
Ex. 3.1(contd) - Algebraic Model

• Identify the decision variable, write expressions
  fro the total taste index and the constraints in
  terms of the xs. Then add explicit constraints
  to ensure that the xs are nonnegative.
• Maximize 37(85)x1 65(95)x2Subject
  to120x1160x24505x110x22537x165x2120x1,x
  2 0

9
Ex. 3.1(contd) - Graphical Model

• When there are only two decision variables the
  problem can be solved graphically.
• To graph this, consider the associate equality
  (120x1160x2450) and find where the associated
  line crosses the axes.
• Graph the constraints on the figure as shown on
  the next slide.

10
Ex. 3.1(contd) - Graphical Model

• To see which feasible point maximizes the
  objective, draw a sequence of lines where, for
  each, the objective is a constant.
• The last feasible point that it touches is the
  optimal point.

11
Ex. 3.1(contd) - Spreadsheet Model

• Common elements in all LP spreadsheet models are
• Inputs all numeric data given in the statement
  of the problem (Blue border)
• Changing cells the values in these cells can be
  changed to optimize the objective (Red border)
• Target(objective) cell contains the value of
  the objective (Double line black boarder)
• Constraints specified in the Solver dialog box
• Nonnegativity check an option in a Solver
  dialog box to indicate nonnegative changing cells

12
Ex. 3.1(contd) - Spreadsheet Model

• Three stages of the complete solution
• Model development stage enter all inputs, trial
  values for the changing cells, and formulas
  relating these in spreadsheet
• Invoke Solver designate the objective cell,
  changing cells, the constraints and selected
  options, and tell Solver to find the optimal
  solution.
• Sensitivity analysis see how the optimal
  solution changes as the selected inputs vary

13
Ex. 3.1(contd) - Spreadsheet Model

• Solver dialog box for this model.

14
Ex. 3.1(contd) - Spreadsheet Model

• Optimal Solution for the Dessert Model

15
Ex. 3.1(contd) - Spreadsheet Model

• In this solution the calorie and fat constraints
  have been met exactly, thus they are binding. The
  constraint on grams in nonbinding, the positive
  difference in grams is called slack.

16
3.4 Sensitivity Analysis

• Often it is useful to perform sensitivity
  analysis to see how (or if) the optimal solution
  changes as one or more inputs change.
• The Solve dialog box offers you the option to
  obtain a sensitivity report.
• Solvers sensitivity report performs two types of
  sensitivity analysis
• on the coefficients of the objectives, the cs,
  and
• on the right hand sides of the constraints, the
  bs.

17

• The sensitivity report has two sections
  corresponding to the two types of analysis.
  Example 3.1s sensitivity report.

18

• The reduced cost for any decision not currently
  in the optimal solution indicates how much better
  that coefficient must be before that variable
  will enter at a positive level.
• The term shadow price is an economic term. It
  indicates the change in the optimal value of the
  objective function when the right-hand side of
  some constraint changes by a given amount.

19

• The SolverTable Add-in allows us to ask
  sensitivity questions about any of the input
  variables.
• SolverTables can be used in two ways
• One-way table single input cell and any number
  of output cells
• Two-way table two input cells and one or more
  outputs
• The results are easily interpreted.

20

• For the dessert model, check how sensitive the
  optimal dessert plan and total taste index are
  to(1) changes in the number of calories(2) the
  number of daily dessert calories allowed.
• The solution to question (1) can be solved by
  selecting the Data/SolverTable menu item and
  select a one-way table in the first dialog box.
  The second dialog box should be completed as
  shown on the next slide.

21

• The second question asks us to vary two inputs
  simultaneously. This requires a two-way
  SolverTable. Select the two-way option in the
  first SolverTable dialog box to get the two-way
  table dialog box.

22

23
3.5 Properties of Linear Models

• Linear programming is an important subset of a
  larger class of models called mathematical
  programming models.
• Three important properties that LP models possess
• Proportionality
• If a level of any activity is multiplied by a
  constant factor, the contribution of this
  activity to the objective, or to any of the
  constraints in which the activity is involved, is
  multiplied by the same factor.

24

• Additivity
• This property implies that the sum of the
  contributions from the various activities to a
  particular constraint equals the total
  contribution to that constraint.
• Divisibility
• This property means that both integer and
  noninteger levels of the activities are allowed.
• How can you recognize whether a model satisfies
  proportionality and additivity?

25

• Not easy to recognize in a spreadsheet model
  because the logic of the model can be embedded in
  a series of cell formulas.
• Often it is easier to recognize when a model is
  not linear. Two situations that lead to nonlinear
  models are when
• there are products or quotients of expressions
  involving changing cells, and
• there are nonlinear functions, such as squares,
  square roots, or logarithms, of changing cells.

26

• Real-life problems are almost never exactly
  linear. However, a linear approximation often
  yields very useful results.
• In terms of Solver, if the model is linear the
  Assume Linear Model box must be checked in the
  Solver Options dialog box.
• Check the Assume Linear Model box even if the
  divisibility property is violated.

27

• If the Solver returns a message that the
  condition for Assume Linear Model are not
  satisfied it
• can indicate a logical error in your formulation.
• can also indicate that Solver erroneously thinks
  the linearity conditions are not satisfied.
• Try not checking the Assume Linear model box and
  see if that works. In any case it always helps to
  have a well-scaled model.

28
3.6 Infeasibility and Unboundedness

• It is possible that there are no feasible
  solutions to a model. There are generally two
  possible reasons for this
• There is a mistake in the model (an input entered
  incorrectly) or
• the problem has been so constrained that there
  are no solutions left.
• In general, there is no foolproof way to find the
  problem when a no feasible solution message
  appears.

29

• A second type of problem is unboundedness.
• Unboundedness is that the model can be made as
  large as possible. If this occurs it is likely
  that a wrong input has been entered or forgotten
  some constraints.
• Infeasibility and unboundedness are quite
  different. It is possible for a model to have no
  feasible solution but no realistic model can have
  an unbounded solution.

30
Example 3.2 Product Mix Model

• The product mix problem is basically to select
  the optimal mix of products to produce to
  maximize profit.
• The Monet company produces four types of picture
  frames. The four types differ with respect to
  size, shape and materials used.
• Each frame requires a certain amount of skilled
  labor, metal and glass. They also all have
  different selling prices.
• Monet can produce in the coming week but they do
  not want any inventory at the end of the week.
• What should the company do to maximize its profit
  for this week?

31
Ex. 3.2(contd) - Algebraic Model

• Maximize 6x1 2x2 4x3 3x4 (profit
  objective)
• Subject to 2x1 x2 3x3 2x4 ? 4000 (labor
  constraint)
• 4x1 2x2 x3 2x4 ? 10,000 (glass
  constraint)
• x1 ? 1000 (frame 1 sales
  constraints)
• x2 ? 2000 (frame 2 sales
  constraints)
• x3 ? 500 (frame 3 sales
  constraints)
• x4 ? 1000 (frame 4 sales
  constraints)
• x1, x2, x3, x4 ? 0
  (nonnegativity constraint)

32
Ex. 3.2(contd) - Spreadsheet Model

• To develop the spreadsheet model follow these
  steps
• Inputs - Enter the various inputs in the shaded
  ranges. Enter only numbers, not formulas in the
  input cells.
• Range names Name the ranges as indicated.
• Changing cells - Enter any four values in the
  range named Produced.
• Resources used - Enter the formula SUMPRODUCT
  (B9E9,Produced) in cell B21 and copy it to the
  rest of the Used range.
• Revenues, costs, and profits Enter the formulas
  to calculate these values.

33

• The optimal solution for the product mix model is
  shown on the next slide.
• The sensitivity analysis allows us to experiment
  with different inputs to this problem. Simply
  change the inputs and then rerun Solver.
• Use SolverTable to perform a more systematic
  sensitivity analysis on one or more input
  variables.
• Additional insight can be gained from Solvers
  sensitivity report.

34
Example 3.3 Another Product Mix Model

• Pigskin company must decide how many footballs to
  produce each month. It has decided to us a
  6-month planning horizon.
• Pigskin wants to determine the production
  schedule that minimizes the total production and
  holding costs.
• By modeling this type of problem, one needs to be
  very specific about the timing events.
• By modifying the timing assumptions in this type
  of model, one can get alternative and equally
  realistic models with very different solutions.

35
Ex. 3.3(contd) - Algebraic Model

• The decision variables are the production
  quantities for the 6 months (P1 through P6). I1
  through I6 is the corresponding end-of-month
  inventories.
• The obvious constraints are on the production and
  inventory storage capacities for each month, j.
• In addition, "balance constraints that relate to
  Ps and Is are needed. The balance equation for
  the month j is Ij-1 Pj Dj Ij.

36
Ex. 3.3(contd) - Algebraic Model

• By putting all variables (Ps and Is) on the
  left and all known values on the right (a
  standard LP convention), these balance
  constraints become
• P1 I1 100-50
• I1 P2 I2 150
• I2 P3 I3 300
• I3 P4 I4 350
• I4 P5 I5 250
• I5 P6 I6 100
• The goal is to minimize the sum of production and
  holding costs. It is the sum of unit production
  costs multiplied by Ps, plus until holding costs
  multiplied by Is.

37
Ex. 3.3(contd) - Spreadsheet Model

• The difference between this model from the
  product mix model is that some of the constraints
  are built into the spreadsheet itself by means of
  the formulas.
• The only changing cells are production
  quantities.
• The decision variables in an algebraic model are
  not necessarily the same as the changing cells in
  an equivalent spreadsheet model.
• To develop the spreadsheet model
• Inputs - Enter the inputs in the shaded ranges.
• Name ranges Name ranges indicated.

38
Ex. 3.3(contd) - Spreadsheet Model

• Production quantities - Enter any values in the
  range Produced as the production quantities. As
  always, you can enter values that you believe are
  good, maybe even optimal.
• On-hand inventory - Enter the formula B4 B12
  in cell B16. This calculates the first month
  on-hand inventory after production. Then enter
  the typical formula B20 C12 for on-hand
  inventory after production in month 2 in cell C16
  and copy it across row 16.
• Ending inventories - Enter the formula B16 B18
  for ending inventory in cell B20 and copy it
  across row 20.
• Production and holding costs - Enter the formula
  calculate the monthly holding costs. Finally,
  calculate the cost totals in column H by summing
  with the SUM function.

39
Ex. 3.3(contd) - Spreadsheet Model

• The optimal solution from Solver.

40
Ex. 3.3(contd) - Spreadsheet Model

• SolverTable can be used to perform a number of
  interesting sensitivity analyses.
• In multiperiod models, the company has to make
  forecasts about the future, such as the level of
  demand. The length of the planning horizon is
  usually the length of time for which the company
  can make reasonably accurate forecasts.

41
3.10 Decision Support System

• Many people who are not experts need to use
  models.
• It is useful to provide these users with a
  decision support system (DSS) that can help them
  solve problems without having to worry about
  technical details.
• The users sees a front end and a back end.
• The front end allows them to select input values.
  
• The back end then produces a report that explains
  the optimal policy in nontechnical terms.

42

• A front-end for a problem similar to the
  Pigskin model.

43

• A back-end for a problem similar to the Pigskin
  model.