Linear Programming

1
Linear Programming

• Technique for Evaluating Aggregate Planning
  Options

2
Objectives

• Learn a mathematical modeling approach (linear
  programming LP) for strategic resource planning
• Understand characteristics for LPs
• Understand how LPs can be used to determine
  resource, production and inventory aspects of an
  aggregate sales and operations plan
• Understand the impact of using continuous versus
  discrete variables within a LP
• Learn how to use Excel solver to solve LPs and
  how to interpret the output

3
Characteristics of a LP

• A LP model is a model that seeks to maximize or
  minimize a linear objective function subject to a
  set of linear constraints. Thus
• A single objective must exist and be
  mathematically represented as a linear function.
• Resources must be limited and be mathematically
  represented as linear constraints.
• Parameter values are known with certainty.
• There are no interactions between the decision
  variables.
• While the same unit of measure is used within a
  constraint (and the objective), the unit of
  measure can differ across constraints.

4
Example of a LP

• A company makes 3 products A, B and C.
• A B C Available
• Profit 35 45 25
• Labor Hrs 5 7 3 2000 hrs
• Fiberglass 18 25 12 7000 lbs
• At least 100 units each must be made of A, B, C
• How many As, Bs, and Cs should be produced in
  order to maximize total profits?

5

• Incorrect Strategy make as much as possible of
  the most profitable product (B), so make as
  little as possible of the other products (100 As
  and 100 Cs)
• available 2000 7000
• Labor Fiberglass Profit
• make 100 As
• make 100 Cs
• remaining
• How many Bs?

500 1800 3500
300 1200 2500
1200 4000
We run out of fiberglass 1st
1200/7 171
4000/25 160
make 160 Bs 1120 4000 7200
13,200 Total Profit
remaining 80 0
Optimal solution is 13,625 using LP (100 A, 100
B, 225 C) ? Difference of 425
6
LP Formulation Steps

• Define the decision variables (What do they
  represent? Should they be continuous, integer or
  binary?)
• Define the objective function and each constraint
  in words
• Write the mathematical form of the objective
  function using the decision variables (max or
  min)
• Write the mathematical version of each constraint
  
• Write the value of the RHS (constraint value)
• Write the decision variables and the required
  resource associated with each decision variable
  that uses the resource (LHS)
• Write the mathematical relationship (gt, lt, , ,
  ) between the RHS and the LHS of each constraint

7
Linear Programming Formulation
The Decision variables are A of units of
product A to produce, B of units of product B
to produce and C of units of product C to
produce. The objective function is to maximize
profit.
Max Z 35A 45B 25C
ST 5A 7B 3C 2000 Cant exceed
available labor hours
18A 25B 12C 7000 Cant exceed
available fiberglass
A 100
Make at least 100 As
B 100
Make at least 100 Bs
C 100
Make at least 100 Cs
8
Solver Input Screen for LP
9
Excel Answer Report
10
Excel Sensitivity Report
11

• Another Example Using Excel Solver
• A local brewery produces three types of beer
  premium, regular, and light. The brewery has
  enough vat capacity to produce 27,000 gallons of
  beer per month. A gallon of premium beer
  requires 3.5 pounds of barley and 1.1 pounds of
  hops, a gallon of regular requires 2.9 pounds of
  barley and .8 pounds of hops, and a gallon of
  light requires 2.6 pounds of barley and .6 pounds
  of hops. The brewery is able to acquire only
  55,000 pounds of barley and 20,000 pounds of hops
  next month. The brewerys largest seller is
  regular beer, so it wants to produce at least
  twice as much regular beer as it does light beer.
  It also wants to have a competitive market mix
  of beer. Thus, the brewery wishes to produce at
  least 4000 gallons each of light beer and premium
  beer, but not more than 12,000 gallons of these
  two beers combined. The brewery makes a profit
  of 3.00 per gallon on premium beer, 2.70 per
  gallon on regular beer, and 2.80 per gallon on
  light beer. The brewery manager wants to know
  how much of each type of beer to produce next
  month in order to maximize profit.

12

• Example Using Excel Solver
• LP Formulation
• Max Z 3P 2.7R 2.8L
• ST
• P R L lt 27000 capacity
• 3.5P 2.9R 2.6L lt 55000 barley
• 1.1P .8R .6L lt 20000 hops
• R 2L gt 0 21 ratio
• P gt 4000 minimum P requirement
• L gt 4000 minimum L requirement
• P L lt 12000 maximum requirement

13
Instructions for Using Excel to Solve LP Models

• Set up spreadsheet like example in packet.
  (Z-value and LHS column should be
  formulas)
• Select Tools on menu bar. Then select
  Solver.
• Set Target Cell should be the cell of your
  Z-value formula.
• Select Min or Max.
• By Changing Cells should be the range of cells
  for your decision variables values.
• Select Options
• Check 2 boxes Assume Linear Model and Assume
  Non-Negative. Then click OK.
• Select Add to add constraints.

14

• 9. In Cell Reference box point to LHS formula
  of first constraint. Select lt, , or gt. Click
  on Constraint box and point to RHS value of
  first constraint. Click Add for next
  constraint or OK if finished.
• 10. Repeat Step 9 for each other constraint.
• 11. Select Solve.
• 12. If it worked okay you should get the message
  Solver found a solution. All constraints and
  optimality conditions are satisfied. If you do
  not get this message you should modify your
  formulation or check for mistakes.
• 13. In the Solver Results window under Reports
  click on Answer. Then hold down the Ctrl
  button while you click on Sensitivity. Then
  click OK.
• 14. Print your final worksheet showing the new
  values, print the Answer Report and print the
  Sensitivity Report.

15
sumproduct(B3D3,B2D2) is equivalent to
B3B2 C3C2 D3D2
16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23

• 1. The Ohio Creek Ice Cream Company is planning
  production for next week. Demand for Ohio Creek
  premium and light ice cream continue to outpace
  the companys production capacities. Ohio Creek
  earns a profit of 100 per hundred gallons of
  premium and 100 per hundred gallons of light ice
  cream. Two resources used in ice cream
  production are in short supply for next week
  the capacity of the mixing machine and the amount
  of high-grade milk. After accounting for
  required maintenance time, the mixing machine
  will be available 140 hours next week. A hundred
  gallons of premium ice cream requires .3 hours of
  mixing and a hundred gallons of light ice cream
  requires .5 hours of mixing. Only 28,000 gallons
  of high-grade milk will be available for next
  week. A hundred gallons of premium ice cream
  requires 90 gallons of milk and a hundred gallons
  of light ice cream requires 70 gallons of milk.

24

• P of gallons of Premium ice cream to make
• L of gallons of Light ice cream to make
• Max Z 100P 100L
• ST
• .3P .5L 140 capacity of mixing machine
• 90P 70L 28000 max milk available
• Solution P 175 L 175 Z 35,000

25

• Steps for graphically determining the solution
  for a 2 decision variable LP.
• Plot a constraint (find axis intercepts by
  setting one of the decision variable equal to 0)
• Identify which side of the constraint is the
  feasible area.
• Repeat steps 1 and 2 until all constraints are
  addressed.
• Identify the feasible area that satisfies all
  constraints.
• Plot the objective function by randomly choosing
  a value of each decision variable that is in the
  feasible zone.
• Move the objective function through the feasible
  region until it hits an outermost point within
  the feasible region.

26

• 2. The White Horse Apple Products Company
  purchases apples from local growers and makes
  applesauce and apple juice. It costs 0.60 to
  produce a jar of applesauce and 0.85 to produce
  a bottle of apple juice. The company has a
  policy that at least 30 but not more than 60 of
  its output must be applesauce.
• The company wants to meet but not exceed the
  demand for each product. The marketing manager
  estimates that the demand for applesauce is a
  maximum of 5,000 jars, plus an additional 3 jars
  for each 1 spent on advertising. The maximum
  demand for apple juice is estimated to be 4,000
  bottles, plus an additional 5 bottles for every
  1 spent to promote apple juice. The company has
  16,000 to spend on producing and advertising
  applesauce and apple juice. Applesauce sells for
  1.45 per jar apple juice sells for 1.75 per
  bottle. The company wants to know how many units
  of each to produce and how much advertising to
  spend on each in order to maximize profit.

27

• S jars apple Sauce to make
• J bottles apple Juice to make
• SA for apple Sauce Advertising
• JA for apple Juice Advertising
• Max Z 1.45S 1.75J - .6S - .85J SA JA
• ST
• S .3(S J) at least 30 apple sauce
• S .6(S J) no more than 60 apple sauce
• S 5000 3SA dont exceed demand for apple
  sauce
• J 4000 5JA dont exceed demand for apple
  juice
• .6S .85J SA JA 16000 budget

28

• 3. The Jane Deere Company manufactures tractors
  in Provo, Utah. Jeremiah Goldstein, the
  production planner, is scheduling tractor
  production for the next three months. Factors
  that Mr. Goldstein must consider include sales
  forecasts, straight-time and overtime labor hours
  available, labor cost, storage capacity, and
  carrying cost. The marketing department has
  forecasted that the number of tractors shipped
  during the next three months will be 250, 305,
  and 350. Each tractor requires 100 labor hours
  to produce. In each month 29,000 straight-time
  labor hours will be available, and company policy
  prohibits overtime hours from exceeding 10 of
  straight-time hours. Straight-time labor cost
  rate is 20 per hour, including benefits. The
  overtime labor cost rate is 150
  (time-and-a-half) of the straight-time rate.
  Excess production capacity during a month may be
  used to produce tractors that will be stored and
  sold during a later month. However, the amount
  of storage space can accommodate only 40
  tractors. A carrying cost of 600 is charged for
  each month a tractor is stored (if not shipped
  during the month it was produced). Currently, no
  tractors are in storage.
• How many tractors should be produced in each
  month using straight-time and using overtime in
  order to minimize total labor cost and carrying
  cost? Sales forecasts, straight-time and
  overtime labor capacities, and storage capacity
  must be adhered to. (Tip During each month,
  all sources of tractors must exactly equal
  uses of tractors.)

29

• 9 variables
• S1 tractors produced in month 1 using
  straight-time
• S2 tractors produced in month 2 using
  straight-time
• S3 tractors produced in month 3 using
  straight-time
• V1 tractors produced in month 1 using
  overtime
• V2 tractors produced in month 2 using
  overtime
• V3 tractors produced in month 3 using
  overtime
• C1 tractors carried in warehouse at end of
  month 1
• C2 tractors carried in warehouse at end of
  month 2
• C3 tractors carried in warehouse at end of
  month 3
• sources of tractors uses of tractors (for each
  month)
• production beg.inv. sales end.inv.

30

• Min Z 2000S1 2000S2 2000S3 3000V1
  3000V2
• 3000V3 600C1 600C2 600C3
• ST
• S1 V1 0 250 C1 month 1 sources uses
• S2 V2 C1 305 C2 month 2 sources uses
• S3 V3 C2 350 C3 month 3 sources uses
• 100S1 29000 straight-time capacity month 1
• 100S2 29000 straight-time capacity month 2
• 100S3 29000 straight-time capacity month 3
• 100V1 2900 overtime capacity month 1
• 100V2 2900 overtime capacity month 2
• 100V3 2900 overtime capacity month 3
• C1 40 storage capacity month 1
• C2 40 storage capacity month 2
• C3 40 storage capacity month 3

31

• 4. MadeRite, a manufacturer of paper stock for
  copiers and printers, produces cases of finished
  paper stock at Mills 1, 2, and 3. The paper is
  shipped to Warehouses A, B, C, and D. The
  shipping cost per case, the monthly warehouse
  requirements, and the monthly mill production
  levels are
• Monthly Mill
• Destination Production
• A B C D
  (cases)
• Mill 1 5.40 6.20 4.10 4.90
  15,000
• Mill 2 4.00 7.10 5.60
  3.90 10,000
• Mill 3 4.50 5.20 5.50
  6.10 15,000
• Monthly Warehouse
• Requirement (cases) 9,000 9,000 12,000
  10,000
• How many cases of paper should be shipped per
  month from each mill to each warehouse to
  minimize monthly shipping costs?

32

• A1 of units shipped from Mill 1 to
  Destination A
• C3 of units shipped from Mill 3 to
  Destination C
• (12 variables)
• Min Z 5.4A1 6.2B1 4.1C1 4.9D1 4.0A2
  7.1B2
• 5.6C2 3.9D2 4.5A3 5.2B3 5.5C3 6.1D3
• ST
• A1 B1 C1 D1 15000 Mill 1 capacity
• A2 B2 C2 D2 10000 Mill 2 capacity
• A3 B3 C3 D3 15000 Mill 3 capacity
• A1 A2 A3 9000 Destination A demand
• B1 B2 B3 9000 Destination B demand
• C1 C2 C3 12000 Destination C demand
• D1 D2 D3 10000 Destination D demand