Strategic Allocation of Resources

1
Chapter 8

• Strategic Allocation of Resources
• (Linear Programming)

2

• 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?

3

• 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
4
Linear Programming Formulation
A of units of product A to produce B of
units of product B to produce C of units of
product C to produce
Max Z 35A 45B 25C ST
5A 7B 3C 2000 labor hours
18A 25B 12C 7000 fiberglass
A 100 minimum A
B 100 minimum B
C 100 minimum C
5

• Linear Programming using Lindo software
• Max 35 A 45 B 25 C
• Subject to
• 2) 5 A 7 B 3 C lt 2000
• 3) 18 A 25 B 12 C lt 7000
• 4) A gt 100
• 5) B gt 100
• 6) C gt 100
• End
• LP Optimum found at step 4

6
Objective Function Value

• 1) 13625.000
• Variable Value Reduced Cost
• A 100.000000 .000000
• B 100.000000 .000000
• C 225.000000 .000000
• Row Slack or Surplus Dual Prices
• 2) 125.000000 .000000
• 3) .000000 2.083333
• 4) .000000 -2.500000
• 5) .000000 -7.083333
• 6) 125.000000 .000000
• No. Iterations 4

7

• Ranges in which the basis is unchanged
• Obj Coefficient Ranges
• Variable Current Allowable Allowable
• Coef Increase Decrease
• A 35.000000 2.500000 Infinity
• B 45.000000 7.083333 Infinity
• C 25.000000 Infinity 1.666667
• Righthand Side Ranges
• Row Current Allowable Allowable
• RHS Increase Decrease
• 2 2000.000000 Infinity
  125.000000
• 3 7000.000000 500.000000
  1500.000000
• 4 100.000000 83.333340
  100.000000
• 5 100.000000 60.000000
  100.000000
• 6 100.000000 125.000000
  Inifinity

8

• Example Using Excel Solver
• 10. 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.

9

• 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

10
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.

11

• 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.

12
A B C D E F G
1 P R L Objective
2 Dec Vars 0 0 0 Value (Z)
3 Obj Coef 3 2.7 2.8 sumproduct(B3D3,B2D2)
4
5 Constraints LHS lt,,gt RHS
6 capacity 1 1 1 sumproduct(B6D6,B2D2) lt 27000
7 barley 3.5 2.9 2.6 sumproduct(B7D7,B2D2) lt 55000
8 hops 1.1 0.8 0.6 sumproduct(B8D8,B2D2) lt 20000
9 21 ratio 1 -2 sumproduct(B9D9,B2D2) gt 0
10 min req. P 1 sumproduct(B10D10,B2D2) gt 4000
11 min req. L 1 sumproduct(B11D11,B2D2) gt 4000
12 max req. 1 1 sumproduct(B12D12,B2D2) lt 12000
sumproduct(B3D3,B2D2) is equivalent to
B3B2 C3C2 D3D2
13
(No Transcript)
14
(No Transcript)
15
(No Transcript)
16
(No Transcript)
17
A B C D E F G
1 P R L Objective
2 Dec Vars 4000 9761.905 4880.952 Value (Z)
3 Obj Coef 3 2.7 2.8 52023.81
4
5 Constraints LHS lt,,gt RHS
6 capacity 1 1 1 18642.86 lt 27000
7 barley 3.5 2.9 2.6 55000 lt 55000
8 hops 1.1 0.8 0.6 15138.1 lt 20000
9 21 ratio 1 -2 0 gt 0
10 min req. P 1 4000 gt 4000
11 min req. L 1 4880.952 gt 4000
12 max req. 1 1 8880.952 lt 12000
18
Microsoft Excel 10.0 Answer Report Microsoft Excel 10.0 Answer Report Microsoft Excel 10.0 Answer Report Microsoft Excel 10.0 Answer Report
Worksheet Book1Sheet1 Worksheet Book1Sheet1 Worksheet Book1Sheet1
Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM


Target Cell (Max) Target Cell (Max) Target Cell (Max)
Cell Name Original Value Final Value
E3 Obj Coef Value (Z) 0 52023.80952


Adjustable Cells Adjustable Cells Adjustable Cells
Cell Name Original Value Final Value
B2 Dec Vars P 0 4000
C2 Dec Vars R 0 9761.904762
D2 Dec Vars L 0 4880.952381


Constraints Constraints Constraints
Cell Name Cell Value Formula Status Slack
E6 capacity LHS 18642.85714 E6ltG6 Not Binding 8357.142857
E7 barley LHS 55000 E7ltG7 Binding 0
E8 hops LHS 15138.09524 E8ltG8 Not Binding 4861.904762
E9 21 ratio LHS 0 E9gtG9 Binding 0
E10 min req. P LHS 4000 E10gtG10 Binding 0
E11 min req. L LHS 4880.952381 E11gtG11 Not Binding 880.952381
E12 max req. LHS 8880.952381 E12ltG12 Not Binding 3119.047619
19
Microsoft Excel 10.0 Sensitivity Report Microsoft Excel 10.0 Sensitivity Report Microsoft Excel 10.0 Sensitivity Report Microsoft Excel 10.0 Sensitivity Report Microsoft Excel 10.0 Sensitivity Report Microsoft Excel 10.0 Sensitivity Report
Worksheet Book1Sheet1 Worksheet Book1Sheet1 Worksheet Book1Sheet1 Worksheet Book1Sheet1 Worksheet Book1Sheet1
Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM Report Created 1/15/2003 93520 AM


Adjustable Cells Adjustable Cells Adjustable Cells Adjustable Cells
      Final Reduced Objective Allowable Allowable
Cell Name Name Value Cost Coefficient Increase Decrease
B2 Dec Vars P Dec Vars P 4000 0 3 0.416666667 1E30
C2 Dec Vars R Dec Vars R 9761.904762 0 2.7 0.423076923 0.5
D2 Dec Vars L Dec Vars L 4880.952381 0 2.8 1E30 0.379310345

Constraints Constraints Constraints Constraints
      Final Shadow Constraint Allowable Allowable
Cell Cell Name Value Price R.H. Side Increase Decrease
E6 E6 capacity LHS 18642.85714 0 27000 1E30 8357.142857
E7 E7 barley LHS 55000 0.976190476 55000 18563.63636 7400
E8 E8 hops LHS 15138.09524 0 20000 1E30 4861.904762
E9 E9 21 ratio LHS 0 -0.130952381 0 2551.724138 9034.482759
E10 E10 min req. P LHS 4000 -0.416666667 4000 2114.285714 4000
E11 E11 min req. L LHS 4880.952381 0 4000 880.952381 1E30
E12 E12 max req. LHS 8880.952381 0 12000 1E30 3119.047619
20

• 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.

21

• 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

22

• 2. The Sureset Concrete Company produces
  concrete in a continuous process. Two
  ingredients in the concrete are sand, which
  Sureset purchases for 6 per ton, and gravel,
  which costs 8 per ton. Sand and gravel together
  must make up exactly 75 of the weight of the
  concrete. Furthermore, no more than 40 of the
  concrete can be sand, and at least 30 of the
  concrete must be gravel. Each day 2,000 tons of
  concrete are produced.

23

• S tons of sand to add to mixture
• G tons of gravel to add to mixture
• Min Z 6S 8G
• ST
• S G 1500 sand gravel are 75 of 2000
• S 800 sand no more than 40 of 2000
• G 600 gravel at least 30 of 2000
• Solution S 800 G 700 Z 10,400

24

• 3. A ship has two cargo holds, one fore and one
  aft. The fore cargo hold has a weight capacity
  of 70,000 pounds and a volume capacity of 30,000
  cubic feet. The aft hold has a weight capacity
  of 90,000 pounds and a volume capacity of 40,000
  cubic feet. The shipowner has contracted to
  carry loads of packaged beef and grain. The
  total weight of the available beef is 85,000
  pounds the total weight of the available grain
  is 100,000 pounds. The volume per mass of the
  beef is 0.2 cubic foot per pound, and the volume
  per mass of the grain is 0.4 cubic foot per
  pound. The profit for shipping beef is 0.35 per
  pound, and the profit for shipping grain is 0.12
  per pound. The shipowner is free to accept all
  or part of the available cargo he wants to know
  how much meat and grain to accept in order to
  maximize profit.

25

• BF lbs beef to load in fore cargo hold
• BA lbs beef to load in aft cargo hold
• GF lbs grain to load in fore cargo hold
• GA lbs grain to load in aft cargo hold
• Max Z .35 BF .35BA .12GF .12 GA
• ST
• BF GF 70000 fore weight capacity lbs
• BA GA 90000 aft weight capacity lbs
• .2BF .4GF 30000 for volume capacity cubic
  feet
• .2BA .4GA 40000 for volume capacity cubic
  feet
• BF BA 85000 max beef available
• GF GA 100000 max grain available

26

• 4. 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

• 5. Dr. Maureen Becker, the head administrator at
  Jefferson County Regional Hospital, must
  determine a schedule for nurses to make sure
  there are enough nurses on duty throughout the
  day. During the day, the demand for nurses
  varies. Maureen has broken the day into 12
  two-hour periods. The slowest time of the day
  encompasses the three periods from 1200 A.M. to
  600 A.M., which, beginning at midnight, require
  a minimum of 30, 20, and 40 nurses, respectively.
  The demand for nurses steadily increases during
  the next four daytime periods. Beginning with
  the 600 A.M. 800 A.M. period, a minimum of
  50, 60, 80, and 80 nurses are required for these
  four periods, respectively. After 200 P.M. the
  demand for nurses decreases during the afternoon
  and evening hours. For the five two-hour periods
  beginning at 200 P.M. and ending at midnight,
  70, 70, 60, 50, and 50 nurses are required,
  respectively. A nurse reports for duty at the
  beginning of one of the two-hour periods and
  works eight consecutive hours (which is required
  in the nurses contract). Dr. Becker wants to
  determine a nursing schedule that will meet the
  hospitals minimum requirements throughout the
  day while using the minimum number of nurses.

29

• 12 variables (one for each time block)
• X1 of nurses starting at Midnight working 8
  hours
• X2 2am
• X3 4am
• X4 6am
• X5 8am
• X6 10am
• X7 Noon
• X8 2pm
• X9 4pm
• X10 6pm
• X11 8pm
• X12 10pm

30

• Min Z X1 X2 X3 X4 X5 X6 . X11
  X12
• ST
• X1 X10 X11 X12 30 midn 2am
• X1 X2 X11 X12 20 2am 4am
• X1 X2 X3 X12 40 4am 6am
• X1 X2 X3 X4 50 6am 8am
• X2 X3 X4 X5 60 8am 10am
• X3 X4 X5 X6 80 10amNoon
• X4 X5 X6 X7 80 Noon 2pm
• X5 X6 X7 X8 70 2pm 4pm
• X6 X7 X8 X9 70 4pm 6pm
• X7 X8 X9 X10 60 6pm 8pm
• X8 X9 X10 X11 50 8pm 10pm
• X9 X10 X11 X12 50 10pm midn

31

• 6. The Donnor meat processing firm produces
  wieners from four ingredients chicken, beef,
  pork, and a cereal additive. The firm produces
  three types of wieners regular, beef, and
  all-meat. The company has the following amounts
  of each ingredient available on a daily basis.
• _____________________________________________
• lb/Day Cost/lb()
• Chicken 200 .20
• Beef 300 .30
• Pork 150 .50
• Cereal Additive 400 .05
• Each type of wiener has certain ingredient
  specifications, as follows.
• __________________________________________________
  ______________________________
• Specifications Selling
  Price/lb()
• Regular Not more than 10 beef and pork
  combined
• Not less than 20 chicken 0.90
• Beef Not less than 75 beef 1.25
• All-Meat No cereal additive
• Not more than
• 50 beef and pork combined
  1.75
• The firm wants to know the amount of wieners of
  each type to produce.

32

• 14 variables (you could also formulate it with
  11 variables)
• CR lbs Chicken ingredient in Regular wiener
  per day
• CB lbs Chicken ingredient in Beef wiener per
  day
• CM lbs Chicken ingredient in all-Meat wiener
  per day
• BR lbs Beef ingredient in Regular wiener per
  day
• BB lbs Beef ingredient in Beef wiener per day
• BM lbs Beef ingredient in all-Meat wiener per
  day
• PR lbs Pork ingredient in Regular wiener per
  day
• PB lbs Pork ingredient in Beef wiener per day
• PM lbs Pork ingredient in all-Meat wiener per
  day
• AR lbs Additive ingredient in Regular wiener
  per day
• AB lbs Additive ingredient in Beef wiener per
  day
• R total lbs of Regular wiener
• B total lbs of Beef wiener
• M total lbs of all-Meat wiener

33

• Max Z 0.90R 1.25 B 1.75 M - .2CR - .2CB -
  .2CM - .3BR - .3BB
• - .3BM - .5PR - .5PB - .5PM - .05AR - .05AB
• ST
• CR BR PR AR R R is sum of all ingredients
  in Regular
• CB BB PB AB B B is sum of all
  ingredients in Beef
• CM BM PM M M is sum of all ingredients in
  Meat
• CR CB CM 200 max Chicken ingredient
  available
• BR BB BM 300 max Beef ingredient available
• PR PB PM 150 max Pork ingredient available
• AR AB 400 max Additive ingredient available
• BR PR .1R not more than 10 BRPR combined
• CR .2R not less than 20 CR in Regular
• BB .75B not less than 75 BB in Beef
• BM PM .5M not more than 50 BMPM combined

34

• 7. 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.)

35

• 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.

36

• 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

37

• 8. 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?

38

• 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

39

• 9. A company has three research projects that it
  wants to do, and has three research teams that
  can do the projects. Any team could do any
  project but can only do one project. Some teams
  are better skilled at certain projects and could
  do them at lower costs. The estimated cost of
  each team doing each project (in ,000s) is shown
  below. Which team should do which project?
• Project
• 1 2 3
• A 87 62 76
• Team B 81 76 64
• C 77 54 70

40

• A1 1 if team A does project 1
• 0 if not (9 variables)
• Min Z 87A1 62A2 76A3 81B1 76B2 64B3
• 77C1 54C2 70C3
• ST
• A1 A2 A3 1
• B1 B2 B3 1 Each team does exactly one
  project
• C1 C2 C3 1
• A1 B1 C1 1
• A2 B2 C2 1 Each project is done exactly
  once
• A3 B3 C3 1

41
(No Transcript)