Using the Solver Function

From NeoWiki

Jump to: navigation, search
This article is not up to date, or it needs structural or stylistic changes to conform with the rest of this wiki.

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.


[Solver Descripition]

This article in other languages: Français Italiano
Personal tools