Question

Excel Assignment

Using the information below develop a linear program to determine the best course of action for Grain Agricultural Enterprises.

Draper Agricultural Enterprises operates 3 ranches in the West. The acreage and irrigation water available for the three ranches are shown below:

Farm	Acreage	Water Available (Acre Feet)
1	400	1,500
2	600	2,000
3	300	900

Three crops can be grown; however, the maximum acreage which can be grown of each crop is limited by the amount of appropriate harvesting equipment available. The three crops are describe below:

Crop	Total Harvesting Capacity (In Acres)	Water Requirements (In Acre-Feet/Acre)	Expected Profit (In $/Acre)
Rye	700	6	400
Corn	800	4	300
Barley	300	2	100

Any combination of crops can be grown on a farm.

Answer 1

The following 3 steps are involved in developing a linear program for the given assignment:

Step 1: Define Your Variables

First, we need to decide how much of each of the three crops needs to be grown in each of the three farms.

Hence let: x_ij be the number of tons of crops i (i=1, 2, 3 for Rye, Corn and Barley respectively) that is to be grown in farm j (j=1 for Farm1, j=2 for Farm2 and j=3 for Farm3) where x_ij >=0, i =1,2,3; j=1,2,3

Note: It is given that any combination of the three given crops can be grown on a farm.

Step 2 - Define the Objective Function

The objective is to maximize total profit, say P, i.e.

Maximize P = 400[x₁₁+ x₁₂+x₁₃] + 300[x₂₁+ x₂₂+x₂₃] + 100[x₃₁+ x₃₂+x₃₃] subject to the constraints defined below.

The basic assumptions are:

• Any combination of crops can be grown on a farm
• all the data/numbers given are accurate

Step 3 - Writing the Constraints

• the harvesting capacity of each crop must be respected - a capacity constraint

x₁₁ + x₁₂ + x₁₃ <= 700
x₂₁ + x₂₂ + x₂₃ <= 800
x₃₁ + x₃₂ + x₃₃ <= 300

• the acreage capacity of each farm must be respected - a capacity constraint

x₁₁ + x₂₁ + x₃₁ <= 400
x₁₂ + x₂₂ + x₃₂ <= 600
x₁₃ + x₂₃ + x₃₃ <= 300

• cannot supply more water than a grower has available - a supply constraint

6x₁₁ + 4x₂₁ + 2x₃₁ <= 1500
6x₁₂ + 4x₂₂ + 2x₃₂ <= 2000
6x₁₃ + 4x₂₃ + 2x₃₃ <= 900

• x_ij must be non-negative quantities for all i and j- non-negativity constraint