Great Deal! Get Instant \$10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

# 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 Â»
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 XXXXXXXXXX
Fe
uary XXXXXXXXXX
March XXXXXXXXXX
April XXXXXXXXXX
May XXXXXXXXXX
June XXXXXXXXXX
July XXXXXXXXXX
August XXXXXXXXXX
September XXXXXXXXXX
October XXXXXXXXXX
November XXXXXXXXXX
December XXXXXXXXXX

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 XXXXXXXXXX
Fe
uary XXXXXXXXXX
March 25 200
April XXXXXXXXXX
May XXXXXXXXXX
June XXXXXXXXXX
July 25 150
August XXXXXXXXXX
September XXXXXXXXXX
October 25 200
November XXXXXXXXXX
December XXXXXXXXXX
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
Answered Same Day May 06, 2020 IE 311

## Solution

Pooja answered on May 08 2020
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    Intege
\$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

## Related Questions & Answers

More Questions Â»

### Submit New Assignment

Copy and Paste Your Assignment Here