Dwight and Hattie have run the family farm for over thirty years.They are currently planning the mix of crops to plant on their 120-acre farm for the upcoming season.The table below gives the labor hours and fertilizer required per acre,as well as the total expected profit per acre for each of the potential crops under consideration.Dwight,Hattie,and their children can work at most 6,500 total hours during the upcoming season.They have 200 tons of fertilizer available.What mix of crops should be planted to maximize the family's total profit? (a)Formulate and solve a linear programming model for this problem in a spreadsheet.(b)Formulate this same model algebraically.

Question

Dwight and Hattie have run the family farm for over thirty years.They are currently planning the mix of crops to plant on their 120-acre farm for the upcoming season.The table below gives the labor hours and fertilizer required per acre,as well as the total expected profit per acre for each of the potential crops under consideration.Dwight,Hattie,and their children can work at most 6,500 total hours during the upcoming season.They have 200 tons of fertilizer available.What mix of crops should be planted to maximize the family's total profit?  (a)Formulate and solve a linear programming model for this problem in a spreadsheet.(b)Formulate this same model algebraically.

Quizplus · Accepted Answer

The Answer is (a)This is a resource-allocation problem.The activities are the planting of the three crops and the limited resources are land,labor,and fertilizer.We will start to build a spreadsheet by entering the data.The data for this problem are the labor required,fertilizer required,and expected profit for each crop (per acre).The data in the spreadsheet would be entered as displayed below,where range names of ProfitPerAcre (C4:E4)and TotalAvailable (H7:H9)are assigned to the corresponding data cells.

(a)This is a resource-allocation problem.The activities are the planting of the three crops and the limited resources are land,labor,and fertilizer.We will start to build a spreadsheet by entering the data.The data for this problem are the labor required,fertilizer required,and expected profit for each crop (per acre).The data in the spreadsheet would be entered as displayed below,where range names of ProfitPerAcre (C4:E4)and TotalAvailable (H7:H9)are assigned to the corresponding data cells. The decisions to be made in this problem are how many acres of each crop to plant.Therefore,we add three changing cells in C12:E12 with range name AcresPlanted.The values in AcresPlanted (C12:E12)will eventually be determined by the Solver.For now,an arbitrary value of 1 is entered for each crop. The goal is to maximize the family's total profit.Thus,the objective cell should calculate the total profit.In this case,the total profit will be Total Profit = ($500)(acres of oats)+ ($600)(acres of wheat)+ ($950)(acres of corn)or Total Profit = SUMPRODUCT(ProfitPerAcre,AcresPlanted).This formula is entered into cell H12.With 1 acre of each crop planted,the total cost would be ($500)(1)+ ($600)(1)+ ($950)(1)= $2,050. The functional constraints in this problem involve the limited resources of land,labor,and fertilizer.Given the AcresPlanted (the changing cells in C12:E12),we calculate the total resources used in TotalUsed (cells F7:F9).For land,this will be =SUMPRODUCT(C7:E7,AcresPlanted).Using a range name or an absolute reference for the acres planted,this formula can be copied into cells F8:F9 to calculate the amount of labor and fertilizer used.The total resources used must be <= TotalAvailable (H7:H9),as indicated by the <= in G7:G9. The Solver information and solved spreadsheet are shown below. Thus,oats should be planted on 80 acres and wheat on 40 acres,while not planting any corn,with a resulting total profit of $64,000.(b)To build an algebraic model for this problem,start by defining the decision variables.In this case,the three decisions are how many acres of oats,wheat,and corn to plant.These variables are defined below: Let O = Acres of oats planted,W = Acres of wheat planted,C = Acres of corn planted.Next determine the goal of the problem.In this case,the goal is to achieve the highest possible total profit.Each acre of oats yields a profit of $500,each acre of wheat yields a profit of $600,while each acre of corn yields a profit of $950.The objective function is therefore Maximize Total Profit = $500O + $600W + $950C.There are three limited resources in this problem: 120 acres of land,6500 hours of labor,and 200 tons of fertilizer.Each acre of a given crop that is planted uses up one available acre.The data for labor hours used and fertilizer used per acre planted can be used to calculate the total resources used as a function of the decision variables.The total resources used need to be less than or equal to the amount available.These constraints are therefore as follows: Land: O + W + C ≤ 120 acres,Labor: 50O + 60W + 105C ≤ 6500 hours,Fertilizer: 1.5O + 2W + 4C ≤ 200 tons After adding nonnegativity constraints,the complete algebraic formulation is given below: Let O = Acres of Oats planted,W = Acres of Wheat planted,C = Acres of Corn planted.Maximize Total Profit = $500O + $600W + $950C.subject to Land: O + W + C ≤ 120 acres,Labor: 50O + 60W + 105C ≤ 6500 hours,Fertilizer: 1.5O + 2W + 4C ≤ 200 tons and O ≥ 0,W ≥ 0,C ≥ 0.

The decisions to be made in this problem are how many acres of each crop to plant.Therefore,we add three changing cells in C12:E12 with range name AcresPlanted.The values in AcresPlanted (C12:E12)will eventually be determined by the Solver.For now,an arbitrary value of 1 is entered for each crop.

The goal is to maximize the family's total profit.Thus,the objective cell should calculate the total profit.In this case,the total profit will be Total Profit = ($500)(acres of oats)+ ($600)(acres of wheat)+ ($950)(acres of corn)or Total Profit = SUMPRODUCT(ProfitPerAcre,AcresPlanted).This formula is entered into cell H12.With 1 acre of each crop planted,the total cost would be ($500)(1)+ ($600)(1)+ ($950)(1)= $2,050.

The functional constraints in this problem involve the limited resources of land,labor,and fertilizer.Given the AcresPlanted (the changing cells in C12:E12),we calculate the total resources used in TotalUsed (cells F7:F9).For land,this will be =SUMPRODUCT(C7:E7,AcresPlanted).Using a range name or an absolute reference for the acres planted,this formula can be copied into cells F8:F9 to calculate the amount of labor and fertilizer used.The total resources used must be <= TotalAvailable (H7:H9),as indicated by the <= in G7:G9.

The Solver information and solved spreadsheet are shown below.

Thus,oats should be planted on 80 acres and wheat on 40 acres,while not planting any corn,with a resulting total profit of $64,000.(b)To build an algebraic model for this problem,start by defining the decision variables.In this case,the three decisions are how many acres of oats,wheat,and corn to plant.These variables are defined below: Let O = Acres of oats planted,W = Acres of wheat planted,C = Acres of corn planted.Next determine the goal of the problem.In this case,the goal is to achieve the highest possible total profit.Each acre of oats yields a profit of $500,each acre of wheat yields a profit of $600,while each acre of corn yields a profit of $950.The objective function is therefore Maximize Total Profit = $500O + $600W + $950C.There are three limited resources in this problem: 120 acres of land,6500 hours of labor,and 200 tons of fertilizer.Each acre of a given crop that is planted uses up one available acre.The data for labor hours used and fertilizer used per acre planted can be used to calculate the total resources used as a function of the decision variables.The total resources used need to be less than or equal to the amount available.These constraints are therefore as follows: Land: O + W + C ≤ 120 acres,Labor: 50O + 60W + 105C ≤ 6500 hours,Fertilizer: 1.5O + 2W + 4C ≤ 200 tons After adding nonnegativity constraints,the complete algebraic formulation is given below: Let O = Acres of Oats planted,W = Acres of Wheat planted,C = Acres of Corn planted.Maximize Total Profit = $500O + $600W + $950C.subject to Land: O + W + C ≤ 120 acres,Labor: 50O + 60W + 105C ≤ 6500 hours,Fertilizer: 1.5O + 2W + 4C ≤ 200 tons and O ≥ 0,W ≥ 0,C ≥ 0.

Dwight and Hattie Have Run the Family Farm for Over