Using the Solver Function

From NeoWiki

The Solver in Calc is a tool that help you to calculate the conditions which maximize profit and minimize cost while satisfying some constraints. It can solve linear or non-linear problems, but keep in mind that the non-linear problems are much more difficult to solve. The example below shows the utilisation of the Solver function in Calc.

Problem Presentation

Let us assume that a factory produces three types of machines, A1, A2, A3. Let pi be the cost of the raw material and hi the number of hours which are necessary to produce a machine of the type Ai. The profit per machine of the type Ai is equal to bi.
The decision variables are the numbers xi of machines of the type Ai which are produced. We want to calculate the values of the numbers xi which minimize the total profit.
Those data have to satisfy some constraints, for example:

every number xi must be an integer. We want to produce at least 30 machines of each type.

the total cost of raw material must be less than or equal to $10 000

the total number of working hours must be less than or equal to 5 200.

Machines	A1	A2	A3
Number of machines	x1	x2	x3
Cost of raw material per machine	p1	p2	p3
Number of working hours per machine	h1	h2	h3
Profit per machine	b1	b2	b3
Profit to maximize	b1x1+b2x2+b3x3
Constraints	x1 >= 30, x2 >= 30, x3 >= 30	p1x1+p2x2+p3x3 <= 10 000	h1x1+h2x2+h3x3 <= 5 200

Solving the Problem

• Open a Calc document
• In the spreadsheet, enter the data as in the table below:

The value 1890 contained in the cell B8 is the sum of working hours necessary to produce 30 machines of each type, namely SOMMEPROD(B2:D2;B4:D4).
The value 6300 contained in the cell B9 is the cost of raw material necessary to produce 30 machines of each type, namely SOMMEPROD(B2:D2;B3:D3).
We have to maximize the total profit: SOMMEPROD(B2:D2;B5:D5)

• Choose the Outils > Solver… menu. The Optimization Solver window appears.

• Define the model:

N.B All the references are automatically translated into absolute references by the solver. That explains the presence of the prefix "$" in the cells adresses.

• • Click on the little green arrow to the right of the Set target cell field, then click on the cell which contains the measure to optimize, here B7 which contains the formula giving the total profit. Click on the arrow to the right of the input field again, the value $Sheet1.$B$7 is automatically placed in the Set target cell field.
  • Click on Maximize. It's the goal to assignate.
  • Click on the little green arrow to the right of the Set target cell field, in order to choose the cells containing the decision variables whose values allowing the profit maximization have to be computed. Select the three cells B2, C2, D2 by dragging the mouse. Click on the arrow to the right of the input field again. The range $Sheet1.$B$2:$D$2 is automatically placed in the By changing cells field.
• Define the constraints:

Let us set the following constraints:

SOMMEPROD(B2:D2;B4:D4) ≤ 5 200

SOMMEPROD(B2:D2;B3:D3) ≤ 10 000

B2 ≥ 30

C2 ≥ 30

D2 ≥ 30

• • Click the Add button. The Constraint window appears.

• • Click on the little green arrow to the right of the Cell reference field and click on the cell B8, then on the arrow again.
  • In the Constraint field enter the value you desire, namely 5200.
  • Click OK.
  • Click on the Add button again and add the four other constraints in the same way. Change the sign "<=" into ">=" for the constraints that are imposed on the values xi.
• Choose the options :
  • Click on the Options button

• • In the Options window that appears, leave the default option Assume linear model checked and check the Allow only integer values option.
  • Click OK.
• Click on the Solve button.
• The message Solution found appears in a dialog box. Click OK.

The values of cells B2 to D2 have been changed so that the profit may be maximum, under the conditions set by the constraints.
In that example we get x1= 82, x2 = 31 et x3 = 30.

• You can save that model by clicking the Save button. By clicking the Load button, you will be able to display it later and, for instance, to modify the values of the constraints or of the production costs.