Question

A farmer owns 450 acres of land. He is going to plant each acre with wheat or corn. Each acre planted with wheat yields 2,000 pounds of wheat, requires three workers, and two tons of fertilizer. Each acre planted with corn yields 3,000 pounds of corn, requires two workers, and four tons of fertilizer. The wheat can be sold at the selling price of $2.5 per pound, whereas the corn at the price of $3.4 per pound. To pay for the workers and the fertilizers it is estimated that the farmer will have to incur a cost of $0.5 per pound of wheat and of $0.4 per pound of corn. There are currently 1,000 workers and 1,200 tons of fertilizer available.

Using excel:

Create a model in Excel and use Solver to help the farmer maximize the profit from his land. (show the decision variables, objective function, & constraints)

Generate the sensitivity report from Solver. Imagine you can change the selling price for a pound of wheat\t, while all the other parameters remain the same. What is the minimum selling price per pound of wheat that would make it optimal to produce wheat? Explain your reasoning.

What amount of extra profits will the company make if it has extra 100 tons of fertilizers available? Explain your reasoning.

Suppose the farmer has the possibility to acquire additional 50 acres of land to increase the production. What amount of extra profits will the farmer attain with this extra land?

(Please attach excel file solution if possible)

Answer 1

A grower produces corn and wheat. Create a plan that minimizes profit by determining how much of each crop to plant given constraints on the amount of acreage, amount of soil additive, and the number of field hands. Create a plan, support the plan graphically, and find the maximum profit that changes the planting arrangement when soil additives are constrained differently. Complete the following steps to set up a model with the given information:

1. Enter given data for the input variables of Profit per acre and resources needed per acre (per crop type), and the decision variable Planting area for each crop.

• Name the range for Planting area.

2. Enter data for the constraints of Resources used <= Resources available for Field hands and Soil additive, and for Acres used <= Acres available.

• Name the ranges for Resources available, Resources used, Acres available, Acres used.

• Enter a formula for Field hands used that is the sum product of Planting area and Field hands needed per acre.

• Enter a formula for Soil additive used that is the sum product of Planting area and Soil additive needed per acre.

• Enter a formula for Acres used that is the sum of Planting area for both crops.

3. Enter a line for Profit with a formula that sums the product of Profit per acre and Planting area.

• Name the range for Profit.

4. Run the Solver tool to find the input of Profit and output of optimal number of Planted area for both crops that meet the constraints of Field hands, Soil additive, and Limitations on acres.

Your model should look like this:

The optimal Planting area per crop, according to the Solver tool, is 200 acreas of wheat and 200 acreas of corn. Create a graph that supports this solution by following the steps below:

1. Graph the line of constraint for Field hands and Soil additive constraints for both crops and Limitations on acreage:

• Find the intercepts of each line at the maximum constraint, considering the formula:

2. Graph the line of the objective Planting area and it’s slope:

• Use the formula in step 1 above to graph the line.

• Find the slope of the line with the formula:

• Use the slope of the line to find the optimal solution point (200,200) and graph it as the “isopoint” line.

Your graph should look like this:

Run SolverTable with Soil additive availability varying in increments of 100. Your SolverTable result should look like this:

These results show that wheat should not be planted when there are 1800 or more tons of Soil additive available. Also shown is Corn is not recommended for planting when the availability of Soil additive is at or below 600 tons. To observe the change in profit at each increment an optional calculation can be performed that finds the difference between each profit and the profit at the previous step. That optional calculation would look like this:

It is evident that while profit increases, it is increasing at a decreasing rate and levels out at $1,350.00.