Linear Programming Models

1
Chapter 4

• Linear Programming Models

2
Example 4.1 Advertising Model

• General Flakes Company advertises a low-fat
  breakfast cereal in a variety of 30 second
  television ads placed in a variety of television
  shows.
• The ads in different shows vary by cost and by
  the type of viewers they are likely to reach.
• Viewers have been separated into six mutually
  exclusive categories.
• The rating service can supply information on the
  number of viewers.

3
Ex. 4.1(contd) - Advertising Model

• It wants to know how many ads to place on each of
  several television shows to obtain required
  exposures at minimum costs.
• This model is essentially the opposite of the
  product mix model.
• LP models ten to fall into types from a
  structural point of view, even though their
  actual contexts might be very different.

4
Ex. 4.1(contd) The Model
5
Ex. 4.1(contd) Developing the Model

• Follow these steps to develop the model
• Input values and range names. Enter the inputs
  given.
• Ads purchased. Enter any values in the
  Number_ads_purchased range.
• Exposures obtained. Enter the formula
  SUMPRODUCT(B6I6,Number_ads_purchased) in cell
  B23 and copy it down to cell B28.
• Total cost. In cell B31 enter the formula
  SUMPRODUCT(B14I14,Number_ads_purchased)
• The solution is not one that would be expected.

6
Ex. 4.1(contd) Sensitivity Analysis

• Solvers sensitivity report is enlightening for
  this solution.

7
Ex. 4.1(contd) Dual Objective Extension of the
Model

• General Flakes has two competing objectives
• Obtain as many exposures as possible
• Keep the total advertising cost as low as
  possible
• The original model minimized total cost and
  constrained the exposures to be at least as large
  as a required level.
• An alternative is to maximize the total number of
  excess exposures and put a budget constraint on
  total cost.

8
Ex. 4.1(contd) Dual Objective Extension of the
Model

• To implement the alternative requires only minor
  modifications to the original.
• Excess exposures. Enter the formula B23-D23 in
  cell F23. This cell becomes the new target cell
  to maximize.
• Budget constraint. Calculate total cost but
  constrain it to be less than or equal to cell
  D23.
• Solver dialog box. Modify the Solver dialog box
  as shown.

9
Ex. 4.1(contd) Dual Objective Extension of the
Model

• For two objective models, one objective must be
  optimized and a constraint must be put on the
  other.
• The result is a trade-off curve.

10
Ex. 4.1(contd) Using Integer Constraints

• To force the changing cells to have integer
  values, you simply add another constraint in the
  Solver dialog box.
• Be aware that Solver must do a lot more work to
  solve problems with integer constraints.

11
Example 4.2 Static Workforce Model

• A post office requires different numbers of
  full-time employees on different days of the
  week. The number of full-time employees required
  each day is given.
• Union rules state that each full-time employee
  must work 5 consecutive days and then receive 2
  days off. They only want to employ full-time
  employees.
• Its objective is to minimize the number of
  full-time employees that must be hired.

12
Ex. 4.2 (contd) The Solution

• In real employee scheduling problems much of the
  work involves forecasting and queuing analysis to
  obtain worker requirements. All of which must be
  done before any schedule optimizing.
• The key to this model is choosing the correct
  changing cells.
• The trick is to define to numbers of employees
  working each of the 7 possible 5 day shifts.

13
Ex. 4.2 (contd) The Model
14
Ex. 4.2 (contd) Developing the Model

• To form the spreadsheet, proceed as follows.
• Inputs and range names. Enter the number of
  employees needed on each day of the week.
• Employees beginning each day. Enter any trial
  values for the number of employees beginning work
  on each day in the Employee_starting range.
• Employees on hand each day. Enter the formula
  B4 in cell B14 and copy it across to cell F14.
  Proceed similarly for rows 15-20, being careful
  to take wrap arounds into account.After
  completing these rows calculate the total number
  who show up each day by entering the formula
  SUM(B14B20) in cell B21 and copying across to
  cell H21.
• Total employees. Calculate the total number of
  employees in cell B25 with the formula
  SUM(Employees_Starting).

15
Ex. 4.2 (contd) The Solver

• Invoke Solver with this dialog box.

16
Ex. 4.2 (contd) The Solution

• A drawback is the number of employees starting
  work on some days is a fraction.
• Its simple to add an integer constraint on the
  changing cells.
• Set Solvers Tolerance to 0 to ensure that you
  get the optimal solution.

17
Ex. 4.2 (contd) Sensitivity Analysis

• How does the work schedule and the total number
  of employees change as the number of employees
  required each day changes?
• Use SolverTable after altering the model
  slightly.
• Move the original requirements up to row 12,
  enter a trail value for the extra number required
  per day in cell K12.
• Enter the formula B12K12 in cell B27 and copy
  it across to H27.
• Solvers sensitivity report was not used
• Solver does not offer a sensitivity report for
  models with integer constraints.
• The sensitivity report is not suited for
  questions about multiple input changes.

18
Ex. 4.2 (contd) Sensitivity Analysis for the
Model
19
Ex. 4.2 (contd) Modeling Issues

• This type of problem dealt with what is called a
  static scheduling model, because it is assumed
  that the post office faces the same situation
  each week.
• In reality demands change and dynamic scheduling
  models are needed.
• In many scheduling models, heuristic methods
  (clever trial and error algorithms) can often be
  used.
• Heuristic solutions are often close to optimal,
  but they are never guaranteed to be optimal.

20
Example 4.3 Aggregate Planning Model

• During the next four months the SureStep Company
  must meet (on time) the following demands for
  pairs of shoes.
• At the beginning of month 1, 500 pairs of shoes
  are on hand, and SureStep has 100 workers.
• A worker is paid 1,500 per month. Each worker
  can work up to 160 hours a month before he or she
  receives overtime.
• A worker is forced to work 20 hours of overtime
  per month and is paid 13 per hour for overtime
  labor.

21
Ex. 4.3 (contd) Aggregate Planning Model

• It takes 4 hours of labor and 15 of raw material
  to produce a pair of shoes.
• At the beginning of each month workers can be
  hired or fired. Each hired worker costs 1600,
  and each fired worker cost 2000.
• At the end of each month, a holding cost of 3
  per pair of shoes left in inventory is incurred.
• SureStep wants to us LP to determine its optimal
  production schedule and labor policy.

22
Ex. 4.3 (contd) The Solution

• Most difficult aspect is knowing which variables
  the company gets to choose and which are
  determined by these decisions.
• The company gets to choose
• The number of workers to hire and fire.
• The number of shoes to produce.
• How many overtime hours to use within this limit.
• All the rest of the are determined.

23
Ex. 4.3 (contd) The Model
24
Ex. 4.3 (contd) Developing the Model

• Developed as follows
• Inputs. Enter the input data in the range B4B14
  and in the Forecasted_demand range.
• Production, hiring and firing plans. Enter any
  trial values for the number of pairs of shoes
  produced each month, the overtime hours used each
  month, the workers hired each month, and the
  workers fired each month.
• Workers available each month. In cell B17 enter
  the initial number of workers available with the
  formula B5. Because the number of workers
  available at the beginning of any other month is
  equal to the number of workers from the previous
  month, enter the formula B20 in cell C17 and
  copy it to the range D17E17. Then in cell B20
  calculate the number of workers available in
  month 1 with the formula B17B18-B19 and copy
  this formula to the range C20E20.
• Overtime capacity. Enter the formula B7B20 in
  cell B25 and copy it to the range C25E25.

25
Ex. 4.3 (contd) Developing the Model

• Production capacity. Calculate the regular-time
  hours available in month 1 in cell B22 with the
  formula B6B20 and copy it to the range
  C22E22 for the other months. Then calculate
  the total hours available for production in cell
  B27 with the formula SUM(B22B23) and copy it to
  the range C27E27. Calculate the production
  capacity for month 1 by entering the formula
  B27/B12 in cell B32, and copy it to the range
  C32E32.
• Inventory each month. Enter the formula B4B30
  in cell B34. For any other month, the inventory
  after production is the previous months ending
  inventory plus tat months production, so enter
  the formula B37C30 in cell C34 and copy it to
  the range D34E34. Then calculate the month 1
  ending inventory in cell B37 with the formula
  B34-B36 and copy it to the range C37E37.

26
Ex. 4.3 (contd) Developing the Model

• Monthly costs. Calculate the various costs shown
  in rows 40 through 45 for month 1 by entering the
  formulas B8B18 B9B19 B10B20
  B11B23 B13B30 B14B37 in cells
  B40 through B45. Then copy the range B40B45 to
  the range C40E45 to calculate these costs for
  the other months.
• Totals. In row 46 and column F, use the SUM
  function to calculate cost totals, with the value
  in F46 being the overall total cost.

27
Ex. 4.3 (contd) Using Solver

• It is often best to ignore such constraints,
  especially when the optimal values are fairly
  large, as are the production quantities in this
  model.
• If the solution then has noninteger values, they
  can be rounded to integers for a solution that is
  at least close to the optimal integer solution.

28
Ex. 4.3 (contd) Solution Sensitivity Analysis

• Because integer constraints make a model harder
  to solve, use them sparingly only when they are
  really needed.
• To ensure that Solver finds the optimal solution
  in a problem where some or all of the changing
  cells must be integers, set the tolerance to 0.
• Many sensitivity analyses could be performed on
  this model.
• One of them uses SolverTable to see how the
  overtime hours used and tot total cost vary with
  the overtime wage rate.

29
Ex. 4.3 (contd) The Rolling Planning Horizon
Approach

• Aggregate planning model is usually implemented
  via a rolling planning horizon.
• SureStep works with a 4-month planning horizon.
• To implement the rolling planning horizon
  context, view the demands as forecasts and
  solve a 4-month model with these forecasts.
• Only month 1 production is implemented.
• Month 1s actual demand is observed and then used
  to calculate the next 4 months.

30
Backlogging NonSmooth Functions

• The term backlogging means that the customer's
  demand will be met at a later date.
• Backordering means the same thing.
• Formulas that contain IF functions accurately
  compute holding and shortage costs but they make
  the target cell a nonlinear function of the
  changing cells.
• When certain functions are used to relate the
  target cell to the changing cells, the resulting
  model becomes not only nonlinear but nonsmooth
• Nonsmooth functions can have discontinuities
  and should be avoided in optimization models.
  They can be handled with a genetic algorithm.

31
4.5 Blending Models

• Linear programming can find the optimal
  combination of outputs as well as the mix of
  inputs that are used to produce the desired
  outputs.
• Blending models usually have various quality
  constraints, often expressed as required
  percentages of various ingredients.
• To keep these models linear (and avoid dividing
  by 0), it is important to clear denominators.