A farmer must decide what crops to grow on a 300-hectare tract of land. He can grow oats, wheat or barley, which yield 50, 100 and 80 kg/hectare respectively, and sell for $ 1.00, $, 0.80 and $0.60 per kg respectively. Production costs (fertilizer, labour, etc.) are $40, $50 and $40 per hectare for growing oats, wheat and barley, respectively. Government regulations restrict the farmer to a maximum of 150 hectares of wheat and his crop rotation schedule requires that he plant at least 60 hectares in oats and 70 hectares in barley. Because of his storage arrangements, the farmer wants the number of hectares of oats to be equal to or less than half the number of hectares of barley. The farmer would like to maximize profit. Let X1, X2 and X3 be the number of hectares planted in oats, wheat and barley respectively. Some useful information can be computed as follows: kg produced revenue cost per profit per Product Hectares per hectare per hectare hectare hectare Oats X1 50*X1 50*X1 40*x1 10 Wheat X2 100*X2 80*X2 50*X2 30 Barley X3 80*X3 48*X3 40*X3 8 The tables below are copied from the SOLVER output. 60 120 120 Hectares Hectares Hectares of Oats of Wheat of Barley Profit Constraints 10 30 8 5160 Total to plant 1 1 1 300 = 300 Minimum area oats 1 0 0 60 >= 60 Maximum area wheat 0 1 0 120 <= 150 Minimum area barley 0 0 1 120 >= 70 Oats-barley ratio 1 0 -0.5 0 <= 0. Microsoft Excel Answer Report Objective Cell Cell Name Original Value Final Value $F$25 Profit 0 5160 Variable Cells Cell Name Original Value Final Value $C$22 Hectares of Oats 0 60 $D$22 Hectares of Wheat 0 120 $E$22 Hectares of Barley 0 120 Constraints Cell Name Cell Value Formula Status Slack $F$26 Total to plant 300 $F$26=$H$26 Binding 0 $F$27 Minimum area oats 60 $F$27>=$H$27 Binding 0 $F$28 Maximum area wheat 120 $F$28<=$H$28 Not Binding 30 $F$29 Minimum area barley 120 $F$29>=$H$29 Not Binding 50 $F$30 Oats-barley ratio 0 $F$30<=$H$30 Binding 0 Microsoft Excel Sensitivity Report Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $C$22 Hectares of Oats 60 0 10 64 1E+30 $D$22 Hectares of Wheat 120 0 30 1E+30 21.33333333 $E$22 Hectares of Barley 120 0 8 22 1E+30 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $F$26 Total to plant 300 30 300 30 120 $F$27 Minimum area oats 60 -64 60 40 10 $F$28 Maximum area wheat 120 0 150 1E+30 30 $F$29 Minimum area barley 120 0 70 50 1E+30 $F$30 Oats-barley ratio 0 44 0 15 60 The questions below are independent of each other. When answering any one of them, ignore the others. Answer all the questions below ACCORDING TO the above reports. a) What are the optimal values of the decision variables? What is the optimal value of the objective function? (3 points) b) The Government regulations changed the restrictions to a maximum of 130 hectares of wheat, does this impact the optimal profits? Justify your answer. (3 points) c) The profit per hectare of wheat has decreased to $28. How does this affect the optimal values of the decision variables and the profits? Justify your answer. (5 points) d) The number of hectares to be planted has increased to 320, and the minimum required area for oats has increased to 70. Management would like your advice on how these changes will affect profits. Justify your answer. (6 points) e) The selling price per kg for oats has increased to $ 1.20. How does this affect the optimal solution? Justify your answer. (5 points)(f) An additional 300 kilos of plastic are available to purchase at a cost of $8 per unit instead of the original cost of $6 per unit. How does the formulation change? (6 points) (g) Formulate the original linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding Excel Spreadsheet and the Sensitivity Report). Include managerial statements that communicate the results of the analysis. (i.e. describe verbally the results). (10 points) (h) What would be the impact on profits, if Harris tools force the production of exactly 10 smafies (i.e. S = 10)? Justify your answer from the sensitivity report (3 points). (i) Harris tools would like to increase its profit as such it is considering increasing the capacity of ONE of its resources. What is your best advice? Justify your answer from the sensitivity report (2 points)