Docstoc

Linear Programming ISQA 511 Dr Mellie Pullman

Document Sample
Linear Programming ISQA 511 Dr Mellie Pullman Powered By Docstoc
					 ISQA 511
 Dr. Mellie Pullman




EVALUATING CONSTRAINED RESOURCES
W/ LINEAR PROGRAMMING
Tinker Toys
   We need to allocate scarce resources among
    several alternatives
     resources=  ?
     alternatives=?

   Need to get into teams
   Your job is to produce Tinkertoys with three products
    (Turnstiles, Robots, & Front Wheel Assemblies)
Parts Required and Availability


                       Number of   Robot            Turnstile        Front Wheel      Parts left over
                       parts
                       available
   Part                            Units required   Units required   Units required

   Blue Rods               12                              4

   Orange Rods             12              1               1               1

   Green Rods              10                                              1

   One-hole Spools         18              1               1               2

   Multi-hole Spools       6               1               1

   Wood Caps               8               1                               1

   Wood Washers            6               1

   Wood Bearings           6                                               1

   Yellow Rods             10              2               1
Objectives

   1) Make as many of the three finished products as
    possible to maximize the total number of toys
    produced,
     how   many of each type of toy should be made?
   2) Make the number of finished products that make
    the most revenue.
     Robots@ $30, Turnstiles @ $10, Front Wheel
      Assemblies @ $20.
Maximize number of toys

                Number of   Robot      * units   Turnstile   * units   Front Wheel   * units   Total   Parts left
                parts                                                                          Parts   over
                available                                                                      used
 Part                       Units                Units                 Units
                            required             required              required
 Blue Rods          12                               4

 Orange Rods        12         1                     1                      1

 Green Rods         10                                                      1

 One-hole           18         1                     1                      2
 Spools
 Multi-hole          6         1                     1
 Spools
 Wood Caps           8         1                                            1

 Wood Washers        6         1

 Wood                6                                                      1
 Bearings
 Yellow Rods        10         2                     1

 TOTAL UNITS
Maximize Overall Profit


                Number of   Robot      * units   Turnstile   * units   Front Wheel   * units   Total   Parts left
                parts                                                                          Parts   over
                available                                                                      used
 Part                       Units                Units                 Units
                            required             required              required
 Blue Rods          12                               4

 Orange Rods        12         1                     1                      1

 Green Rods         10                                                      1

 One-hole           18         1                     1                      2
 Spools
 Multi-hole          6         1                     1
 Spools
 Wood Caps           8         1                                            1

 Wood Washers        6         1

 Wood                6                                                      1
 Bearings
 Yellow Rods        10         2                     1

 TOTAL UNITS

 Profit                       $30                  $10                     $20
Determining the Optimal Strategy in a
constrained resource world
 Try multiple attempts with different scenarios
OR
 Use Linear Programming (LP)

     You will need to install Solver on your laptop
     In Excel:
           Click Tools
           Click Add-ins
           Click Solver Add-in
Where to find it in Excel 2007
1
        2




    3
What is Linear Programming?
    A sequence of steps that will lead to an
     optimal solution.
    Used to
      allocate scarce resources (energy, food, land)
      assign labor (shifts, Reg vs. OT, productivity)
      determine lowest cost and emission transportation
       schemes
      solve blending problems (food, chemicals or
       portfolios)
      solve many other types of constrained resources
       problems
Four essential conditions:

    Explicit Objective: What are we maximizing or
     minimizing? Usually profit, units, costs, emissions,
     labor hours, etc.
    Limiting resources create constraints:
     workers, equipment, parts, budgets, etc.
    Linearity (2 is twice as good as 1, if it takes 3
     hours to make 1 part then it takes 6 hours to make
     2 parts)
    Homogeneity (each worker has an average
     productivity)
Bank Loan Processing

   A credit checking company requires different
    processing times for consumer loans.
     Housing  loans (H) require 1 hour of credit review and 4
      hours of appraising. Car loans (C) require 1 hour of
      credit review and 1 hour of appraising.
     The credit reviewers have 200 hours available; the
      appraisers have 400 hours available.
     Evaluating Housing loans yields $10 profit while
      evaluating Cars yields $5 profit. How many of each
      loan type should the company take?
Graphical Approach (2 variables)
   Formulate the problem in mathematical equations
   Plot all the Equations
   Determine the area of feasibility
     Maximizing   problem: feasible area is on or below the
      lines
     Minimization: feasible area is on or above the lines
     Plot a few Profit line (Iso-profit) by setting profit
      equation = different values.
     Answer point will be one of the corner points (most
      extreme)
Equations
   Maximize Profit : $10 H + $5 C
   Constrained Resources
     1H + 1C < 200 (credit reviewing hours)
     4H + 1C < 400 (appraising hours)

     H>0;   C>0 (non-negative)
     H= ?
     C=?
Farmer Gail (land and resource limits)

 Farmer Gail in Pendleton owns 45 acres of land. Gail is
 going to plant each acre with wheat or corn. Each acre
 planted with wheat yields $200 profit while corn yields $300.
 The labor and fertilizer needed for each acre given below.
 100 workers and 120 tons of fertilizer are available.


                       Wheat              Corn
     Labor /acre       3 workers          2 workers
     Fertilizer/acre 2 tons               4 tons
Farmer’s Wheat and Corn Problem
     Variables:
       Acres planted in wheat = W
       Acres planted in corn = C

     Objective Function:
         : Maximize profit $200 W + $300 C
     Constraints:
       Labor:      3 W + 2 C < 100
       Fertilizer: 2 W + 4 C < 120

       Land:        1W + 1 C < 45
       Non-Negativity: P1 & P2 > 0
Solver Set-up on Excel
       These 2 cells will
       change to find the
         solution. They
     represent W & C (our
          unknowns)




                      Wheat         Corn         LSE       RSE
VARIABLES                       0            0
                                                            =SUMPRODUCT(C2:D2,C3:D3)
Profit                        200          300         0
                                                                       =SUMPRODUCT(C2:D2,C5:D5)
Labor                          3            2          0         100
Fertilizer                     2            4          0         120
Land                           1            1          0          45
Note: The
inequality
signs are
   NOT
 typed in,
 they are
an option
Answer Report

Target Cell (Max)
    Cell       Name         Original Value Final Value
   $D$4 Profit Total                     0       10000


Adjustable Cells
   Cell        Name         Original Value Final Value
  $B$3 Decision Wheat                    0           20
  $C$3 Decision Corn                     0           20
                                                                              What does slack
Constraints
                                                                              mean here ?
   Cell       Name           Cell Value        Formula      Status    Slack
  $D$6 Workers Function               100    $D$6<=$F$6   Binding         0
  $D$7 Fertilize Function             120    $D$7<=$F$7   Binding         0
  $D$8 Land Function                    40   $D$8<=$F$8   Not Binding     5
  $B$3 Decision Wheat                   20   $B$3>=0      Not Binding    20
  $C$3 Decision Corn                    20   $C$3>=0      Not Binding    20
   Sensitivity Report
                        Reduced cost: how much more profitable would
                        W or C have to be to be included in the answer?
                                                                           Profit of Wheat could
Adjustable Cells                                                           increase by $250 or
                        Final Reduced Objective Allowable Allowable        decrease by $50 and
   Cell     Name        Value   Cost Coefficient Increase Decrease
  $B$3 Decision Wheat      20       0        200       250          50     we would still use
  $C$3 Decision Corn       20       0        300       100 166.6666667     plant 20 acres.
Constraints
                          Final Shadow Constraint Allowable Allowable
   Cell       Name        Value Price    R.H. Side Increase Decrease
  $D$6 Workers Function     100       25        100       20          40
  $D$7 Fertilize Function   120     62.5        120       40 53.33333333
  $D$8 Land Function         40        0         45    1E+30           5



If we could get another worker, each worker contributes $25 (shadow price) to
profit for the range (100+20 =120) to (100 - 40=60) or between 60 and 120 workers.
So, how much are we willing to pay for an extra worker? How much are we willing
to pay for an extra ton of fertilizer? How much for an extra acre of land ?
Transportation Networks
  Transportation model optimizes shipments between
  coming from m origins to n destinations.
                 Mexico
                                          Warehouse
                 Plant


    Tennessee
                              Warehouse
      Plant


              Toronto                           Warehouse

                Plant
                              Warehouse
Rent'R Cars is a multi-site rental car company in the city. It is trying
out a new "return the car to the location most convenient for you"
policy to improve customer service. But this means that the company
has to constantly move cars around the city to maintain required levels
of vehicle availability. The supply and demand for economy cars, and
the total cost of moving these vehicles between sites, are shown
below.



From\To     D            E            F           G               Supply
A           $9           $8           $6          $5              50
B           $9           $8           $8          $0              40
C           $5           $3           $3          $10             75
Demand      50           60           25          30              165

Set up the equations for to determine the minimal moving costs.
Note: Variable AD will be the number of cars moved from A to D.
Space Allocation
   Planes: how much space to allocate to people or
    cargo (profit maximizing)
   Retail Space: which products to put on display
    (profit maximizing)
   Warehouse Space: how much product to store
Stereo Warehouse
   The retail outlet of Stereo Warehouse is planning a special
    clearance sale. The showroom has 400 square feet of floor
    space available for displaying the week’s specials, model X
    receiver and series Y speakers. Each receiver has a
    wholesale cost of $100, requires 2 square feet of display
    space, and will sell for $150. The wholesale cost for a pair
    of speakers is $50, the pair requires 4 square feet of space
    and will sell for $70. The budget for stocking stereo items is
    $8000. The sales potential for the receiver is considered to
    be no more than 60 units. However, the budget-priced
    speakers appear to have unlimited appeal. The store
    manager, desiring to maximize gross profit, must decide how
    many receivers and speakers to stock.
Financial Portfolio Selection

   Welte Mutual funds has just obtained $100,000 and is now looking for
    investment opportunities. The firm’s top financial analyst recommends these
    5 options. The projected rates of return are shown below:
    Atlantic Oil            7.3%
    Pacific Oil             10.3%
    Midwestern Steel        6.4%
    Huber Steel             7.5%
    Government Bonds        4.5%
       neither oil or steel should receive more than $50,000 of the total investment.
       Government bonds should be at least 25% of the steel industry.
       The investment in Pacific Oil is risky thus cannot be more than 60% of the total
        oil industry investment
   What is the best investment plan for Welte?
Knapsack Problems (Binary)
   You are running away from home and want to take
    all your favorite things (Ipod, knife, sweater, etc.) but
    only have so much room in your knapsack. You assign
    different values to each item and try to maximize the
    value of what you fit into the knapsack.
   You take the item (1) or you don’t (0).
   Note: This is a constraint called “Binary”
    under SOLVER.
Capital Budgeting: Arm&Hammer

    A&H specializes in sewage and parking lot
     construction. It has 6 possible projects that could
     be done but a limited amount of capital and time
     for the analyst to do project management. You
     must decide which projects to do.
    Note: this is the knapsack problem because you
     either do a project (1) or don’t (0).
Capital Budgeting: Arm&Hammer


     Project          Payoff    Budget   Analyst time
     TN Sewer         $100000   $50000   3.5
     NC Sewer 1       $70000    $20000   3.0
     NC Sewer 2       $120000   $40000   2.0
     Parking Lot AK   $40000    $10000   .50
     Parking Lot TN   $80000    $30000   2.5
     Parking Lot, GA $50000     $20000   1.0
     AMOUNT AVAILABLE           $90000   6.0

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:21
posted:1/9/2012
language:English
pages:28