Question

LINEAR PROGRAMMING

A farmer in Wood County has 950 acres of land. She is going to plant each acre with corn, soybeans, or wheat. Each acre planted with corn yields a $2,200 profit; each with soybeans yields $2,450 profit; and each with wheat yields $3,150 profit. She has 192 workers and 195 tons of fertilizer. The table below shows the requirement per acre of each of the crops.

	CORN	SOYBEANS	WHEAT
Labor (workers)	0.2	0.2	0.4
Fertilizer (tons)	0.2	0.3	0.2

Assuming a linear relationship, use Excel Solver to determine the optimal planting mix of corn, soybeans, and wheat to maximize her profits. In formulating your linear program, do NOT restrict the three decision variables to integer values. (However, round the final values of these decision variables to the nearest whole number when entering those answers. Use these rounded decision variable values to compute “Total Profit” and enter that answer to the nearest whole number. Lastly, also use the rounded decision variable values to compute the three “Resources Used” answers and enter them to the nearest whole number. Leave no cells blank – be certain to enter “0” wherever required.)

Decision for corn
Decision for soybeans
Decision for wheat
Total profit	$

	Resources              Used
Labor (workers)
Fertilizer (tons)
Acres Planted

Answer 1

Spreadsheet model and solution using Solver is following:

FORMULAS:

E3 =SUMPRODUCT(B3:D3,$B$10:$D$10) copy to E5:E7

Optimal result:

Decision for Corn = 890

Decision for Soybeans = 50

Decision for Wheat = 10

Total profit = $ 2,112,000

Resources used

Labor (workers) = 890*.2+50*.2+10*.4 = 192

Fertilizer (tons) = 890*.2+50*.3+10*.2 = 195

Acres planted = 890+50+10 = 950