Share
Explore BrainMass

Solving a Linear Programming Problem by Using M S Excel

1. The Adams family owns a total of 400 acres of farmland in North Carolina on which they grow corn and tobacco. Each acre of corn costs $125 to plant, cultivate, and harvest; each acre of tobacco costs $235. The Adams family has a budget of $80,000 for next year. The government rules limit the number of acres of tobacco that can be planted to 150. The profit from each acre of corn is $240; the profit from each acre of tobacco is $450. The Adams family wants to know how many acres of each crop to plant in order to maximize their profit.

Formulate a linear programming model for this problem by determining
(a) The decision variables.
x1 = tobacco
x2 = corn
(b) The objective function.
Maximize Z = $450x1 + $240x2
(c) All the constraints.
$235x1 + $125x2 <= 80,000
x1<=150
x1+x2<=400
x1,x2>=0

Answer the following (please explain and show work):
(a) If the profit from an acre of corn increases from $240 to $300, will the number of acres of corn and tobacco planted change? Will the total profit change?
(b) The Adams family has an opportunity to lease some extra land from a neighbor. The neighbor is offering the land to them for $150 per acre. Should the Adams lease the land at that price? What is the maximum price the Adams should pay their neighbor for the land, and how much land should they lease at that price?
(c) The Adams are considering taking out a loan to increase their budget. For each dollar they borrow, how much additional profit would they make? If they borrowed an additional $5,000, would the number of acres of corn and tobacco they plant change?

Solution Summary

The given problem is formulated as a LPP by identifying decision variables, objective function and constraints and it is solved by using Solver tool of M S Excel. The general procedure for finding a solution to a LPP using solver is discussed

$2.19