Management Science

1
Management Science
QM 6433 -- Spring 2007

• Introduction to Linear Programming
• Week of 3/19/2007

Instructor John Seydel, Ph.D.
2
This Weeks Student Objectives

• Upon completion of this weeks course
  activities, you should be able to
• Model appropriate situations as LP problems
• Model LP problems using Excel
• Use Solver to solve LP problems modeled in Excel
• Interpret and apply LP optimality results
• Discuss and give examples of the three major
  types of decision models
• Summarize the components of LP models
• Summarize the LP problem-solving procedure

3
Refer to Our Course Outline

• Spreadsheet modeling techniques
• Further reinforce/strengthen this week
• Basic decision modeling concepts
• Further reinforce/strengthen this week
• Specific modeling/solution techniques
• Decision analysis
• Techniques
• Applications
• Linear programming
• Simulation modeling analysis
• Intro to Monte Carlo methods
• Modeling stochastic decision problems and solving
  with CB
• Multicriteria decision making
• Project management concepts tools

4
Mini-Case Par, Inc.

• Review the scenario
• Note the modeling and solution process
• Two related approaches to modeling
• An Excel representation
• Worksheet
• Solver
• A mathematical specification
• Review the solution
• Optimal solution recommended course of action
  and corresponding results
• Sensitivity information more next week

5
Defining the Problem for Par, Inc.

• Whats the decision involved?
• Determine the product mix for the season
• Two decisions
• Number of standard bags to produce
• Number of delux bags to produce
• What criteria might be involved?
• Although capacity is addressed, its pretty clear
  this is about profit
• No targets have been indicated, so this involves
  the objective of profit maximization
• What limits the progress toward the objective?
• Heres where the capacity issue surfaces
• Production capacity is limited four areas
• Cutting dyeing
• Sewing
• Finishing
• Inspection packaging

6
Alternatives Available to Par, Inc.

• First, define
• x1 as the number of standard bags to produce
• x2 as the number of deluxe bags to produce
• Lets now identify the alternatives
• Then how about following choices?
• x1 100, x2 100
• x1 100, x2 50
• x1 50, x2 100
• x1 103, x2 57
• x1 5000, x2 5000
• . . .
• Note that the choices (alternatives) are
  essentially unlimited!
• Do we really want to evaluate each of these?
• Not really lets put together a decision model
  to assist us
• Well start by looking at the information
  available . . .

7
What Info Do We Have Available and How Reliable
Is It?

• Profitability
• Standard 10 per bag
• Deluxe 9 per bag
• Resource availability
• Cutting dyeing 630 hours
• Sewing 600 hours
• Finishing 708 hours
• Inspection packaging 135 hours
• Resource requirements (hours per bag)
• At this point, lets assume these are reliable
  data

8
The Decision Model for Par, Inc.

• Model components
• Inputs
• Controllable (i.e., decision variables)
  standard, deluxe
• Uncontrollable
• Profitability standard, deluxe
• Resource availability cutting/dyeing, sewing,
  finishing, inspection/packaging
• Resource requirements one for each bag type /
  resource type
• Outputs
• Current value for the objective (total profit)
• Current values for constraints (amounts consumed
  of each resource type)
• Solution methodologies to consider
• Trial and error (based upon Excel model)
• Linear programming
• The solution
• Recommendation and results
• Sensitivity analysis

9
Using Excel to Model Problems Like Par, Inc.

• Modeling involves
• Entering inputs
• Controllable (lets just start arbitrarily with
  100 of each bag type)
• Uncontrollable
• Specifying relationships (i.e., the formulae for
  the output cells)
• We can then test various solutions i.e., we use
  various combinations values for the decision
  variables
• Check to make sure resource consumption doesnt
  exceed resource availabilities
• Keep track of the total profit for each
  combination tried
• Do this until were comfortable that the
  objective (profit maximization) has been achieved

10
About Solutions

• The value of the objective (total profit in this
  case) is what we use to evaluate a given solution
• Note that a solution
• Is not the value of the objective function
• It is instead any set of values chosen for the
  decision variables
• A feasible solution is any decision variable
  combination that does not cause the constraints
  to be violated
• An optimal solution is one that is both
• Feasible and
• One for which no other combination of decision
  variable values results in a better value for the
  objective function

11
One Way to Model This with Excel
12
The Basic Excel Model

• Note the color coding
• Green decision variables OK to go ahead and
  change these
• Yellow uncontrollable inputs change only with
  caution
• Red (pink) outputs stop, dont change these as
  theyre formulae
• The results cells here all have similar formulae,
  using the SUMPRODUCT() function
• Multiplies
• Amount of each type of golf bag (values for x1
  and x2)
• Corresponding resource usage or profitability
• Adds results
• Now the hard work begins play around with the
  values for x1 and x2
• Watch the total profit amount (cell J9)
• Make sure the Used values dont exceed the
  Available amounts
• A better way use Excels Solver . . .

13
Setting Up Solver for the Par Exercise

• Were now moving from what-if analysis to linear
  programming
• Solver applies the simplex algorithm to a set of
  data and relationships
• Were enhancing the solution methodology by
  telling Excel what to do with the basic model
• Try various combinations for x1 and x2
• Monitor the value in cell J9
• Make sure the values in E9H9 dont exceed the
  values in E8H8, respectively
• Also
• Use only linear combinations (more on this later)
• Dont consider negative values of x1 and x2
• Once the basic models completed, start Solver
• Tools Solver
• Specify how the basic model should be treated and
  then run Solver . . .

14
Solver Specifications and Execution

• Specifications
• Primary
• Target cell (J9)
• Objective (Maximize)
• Changing cells (C5C6)
• Add constraints (E9H9 lt E8H8)
• Options (but not really optional) click on the
  Options button
• Check Assume Non-Nonegative
• Check Assume Linear Model
• When done, click on Solve
• A feasible optimal solution should result if
  so
• Select only the Sensitivity report
• Well look at this another time
• The other reports arent much value to us
• Keep the solution and click OK
• Examine the solution (and apply, if appropriate)

15
The Solution for Par, Inc.

• In brief
• x1 538.4
• x2 253.1
• Profit 7,662
• That is, produce 538.4 standard bags and 253.1
  deluxe bags
• However, this is technically infeasible
• Partial bags are not possible
• This is a fault of our model, which doesnt
  constrain the decision variables to integer
  values (part of the problem definition stage)
• However, its not much of a problem, as we can
  try rounding the given solution
• x1 538
• x2 253
• All constraints are met, and profit is only 5
  less
• Therefore, Par should produce 538 standard bags
  and 253 deluxe bags, which will result in a
  profit of 7,662 and will use essentially all of
  the capacity available in the cutting/dyeing and
  finishing areas but will leave 121 hours of
  unused capacity in the sewing area and 13 hours
  in the inspection/packaging area

16
About the Integer Constraints

• In this case (Par, Inc.), failing to incorporate
  integer constraints didnt cause much of a
  problem, as the optimal noninteger solution was
  close to the optimal integer solution
• Often, however, this is not the case, and major
  work is involved in modifying a noninteger
  solution so that the integer solution is both
  feasible (meets the other constraints) and
  optimal
• For our purposes, since we are dealing with small
  scale and relatively simple problems, well not
  worry about adding integer constraints
• While its easy to incorporate such constraints,
  the problems well address wont require it
• Sensitivity information (to be discussed another
  time) is not available if integer constraints are
  specified

17
Linear Programming Solution Possibilities

• There are four situations that can result from
  running Solver on a LP problem (such as Par,
  Inc.)
• What we want is the feasible optimum Solver
  found a solution
• However, we may instead run into the following
• Infeasible Solver could not find a feasible
  solution
• Unbounded The Set Cell values do not converge
• Alternate optima
• No Solver message indicates this
• Instead, we need to look at the sensitivity
  report (more next time)
• Lets take a closer (but brief) look at these
  other three situations

18
Beyond (or Short of) a Feasible Optimum

• Infeasible
• Overconstrained possibly bad constraints
  (especially equalities)
• Check the
• Model (is something misspecified?)
• Way the model has been implemented
• Equality constraints to see if they can be
  modeled as inequalities
• Unbounded
• Underconstrained misspecified constraints
• Check to see that the inequalities are pointed in
  the correct direction
• Is a constraint missing?
• Alternate optima
• This occurs when the objective function is
  parallel to a constraint
• A good result this means more than one course of
  action leads to optimum results
• In general, Solver terminating with infeasible or
  unbounded solutions means that somethings wrong
  with either the model or the way it has been
  implemented in Excel, so reexamine and/or check
  your setup

19
Now, About the Underlying Mathematical Model for
Par, Inc.

• Recall that
• x1 is the number of standard bags to produce
  (cell C5 on the worksheet)
• x2 is the number of deluxe bags to produce (cell
  C6 on the worksheet)
• Note the formulae on the Par worksheet
• Constraints (Cutting Dyeing, for example)
• SUMPRODUCT(E5E6,C5C6)
• That is, 0.70x1 1.00x2
• So, the constraint is 0.70x1 1.00x2 630
• Total profit
• SUMPRODUCT(J5J6,C5C6)
• That is, 10x1 9x2
• Thus the objective function is Maximize 10x1
  9x2
• Based on this, we can now take a look at the
  mathematical representation of the overall model
  . . .

20
LP Model for Par, Inc.Mathematical
Representation

• Maximize 10x1 9x2 (Total Profit)
• Subject to
• 0.70x1 1.00x2 630 (CD)
• 0.50x1 0.83x2 600 (Sew)
• 1.00x1 0.67x2 708 (Finish)
• 0.10x1 0.25x2 135 (IP)
• x1 0
• x2 0

21
Mathematical Models A Review

• General decision-making model
• y f(x1, , xn, a1, , an , b1, , bm , c1,
  , cn)
• Components
• Inputs
• Controllable (xi)
• Uncontrollable (ai, bj, ci)
• Outputs (y)
• Examples
• Descriptive simulation (e.g., Gaming Company)
• Prescriptive decision theory (e.g., bidding)
• Predictive regression (e.g., data analysis)
• Special (but very important) case optimization
  models
• General format
• Linear programming

22
General Format for an Optimization Model

• Max (or Min) f0(x1, x2, , xn)
• Subject to
• f1(x1, x2, , xn) b1
• fk(x1, x2, , xn) bk
• fm(x1, x2, , xn) bm
• Note If all the functions in an optimization
  are linear, the model is a Linear Programming
  (LP) model

23
General Format for a Linear Programming Model

• Max (or Min) c1x1 c2x2 cnxn
• Subject to a11x1 a12x2 a1nxn b1
• ak1x1 ak2x2 aknxn bk
• am1x1 am2x2 amnxn bm
• Notice how the Par model follows this format

24
So What . . . !?

• If a situation can be modeled such that theres
  an underlying LP model, we can use Solver to help
  find an optimal solution
• Knowing about the underlying LP model helps us
  apply the sensitivity information provided by
  Solver
• Therefore, its helpful to be aware of the LP
  model underlying an Excel model
• That there are decision variables
• That there is an objective function
• That there are constraints
• In this way, we can develop Excel models that can
  be solved as LP models

25
Some Modeling Suggestions that Facilitate
Interpreting LP Output

• Be sure you understand (and thus have precisely
  defined) your decision variables
• Name your variables and ranges so Excel reports
  are easier to interpret
• Units of measure must be clear when you develop
  your model
• In general, a thorough modeling process will
  facilitate interpreting the solution

26
Now, Consider Whats Different about LP

• That is, compare to
• Decision theory applications
• Tools decision trees, payoff matrices
• Examples bidding, demand management
• Simulation applications
• All of the above involve one or more decision
  variables
• Constraints may or may not be explicit and/or
  relevant
• All seek to address a decision criterion (e.g.,
  profit)
• So, whats fundamentally different about LP . . .
  ?
• Consider the objective functions
• Decision theory Maximize y f(x,Demand,P(Demand)
  ) ? Fish House
• Simulation Minimize y f(Q,ROP,Demand,P(Demand)
  ) ? GameCo
• LP Maximize y f(x,c) ? Par, Inc.
• Other differences
• Continuous decision variables (infinite set of
  alternatives
• Explicit constraints
• Linear functions

27
LP Models A Summary

• There are three (yes, 3) ways to model situations
  that can be represented as linear programs (i.e.,
  LP models)
• As spreadsheet models
• As mathematical models
• Graphically
• The last approach (covered extensively in the
  textbook) is very helpful for visualizing the
  relationships involved
• However, it has no practical value, as its
  limited to problems involving two dimensions
  (decision variables)
• Most practical LP problems involve a hundred
  decision variables and constraints
• We thus need to use the simplex (or an
  alternative) algorithm
• The process and details are beyond our scope, and
  unnecessary, since we have software that does
  this for us
• Excel
• Specialized commercial software
• However, fifty years ago, this was done manually
  with rooms full of people cranking out partial
  solutions on mechanical calculators

28
Another Case An Ag Decision

• The scenario
• The model
• Excel representation
• Mathematical specification
• The solution use Solver

29
An Excel Model for the Ag Decision
30
A Math Model for the Ag Decision

• Maximize 256.0x1 284.5x2 (Total Profit)
• Subject to
• 1x1 1x2 100 (Acreage)
• 50x1 75x2 6000 (Water)
• x1 0
• x2 0

31
A Graphical Representation of the Ag Decision
Acreage constraint (x1 x2 100)
Objective function isoquants two of many (Total
Profit 270x1 300x2)
Set of all feasible solutions
x1 0
Water constraint (50x1 75x2 6000)
x2 0
32
Obtaining the Optimal Solution for the Ag Decision

• Solver specifications (absolute references
  created automatically by Solver)
• Target cell ? K12 (Maximize)
• Changing cells ? B6B7
• Constraints ? B12D12 lt B9D9
• Options ? standard ones (same as for Par, Inc.)
• The solution report , based on Solver results (or
  the graphical representation)
• The farmer should plant 60 acres in water melons
  and 40 acres in cantaloupe. The resulting profit
  for the season will be 26,740, and all acreage,
  as well as the water available, will be consumed
  if this recommendation is implemented.
• Next time well address the importance and
  usefulness of the information on the Sensitivity
  report

33
Some Comments about The Excel Model for the Ag
Decision

• The Excel model used in the examples was intended
  to represent a relatively unsophisticated
  approach to modeling the problem on a spreadsheet
• This was developed essentially by entering data
  (inputs) as they were encountered through a first
  reading of the scenario
• Once the data were entered, some labels were
  added and some output cells (e.g., water used,
  labor cost, and revenue per acre) were created
  with formulae based upon the input cells
• Then, any additional required output cells (e.g.,
  total revenue profit) were incorporated with
  the appropriate formulae
• Finally, some minor rearranging was done to
  assist in the reading of the worksheet
• The idea is that we can often model a problem in
  a way that makes sense to us and then apply LP to
  determine the solution we need
• Alternatively, we might instead try initially to
  model the problem in a manner more parallel to
  the underlying math model . . .

34
An Alternative Excel Representation for the Ag
Decision
35
Which Excel Model is Better?

• Either one, or some other, which ever makes more
  sense to the modeler
• Of course, the Excel models should be
• Valid i.e., they need to represent the actual
  situation correctly and completely, yet concisely
• Correct i.e., they need to be checked to ensure
  the relationships are correctly expressed
• Properly designed i.e., they should follow the
  guidelines for spreadsheet design

36
A Summary The SPSF as Applied to LP

• Define the problem
• Define the decision variables
• Specify the objective function
• In plain English
• In terms of the DV
• Write the constraints
• In plain English
• In terms of the DV
• Consider the alternatives
• Solve the resulting model
• Interpret the solution
• Basic solution report (prescribed course of
  action and associated consequences)
• Sensitivity report (address shadow prices,
  reduced costs, ranges of feasibility and
  optimality) more next time
• Revise and re-solve if appropriate

37
Note Where LP Fits

• Recall the four basic types of decision modeling
• What-if analysis
• Making changes in the controllable inputs to see
  how a problems outcome is affected e.g., the
  Gaming Company case
• Sensitivity analysis
• Determining how changes in the uncontrollable
  inputs affect a problems solution e.g., the MBA
  case (often used in conjunction with
  optimization)
• Optimization
• Using an mathematical approach to determine the
  values of the controllable inputs that result in
  the best (i.e., optimum) problem outcome e.g.,
  decision analysis (the Fish House exercise and
  the SkullNet mini-case) and LP (Par, Inc., and
  the Ag Decision)
• Goal-seeking
• Making changes in the controllable inputs until
  a desired outcome results
• Linear programming is a constrained optimization
  technique for use with multiple decision
  variables each of which can take on an
  essentially infinite number of values the inputs
  are considered to be deterministic
• Deterministic
• Means constant, or known with certainty
  typically they are sufficiently precise estimates
  determined by prior data analysis or expert
  opinion
• The opposite is stochastic

38
LP Models The Components

• Inputs
• Decision variables (controllable inputs)
• Parameters (uncontrollable inputs)
• Outputs
• Objective function
• Constraints
• Solution procedure simplex algorithm
• Refer again to the scientific problem-solving
  framework
• The problem definition stage refers to the first
  2 of these
• The alternatives stage refers to the 3rd

39
Summary of the Steps Involved in LP

• Before proceeding
• Whats the decision involved?
• What objectives might be involved?
• What limits the progress on the objectives?
• What info do we have available and how reliable
  is it?
• Then model
• After that, solve
• Finally, examine the solution
• Does it make sense?
• How robust is it?
• Other . . .

40
Summary of Objectives

• Model appropriate situations as LP problems
• Model LP problems using Excel
• Use Solver to solve LP problems modeled in Excel
• Interpret and apply LP optimality results
• Discuss and give examples of the three major
  types of decision models
• Summarize the components of LP models
• Summarize the LP problem-solving procedure

41
Appendix
42
Decision Analysis Techniques and Applications

• Modeling/solution approaches
• Payoff matrix / EMV
• Use EMV to identify optimal solution
• Use sensitivity analysis to address the
  robustness of the solution
• Decision tree / EMV
• Some applications weve addressed
• Demand management
• Purchasing decision
• Pricing (via competitive bidding)

43
Par An Outline of the Modeling/Solution Process

• Before proceeding
• Define the problem
• Whats the decision involved?
• What criteria might be involved?
• What limits the progress on the objectives?
• Consider the alternatives
• What info do we have available and how reliable
  is it?
• Then model
• After that, solve
• Finally, examine the solution
• Does it make sense?
• How robust is it?
• Other concerns?

44
Recall the Decision Model for the Gaming Company

• There is an underlying mathematical function
  (i.e, model)
• y f(x1, x2, a1, a2, . . . , am)
• We have no idea what the actual function is
• We could figure it out
• But we dont need to, thanks to Excel
• Components of this model
• Inputs
• Controllable (xi) order quantity, reorder point
• Uncontrollable (aj)
• Cost parameters (setup, carrying, stockout)
• Weekly demand values (described by probability
  distribution)
• Output (y) total cost over the 10 week planning
  horizon
• Because the uncontrollable inputs include
  uncertain values (demand) we use simulation
  analysis to address this problem

45
Spreadsheet Design Guidelines

• Organize the data, then build the model around
  the data.
• Do not embed numeric constants in formulas.
• Things which are logically related should be
  physically related.
• Use formulas that can be copied.
• Column/rows totals should be close to the
  columns/rows being totalled.
• The English-reading eye scans left to right, top
  to bottom
• Use color, shading, borders and protection to
  distinguish changeable parameters from other
  model elements.
• Use text boxes and cell notes to document various
  elements of the model.

46
The Scientific Problem-Solving Framework (SPSF)

• Define the problem
• Define decision variables
• Determine criteria of importance
• Specify whether criteria are associated with
  goals or with objectives
• Identify constraints
• Consider alternatives
• Identify them
• Evaluate them
• Select best one
• Implement solution
• Monitor and revise solution re-solve if
  appropriate