Question 2 fromn Assignment 3:

Excel Solver, Answer report and Sesitivity report:

Microsoft Excel 16.0 Sensitivity Report Worksheet: [Assignment 4.xIsx]Question 2 Report Created: 2023-07-30 2:09:40 PM Refer back to the model you created for Q2 in Assignment 3, about the family farming model. Use sensitivity analysis to answer the following questions. (Note that the final answer was given to that question, so make sure you reach the correct answer for the model before continuing). (a) Is this solution a unique optimal solution? (b) use Solver to identify one other optimal solution? (c) would the total profit increase if barley sales could be increased by 10% ? If so, by how much? (d) Would the availability of more water increase the total profit? If so, by how much? Include screenshots of Excel/Solver to help justify your answers. A family farming concern owns five parcels of farmland broken into a southeast sector, north sector, northwest sector, west sector, and southwest sector. The farm concern is involved primarily in growing wheat, alfalfa, and barley crops and is currently preparing the production plan for next year. The Water Authority has just announced its yearly water allotment, with this farm receiving 7,500 acrefeet. Each parcel can tolerate only a certain amount of irrigation per growing season, as specified in the following table. Each crop needs a minimum amount of water per acre, and there is a projected limit on sales of each crop, as noted in the following table. Wheat can be sold at a net profit of $2 per bushel, alfalfa at $40 per ton, and barley at $50 per ton. One acre of land yields an average of 1.5 tons of alfalfa and 2.2 tons of barley. The wheat yield is approximately 50 bushels per acre. What is the best planting plan for this farm? Microsoft Excel 16.0 Answer Report Worksheet: [Assignment 4.xIsx]Question 2 Report Created: 2023-07-30 2:09:39 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.109 Seconds. Iterations: 7 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