Introduction to Optimization for Business Problem Solving

1
Introduction to Optimization for Business Problem
Solving
2
What is Optimization?In a business problem
context
Realism

• Loosely Finding the best solution to a
  problem
• More precise Finding the answer to a problem
  that minimizes (maximizes) some objective or goal
  of a decision maker while taking into account
  business constraints
• Mathematical version Finding the values of a
  set of decision variables that minimizes
  (maximizes) some objective function subject to
  constraints (equations or inequalities) on the
  decision variables

3
An Observation

• Many useful, important problems in business can
  be formulated as
• Max or Min f(x1,x2 , ...xn) (objective
  function)
• Subject to f1(x1,x2 , ...xn) ? b1 (1st
  constraint)
• f2(x1,x2 , ...xn) ? b2 (2nd constraint)
• fm(x1,x2 , ...xn) ? bm (mth constraint)
• xi ? 0 , i1..n, (decision
  variables)

f(x) just means some function of x
4
Some of the toughest mathematical problems solved
routinely in business today are optimization
problems
So, what is it that makes these problems
difficult? Recall the Traveling Salesman Problem
from day one of class.
5
Starting with a Modeling Challenge

• Modeling, solving, analysis, embedding
• Modeling can be very challenging and can affect
  our ability to solve the problem
• Download IRSSuperComputers-Shell.xls
• IRS has determined monthly needs for
  supercomputing resources for next 12 months. They
  can rent computers for either 1, 2, or 3 months.
  The per computer costs decline if we rent for
  more months. Their problem is to figure out a
  supercomputer rental plan that meets their needs
  for the next 12 months and that does so at
  minimum cost.
• They also need to know how their total rental
  costs increase as the monthly need for
  supercomputers increases.

6
Optimization problems ? some very clever, very
important, very cool computational methods

• Calculus
• Classic simplex method for solving LPs
• Interior point methods for LPs
• Branch bound, cutting planes for integer
  problems
• Various heuristic approaches for solving very
  difficult combinatorial problems
• Simulated annealing, tabu search, genetic
  algorithms, ant colony optimization, swarm
  optimization

7
Example 1 Simple 1 week, days-off problem

• Formulated model in Excel and we will solve it
  using Solver
• Goal 1 give flavor of optimization applied to
  scheduling
• Goal 2 illustrate fact that scheduling policies
  affect staffing needs
• Goal 3 real scheduling problems can lead to huge
  optimization problems

SchedulingDSS_Northpark.xls
8
Example 2 Simple 1 day, shift scheduling problems

• Formulated model in Excel and we will solve it
  using Solver
• Goal 1 see difference between shift and days-off
  scheduling
• Goal 2 treat staffing requirements as both hard
  and soft constraints
• Goal 3 real scheduling problems can lead to huge
  optimization problems

ShiftSchedulingModel1.xls ShiftSchedulingModel2.x
ls
9
Just a few examples (of many, many applications)
Data mining
Production Planning
Staff Scheduling
Logistics
Cash Flow Planning
Project Mgt.
Market Research Planning
10
The Challenge of Staff Scheduling
1
So, how much staff is needed and how should they
by scheduled?
2
3
11
Recent Examples from Interfaces

• Implementing Large-Scale Optimization Models in
  Excel Using VBAInterfaces, July-August 2007 37
  370 - 382.
• General Motors Optimizes its Scheduling of
  Cold-Weather Tests, Interfaces, 34, 5, Sept-Oct
  2004
• Improving Volunteer Scheduling for the Edmonton
  Folk Festival, Interfaces, 34, 5, Sept-Oct 2004
  (Uses Excel Solver and VBA)
• GE Plastics Optimizes the Two-Echelon Global
  Fulfillment Network at Its High Performance
  Polymers Division Interfaces, 34, 5, Sept-Oct
  2004 (Uses Excel Solver and VBA)
• Optimizing On-Demand Aircraft Schedules for
  Fractional Aircraft Operators Interfaces, 33, 5,
  Sept-Oct 2005
• UPS Optimizes its Air Network, Interfaces, 34, 1,
  Jan-Feb 2004
• NBCs Optimization Systems Increase Revenues and
  Productivity, Interfaces, 32, 1, Jan-Feb 2002
• Heery International's Spreadsheet Optimization
  Model for Assigning Managers to Construction
  Projects, Interfaces Volume 30, Number 6, Nov/Dec
  2000
• Design and Use of the Microsoft Excel Solver,
  Interfaces Volume 28, Number 5, Sep/Oct 1998
• Lets search for optimization and spreadsheet
  at Interfaces site

12
The OpenCourseWare Experiment at MIT

• http//ocw.mit.edu/index.html
• A few years ago, MIT announced they would make
  all of their course materials available to the
  public via the web, free of charge
• Mass. Institute of Technology just release their
  first courseware sites to the public
• One of the first pilot courses in the Sloan
  School of Management is
• 15.053-Introduction to Optimization
• syllabus has nice set of themes about
  optimization in business
• the entire course notes and materials are
  publicly available
• found a nice link from there to a web site,
  LP-Explorer, that shows graphical solution of
  2-variable linear programs using the simplex
  method
• Other courses
• 15.073J-Logistical and Transportation Planning
  Methods (urban operations research) includes
  500 page online textbook
• 15.094-Systems Optimization Models and
  Computation

13
Making LPs Practical for Business

• Mathematical theory
• WWII
• Computers
• George Dantzig discovers Simplex Method
• Tons O research
• LINDO, CPLEX, GAMS, AMPL, GNU GLPK
• Terrific applications in industry
• Whats Best? optimization spreadsheets
• Solver optimization for the masses
• Example Tactical Scheduling Analysis

14
Some Optimization Concepts

• A potential solution is feasible if it satisfies
  all the constraints we build in the model
• a model is infeasible if no solution satisfies
  all the constraints (p96)
• A potential solution is optimal if it is feasible
  AND it is better than all other feasible
  solutions in minimizing (or maximizing) our
  objective
• a model is unbounded (p97) if we can make the
  objective as big as we want (assume were
  maximizing) and still satisfy the constraints
• So, how do we search among the (potentially huge
  number of) feasible solutions to find the optimal
  solution?
• thats what optimization algorithms such as those
  built into the Excel Solver do

15
Big Steps in Doing Optimization Modeling of
Business Problems
DessertPlanning-MI.xls

• Decision variables?
• Objective?
• Constraints?

Formulate Model

• Pick algorithm
• Feasible?
• Optimal, good unbounded?

Solve Problem

• How does optimal solution change for changes in
• problem data
• additional constraints
• relaxed constraints

Sensitivity Analysis
16
1. Develop or formulate the model

• This is the art and craft of modeling
• Capture essence of the problem but keep the thing
  of reasonable size and complexity
• Often not clear what are easy and what are
  hard
• Identify and represent objective function
• Identify represent constraints
• Representing optimization problems
• algebra with paper pencil
• Example p77 of Practical Management Science
• what makes this a linear problem?
• algebraic modeling languages (e.g. AMPL, GAMS,
  LINGO)
• Example multimip1
• Excel or other spreadsheets
• Solver uses the familiar notions of ranges and
  formulas along with custom dialog boxes to
  represent optimization problems
• Whats Best? (originally developed for Lotus 123)
  is another spreadsheet add-in and is available
  from Lindo Systems, Inc.

17
2. Solve the optimization problem

• Depending on the specific nature of the problem,
  different algorithms may be used
• One variable - A manual line search or hill
  climbing
• Linear simplex method (Ch 3), interior point
  methods
• Non-linear (Ch 7) calculus based methods
• Integer, linear (Ch 6) branch and bound
• Non-linear, integer (Ch 8) branch and bound
  heuristics such as genetic algorithms, tabu
  search, simulated annealing, ant colony, swarm
• Solver does a nice job of choosing an appropriate
  algorithm
• May find our formulation has no feasible solution
• Our constraints cannot all be satisfied
• We have made a modeling error that must be fixed
• May or may not be able to find a provably optimal
  solution
• Depends on the specific mathematical problem we
  are solving and on the solution algorithm used
• Often well be satisfied with a good solution
• Must remember that model likely based on numerous
  simplifying assumptions

18
About optimization in PMSWell visit these
during the term

• Linear programming Ch 3 and Ch 4
• objective and constraints must be linear
• Integer programming Ch 6
• when you want to force some variables to take
  integer values only
• useful for yes/no type problems (investment
  planning, facility location, routing, scheduling)
• Non-linear models Ch 7
• many real problems like pricing contain
  non-linear elements
• many marketing models and finance models
• Genetic or evolutionary algorithms Ch 8
• just a different way of solving many kinds of
  optimization problems
• can handle all kinds of bizarre formulas
  containing Excel functions like Min(), Max(),
  If(), Abs()
• based on analogy with evolution of life (genes,
  mutations, offspring, etc.)

19
Some Mathematical Programming Solver Tutorials
and Optimization Resources

• e-Optimization.com
• A High-Level Look at Optimization Past, Present
  and Future
• http//www.frontsys.com/tutorial.htm (Frontline)
• Practical Optimization A Gentle Guide
• http//www.sce.carleton.ca/faculty/chinneck/po.htm
  l

20
Weve already done some optimization

• Break even
• decision variable?
• what was our objective in using Goal Seek?
• constraints?

• Up to this point
• how have we optimized?
• how did we get the value of the objective
  function for different values of the decision
  variables?
• how many decision variables in each problem?
• have we modeled constraints?

• Golf club pricing
• decision variable?
• objective function?
• min or max?
• constraints?

Lets revisit Example 2.2 - LinksA typical
pricing problem
21
Lets revisit Example 2.2 - LinksA typical
pricing problem

• Based on ideas in economics and marketing
• demand is influenced by price
• Businesses attempt to set prices to maximize
  profit in general
• Well explore the basic components of an
  optimization problem
• Well explore the basic ideas behind trying to
  find optimal solutions
• hill climbing
• using Solver
• using Data Tables to verify optimal solution
• well add one simple constraint a cliff

22
Links Pricing Model

• If Links Company charges P dollars per unit, then
  its profit will be (P 250)D, where D is the
  number of units demanded.
• 250 is the per unit cost
• The problem, however, is that Demand depends on
  Price.
• As P ? D ? and as P ? D ?
• Therefore the first step is to find how D varies
  with P the demand function.
• In fact, this is the first step in almost any
  pricing problem.
• Recall we tried linear, exponential, and power
  functions and compared them using Mean Absolute
  Percent Error.

Power function model
What are a and b and where did we get them? How
do a and b affect the shape of the function?
23
Pricing Model -- continued

• Lets start with the best fit Power Function
• A constant elasticity demand function of the form
  D aPb.
• You might recall from economics that the
  elasticity of demand is the percentage change in
  demand caused by a 1 increase in price.
• The larger the (magnitude of) elasticity is, the
  more demand reacts to price. The advantage of the
  constant elasticity demand function is that the
  elasticity remains constant over all points on
  the demand curve.

24
Lets look at Profit function
DDemand, PPrice, CUnit Cost
Notice that Profit is a non-linear function of
price. Why?
Recall that a and b are parameters of the power
function
25
Heres the pricing optimization problem

• Max aPb(P-Cost) (objective function)
• Subject to
• P ? 0

Price (P) is our only decision variable
Our only constraint is that price must be
non-negative
Lets look at Solver for solving this problem
using a number of techniques. Open
GolfClubsOptimization-InClass.xls.
26
Solver Main Dialog
Objective function cell a formula
Maximize, minimize or try to hit a target
Attempt to solve
Decision variables
See Options slide
Premium Solver
Build constraints using
Clear dialog box
If you use range names, theyll show up here and
will make model more readable.
27
About Constraints and Ranges

• Can define constraints using ranges

B16E16ltB18E18 Equivalent to four separate
constraints B16ltB18, C16ltC18, D16ltD18, E16ltE18

• If range name ProducedB16E16 and
  CapacityB18E18

ProducedltCapacity Equivalent to four separate
constraints B16ltB18, C16ltC18, D16ltD18, E16ltE18
28
Solver Options
Solution search time limits
Save model to, or Load model from range on
spreadsheet
Tell Solver problem is linear
Assume all decision variables gt0
Advanced options for non-linear problems
29
Modeler Beware

• Dangerous to blindly use Solver without
  understanding a little about what it does and how
  it does it.
• For some problems, Solver can guarantee a
  globally optimal solution.
• concave or convex objective function subject to
  linear constraints (see Section 7.2)
• For other, very realistic, problems an optimal
  solution cannot be guaranteed
• hilly objective functions with many peaks and
  valleys http//www.projectcomputing.com/resource
  s/psovis/index.html
• integer constraints, non linear constraints such
  as use of IF(), MAX(), MIN(), ABS() functions
• Open NastyFunction.xls

30
Lets throw Solver at this function
31
Linear Programming
DessertPlanning-MI.xls ProductMix-InClass.xls
LPlinear program

• Many useful, important problems can be formulated
  as
• Maximize c1x1 c2x2 cnxn (objective
  function)
• Subject to a11x1 a12x2 a1nxn ? b1 (1st
  constraint)
• a21x1 a22x2 a2nxn ? b2 (2nd
  constraint)
• am1x1 am2x2 amnxn ? bm (mth
  constraint)
• xi ? 0 , i1..n, (decision
  variables)
• The ci and aij are just numeric coefficients
  that are multiplied by the values of the decision
  variables (xi)

LP
32
The Major Assumptions for Linear Programs (Sec
3.5)

• Objective function and constraints are linear
  functions of the decision variables
• WHAT DOES THAT MEAN?
• is 5x3y linear? (x and y are the variables)
• is 5x23y linear? what about exx? 105xy?
• Decision variables are divisible
• CAN BE FRACTIONAL VALUES
• Practical implication?
• Coefficients in objective function and
  constraints are given numbers
• NO RANDOMNESS
• Practical implication?

33
About Linear Programming

• The constraints define the feasible region
• Just a set of linear inequalities
• The shape of the feasible region is a polytope
  (in 2D its a polygon and in 3D a polyhedron)
• Turns out that the optimal solution will always
  lie at a corner of the feasible region
• Simplex methods provides an efficient way of
  running from corner to corner in the polytope
  looking for the best solution
• Impact of adding constraints on feasible region
  and optimal solution?

34
We slide the isoprofit line up in a perpendicular
direction until moving it further will result in
it leaving the feasible region. Wherever it
intersects the feasible region last is the
optimal solution (or solutions).
See Section 3.3, p76-78 in PMS for more on
graphical solutions to linear programs
35
Next Steps

• Lets solve another optimization problem together
• Adverstising Optimization (AdvOpt-Shell.xls)
• More about linear (including integer)
  optimization problems
• Many business problems require some or all of the
  decision variables to be integers. Examples???
• Basic ideas about how linear and integer programs
  are solved
• read Sec 3.6 (p76-78) if havent already
• read Sec 6.1-6.3 (285-295)
• Well continue Excel based application
  development using an optimization modeling example

36
Yet Another Observation

• Many useful, important problems can be formulated
  as
• Maximize c1x1 c2x2 cnxn (objective
  function)
• Subject to a11x1 a12x2 a1nxn ? b1 (1st
  constraint)
• a21x1 a22x2 a2nxn ? b2 (2nd
  constraint)
• am1x1 am2x2 amnxn ? bm (mth
  constraint)
• xi ? 0 , i1..n, (decision
  variables)
• Some of the xi must be integers

MIP
MIPmixed integer-linear program
37
About Integer Programming

• Some or all variables restricted to be integers
  (or binary)
• Can be very difficult to solve given explosion in
  problem size
• n binary variables ? 2n possible solutions
• Solver uses Branch Bound technique
• a way to implicitly enumerate all the possible
  solutions without actually doing it
• Subproblems are just linear programs (i.e. the
  integer constraints are relaxed)
• http//mat.gsia.cmu.edu/orclass/integer/node13.htm
  l
• If you want to see a nice example of how branch
  and bound works

38
Some Advanced Solver Options

• Max Time Solver will run for X seconds before
  pausing
• Iterations Solver will make this many
  iterations in the solution algorithm details
  depend on which solution algorithm is used.

39
Our Deterministic Optimization World
Linear Models
Non-linear Models
Linear programs easy
Non-linear programs some easy, some really,
really hard
Mixed integer linear programs some easy, some
really, really hard
Non-linear integer programs generally really
tough
40
Excel Solver for our Deterministic Optimization
World
Non-linear Models
Linear Models
Non-linear programs Standard solver- GRG Premium
solver Genetic alg.
Linear programs Standard solver AlgSimplex method
Non-linear integer programs Branch Bound
Standard solver- GRG or Premium solver
Genetic alg.
Mixed integer linear programs Standard
solver AlgBranch Bound
41
Some Advanced Solver Options

• Precision Specifies how close a number needs to
  be to an integer value to be considered an
  integer and how close to constraint values
  solutions need to be considered feasible
• Tolerance For integer problems, Solver will
  stop if best solution so far is within this
  specified percentage of known optimal value

The GRG2 (nonlinear) algorithm uses the
Convergence edit box and Estimates, Derivatives,
and Search option button groups.