Answered step by step
Verified Expert Solution
Question
1 Approved Answer
can you please assist in decision analysis area. I have attached the questions, answers and the spreadsheets needed, but not sure how to get the
can you please assist in decision analysis area. I have attached the questions, answers and the spreadsheets needed, but not sure how to get the answers in the solver spreadsheet. thanks for your help.
Microsoft Excel 15.0 Answer Report Worksheet: [Book1]Sheet1 Report Created: 6/8/2016 1:18:01 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: GRG Nonlinear Solution Time: 0 Seconds. Iterations: 5 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Convergence 0.0001, Population Size 100, Random Seed 0, Derivatives Forward, Require Bounds Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Min) Cell $F$5 Cost Name Variable Cells Cell Name $C$4 Number of pounds Oat $D$4 Number of pounds Grain $E$4 Number of pounds Mineral Constraints Cell Name $F$12 Feed Needed $F$7 Nutrient A $F$8 Nutrient B $F$9 Nutrient C $F$10 Nutrient D $F$11 Nutrient E Original Value Final Value $ - $ 2.35 Original Value 0.00 0.00 0.00 Cell Value 5.000001 8.50 3.00 24.50 8.00 5.00 Final Value Integer 1.50 Contin 1.00 Contin 2.50 Contin Formula $F$12=$H$12 $F$7>=$H$7 $F$8>=$H$8 $F$9>=$H$9 $F$10>=$H$10 $F$11>=$H$11 Status Binding Not Binding Not Binding Not Binding Binding Binding Slack 0 2.50 1.00 15.50 0.00 0.00 Microsoft Excel 15.0 Sensitivity Report Worksheet: [Book1]Sheet1 Report Created: 6/8/2016 1:18:01 PM Variable Cells Cell Name $C$4 Number of pounds Oat $D$4 Number of pounds Grain $E$4 Number of pounds Mineral Final Value 1.5000025 0.999999 2.4999995 Reduced Gradient 0 0 0 Constraints Cell $F$12 $F$7 $F$8 $F$9 $F$10 $F$11 Name Feed Needed Nutrient A Nutrient B Nutrient C Nutrient D Nutrient E Final Lagrange Value Multiplier 5.000001 0.110000029 8.5000015 0 3 0 24.4999995 0 8 0.199999988 5 0.039999992 Microsoft Excel 15.0 Limits Report Worksheet: [Book1]Sheet1 Report Created: 6/8/2016 1:18:01 PM Cell $F$5 Cost Cell $C$4 $D$4 $E$4 Objective Name Variable Name Number of pounds Oat Number of pounds Grain Number of pounds Mineral Value $ 2.35 Value 1.50 1.00 2.50 Lower Objective Limit Result 1.50 2.35 1.00 2.35 2.50 2.35 Upper Objective Limit Result 1.50 2.35 1.00 2.35 2.50 2.35 Number of pounds Cost Constraints Nutrient A Nutrient B Nutrient C Nutrient D Nutrient E Feed Needed Oat Grain Mineral 1.50 1.00 2.50 $ 0.33 $ 0.43 $ 0.57 $ 2.0 0.5 3.0 1.0 0.5 3.0 1.0 5.0 1.5 0.5 1 1.0 0.5 6.0 2.0 1.5 1 2.35 8.50 3.00 24.50 8.00 5.00 1 5.000001 LHS >= >= >= >= >= = Sign 6 2 9 8 5 5 RHS \fProblem 3-32 Number of pounds Cost Constraints: Nutrient A Nutrient B Nutrient C Nutrient D Nutrient E Feed needed Oat 1.500 $0.33 2.0 0.5 3.0 1.0 0.5 1 Grain Mineral 1.000 2.500 $0.44 $0.57 $2.36 3.0 1.0 5.0 1.5 0.5 1 1.0 0.5 6.0 2.0 1.5 1 8.50 >= 6 3.00 >= 2 24.50 >= 9 8.00 >= 8 5.00 >= 5 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 Cell $B$4 $C$4 $D$4 Name Number of pounds Oat Number of pounds Grain Number of pounds Mineral Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 1.500 0.000 0.33 0.11 0.02 1.000 0.000 0.44 0.01 0.11 2.500 0.000 0.57 1.00E+030 0.02 Constraints Cell $E$12 $E$7 $E$8 $E$9 $E$10 $E$11 Name Feed needed Nutrient A Nutrient B Nutrient C Nutrient D Nutrient E Final Shadow Constraint Allowable Value Price R.H. Side Increase 5.00 0.10 5 1 8.50 0.00 6 2.5 3.00 0.00 2 1 24.50 0.00 9 15.5 8.00 0.22 8 0.75 5.00 0.02 5 0.5 Allowable Decrease 0.6 1.00E+030 1.00E+030 1.00E+030 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 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 332. what is the new target (green) value? Answer -->: Question D: For what price range of oats is the current solution optimal? 0.456 0.114 Good-to-Go Suitcase Company Solution value Selling price per unit Material cost per unit Labor cost per unit Profit Constraints Cutting & Coloring Assembly Finishing Quality & Packaging Standard 540.00 $36.05 $6.25 $19.80 $10.00 0.70 0.50 1.00 0.10 Deluxe Luxury 252.00 0.00 $39.50 $43.30 $29,421.00 $7.50 $8.50 $5,265.00 $23.00 $25.30 $16,488.00 $9.00 $9.50 $7,668.00 1.00 0.83 0.67 0.25 1.00 0.67 0.90 0.40 Cost 630.00 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: 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 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 Solution value Selling price per unit Material cost per unit Labor cost per unit Profit Constraints Fabrication Sewing Assembly Tinitote demand Tubbytote demand Toddletote demand Toddletote max prod ratio Tinitote min prod Tubbytote min prod Toddletote min prod TiniTote TubbyTote ToddleTote 100.00 35.00 90.00 $63.75 $82.50 $66.00 $15,202.50 $4.00 $6.00 $5.50 $1,105.00 $50.50 $67.75 $51.00 $12,011.25 $9.25 $8.75 $9.50 $2,086.25 3.0 2.0 1.0 1.0 4.0 1.0 3.0 2.0 2.0 2.0 1.0 -0.4 1.0 -0.4 1.0 0.6 1.0 1.0 Cost 620.00 = 90 35.00 >= 35 90.00 >= 80 LHS Sign RHS Microsoft Excel 14.0 Sensitivity Report Problems 4-24to27. Strollers-to-Go Company Variable Cells Cell $B$4 $C$4 $D$4 Name Solution value TiniTote Solution value TubbyTote Solution value ToddleTote Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 100.00 0.00 9.25 5.00 3.33 35.00 0.00 8.75 4.10 1.00E+030 90.00 0.00 9.50 1.00E+030 3.33 Constraints Cell $E$10 $E$11 $E$12 $E$13 $E$14 $E$15 $E$16 $E$17 $E$18 $E$19 Name Fabrication Sewing Assembly Tinitote demand Tubbytote demand Toddletote demand Toddletote max prod ratio Tinitote min prod Tubbytote min prod Toddletote min prod Final Shadow Constraint Allowable Allowable Value Price R.H. Side Increase Decrease 620.00 3.60 620.00 110.50 43.33 415.00 0.00 500.00 1.00E+030 85.00 385.00 0.00 480.00 1.00E+030 95.00 100.00 0.00 180.00 1.00E+030 80.00 35.00 0.00 70.00 1.00E+030 35.00 90.00 0.00 160.00 1.00E+030 70.00 0.00 3.85 0.00 13.00 8.67 100.00 0.00 90.00 10.00 1.00E+030 35.00 -4.10 35.00 8.13 35.00 90.00 0.00 80.00 10.00 1.00E+030 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 and which one? Please put answers below in yellow fields respectively since this is not binding, firm would not be interested in obtaining any additional sewing timeStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started