>

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,

subject to

18*x+36*y<=2100 (Capital costs)

16*x+48*y<=2400 (Labor costs),

x>= 0,

y>= 0.

Set Up: Open Google Doc -Spreadsheet (or Excel) and the put the various values and formulas into some cells.

  1. To help us be organized, use the labels Oats, Corn, Variable, Capital Costs, Labor Costs and Revenue in a reasonable selection of cells.
  2. Choose cells to represent your decision variables. Because of where I labeled, I used B5 to represent x and B6 to represent y.
  3. I then entered the costs and revenue per acre.
  4. Put the objective function in another cell, say into B10. So type "=55*B5+125*B6" into B10.
  5. Similar to step 1, we use the labels Capital, Labor, Function, Relation and Value to organize the formulas.
  6. Enter each of the explicit constraint functions, relations and values. For example, enter =18*B5+36*B6 into B13 and 2100 into D14.

Your spreadsheet will now look something like this.

 

Solving the Linear Programming Problem:

  1. On the Tools menu, click Solve.
  2. In the Optimization Type pick either Maximize or Minimize.
  3. In the Cell to optimize enter the cell reference for the objective function. In our case, enter B9. (Shortcut: Click on the Set Target Cell box, then click on cell B9 in the spreadsheet). The Solve screen should then have "Sheet1!B9" in this box.
  4. In the Cells to change select the variable cells. In our case, these are B5 and B6. The simplest thing is to hold down "shift" and then click-n-drag these two cells. The Solve screen should then have "Sheet1!B5:B6" in this box.
  5. In the Subject to the Constraints box, enter any constraints you want to apply. For each constraint do the pretty obvious clicking-and-selecting.

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.