Linear Programming Cranberries can be harvested using either a \"wet\" method or a \"dry\" method. Dry-harvested cranberries can be sold at a premium, while wet-harvested cranberries are used mainly for cranberry juice and bring in less revenue. Fresh made Cranberry Cooperative must decide how much of its cranberry crop should be harvested wet and how much should be dry harvested. Fresh Made has 5000 barrels of cranberries that can be harvested using either the wet or dry method. Dry cranberries are sold for $32.50 per barrel and wet cranberries are sold for $17.50 per barrel. Once harvested, cranberries must be processed through several operations before they can be sold. Both wet and dry cranberries must go through dechaffing and cleaning operations. The dechaffing and the cleaning operations can each be run 24 hours per day for the 6-week season (for a total of 1008 hours). Each barrel of dry cranberries requires 0.18 hours in the dechaffing operation and 0.32 hours in the cleaning operation. Wet cranberries require 0.004 hours in the dechaffing operation and 0.10 hours in the cleaning operation. Wet cranberries must also go through a drying process. The drying process can also be operated 24 hours per day for the 6-week season, and each barrel of wet cranberries must be dried for 0.22 hours. Given the following LP Formulation and Excel solution: The objective function maximizes the total revenue generated from wet-harvested cranberries and dryharvested cranberries. Each barrel of wet-harvested cranberries generates $17.50 in revenue; dry-harvested cranberries earn $32.50 per barrel. The first constraint ensures that no more than 5,000 total barrels of cranberries are harvested. The second constraint states that every barrel of wet-harvested cranberries requires 0.04 hours in the dechaffing operation, every barrel of dry-harvested cranberries requires 0.18 hours in the dechaffing operation, and there are 1008 dechaffing hours available. The third and fourth constraints represent similar requirements for the cleaning and drying operations. Let W = barrels of cranberries harvested using wet method Let D = barrels of cranberries harvested using dry method Max 17.5 W W 0.04 W 0.10 W 0.22 W W + + + , + 32.5 D D 0.18 D 0.32 D D 5,000 Total Harvest 1,008 Dechaffing 1,008 Cleaning 1,008 Drying 0 Nonegativity Use Excel Solver to Answer a) How many barrels should be dry harvested? How many barrels should be wet harvested? b) Supposed that Fresh Made can increase its dechaffing capacity by using an outside firm for this operation. Fresh Made will still use its own dechaffing operation as much as possible, but it can purchase additional capacity from this outside firm for $500 per hour. Should Fresh Made purchase additional dechaffing capacity? Why or Why not? c) Interpret the shadow price for the constraint corresponding to the cleaning operation. How would you explain the meaning of this shadow price to management? Max Maximize Z = 32.5 D Constraints, W + D 5,000 Total Harvest 0.04 W + 0.18 D 1,008 Dechaffing 0.10 W + 0.32 D 1,008 Cleaning 0.22 W 1,008 Drying W , D 0 Nonegativity 122136.4 Decision variables: W D 17.5 W + 2690.909 2309.091 Constrains, 5000 523.27 1008 592 5,000 1,008 1,008 1,008 Total Harvest Dechaffing Cleaning Drying How many barrels should be dry harvested? 2309.09 How many barrels should be wet harvested?2690.91 Supposed that Fresh Made can increase its dechaffing capacity by using an outside firm for this operation. Fresh Made will still use its own dechaffing operation as much as possible, but it can purchase additional capacity from this outside firm for $500 per hour. Should Fresh Made purchase additional dechaffing capacity? Why or Why not? As the Dechaffing capacity usnot getting used completely so they shoud not purchase additional . Interpret the shadow price for the constraint corresponding to the cleaning operation. How would you explain the meaning of this shadow price to management? The shadow price of cleaning operation is 68.18 indicates per 1 extra hour of cleaning operation would increase the profit by $68.18. So if we can increase the cleaning operation at price lower than $68.18 per unit we should do it. Microsoft Excel 14.0 Answer Report Worksheet: [Solution.xlsx]Sheet1 Report Created: 4/1/2016 4:28:00 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.047 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name Original Value Final Value $D$5 Z = 122136.363636 122136.363636 Variable Cells Cell Name Original Value Final Value Integer $D$8 W 2690.90909091 2690.90909091 Contin $D$9 D 2309.09090909 2309.09090909 Contin Constraints Cell Name Cell Value Formula Status $C$13 D 5000 $C$13<=$E$13 Binding $C$14 D 523.272727273 $C$14<=$E$14 Not Binding $C$15 D 1008 $C$15<=$E$15 Binding $C$16 D 592 $C$16<=$E$16 Not Binding Slack 0 484.72727273 0 416 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solution.xlsx]Sheet1 Report Created: 4/1/2016 4:28:00 PM Variable Cells Final Cell Name Value $D$8 W 2690.9090909 $D$9 D 2309.0909091 Reduced Cost Objective Allowable Coefficient Increase 0 17.5 15 0 32.5 23.5 Allowable Decrease 7.34375 15 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $C$13 D 5000 10.681818182 5000 1300 1850 $C$14 D 523.27272727 0 1008 1.000E+030 484.72727273 $C$15 D 1008 68.18 1008 592 416 $C$16 D 592 0 1008 1.000E+030 416 Microsoft Excel 14.0 Limits Report Worksheet: [Solution.xlsx]Sheet1 Report Created: 4/1/2016 4:28:00 PM Objective Cell Name Value $D$5 Z = 122136.36364 Variable Cell Name Value $D$8 W 2690.9090909 $D$9 D 2309.0909091 Lower Objective Limit Result 0 75045.454545 0 47090.909091 Upper Objective Limit Result 2690.9090909 122136.36364 2309.0909091 122136.36364 Max Maximize Z = 32.5 D Constraints, W + D 5,000 Total Harvest 0.04 W + 0.18 D 1,008 Dechaffing 0.10 W + 0.32 D 1,008 Cleaning 0.22 W 1,008 Drying W , D 0 Nonegativity 122136.4 Decision variables: W D 17.5 W + 2690.909 2309.091 Constrains, 5000 523.27 1008 592 5,000 1,008 1,008 1,008 Total Harvest Dechaffing Cleaning Drying How many barrels should be dry harvested? 2309.09 How many barrels should be wet harvested?2690.91 Supposed that Fresh Made can increase its dechaffing capacity by using an outside firm for this operation. Fresh Made will still use its own dechaffing operation as much as possible, but it can purchase additional capacity from this outside firm for $500 per hour. Should Fresh Made purchase additional dechaffing capacity? Why or Why not? As the Dechaffing capacity usnot getting used completely so they shoud not purchase additional . Interpret the shadow price for the constraint corresponding to the cleaning operation. How would you explain the meaning of this shadow price to management? The shadow price of cleaning operation is 68.18 indicates per 1 extra hour of cleaning operation would increase the profit by $68.18. So if we can increase the cleaning operation at price lower than $68.18 per unit we should do it. Microsoft Excel 14.0 Answer Report Worksheet: [Solution.xlsx]Sheet1 Report Created: 4/1/2016 4:28:00 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.047 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name Original Value Final Value $D$5 Z = 122136.363636 122136.363636 Variable Cells Cell Name Original Value Final Value Integer $D$8 W 2690.90909091 2690.90909091 Contin $D$9 D 2309.09090909 2309.09090909 Contin Constraints Cell Name Cell Value Formula Status $C$13 D 5000 $C$13<=$E$13 Binding $C$14 D 523.272727273 $C$14<=$E$14 Not Binding $C$15 D 1008 $C$15<=$E$15 Binding $C$16 D 592 $C$16<=$E$16 Not Binding Slack 0 484.72727273 0 416 Microsoft Excel 14.0 Sensitivity Report Worksheet: [Solution.xlsx]Sheet1 Report Created: 4/1/2016 4:28:00 PM Variable Cells Final Cell Name Value $D$8 W 2690.9090909 $D$9 D 2309.0909091 Reduced Cost Objective Allowable Coefficient Increase 0 17.5 15 0 32.5 23.5 Allowable Decrease 7.34375 15 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $C$13 D 5000 10.681818182 5000 1300 1850 $C$14 D 523.27272727 0 1008 1.000E+030 484.72727273 $C$15 D 1008 68.18 1008 592 416 $C$16 D 592 0 1008 1.000E+030 416 Microsoft Excel 14.0 Limits Report Worksheet: [Solution.xlsx]Sheet1 Report Created: 4/1/2016 4:28:00 PM Objective Cell Name Value $D$5 Z = 122136.36364 Variable Cell Name Value $D$8 W 2690.9090909 $D$9 D 2309.0909091 Lower Objective Limit Result 0 75045.454545 0 47090.909091 Upper Objective Limit Result 2690.9090909 122136.36364 2309.0909091 122136.36364