Linear Programming Models: Graphical and Computer Methods

Simple Minimization Problem Using QM In this tutorial, we will solve a simple minimization linear programming problem using Excel QM. The dean of the Western College of Business must plan the school’s course offering for the upcoming fall semester. Student demands make it necessary to offer at least 30 undergraduate and 20 graduate classes in the term. Faculty contracts also dictate that at least 60 courses be offered in total. Each undergraduate class costs the college an average of $2,500 and each graduate class costs $3,000. How many of each should be taught in the fall to minimize total costs? Before we get started on Excel, it is best if we try to first identify the objective functions and the constraints. Clues to identifying the objective function include the words minimize or maximize and in this case, it’s minimizing total costs.

X1 = number of undergraduate courses X2 = number of graduate courses Minimize cost = $2,500X1 + $3,000X2

Next, we need to identify our constraints. We have a constraint on the total number of classes taught and the minimum number of undergraduate and graduate classes that need to be included. These can be expressed as:

X1  30

X2  20

X1 + X2  60

Now, let’s open Excel QM and solve our problem. Click on the Excel QM tab  Alphabetical  Linear, Integer, & Mixed Integer Programming.

In the Spreadsheet Initialization window, be sure to identify that we have two variables (X1 and X2) and 3 constraints and that it’s a minimization problem.

Click OK. A spreadsheet will display.

In the boxes for our objective function, enter 2,500 under x1 and 3,000 under x2. Our constraints are identified as follows: for Constraint 1, enter a 1 under x1 and 30 under RHS; for Constraint 2, enter a 1 under x2, and 20 under RHS; and for Constraint 3, enter a 1 under each variable and 60 under RHS. Here we are literally telling the computer that our first constraint has one X1 that is greater than 30; our second has 1 X2 that is greater than 20; and our third has one of each and has to be greater than 60. That is it. We have now entered our objective function and all our constraints. Once you have the data entered correctly, click the Data tab and then Solver.

A Solver Parameters window will appear.

Click Solve and then OK in the Solver Results window. Our results are shown below.

Data x 1 x 2

Objective 2500 3000 sign RHS

Constraint 1 1 > 30

Constraint 2 1 > 20

Constraint 3 1 1 > 60

Results Variables 40 20

Objective 160000

We can see that it is telling us that we can minimize our costs at $160,000 by offering 40 undergraduate classes and 20 graduate classes. Click here to download the completed spreadsheet table so you can compare it to yours. This concludes our tutorial on a simple minimization linear programming problem using Excel QM.