Script

LINEAR PROGRAMMING – Example 2

Slide 1

  • Welcome back.
  • In this module we’ll look at an example that has multiple optimal solutions and show how generate them.

Slide 2

  • Consider this situation
  • An electronics company can manufacture three different styles of 30-inch color TV’s
  • One with a standard screen requires .2 hours of production time, .1 hours of assembly time and .05 hours of quality inspection time and nets a $60 profit per unit for the company.
  • A second model is the flat screen model that requires .2 hours of production time, .3 hours of assembly time and .075 hours of quality inspection time and nets a $90 profit per unit for the company.
  • And a plasma screen TV that requires .4 hours of production time, .4 hours of assembly time and .15 hours of quality inspection time and nets a $100 profit per unit for the company.
  • Weekly availability of labor is 600 man-hours in the production area, 400 in the assembly area and 160 in the quality inspection area.

Slide 3

  • We begin building the mathematical model
  • by defining the decision variables which simply are
  • The number of standard screen TV’s produced weekly
  • The number of flat Screen TV’s produce weekly
  • And the number of plasma screen TV’s produced weekly
  • The objective to Maximize the total weekly profit which is found multiplying the unit profits times the weekly production quantities
  • So we want to maximize 60X1 + 90X2 + 100X3
  • And the constraints can be expressed by
  • The hours in each area used each week is less than
  • the number of hours available for that area each week
  • So for the production area we have .2X1 + .2X2 + .4X3 is less than or equal to 600
  • for the assembly area we have .1X1 + .3X2 + .4X3 is less than or equal to 480
  • andfor the inspection area we have .05X1 + .075X2 + .15X3 is less than or equal to 160.
  • And of course the non-negativity constraints saying we cannot produce a negative number of television sets.

Slide 4

  • We set up the spreadsheet in the usual way
  • The formula for the objective function in cell F6 is entered with $ signs for the decision variables so that these cells won’t change as the formula is dragged down to cell F9
  • The Solver dialogue box shows that F6 is the target cell that the decision variables are in cells C4 to E4 and the functional constraints were entered all at once since they were all less than or equal to constraints.
  • The Assume linear model and assume non-negativity boxes are checked in the Options box and then we clicked Solve

Slide 5

  • Giving the optimal solution of
  • 1600 standard televisions, 1066 and 2/3 Flat screen TV’s, and no plasma TV’s to be produced each week –
  • the 2/3 of a flat screen TV is simply considered work in progress that will be completed during the next week.
  • This solution nets an optimal weekly profit of $192,000

Slide 6

  • When go to the Sensitivity Report,
  • we see that the Allowable Increase for the objective function coefficient for Standard TV’s is 0 as the Allowable Decrease for the objective function coefficient for Flat Screen TV’s. This indicates alternate optimal solutions exist.

Slide 7

  • When alternate optimal solutions exist we can generate ones with certain characteristics that management may desire.
  • For example, to generate an optimal solution that makes as many standard TV’s as possible….
  • We add a constraint that says the objective function value in cell F6 must equal the optimal value of 192,000
  • Now we can set the objective function to Max X1 – since the number of standard TV’s is the value in cell C4 – we change the target cell in the dialogue box to C4
  • Now suppose we want an optimal solution that produces as few standard TV’s as possible.
  • We do the same thing. We make a constraint out of setting cell F6 to 192000
  • but this time when we set the target cell to C4, we put the bullet in front of MIN
  • And if we want the optimal solution that produces exactly twice the number of standard TV’s as flat screen TV’s we can do something different.
  • Since we know the optimal profit generated as it stands is 192000, we can simply add one more constraint to the model that says the number of standard TV’s, X1, must be twice the number of flat screen TV’s.

Slide 8

  • Here are the results from these analyses.
  • To maximize the number of standard TV”s,
  • In the Solver dialogue box
  • We added to the constraints that cell F6 must equal 192000
  • and we switched the target cell to C4.
  • This gave an optimal weekly production of 2600 standard and 400 flat screen TV’s.

Slide 9

  • Then
  • To miximize the number of standard TV”s,
  • In the Solver dialogue box
  • We added to the constraints that cell F6 must equal 192000
  • and we switched the target cell to C4 and put the bullet in front of MIN
  • This gave the optimal weekly production of 1600 standard and 1066 2/3 flat screen TV’s that we got before.

Slide 10

  • Finally
  • To get the optimal result that also set production of standard TV’s to exactly twice that of flat screen TV’s
  • In the solver dialogue box
  • We left the target cell as F6
  • But we added a constraint that said that the number in cell C4 (the number of standard TV’s) equals 2 times cell D4 (the number of flat screen TV’s).
  • This resulted in a optimal solution that produces 1828.571 standard TV’s and 914.2857 flat screen TV’s per week.

Slide 11

  • Let’s review what we’ve discussed in this module.
  • We showed how to recognize on the sensitivity report that a problem has multiple optimal solutions
  • And we showed how to generate optimal solutions that:
  • Maximized a variable
  • Minimized a variable
  • And established some relationship between the variables.

That’s it for this module. Do any assigned homework and I’ll be back to talk to you again next time.