Problem P 4-18:
Oat Grain Mineral
Number of pounds 1.500 1.000 2.500
Cost $0.33 $0.44 $0.46 $2.08
Constraints:
Nutrient A 2.0 3.0 1.0 8.50 >= 6
Nutrient B 0.5 1.0 0.5 3.00 >= 2
Nutrient C 3.0 5.0 6.0 24.50 >= 9
Nutrient D 1.0 1.5 2.0 8.00 >= 8
Nutrient E 0.5 0.5 1.5 5.00 >= 5
Feed needed 1 1 1 5.00 = 5
LHS Sign RHS
Microsoft Excel 12.0 Sensitivity Report
Worksheet: [P4-18_MySOLN.xls]P3-32
Report Created: 3/23/2013 6:50:04 PM
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$4 Number of pounds Oat 1.500 0.000 0.33 0.11 0.02
$C$4 Number of pounds Grain 1.000 0.000 0.44 0.01 0.11
$D$4 Number of pounds Mineral 2.500 0.000 0.57 1E+30 0.02
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$12 Feed needed 5.00 0.10 5 1 0.6
$E$7 Nutrient A 8.50 0.00 6 2.5 1E+30
$E$8 Nutrient B 3.00 0.00 2 1 1E+30
$E$9 Nutrient C 24.50 0.00 9 15.5 1E+30
$E$10 Nutrient D 8.00 0.22 8 0.75 0.5
$E$11 Nutrient E 5.00 0.02 5 0.5 1.5
Consider the boarding stable fee problem presented in ch3, problem 3-32, page 112. Use "Solver" to create the Sensitivity Report for this LP (Linear Programing) problem. Use this report to answer the following questions. (Each question is independent of each other)
Step 1: Create the Sensitivity Report required to help answer the following:
Question A:
A price of grain decreases by $0.01 per pound.
will the optimal solution change? Answer -> :
hint: look at sensitivity report for grain, allowable decrease (in this case).
Question B:
Which constrains are binding? Interpert the shadow price for the binding constraints.
Hint: this question requires two steps:
1) determine which constraint are binding..Answer 1 ->:
2) interpert th shadow price for the binding constraints:
this is the shadow price shown. Show the shadow price for each binding constraint answer 2 ->:
Question C:
What would happen to the total cost if the price of mineral decreased by 20% from its current value?
help steps:
1) multiply .8 times the objective coefficient ?? for mineral .8 * .57 = result ->:
2) subtract result from the objective coefficient...result1 ->
step 2 is the decrease of 20%
3) is result 1 within the allowable decrease?
4) if step 3 is within allowable decrease, what is the new cost?
replace result 1 with solver cost for "mineral" in problem 3-32. what is the new target (green) value? Answer ->:
Question D:
For what price range of oats is the current solution optimal?
Problem P 4-22
Good-to-Go Suitcase Company
Standard Deluxe Luxury
Solution value 540.00 252.00 0.00
Selling price per unit $36.05 $39.50 $43.30 $29,421.00
Material cost per unit $6.25 $7.50 $8.50 $5,265.00
Labor cost per unit $19.80 $23.00 $25.30 $16,488.00
Profit $10.00 $9.00 $9.50 $7,668.00
Constraints Cost
Cutting & Coloring 0.70 1.00 1.00 630.00 < = 630 $10 Assembly 0.50 0.83 0.67 480.00 <= 600 $6 Finishing 1.00 0.67 0.90 708.00 <= 708 $9 Quality & Packaging 0.10 0.25 0.40 117.00 <= 135 $8 Polishing 0.17 0.25 0.33 153.18 <= 170 LHS Sign RHS (b) Suppose Good-to-Go is considering including a polishing process, the cost of which would be added directly to the price. Each Standard suit- case would require 10 minutes of time in this treatment, each Deluxe suitcase would need 15 minutes, and each Luxury suitcase would need 20 minutes. Would the current production plan change as a result of this additional process if 170 hours of polishing time were available?
Explain your answer. Good-to-Go Suitcase Company Standard Deluxe Luxury Solution value 0.00 0.00 0.00 Selling price per unit $36.05 $39.50 $43.30 $29,421.00 Material cost per unit $6.25 $7.50 $8.50 $5,265.00 Labor cost per unit $19.80 $23.00 $25.30 $16,488.00 Profit $10.00 $9.00 $9.50 $7,668.00 Constraints Cost Cutting & Coloring 0.70 1.00 1.00 630.00 <= 630 $10 Assembly 0.50 0.83 0.67 480.00 <= 600 $6 Finishing 1.00 0.67 0.90 708.00 <= 708 $9 Quality & Packaging 0.10 0.25 0.40 117.00 <= 135 $8 Waterproofing 1.00 1.50 1.75 918.00 <= 900 LHS Sign RHS (c) Now consider the addition of a waterproofing process where each Standard suitcase would use 1 hour of time in the process, each Deluxe suitcase would need 1.5 hours, and each Luxury suitcase would require 1.75 hours. Would this change the production plan if 900 hours were available?
Why or why not? Microsoft Excel 14.0 Sensitivity Report Problems P4-22&23. Good-to-Go Suitcase Company Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $B$4 Solution value Standard 540.00 0.00 10.00 3.50 2.56 $C$4 Solution value Deluxe 252.00 0.00 9.00 5.29 1.61 $D$4 Solution value Luxury 0.00 -1.12 9.50 1.12 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $E$10 Cutting & Coloring 630.00 4.38 630 52.36 134.40 $E$11 Assembly 480.00 0.00 600 1E+30 120.00 $E$12 Finishing 708.00 6.94 708 192.00 128.00 $E$13 Quality & Packaging 117.00 0.00 135 1E+30 18.00 Problem 4-22 Question A: 1) what is the optimal production plan? Hint: look at solver "By Changing Fields" (yellow)...answer -->
2) Which of Resources are Scarce? .............answer ->
Question B:
hint:
1) since the additional times are in minutes and the times in solver (constraints) are in hours, we need to convert the minutes to hours. Do the following:
1a) 10 min = 10 divided by 60 = 0.167 hours (standard suitcase)
1b) 15 min = 15 divided by 60 = 0.25 hours (Delux Suitcase)
1c) 20 min = 20 divided by 60 = 0.33 hours (Luxury Suitcase)
2) now you will need to add a new constraint to the solver problem. Enter the above hours respectively for the new constraint. Make the RHS = 170. you will have to put the sumproduct equation for LHS. Then solve...does the product target (green) value change? answer:
Explain your answer?
Question C:
same approach to solve as above. Remember now new constraints are in hours..so nothing to convert here. Also, make sure you use the original solver values when solver. Don't use anything from part B (since these questions are independent of each other).
Would this change the production plan? Why or Why not?
Problem 4-24
Strollers-to-Go Company
TiniTote TubbyTote ToddleTote
Solution value 100.00 35.00 90.00
Selling price per unit $63.75 $82.50 $66.00 $15,202.50
Material cost per unit $4.00 $6.00 $5.50 $1,105.00
Labor cost per unit $50.50 $67.75 $51.00 $12,011.25
Profit $9.25 $8.75 $9.50 $2,086.25
Constraints Cost
Fabrication 3.0 4.0 2.0 620.00 < = 620 $8.25 Sewing 2.0 1.0 2.0 415.00 <= 500 $8.50 Assembly 1.0 3.0 2.0 385.00 <= 480 $8.75 Tinitote demand 1.0 100.00 <= 180 Tubbytote demand 1.0 35.00 <= 70 Toddletote demand 1.0 90.00 <= 160 Toddletote max prod ratio -0.4 -0.4 0.6 0.00 <= 0 Tinitote min prod 1.0 100.00 >= 90
Tubbytote min prod 1.0 35.00 >= 35
Toddletote min prod 1.0 90.00 >= 80
LHS Sign RHS
Microsoft Excel 14.0 Sensitivity Report
Problems 4-24to27. Strollers-to-Go Company
Variable Cells
Final Reduced Objective Allowable Allowable
Name Value Cost Coefficient Increase Decrease
$B$4 Solution value TiniTote 100.00 0.00 9.25 5.00 3.33
$C$4 Solution value TubbyTote 35.00 0.00 8.75 4.10 1E+30
$D$4 Solution value ToddleTote 90.00 0.00 9.50 1E+30 3.33
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$10 Fabrication 620.00 3.60 620.00 110.50 43.33
$E$11 Sewing 415.00 0.00 500.00 1E+30 85.00
$E$12 Assembly 385.00 0.00 480.00 1E+30 95.00
$E$13 Tinitote demand 100.00 0.00 180.00 1E+30 80.00
$E$14 Tubbytote demand 35.00 0.00 70.00 1E+30 35.00
$E$15 Toddletote demand 90.00 0.00 160.00 1E+30 70.00
$E$16 Toddletote max prod ratio 0.00 3.85 0.00 13.00 8.67
$E$17 Tinitote min prod 100.00 0.00 90.00 10.00 1E+30
$E$18 Tubbytote min prod 35.00 -4.10 35.00 8.13 35.00
$E$19 Toddletote min prod 90.00 0.00 80.00 10.00 1E+30
Problem 4-24
Question A:
A1) How many strollers of each type should Stroller-To-Go make?
Hint: Optimal production plan is to make what? Look at the By Changing Fields (yellow). List them as the answer
A2) what is the profit?
Hint: what is shown in target (green field).
A3) Which constraints are Binding?
Everyone should be able to answer this now. Same as previous 4-18 question
Question B:
How Much Labor time is being used in the fabrication, sewing, and assembly areas?
Hint: answers found in Sensitivity report for constraints
Fabrication only?
Sewing?
Assembly?
Question C:
How much would Strollers-To-Go be willing to pay for an additional hour of:
fabrication time?
Sewing time?
Question D:
Is Strollers-to-Go producing any product at its maximum sales level?
Is it producing any product at its minimum level?