Introduction to Optimization for Business Problem Solving - PowerPoint PPT Presentation


PPT – Introduction to Optimization for Business Problem Solving PowerPoint presentation | free to view - id: 1f66a0-ZDc1Z


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation

Introduction to Optimization for Business Problem Solving


This is the art and craft of modeling ... We'll explore the basic ideas behind trying to find optimal solutions 'hill climbing' ... – PowerPoint PPT presentation

Number of Views:174
Avg rating:3.0/5.0
Slides: 42
Provided by: markw


Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Introduction to Optimization for Business Problem Solving

Introduction to Optimization for Business Problem
What is Optimization? In a business problem
  • Loosely Finding the best solution to a
  • 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

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

f(x) just means some function of x
Some of the toughest mathematical problems solved
routinely in business today are optimization
So, what is it that makes these problems
difficult? Recall the Traveling Salesman Problem
from day one of class.
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.

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
  • Various heuristic approaches for solving very
    difficult combinatorial problems
  • Simulated annealing, tabu search, genetic
    algorithms, ant colony optimization, swarm

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
  • Goal 2 illustrate fact that scheduling policies
    affect staffing needs
  • Goal 3 real scheduling problems can lead to huge
    optimization problems

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
  • 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
Just a few examples (of many, many applications)
Data mining
Production Planning
Staff Scheduling
Cash Flow Planning
Project Mgt.
Market Research Planning
The Challenge of Staff Scheduling
So…, how much staff is needed and how should they
by scheduled?
Recent Examples from Interfaces
  • Implementing Large-Scale Optimization Models in
    Excel Using VBA Interfaces, July-August 2007 37
    370 - 382.
  • General Motors Optimizes its Scheduling of
    Cold-Weather Tests, Interfaces, 34, 5, Sept-Oct
  • 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
  • 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

The OpenCourseWare Experiment at MIT
  • http//
  • 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
  • Other courses
  • 15.073J-Logistical and Transportation Planning
    Methods (urban operations research) includes
    500 page online textbook
  • 15.094-Systems Optimization Models and

Making LPs Practical for Business…
  • Mathematical theory
  • WWII
  • Computers
  • George Dantzig discovers Simplex Method
  • Tons O research
  • Terrific applications in industry
  • Whats Best? optimization spreadsheets
  • Solver optimization for the masses
  • Example Tactical Scheduling Analysis

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
  • 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
  • thats what optimization algorithms such as those
    built into the Excel Solver do

Big Steps in Doing Optimization Modeling of
Business Problems
  • 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
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
  • 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,
  • 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.

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
  • Linear simplex method (Ch 3), interior point
  • 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
  • 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
  • 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

About optimization in PMS Well 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.)

Some Mathematical Programming Solver Tutorials
and Optimization Resources
  • A High-Level Look at Optimization Past, Present
    and Future
  • http// (Frontline)
  • Practical Optimization A Gentle Guide
  • http//

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
  • 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 - Links A typical
pricing problem
Lets revisit Example 2.2 - Links A 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

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
  • 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?
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.

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
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
Lets look at Solver for solving this problem
using a number of techniques. Open
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.
About Constraints and Ranges
  • Can define constraints using ranges

B16E16ltB18E18 Equivalent to four separate
constraints B16ltB18, C16ltC18, D16ltD18, E16ltE18
  • If range name ProducedB16E16 and

ProducedltCapacity Equivalent to four separate
constraints B16ltB18, C16ltC18, D16ltD18, E16ltE18
Solver Options
Solution search time limits
Save model to, or Load model from range on
Tell Solver problem is linear
Assume all decision variables gt0
Advanced options for non-linear problems
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//
  • integer constraints, non linear constraints such
    as use of IF(), MAX(), MIN(), ABS() functions
  • Open NastyFunction.xls

Lets throw Solver at this function
Linear Programming
DessertPlanning-MI.xls ProductMix-InClass.xls
LPlinear program
  • Many useful, important problems can be formulated
  • Maximize c1x1 c2x2 … cnxn (objective
  • Subject to a11x1 a12x2 … a1nxn ? b1 (1st
  • a21x1 a22x2 … a2nxn ? b2 (2nd
  • …
  • am1x1 am2x2 … amnxn ? bm (mth
  • xi ? 0 , i1..n, (decision
  • The ci and aij are just numeric coefficients
    that are multiplied by the values of the decision
    variables (xi)

The Major Assumptions for Linear Programs (Sec
  • Objective function and constraints are linear
    functions of the decision variables
  • is 5x3y linear? (x and y are the variables)
  • is 5x23y linear? what about exx? 105xy?
  • Decision variables are divisible
  • Practical implication?
  • Coefficients in objective function and
    constraints are given numbers
  • Practical implication?

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?

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
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

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

MIPmixed integer-linear program
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//
  • If you want to see a nice example of how branch
    and bound works

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

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
Excel Solver for our Deterministic Optimization
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
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.