A farmer in Iowa has a 100-acre farm on which to plant watermelons and cantaloupes. Every acre planted with watermelons requires 50 gallons of water per day and must be prepared for planting with 20 pounds of fertilizer. Every acre planted with cantaloupes requires 75 gallons of water per day and must be prepared for planting with 15 pounds of fertilizer. The farmer estimates it will take 2 hours of labor to harvest each acre planted with watermelons and 2.5 hours of labor to harvest each acre planted with cantaloupes. He believes that watermelons will sell for about $3 each, and cantaloupes will sell for about $1 each. Every acre planted with watermelons is expected to yield 90 saleable units. Every acre planted with cantaloupes is expected to yield 300 saleable units. The farmer can pump about 6,000 gallons of water per day for irrigation purposes from a shallow well. He can buy as much fertilizer as he needs at a cost of $10 per 50-pound bag and can hire laborers to harvest the fields at a rate of $5 per hour. Finally, the farmer decides that the land use for watermelons cannot exceed that for cantaloupes.

If the farmer sells all the watermelons and cantaloupes he produces, how many acres of each crop should the farmer plant to maximize the total profit?

The following table shows the partial sensitivity analysis report produced by Excel Solver.

a. Formulate a linear programming (LP) model for the problem. [4 marks]

b. For the optimal solution, how much land is used to grow each of the two crops, and what is the maximum profit?

c. For the optimal solution, how much land is left unused and how much water is used?

d. Would the optimal solution change if the price for watermelon is increased by $0.5? [2 marks]

e. What would be the optimal solution if the price for cantaloupe is increased by $0.5? [2 marks]

f. How much would the total profit increase if the water available is increased from 6,000 gallons to 6,200 gallons?

g. The farmer has an opportunity to sell 3 acres of land at a price of $1,000. Do you recommend him make the sale?

The answers are

My questions,

In c), why is no water is left used? I do not understand how that was solved.

In f) and g), please explain how to reach the excel solver report. I am not sure how it works.

Variable Cells \begin{tabular}{ccccccc} \hline Cell & Name & Final Value & Reduced Cost & Objective Coefficient & Allowable Increase & Allowable Decrease \\ \hline$B$16 & ? & 48 & 0 & ? & 1E+30 & 66.33333333 \\ \hline$C$16 & ? & 48 & 0 & ? & 99.5 & 540.5 \\ \hline \end{tabular} Constraints \begin{tabular}{ccccccc} \hline Cell & Name & Final Value & Shadow Price & Constraint R.H. Side & Allowable Increase & Allowable Decrease \\ \hline$B$21 & land use LHS & ? & 0 & ? & 1E+30 & 4 \\ \hline$B$22 & water LHS & ? & 4.324 & ? & 250 & 6000 \\ \hline$B$23 & requirement LHS & ? & 39.8 & ? & 20 & 80 \\ \hline \end{tabular} a. Formulate an linear programming (LP) model for the problem. [4 marks] Let x1 be the number of acres of land for watermelon; x2 be the number of acres of land for cantaloupes Maximise total profit or b. For the optimal solution, how much land is used to grow each of the two crops, and what is the maximum profit? [2 marks] x1=48 acres of land for watermelons x2=48 acres of land for cantaloupes The maximum profit is given by 25944 c. For the optimal solution, how much land is left unused and how much water is used? Page 4 4 acres of land is left unused, i.e., 1004848=4. [2 marks] No water is left used. d. Would the optimal solution change if the price for watermelon is increased by $0.5 ? [2 marks] No. With $0.5 increase in the price of watermelon, there is $0.590=$45 increase in the revenue and thus profit per acre, which is smaller than the allowable increase + infinity. Thus, the optimal solution would not be changed. e. What would be the optimal solution if the price for cantaloupe is increased by $0.5 ? [2 marks] The increase in the price of cantaloupe per acre is given by $.5300=$150, which is greater than the allowable increase 99.5. Thus, the optimal solution would change. f. How much would the total profit increase if the water available is increased from 6,000 gallons to 6,200 gallons? The allowable increase for the RHS of the constraint is 250 . The increase in the water is 200=6200 6000 , smaller than the allowable increase 250 . Thus, the shadow price $4.324 can be used to examine the profit increase due to the increase in water. We obtain that the profit increase is given by 200$4.324=$864.8. g. The farmer has an opportunity to sell 3 acres of land at a price of $1,000. Do you recommend him make the sale? [2 marks] The allowable decrease for the RHS of the constraint is 4. The reduction in land is 3 acres, which is smaller than the allowable decrease 4. Thus, the shadow price $0 can be used to examine the revenue loss due to the reduction in land. We obtain that the profit loss is given by 3$0=$0, which is smaller than the price 1,000 . Therefore, we recommend him make the sale. Variable Cells \begin{tabular}{ccccccc} \hline Cell & Name & Final Value & Reduced Cost & Objective Coefficient & Allowable Increase & Allowable Decrease \\ \hline$B$16 & ? & 48 & 0 & ? & 1E+30 & 66.33333333 \\ \hline$C$16 & ? & 48 & 0 & ? & 99.5 & 540.5 \\ \hline \end{tabular} Constraints \begin{tabular}{ccccccc} \hline Cell & Name & Final Value & Shadow Price & Constraint R.H. Side & Allowable Increase & Allowable Decrease \\ \hline$B$21 & land use LHS & ? & 0 & ? & 1E+30 & 4 \\ \hline$B$22 & water LHS & ? & 4.324 & ? & 250 & 6000 \\ \hline$B$23 & requirement LHS & ? & 39.8 & ? & 20 & 80 \\ \hline \end{tabular} a. Formulate an linear programming (LP) model for the problem. [4 marks] Let x1 be the number of acres of land for watermelon; x2 be the number of acres of land for cantaloupes Maximise total profit or b. For the optimal solution, how much land is used to grow each of the two crops, and what is the maximum profit? [2 marks] x1=48 acres of land for watermelons x2=48 acres of land for cantaloupes The maximum profit is given by 25944 c. For the optimal solution, how much land is left unused and how much water is used? Page 4 4 acres of land is left unused, i.e., 1004848=4. [2 marks] No water is left used. d. Would the optimal solution change if the price for watermelon is increased by $0.5 ? [2 marks] No. With $0.5 increase in the price of watermelon, there is $0.590=$45 increase in the revenue and thus profit per acre, which is smaller than the allowable increase + infinity. Thus, the optimal solution would not be changed. e. What would be the optimal solution if the price for cantaloupe is increased by $0.5 ? [2 marks] The increase in the price of cantaloupe per acre is given by $.5300=$150, which is greater than the allowable increase 99.5. Thus, the optimal solution would change. f. How much would the total profit increase if the water available is increased from 6,000 gallons to 6,200 gallons? The allowable increase for the RHS of the constraint is 250 . The increase in the water is 200=6200 6000 , smaller than the allowable increase 250 . Thus, the shadow price $4.324 can be used to examine the profit increase due to the increase in water. We obtain that the profit increase is given by 200$4.324=$864.8. g. The farmer has an opportunity to sell 3 acres of land at a price of $1,000. Do you recommend him make the sale? [2 marks] The allowable decrease for the RHS of the constraint is 4. The reduction in land is 3 acres, which is smaller than the allowable decrease 4. Thus, the shadow price $0 can be used to examine the revenue loss due to the reduction in land. We obtain that the profit loss is given by 3$0=$0, which is smaller than the price 1,000 . Therefore, we recommend him make the sale