University of Business and Technology College of Engineering Industrial Engineering Department Spring 2018 IE 311 Operations Research Contest Problems The following problems are for extra marks. They...

1 answer below »
i would like the 2 answer in the file attached solved as an excel file please


University of Business and Technology College of Engineering Industrial Engineering Department Spring 2018 IE 311 Operations Research Contest Problems The following problems are for extra marks. They are typical to what you would utilize LP for in real life. This is a classic Aggregate Production Planning problem that aim to reduce the production planning cost. [You will cover this topic in more details in the Production Planning and Control Course]. 1. A company would like to plan its production for next year. Its goal is to minimize the total manufacturing cost. The following table present the monthly demand, unit production cost, and production capacity. Month Demand Production Capacity Production Cost $ per unit January 100 400 15 February 300 350 25 March 200 400 20 April 300 380 25 May 400 400 30 June 300 300 25 July 200 300 20 August 100 400 15 September 300 350 25 October 200 400 20 November 300 380 25 December 400 400 30 If the inventory data is as follows:  Maximum Inventory level per month is 350 units.  Inventory holding cost per month is $1.5 per unit.  At the start of January there were 90 unit in inventory Formulate the problem as a LP problem then use the excel solver or any other software to determine how many units the company should:  produce each month  and how many units should be in the inventory each month in order to minimize the total manufacturing cost and satisfy the monthly demand. 3 Marks 2. If the company in addition to the aforementioned information can also use overtime to increase its production capabilities. The following table shows the overtime cost per unit produced in overtime and the maximum amount of units that can be produced by overtime for each month. Month Over time cost $ per unit produced in over time max number of units produced by overtime January 18.75 200 February 31.25 175 March 25 200 April 31.25 190 May 37.5 200 June 31.25 150 July 25 150 August 18.75 200 September 31.25 175 October 25 200 November 31.25 190 December 37.5 200 Formulate the production plan as a LP problem then use the excel solver or any other software to determine how many  units should be produced by regular production each month  units should be produced by over time each month  units should be stored in the inventory each month in order to reduce the total manufacturing cost and satisfy the monthly demand 2 Marks
unifolks_636612093355571888_117896_1.pdf
Answered Same DayMay 06, 2020IE 311

Answer To: University of Business and Technology College of Engineering Industrial Engineering Department...

Pooja answered on May 08 2020
154 Votes
Answer Report 1
    Microsoft Excel 15.0 Answer Report
    Worksheet: [30374.xlsx]Sheet1
    Report Created: 07/05/2018 22:38:20
    Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
    Solver Engine
        Engine: Simplex LP
        Solution Time: 0.187 Seconds.
        Iterations: 28 Subproblems: 0
    Solver Options
        Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
        Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
    Objective Cell (Min)
        Cell    Name    Original Value    Final Value
        $D$2    P1 min total manufacturing cost    4406.25    66293.75
    Variable Cells
        Cell    Name    Original Value    Final Value    Integer
        $B$2    P1    0    360    Contin
        $B$3    P2    0    20    Contin
        $B$4    P3    0    400    Contin
        $B$5    P4    0    380    Contin
        $B$6    P5    0    50    Contin
        $B$7    P6    0    300    Contin
        $B$8    P7    0    250    Contin
        $B$9    P8    0    400    Contin
        $B$10    P9    0    20    Contin
        $B$11    P10    0    400    Contin
        $B$12    P11    0    380    Contin
        $B$13    P12    50    50    Contin
        $B$14    B1    0    90    Contin
        $B$15    B2    0    350    Contin
        $B$16    B3    0    70    Contin
        $B$17    B4    0    270    Contin
        $B$18    B5    0    350    Contin
        $B$19    B6    0    0    Contin
        $B$20    B7    0    0    Contin
        $B$21    B8    0    50    Contin
        $B$22    B9    0    350    Contin
        $B$23    B10    850    70    Contin
        $B$24    B11    650    270    Contin
        $B$25    B12    350    350    Contin
        $B$26    B13    0    0    Contin
    Constraints
        Cell    Name    Cell Value    Formula    Status    Slack
        $D$6    month 1 production levels    360    $D$6<=$F$6    Not Binding    40
        $D$7    month 2 production levels    20    $D$7<=$F$7    Not Binding    330
        $D$8    month 3 production levels    400    $D$8<=$F$8    Binding    0
        $D$9    month 4 production levels    380    $D$9<=$F$9    Binding    0
        $D$10    month 5 production levels    50    $D$10<=$F$10    Not Binding    350
        $D$11    month 6 production levels    300    $D$11<=$F$11    Binding    0
        $D$12    month 7 production levels    250    $D$12<=$F$12    Not Binding    50
        $D$13    month 8 production levels    400    $D$13<=$F$13    Binding    0
        $D$14    month 9 production levels    20    $D$14<=$F$14    Not Binding    330
        $D$15    month 10 production levels    400    $D$15<=$F$15    Binding    0
        $D$16    month 11 production levels    380    $D$16<=$F$16    Binding    0
        $D$17    month 12 production levels    50    $D$17<=$F$17    Not Binding    350
        $H$6    LE ending inventory    350    $H$6<=$J$6    Binding    0
        $H$7    LE ending inventory    70    $H$7<=$J$7    Not Binding    280
        $H$8    LE ending inventory    270    $H$8<=$J$8    Not Binding    80
        $H$9    LE ending inventory    350    $H$9<=$J$9    Binding    0
        $H$10    LE ending inventory    0    $H$10<=$J$10    Not Binding    350
        $H$11    LE ending inventory    0    $H$11<=$J$11    Not Binding    350
        $H$12    LE ending inventory    50    $H$12<=$J$12    Not Binding    300
        $H$13    LE ending inventory    350    $H$13<=$J$13    Binding    0
        $H$14    LE ending inventory    70    $H$14<=$J$14    Not Binding    280
        $H$15    LE ending inventory    270    $H$15<=$J$15    Not Binding    80
        $H$16    LE ending inventory    350    $H$16<=$J$16    Binding    0
        $H$17    LE ending inventory    0    $H$17<=$J$17    Not Binding    350
        $L$6    LE Beginning balances    90    $L$6=$N$6    Binding    0
        $L$7    LE Beginning balances    100    $L$7=$N$7    Binding    0
        $L$8    LE Beginning balances    300    $L$8=$N$8    Binding    0
        $L$9    LE Beginning balances    200    $L$9=$N$9    Binding    0
        $L$10    LE Beginning balances    300    $L$10=$N$10    Binding    0
        $L$11    LE Beginning balances    400    $L$11=$N$11    Binding    0
        $L$12    LE Beginning balances    300    $L$12=$N$12    Binding    0
        $L$13    LE Beginning balances    200    $L$13=$N$13    Binding    0
        $L$14    LE Beginning balances    100    $L$14=$N$14    Binding    0
        $L$15    LE Beginning balances    300    $L$15=$N$15    Binding    0
        $L$16    LE Beginning balances    200    $L$16=$N$16    Binding    0
        $L$17    LE Beginning balances    300    $L$17=$N$17    Binding    0
        $L$18    B5 Beginning balances    400    $L$18=$N$18    Binding    0
        $B$2    P1    360    $B$2>=0    Not Binding    360
        $B$3    P2    20    $B$3>=0    Not Binding    20
        $B$4    P3    400    $B$4>=0    Not Binding    400
        $B$5    P4    380    $B$5>=0    Not Binding    380
        $B$6    P5    50    $B$6>=0    Not Binding    50
        $B$7    P6    300    $B$7>=0    Not Binding    300
        $B$8    P7    250    $B$8>=0    Not Binding    250
        $B$9    P8    400    $B$9>=0    Not Binding    400
        $B$10    P9    20    $B$10>=0    Not Binding    20
        $B$11    P10    400    $B$11>=0    Not Binding    400
        $B$12    P11    380    $B$12>=0    Not Binding    380
        $B$13    P12    50    $B$13>=0    Not Binding    50
        $B$14    B1    90    $B$14>=0    Not Binding    90
        $B$15    B2    350    $B$15>=0    Not Binding    350
        $B$16    B3    70    $B$16>=0    Not Binding    70
        $B$17    B4    270    $B$17>=0    Not Binding    270
        $B$18    B5    350    $B$18>=0    Not Binding    350
        $B$19    B6    0    $B$19>=0    Binding    0
        $B$20    B7    0    $B$20>=0    Binding    0
        $B$21    B8    50    $B$21>=0    Not Binding    50
        $B$22    B9    350    $B$22>=0    Not Binding    350
        $B$23    B10    70    $B$23>=0    Not Binding    70
        $B$24    B11    270    $B$24>=0    Not Binding    270
        $B$25    B12    350    $B$25>=0    Not Binding    350
        $B$26    B13    0    $B$26>=0    Binding    0
Sensitivity Report 1
    Microsoft Excel 15.0 Sensitivity Report
    Worksheet: [30374.xlsx]Sheet1
    Report Created: 07/05/2018 22:38:21
    Variable Cells
                Final    Reduced    Objective    Allowable    Allowable
        Cell    Name    Value    Cost    Coefficient    Increase    Decrease
        $B$2    P1    360    0    15    8.5    1E+30
        $B$3    P2    20    0    25    0.5    3
        $B$4    P3    400    0    20    6.5    1E+30
        $B$5    P4    380    0    25    3    1E+30
        $B$6    P5    50    0    30    1E+30    0.5
        $B$7    P6    300    0    25    6.5    1E+30
        $B$8    P7    250    0    20    2    6.5
        $B$9    P8    400    0    15    6.5    1E+30
        $B$10    P9    20    0    25    0.125    2
        $B$11    P10    400    0    20    6.5    1E+30
        $B$12    P11    380    0    25    3    1E+30
        $B$13    P12    50    0    30    1E+30    0.125
        $B$14    B1    90    0    0.75    1E+30    1E+30
        $B$15    B2    350    0    1.5    8.5    1E+30
        $B$16    B3    70    0    1.5    0.5    3
        $B$17    B4    270    0    1.5    0.5    3
        $B$18    B5    350    0    1.5    0.5    1E+30
        $B$19    B6    0    0    1.5    1E+30    6.5
        $B$20    B7    0    13    1.5    1E+30    13
        $B$21    B8    50    0    1.5    2    6.5
        $B$22    B9    350    0    1.5    2    1E+30
        $B$23    B10    70    0    1.5    0.125    3
        $B$24    B11    270    0    1.5    0.125    3
        $B$25    B12    350    0    1.875    0.125    1E+30
        $B$26    B13    0    31.125    1.125    1E+30    31.125
    Constraints
                Final    Shadow    Constraint    Allowable    Allowable
        Cell    Name    Value    Price    R.H. Side    Increase    Decrease
        $D$6    month 1 production levels    360    0    400    1E+30    40
        $D$7    month 2 production levels    20    0    350    1E+30    330
        $D$8    month 3 production levels    400    -6.5    400    20    280
        $D$9    month 4 production levels    380    -3    380    20    80
        $D$10    month 5 production levels    50    0    400    1E+30    350
        $D$11    month 6 production levels    300    -6.5    300    0    300
        $D$12    month 7 production levels    250    0    300    1E+30    50
        $D$13    month 8 production levels    400    -6.5    400    50    50
        $D$14    month 9 production levels    20    0    350    1E+30    330
        $D$15    month 10 production levels    400    -6.5    400    20    280
        $D$16    month 11 production levels    380    -3    380    20    80
        $D$17    month 12 production...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?