Using the Solver Function

From NeoWiki

Revision as of 09:19, 16 April 2007 (edit) Jgd (Talk | contribs) (upload English imges) ← Previous diff		Current revision (17:39, 20 April 2007) (edit) (undo) Jgd (Talk | contribs) m (→Solving the Problem)
(One intermediate revision not shown.)
Line 56:		Line 56:
	** Click on the little green arrow to the right of the {{prefName|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 {{prefName|Set target cell}} field.		** Click on the little green arrow to the right of the {{prefName|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 {{prefName|Set target cell}} field.
	** Click on {{prefName|Maximize}}. It's the goal to calculate.		** Click on {{prefName|Maximize}}. It's the goal to calculate.
-	** Click on the little green arrow to the right of the {{prefName|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 {{prefName|By changing cells}} field.	+	** Click on the little green arrow to the right of the {{prefName|By changing cells}} 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 {{prefName|By changing cells}} field.
	* Define the constraints:		* Define the constraints:
	*: Let us set the following constraints:		*: Let us set the following constraints:
Line 76:		Line 76:
	* The message '''Solution found''' appears in a dialog box. Click {{button|OK}}.		* The message '''Solution found''' appears in a dialog box. Click {{button|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.<br>		*: The values of cells B2 to D2 have been changed so that the profit may be maximum, under the conditions set by the constraints.<br>
-	*: In that example we get x1= 82, x2 = 31 et x3 = 30.	+	*: In that example we get x1= 82, x2 = 31 and x3 = 30.
	*: [[image:Optimization2.png|center]]		*: [[image:Optimization2.png|center]]

---

Current revision

The Solver in Calc is a tool that helps 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 maximize the total profit.

These 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 SUMPRODUCT(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 SUMPRODUCT(B2:D2;B3:D3).
We have to maximize the total profit: SUMPRODUCT(B2:D2;B5:D5)

• Choose Solver… from the Tools 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 calculate.
  • Click on the little green arrow to the right of the By changing cells 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:

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

  SUMPRODUCT(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 and 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, modify the values of the constraints or of the production costs.

Link

[Solver Descripition]