How to Solve a Linear Programming model with OpenSolver

OpenSolver is a great Excel Add-in that solves optimization models. The following article will describe how to solve a Linear Programming model using this tool (first you must Download and install OpenSolver in Excel). For educational purposes we will consider a linear programming model with two decision variables and four constraints, nevertheless you can easily extend its application to larger problems.

Maximize 30X+20Y

Subject to:

  • 5X+2Y\leq 140
  • 3X+2Y\leq 120
  • X,Y\geq 0

We will then need to prepare an Excel spreadsheet that includes the model’s parameters and variables (this step is similar to loading a problem in Frontline’s Solver). You can see that cells B2 and C2 (yellow color) have been assigned as the decision variables and cell E2 corresponds to the objective function (blue cell), which is a formula that links the decision variables to their respective parameters. Finally cells D5 and D6 are formulas that represent the “left-hand side (LHS)” of the problem’s constraints (for example, cell D5 corresponds to B2*B5+C2*C5 or equivalently SUMPRODUCT(B2:C2;B5:C5)).

opensolver objective function

Once the previous step has been completed you must run OpenSolver, whose menu can be accessed from the “Data” tab in Excel. Then select “Model…” as shown below:

opensolver menu

The interface for implementing the model is quite similar to the traditional Solver (Frontline) version. The objective cell is defined (E2) in maximization; next you must select the range of decision variables (as shown in the following image) and the constraints. If you try to replicate the structure of the example we are going over in this article, then it should look like this:

opensolver model

Then we select “Save Model” (the structure of the template will change and become different colors, one of the characteristics OpenSolver has that makes this Add-in such a intuitive tool for the user).

opensolver excel

Finally we select “Solve”:

solve opensolver

The program is run and gives us the optimal solution (X=0 and Y=60) and the optimal value V(P)=1,200 for the optimization problem:

optimal solution opensolver

The results achieved are consistent with those obtained in the graphical solution of the problem that we went over in the article “What does a Shadow Price of Zero mean in Linear Programming” as shown in the following image:

infinite solutions

Below you can download the file that contains OpenSolver’s solution to this problem so that you can familiarize yourself with this Excel Add-in: OpenSolver Excel.

Rating: 5.0/5. From 1 vote.
You voted 5, 3 years ago.
Please wait...

, , , , ,

No comments yet. Be the first to comment!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Our Site is Proudly Hosted on