Question

1) Walnut Orchard has three farms that grow wheat and corn. Because of differing soil conditions, there are differences in yields and costs of growing crops on the three farms (see Table 1). Corn yield/acre Cost/acre of corn Wheat yield / acre Cost/ acre of wheat Acres available Farm 1 500 bushels $800 350 bushels $1000 100 Table 1 Farm 2 650 bushels $920 400 bushels $900 120 Farm 3 450 bushels $700 375 bushels $800 90 In addition, each crop will generate the revenue and require fertilizer and insecticide applications in the amounts shown in Table 2. Formulate a linear programming problem to help determine a planting plan that will maximize Walnut Orchard's profit from the three farms. Supply Corn Revenue per bushel $4 Fertilizer (lbs. / acre) 10 Insecticide (gal. / acre) 50 Minimum desired yield (bushels) 65,000 Table 2 Wheat 7$ 12 35 40,000 3,000 lbs. 9,000 gal. Specify the input data, decision variables, objective function, and constraints.

Answer 1

Model:

Wheat and corn farming				Range names used:
					AcresAvail	=Model!$B$16:$C$16
Bushels per acre	Farm 1	Farm 2			AcresPlanted	=Model!$B$12:$C$13
Corn	500	650			AcresUsed	=Model!$B$14:$C$14
Wheat	400	350			ReqdYields	=Model!$F$12:$F$13
					TotCost	=Model!$B$18
Cost per acre	Farm 1	Farm 2			Yields	=Model!$D$12:$D$13
Corn	$100	$120
Wheat	$90	$80
Acres to plant	Farm 1	Farm 2	Yield		Required		Original reqts		Percentage change in both
Corn	0	10.769	7000	>=	7000		7000		0%
Wheat	27.5	0	11000	>=	11000		11000
Acres used	27.5	10.769
	<=	<=
Acres available	100	100
Total cost	$3,767

STS-1:

Oneway analysis for Solver model in Model worksheet Pct change in reqts (cell SJ$12) values along side, output cell(s) along top Data for chart TotCost When you select an output from the dropdown list in cell SKS4, the chart will adapt to that output. -50% -40% -30% -20% -10% 0% 10% 20% 30% 40% 50% $1,884 $2,260 $2,6371 $3,014 $3,391 $3,767 $4,144 $4,521 $4,898 $5,274 $5,6511 The total cost increases linearly over this range. 1883.65 2260.38 2637.12 3013.85 3390.58 3767.31 4144.04 4520.77 4897.5 5274.23 5650.96 Sensitivity of TotCost to Pct change in reqts 6000 5000 4000 3000 2000 1000 0 3056 4056 5056 -50% -40% -30% -20% -10% 096 1056 2056 Pct change in reqts ($J$12)