Scribd Logo
Upload

Welcome to Scribd!

  • 298 views

    Linear Programming Using Excel Solver

    Uploaded by

    S.h. Fahad Fiaz
    This document discusses linear programming and how to solve linear programming problems using Excel Solver. It provides definitions of key Excel and linear programming terms. It then walks through an example linear programming problem step-by-step to demonstrate how to set up and solve the problem using Excel Solver. The example maximizes profit for a manufacturing company producing two products given constraints on available resources. It shows how to enter the data, activate and set parameters for Solver, obtain the optimal solution, and perform sensitivity analysis on the solution.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Linear Programming Using Excel Solver

    Uploaded by

    S.h. Fahad Fiaz
    298 views27 pages
    This document discusses linear programming and how to solve linear programming problems using Excel Solver. It provides definitions of key Excel and linear programming terms. It then walks through an example linear programming problem step-by-step to demonstrate how to set up and solve the problem using Excel Solver. The example maximizes profit for a manufacturing company producing two products given constraints on available resources. It shows how to enter the data, activate and set parameters for Solver, obtain the optimal solution, and perform sensitivity analysis on the solution.

    Original Description:

    Linear programming

    Original Title

    4. Linear Programming Using Excel Solver

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses linear programming and how to solve linear programming problems using Excel Solver. It provides definitions of key Excel and linear programming terms. It then walks through an example linear programming problem step-by-step to demonstrate how to set up and solve the problem using Excel Solver. The example maximizes profit for a manufacturing company producing two products given constraints on available resources. It shows how to enter the data, activate and set parameters for Solver, obtain the optimal solution, and perform sensitivity analysis on the solution.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    298 views27 pages

    Linear Programming Using Excel Solver

    Uploaded by

    S.h. Fahad Fiaz
    This document discusses linear programming and how to solve linear programming problems using Excel Solver. It provides definitions of key Excel and linear programming terms. It then walks through an example linear programming problem step-by-step to demonstrate how to set up and solve the problem using Excel Solver. The example maximizes profit for a manufacturing company producing two products given constraints on available resources. It shows how to enter the data, activate and set parameters for Solver, obtain the optimal solution, and perform sensitivity analysis on the solution.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 27

    ADVANCED

    OPERATIONS
    RESEARCH

    A OR

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    By: HakeemUrRehman
    1
    IQTMPU

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    Excel Lingo
    On the toolbar at the bottom of the screen, click on:
    Start All Programs Microsoft Office Microsoft Office Excel 2007
    Spreadsheet: A two-dimensional array of rectangles.
    Cell: Each rectangle in excel (Four types of information can be typed
    into a cell: Number, Fraction, Function, and Text.) It is identified by its
    column and row location on the spreadsheet, which are designated by
    letter and numbers, respectively (i.e. cell A1).
    SUMPRODUCT: A function that first multiplies the numbers in n
    consecutive cells (i.e. A1 through E1) by the numbers in another set of
    n consecutive cells (i.e. A5 through E5), respectively, then takes the
    sum of the n number of products (i.e. A1*A5 + B1*B5 + C1*C5 + D1*D5
    + E1*E5), and finally deposits that sum in the cell you have selected
    (i.e. F1).
    Cell reference: Lets you repeat patterns of information between cells,
    which occurs a selected cell refers to information typed in another cell.
    Absolute reference: A cell that always refers to the originally
    referred cell; if the location of the selected cell changes, the referred
    cell will not change. It includes a $ sign before the cells column
    (i.e. $A1), row (i.e. A$1), or both (i.e. $A$1).
    Relative reference: A cell that initially refers to the originally
    selected cell; if the location of the selected cell changes, the referred
    2
    cell will change and the location of the new referred
    cell will reflect
    the location change of the selected cell. It omits the $ sign (i.e. A1).

    LINEAR PROGRAMMING USING EXCEL


    SOLVER

    How to activate Solver:

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    Solver can find a solution to:

    Systems of equations
    Inequalities
    Optimization problems
    Linear programs***
    Integer programs
    Nonlinear programs

    EXAMPLE

    XYZ manufacturing company has a division that produces two


    models of grates, modelA and modelB. To produce each modelA
    grate requires 3 g. of cast iron and 6 minutes of labor. To produce
    each modelB grate requires 4 g. of cast iron and 3 minutes of
    labor. The profit for each modelA grate is Rs.2 and the profit for
    each modelB grate is Rs.1.50. One thousand g. of cast iron and 20
    hours of labor are available for grate production each day. Because
    of an excess inventory of modelA grates, Companys manager has
    decided to limit the production of modelA grates to no more than
    180 grates per day.
    Solve the given LP problem and perform sensitivity analysis.
    LP MODEL: Let X1 and X2 be the number of modelA and modelB
    grates respectively.
    The complete LP model is as follow:
    Maximum: Z = 2X1 + 1.5X2 2X1 + (3/2)X2
    Subject to:
    3X1 + 4X2 1000 (Cast Iron Constraint)
    6X1 + 3X2 1200 (Labor Hour Constraint)
    X1
    180 (Production limit of Model-A Constraint)
    5
    X1, X2 0

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP I: ENTER THE DATA & FUNCTION

    Cell I8: Enter:


    =SUMPRODUCT($G$6:$H$6,G8:
    H8)
    Drag to cells G11:H11

    LINEAR PROGRAMMING USING EXCEL

    II:
    RECORD
    THE
    SOLVERSOLVER

    STEP
    PARAMETERS

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)

    The Solver Parameters dialog


    box:
    Buttons
    WINDOWS
    Set Target Cell
    window: Identifies the cell
    that Solver will use to
    record the optimal z-value
    for the problem.
    By Changing Cells
    window: Identifies the
    cells that Solver will use to
    record the optimal solution
    for the decision variables.
    Subject to the
    Constraints window:
    Identifies the nonnegativity constraints and
    the constraints given by
    the problem.

    Options button:
    Identifies the type of
    optimization problem;
    remember to check off the
    Assume Linear Model
    option.
    Add button: Used to
    insert the constraints;
    identified constraints are
    displayed in the Subject
    to the Constraints
    window.
    Solve button: Used to
    determine the optimal
    value for the objective z
    and the decision variables.
    8

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)

    SET TARGET CELL:

    With the CURSOR in the


    Set Target Cell Box:
    Click on Cell I8

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)

    EQUAL
    TO:
    LEAVE THE BUTTON
    FOR Max
    HIGHLIGHTED

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)

    BY
    CHANGING
    CELLS:
    WITH
    THE
    CELLS
    CELLS

    THE CURSOR IN
    BY
    CHANGING
    BOX: HIGHLIGHT
    G6 & H6

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)
    SUBJECT
    TO
    CONSTRAINTS:
    In
    the
    Solver
    Parameters dialog
    box, click on the
    Add button.
    Fill in the Cell
    Reference
    and
    Constraint
    windows by clicking
    on the changing
    cells
    and
    the
    function cells.
    Click on the OK
    button after adding
    each constraint.

    THE

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)
    SUBJECT
    (Cont):

    TO

    THE

    CONSTRAINTS

    With the cursor in the cell reference box: highlight cells I9


    through I11. Leave the direction as . With the cursor in
    the constraint box: : highlight cells K9 through K11.
    If more constraints were to be added, click Add and follow
    the same procedure.

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)
    OPTIONS
    :

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)
    SOLVE:

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    STEP II: RECORD THE SOLVER PARAMETERS
    (Cont)
    REPORT:

    LINEAR PROGRAMMING USING EXCEL


    SOLVER

    Analyzing the Excel Spreadsheet

    LINEAR PROGRAMMING USING EXCEL


    SOLVER

    THE ANSWER REPROT

    LINEAR PROGRAMMING USING EXCEL


    SOLVER

    THE SENSITIVITY REPROT

    Range of Optimality
    Changing the profit coefficient of the objective
    function
    Will the original optimal solution still be
    optimal?
    Range of Optimality?
    Profit coefficient for X1
    2, range of optimality (2 + 1, 2 0.875) = (3,
    1.125)
    Profit coefficient for X2

    LINEAR PROGRAMMING USING EXCEL


    SOLVER
    THE
    REPROT

    SENSITIVITY

    Changing the RHS CAST IRONS


    Binding Constraints
    3X1 + 4X2 1000 (Cast Irons Constraint)
    3(120) + 4 (160) = 1000

    Suppose we increase one gram Cast Iron, whats the


    impact on the optimal profit?
    The unit change in the objective function is the shadow
    price of the resource.
    Shadow price of Cast Iron Gram = 0.2
    Range of Feasibility: (1000 + 600, 1000 300) =
    (1600, 700)

    LINEAR PROGRAMMING USING EXCEL


    SOLVER

    THE SENSITIVITY REPROT

    Changing the RHS LABOUR HOUR


    Binding Constraints
    6X1 + 3X2 1200 (Labor Hours Const.)
    6(120) + 3(160) = 1200
    Suppose we increase one Labour hour, whats the
    impact on the optimal profit?
    The unit change in the objective function is the shadow
    price of the resource.
    Shadow price of Labour Hour = 0.23333
    Range of Feasibility: (1200 + 225, 1000 450) =
    (1425, 550)

    LINEAR PROGRAMMING USING EXCEL


    SOLVER

    THE SENSITIVITY REPROT

    Changing the RHS LABOUR HOUR


    NONBinding Constraints
    X1
    180 (Model-A Production Cont.)
    Optimum: 120 + 0 = 120 (ModelA Grates)
    We have 60 excessive ModelA Grates (slack)
    Increasing the Grates?
    Decreasing the Grates?
    Shadow price of ModelA = 0
    Range of Feasibility: (180 + , 180 60) = (, 120)

    EXAMPLE: PRODUCTION
    Cool-bike Industries manufactures boys and girls bicycles in bothSCHEDULING
    20-inch and 26-inch models.
    Each week it must produce at least 200 girl models and 200 boy models. The following table
    gives the unit profit and the number of minutes required for production and assembly for each
    model.
    Bicycle
    Unit Profit
    Production Minutes
    Assembly Minutes
    20-inches girls

    $27

    12

    20-inches boys

    $32

    12

    26-inches girls

    $38

    12

    26-inches boys

    $51

    18

    The Production and assembly areas run two (eight-hour) shifts per day, five days per week. This
    week there are 500 tires available for 20-inch models and 800 tires available for 26-inch models.
    Determine Cool-bikes optimal schedule for the week. What profit will it realize for the week?

    X11 = Number of 20-inch girls bicycles produced this week; X22 = Number of 20-inch boys
    bicycles produced this week; X33 = Number of 26-inch girls bicycles produced this week; X44
    = Number of 26-inch boys bicycles produced this week

    MAX 27X11 + 32X22 + 38X33 + 51X44


    S.T.
    X11
    +
    X33

    200 (Min girls models)


    X22
    +
    X44
    200 (Min boys models)
    12X11 + 12X22 + 9X33 + 9X44 4800 (Production minutes)
    6X11 + 9X22 + 12X33 + 18X44 4800 (Assembly minutes)
    2X11 + 2X22

    500 (20-inch tires)


    2X33 + 2X44

    800 (26-inch tires)


    23
    All X's 0

    EXAMPLE: PRODUCTION SCHEDULING


    (Cont)
    = Number of 20-inch girls bicycles produced this week; X = Number of 20-inch boys

    X11
    2
    2
    bicycles produced this week; X33 = Number of 26-inch girls bicycles produced this week; X44
    = Number of 26-inch boys bicycles produced this week

    MAX 27X11 + 32X22 + 38X33 + 51X44


    S.T.
    X11
    +
    X33

    200 (Min girls models)


    X22
    +
    X44
    200 (Min boys models)
    12X11 + 12X22 + 9X33 + 9X44 4800 (Production minutes)
    6X11 + 9X22 + 12X33 + 18X44 4800 (Assembly minutes)
    2X11 + 2X22

    500 (20-inch tires)


    2X33 + 2X44

    800 (26-inch tires)


    All X's 0

    24

    EXAMPLE: PRODUCTION SCHEDULING


    (Cont)
    + 38X + 51X

    MAX 27X11 + 32X22


    3
    4
    3
    4
    S.T.
    X11
    +
    X33

    200 (Min girls models)


    X22
    +
    X44
    200 (Min boys models)
    12X11 + 12X22 + 9X33 + 9X44 4800 (Production minutes)
    6X11 + 9X22 + 12X33 + 18X44
    4800 (Assembly minutes)
    2X11 + 2X22

    500 (20-inch tires)


    2X33 + 2X44

    800 (26-inch tires)


    All X's 0

    25

    EXAMPLE: PRODUCTION SCHEDULING


    (Cont)
    + 38X + 51X

    MAX 27X11 + 32X22


    3
    4
    3
    4
    S.T.
    X11
    +
    X33

    200 (Min girls models)


    X22
    +
    X44
    200 (Min boys models)
    12X11 + 12X22 + 9X33 + 9X44 4800 (Production minutes)
    6X11 + 9X22 + 12X33 + 18X44
    4800 (Assembly minutes)
    2X11 + 2X22

    500 (20-inch tires)


    2X33 + 2X44

    800 (26-inch tires)


    All X's 0

    26

    QUESTIONS

    27

    You might also like