Solving a Linear Programing Problem Using the Excel Solver
The following tutorial describes how to solve the linear program.
Example: A farmer has 100 acres on which to plan oats or corn. Each acre of oats requires $18 capital and 2 hours of labor. Each acre of corn requires $36 capital and 6 hours of labor. Labor costs are $8 per hour. The farmer has $2100 available for capital and $2400 available for labor. If the revenue is $55 from each acre of oats and $125 from each acre of corn, what planting combination will produce the greatest total profit? What is the maximun profit?
We set up and solve the associated linear programming problem.
We let x = total acres of oats, and y= total acres of corn.
Setting the information into a chart :
|x = acres of oats||y = acres of corn|
|Capital costs||$ 18||$ 36|
|Hours of labor per acre||2||6|
|Cost of labor per acre||2*8=$16||6*8=$48|
|Revenue per acre||$55||$125|
Our constraints are
Capital <= $2100, Labor <=$2400.
We wish to maximize profit = 55*x + 125*y, i.e., this is our objective function.
Hence, our linear programming problem is
Maximize 55*x + 125*y,
18*x+36*y<=2100 (Capital costs)
16*x+48*y<=2400 (Labor costs),
Set Up: Open Google Doc -Spreadsheet (or Excel) and the put the various values and formulas into some cells.
Your spreadsheet will now look something like this.
Solving the Linear Programming Problem:
When you are done specifying the constraints, the solver parameters box should look as follows:
Click Solve. The values in B5 and B6 should have become 50 and 33.3, and that the maximum profit is $6916.6 and your spreadsheet will look like the following. Thus x=50 and y=33.3 are the number of acres that the farmer should devote to oats and corn, respectively.