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.
There are stands (areas of forest) that will be harvested. Each has a volume of wood. Let the index of stands be i.
The distance between each stand (i) and paper mill (j) is given.
How to supply the paper mills (supply >= demand), with the objective function of minimization of the Distance?
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.
We define a binary variable, aloc(i,j).
The volume for each destination is aloc(i,j)*volume(i)
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)
The objective function is minimize aloc(i,j)*distance(i,j)
Once the Excel file is ready, just set the Model in opensolver.
Set the objective function, each constraint and save the model.
Press solve, and it will give the solution.
OpenSolver shows the formulation, with the colored boxes. The “Show/Hide Model” makes it easy to hide the formulation.
It’s very easy, and similar to Solver. And also powerful, as we saw in Introduction.
The Excel file for download is here.