Linear Programming with LibreOffice Calc Solver

Hi,

I wrote about Linear Programming using a car factory example in my previous post Linear Programming. That was a manual graphical method computation. In this post, I’ll solve the same maximization problem using LibreOffice Calc.

Lets prepare the data –

Linear Programming with LibreOffice Calc Solver 01

What we need to find out? We need to find out how many cars of both types can be produced. Let’s leave one blank cell for each SUV (highlighted in yellow colour).

Linear Programming with LibreOffice Calc Solver 02

What’s out objective function? Maximum profit obtained using x number of SUV cars and y number of Sedan cars.

Let’s add another blank cell. The formula of this cell would be =SUMPRODUCT(B6:C6,B9:C9)

This is nothing but, number of SUV cars x SUV profit + number of Sedan cars x Sedan profit

Linear Programming with LibreOffice Calc Solver 03

We have constraints on raw material, machine and man power, right? Let’s add the the first constraint as below.

Number of SUV cars x raw material required by SUV car (2) should be lesser than or equal to maximum raw material available (18 tonnes). This is represented using the following formula.

=SUMPRODUCT(B3:C3,B9:C9)

Linear Programming with LibreOffice Calc Solver 04.PNG

Similarly we are adding another two constraints using the following formulas.

=SUMPRODUCT(B4:C4,B9:C9)

=SUMPRODUCT(B5:C5,B9:C9)

Finally out data set looks like this.

Linear Programming with LibreOffice Calc Solver 05

Open Tools> Solver

Linear Programming with LibreOffice Calc Solver 06

Let’s fill the solver window as below.

Target cell = Profit cell G9

We want maximization function, because we need to know the maximum count of cars can be produced.

Optimize results to = Maximum

We want to get the best results by changing the value of the count of cars shall be produced.

By changing cells = number of SUV and Sedan cars = B9 and C9.

  1. Constraints cells B12:B14 should be lesser than maximum available resources D12:D14
  2. Number of cars should not be negative
  3. I tested a third constraint, which is maximum number of cars shall not exceed 10 (You may ignore this).

Finally, my selection is given as below.

Linear Programming with LibreOffice Calc Solver 07

Ok Siri, get me the results.

Linear Programming with LibreOffice Calc Solver 08

Linear Programming with LibreOffice Calc Solver 09

This solver suggests me not to produce any Sedan cars. It asks me to produce 2 SUV cars. This is same as that of the results I got in graphical analysis Linear Programming (point c in the below given graph).

Maximation with Linear Programming with 3 constraints using graphical method feasible area

In addition LibreOffice Calc, suggests me maximum resources can be consumed!

See you in another interesting post.

 

 

 

 

 

 

 

One thought on “Linear Programming with LibreOffice Calc Solver

  1. Pingback: Linear Programming – Covering Model using LibreOffice Calc Solver | JavaShine

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s