Posted on

# Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

P4-18, Sensitivity Report 1

Problem 3-32

Oat Grain Mineral
Number of pounds 1.500 1.000 2.500
Cost \$0.33 \$0.44 \$0.57 \$2.36
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

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

4-18:
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) determing 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

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?

P4-22 Sensitivity Report

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 LHS Sign RHS 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 respectivly 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:

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

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
Cell 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
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? Please put answers below in yellow fields respectively
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? since this is not binding, firm would not be interested in obtaining any additional 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?

For instant digital download of the above solution, Please click on the “PURCHASE” link below to get the tutorial for Managerial Decision Modeling Module 4_P 4-18_P4-22_P4-24_Sensitivity Report_Worksheet Answer

For instant digital download of the above solution or tutorial, please click on the below link and make an instant purchase. You will be guided to the PAYPAL Standard payment page wherein you can pay and you will receive an email immediately with a download link.