OpenSolver Example 1

This is a very easy example, inspired on Forest Planning.

Suppose we have two paper mills, ABC01 and ABC02. Each has a demand of wood in a given period.

os01.JPG

There are stands (areas of forest) that will be harvested. Each has a volume of wood. Let the index of stands be i.

os2.JPG

The distance between each stand (i) and paper mill (j) is given.

os03.JPG

How to supply the paper mills (supply >= demand), with the objective function of minimization of the Distance?

 


General rules

Because it is a linear problem, we can use only linear functions like “sumproduct” and “sum”. “Sumif” or “vlookup” are non linear.

There are a lot of advantages in modelling as a linear problem. We can use all the power of linear solvers like CBC. We have the guarantee of an optimal solution. Besides, the solution time will be faster than in the non linear case.

The model is very similar to solver.


Solution

We define a binary variable, aloc(i,j).

os04.JPG

The volume for each destination is aloc(i,j)*volume(i)

os05.JPG

It must be greater than demand(j)
each j, sum(i, aloc(i,j)*volume(i)) >= demand(j)

There is a need to impose a structural constraint: each stand must be destined to only one paper mill.
each i, sum(j, aloc(i,j)) <=1  (note isn’t an obligation to harvest the stand)

os06.JPG
The objective function is minimize aloc(i,j)*distance(i,j)

os07.JPG
Once the Excel file is ready, just set the Model in opensolver.

os08.JPG

Set the objective function, each constraint and save the model.

os09.JPG
Press solve, and it will give the solution.

os10.JPG
OpenSolver shows the formulation, with the colored boxes. The “Show/Hide Model” makes it easy to hide the formulation.

 

os08.JPG

It’s very easy, and similar to Solver. And also powerful, as we saw in Introduction.

Arnaldo Gunzi


 

The Excel file for download is here.

 


 

Read also

OpenSolver – Introduction

 

Advertisements

Tutorial Open Solver – Introduction

Solver versus OpenSolver

Perhaps the easiest and most famous tool for linear programming is Microsoft Excel Solver. It is the tool people know from college, or some friend talked about, along with the infamous and magical “goal seek”.

Excel solver is quite intuitive, easy and ubiquitous (since Excel is ubiquitous). Solver is developed by a company named Frontline (http://www.solver.com/), not by Microsoft.

 

Frontline.JPG

 

I am a great fan of Excel, but I hate Solver. Mainly because Frontline solver has a limitation of 200 variables.

Try to solve more than 200 variables, and the following message will appear:

 

SolverMessage.JPG
Unfortunately my Excel is in portuguese, but the message is clear

It is very very frustrating to discover this limitation after you spent some hours modelling the problem.

Frontline provides more powerful versions of solver, but it is not free. If you are a company, that’s ok, just buy it. But if you are a student, it is not a trivial value. Luckly, there are alternatives.

 


 

OpenSolver

The OpenSolver tool can help a lot in these cases. It is a Great (upper case Great) initiative by Andrew Mason, University of Auckland, New Zealand. It has a clean, easy interface, and calls a free source solver like CBC (another Great initiative by Coin-OR – http://www.coin-or.org/).

Opensolver.JPG

 

Because the solver is CBC, we don’t have limitation on number of variables, constraints or whatever.

 

And because OpenSolver is open source, it is easy to learn a lot from the VBA code and also help the development of it. The limit is our imagination.


 

Next posts will cover these topics.

  • OpenSolver Example 1
  • OpenSolver Example 2
  • OpenSolver Example 3
  • OpenSolver Example with VBA
  • Conclusion: Spreadsheet versus Professional tools

 

Enjoy!

Arnaldo Gunzi.