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

Author: Arnaldo Gunzi

"Navegadores antigos tinham uma frase gloriosa: Navegar é preciso, viver não é preciso"

3 thoughts on “OpenSolver Example 1”

  1. Hello Arnaldo,

    Great post. Thank you for that.

    I download your example, and I have a question. If the demand of ABC01 is too big, let’s say 10 000, and you clic on solve, there appears a prompt about not feasible solution, it´s fine. But what is not fine for me is that the constraint from column H is not satisfied.

    What can I do if I want to obtain a more realistic solution from the solver. The closer solution satisfying constraint from H column without matching the demand. How can I correct that? Priority over a constraint against infeasible solution(to keep a more realistic solution).

    Thank you in advance.

    Like

      1. Hi, thank you for your response.

        I’ll look up how to set hard and soft constraints using Opensolver.
        I´ll be checking this site for any other reply, suggestion or new example.

        Have a great day Arnaldo.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s