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
Subject to:
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)).
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:
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:
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).
Finally we select “Solve”:
The program is run and gives us the optimal solution ( and ) and the optimal value for the optimization problem:
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:
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.