Creating variables and program calculation formulas

OBJECTIVE

Maestro* allows the entry of two types of calculation formulas in the Quantity field:

  • Simple – which uses only numerical values.
  • Complex – which uses numerical values and variables.

The information contained in this document are also applicable to the following options:

 

 

Attention! This procedure only applies to construction quotations.

 

PREREQUISITIE

 

Summary

Calculation formulas can be defined for Quotations, Define Templates and Define Assemblies, in the following areas:

A formula can use numbers and/or variables and/or operators (see the table of formulas).

 

Steps

Variables Tab

This tab allows for:

  • Creation of variables that can be used in various places within the Detail tab;
  • Enter the data from the statement of quantities made in the plans in order to make the data available in the Detail tab;
  • Facilitate the quantity changes used in several areas in the Detail tab;
  • Bridge between the Calculating Sheet and the Detail tab by allowing the user to create dynamic variables that use the values of the Calculating Sheet to make them available in the Detail tab.

It is possible to enter simple or complex formulas in this tab.

Create new variables

  1. From the main window in maestro*, in the left menu, click on the Projects module, then Estimating/Quotations.
  2. In the section on the right, in the Processing group, click on Quotations-Advanced Mode. The Selection of Quotation window appears.
  3. Select a quotation and click on Accept.
  4. Click on the Variables tab.
  5. Complete the information according to the table below.

Fields

Enter / Select

Name

Name of the new variable

Value

Value of the variable

NOTE: Enter a value or a formula.

Formula

Calculation formula for the variable

Example: 40*25 equals 1000. Maestro* enters this result in the Value column.

NOTE: The formula of a variable can use a value of another variable. For example, 50*VAR1 equals 100 if VAR1 has a Value of 2.

Unit

Unit of measure for the variable

Description

Description of the variable in order to clarify its use

  1. Click on Save.

The variables created will then be available in the Detail tab according to the procedure of the Detail Tab section of this document.

Create variables using the values from cells in the “Calculating Sheet” tab.

  1. From the main window in maestro*, in the left menu, click on the Projects module, then Estimating/Quotations.
  2. In the section on the right, in the Processing group, click on Quotations-Advanced Mode. The Selection of Quotation window appears.
  3. Select a quotation and click on Accept.
  4. Click on the Variables tab.
  5. Complete the information according to the table below.

Fields

Enter / Select

Name

Name of the new variable

Calculating Sheet

The value of the Calculating Sheet cell. Maestro* displays the value of this cell in the Value column.

NOTE: Example 1. If the value «1:g10» is entered in the Calculating Sheet column, maestro* will get the value of the cell «g10» from the first tab in the Calculating Sheet tab.

Example 2. If the value «Concrete: g10» is entered in the Calculating Sheet column, maestro* will get the value of the cell «g10» from the Concrete tab in the Calculating Sheet tab.

Unit

Unit of measure for the variable

Description

Description of the variable in order to clarify its use

  1. Click on Save.

The variables created will then be available in the Detail tab according to the procedure of the Detail Tab section of this document.

Detail Tab

In this tab, it is possible to define the calculating formulas for the quantity of the materials, resources, sub-contractors and/or assemblies.

 

It is useful to program certain variables and/or formulas in the quotation templates for elements that are used from one quotation to another.

Example : project duration in months, project duration in weeks, bid quotations, maintenance quotations, permits, construction site trailer, number of hours worked per employee per week, risk factor, material quantities from which some labour quantities are calculated, etc.

  1. From the main window in maestro*, in the left menu, click on the Projects module, then Estimating/Quotations.
  2. In the section on the right, in the Processing group, click on Quotations-Advanced Mode. The Selection of Quotation window appears.
  3. Select a quotation and click on Accept.
  4. Click on the Detail tab.
  5. Double-click in the appropriate cell in the Quantity column. The Formula window appears.
  6. The following variables are available by default:

Formulas

Description

QtyModule

Takes the value of the Quantity column of assembly.

NOTE: This variable is only applicable for the elements of assemblies.

<COSTING_TOTAL> 

Takes the value entered in the Cost column of the Total division.

<SELLING_TOTAL> 

Takes the value entered in the Selling column of the Total division.

<ACTIVITY_COST> 

Takes the value entered in the Cost column of the current activity.

<PHASE_COST> 

Takes the value entered in the Cost column of the current phase.

<SECTION_COST> 

Takes the value entered in the Cost column of the current section.

<DIVISION_COST> 

Takes the value entered in the Cost column of the current division.

<ACTIVITY_QTY> 

Takes the value entered in the Quantity column for the current activity.

<PHASE_QTY> 

Takes the value entered in the Quantity column of the current phase.

<SECTION_QTY> 

Takes the value entered in the Quantity column for the current section.

<DIVISION_QTY>

Takes the value entered in the Quantity column of the current division.

  1. Enter the formula in the white box using numbers and/or variables as well as the standard operators (addition, subtraction, division, multiplication, etc.).

 

The Disabled option, if checked, allows user to disable (without erasing) a formula in order to enter a number manually in the Quantity column of the Detail tab.
  1. Click on OK.

 

To select a formula, it is possible to double-click on a variable located on the right of the screen. It will automatically be entered into the white box.

An exponential formula can be entered with the help of the ^ sign. For example, if the current division displays a value of 2, the formula <QTE_DIVISION> ^2 will give a result of 4.

Refer to the table in the annex to see the functions and operators available in the formulas.

  1. Click on Save.

 

Annex

Table of formulas

The following table shows the functions and operators that the user can use in the formulas for the quotation calculations.

Formulas

Allows the user to calculate a value from functions supported by the VBScript language.

The available operators are:

+

Addition

-

Substraction

*

Multiplication

^

Power

/

Floating division

\

Integer division

MOD

Modulus – remainder of a division

<

Less than

<=

Less than or equal to

>

Greater than

>=

Greater than or equal to

<>

Not equal to

NOT

Logical not

AND

Logical and

OR

Logical or

XOR

Logical xor

EQV

Equality

IMP

Implication

&

Concatenation

NOTE: It is not necessary to enter capital letters in the formulas.

 

Mathematical Expressions:

Abs(nbr)

return absolute value

Atn(nbr)

return arc tangeant

Cos(nbr)

return cosinus

Exp(nbr)

return power

Int(nbr)

return integer

Fix(nbr)

return integer

Log(nbr)

return naperian log

Sqr(nbr)

return square root

Tan(nbr)

return tangeant

Sgn(nbr)

return sign

Sin(nbr)

return sinus

 

Functions:

NOTE: The functions IIF, UNIT, ROUND, MAX are used in the Variables and Detail tabs of the quotations, quotation templates and in the assemblies. The MAX_PP function is used exclusively in the Variables tab of the assemblies. The SUMEX function is used exclusively in the Detail tab of the same three areas.

IIF(Expr, true part, false part): Displays ‘true part’ or ‘false part’ based on the result of ‘Expr’.

UNIT(nbr OR expression,whole nbr): Display a multiple of ‘whole nbr’ in function with ‘nbr’ OR the result of the expression rounded to the higher unit.

ROUND(expr,[nb decimals required]): returns the rounded to the whole or the closest decimal according to the case.

Example 1: ROUND(5/3,1) = 1.7 = rounds to the 1st higher decimal.

Example 2: ROUND(11/4,2) = 2.75 = rounds to the 2nd higher decimal.

Example 3: ROUND(13/5,0) = ROUND(2.6) = 3 = rounds to the closest unit.

Example 4: ROUND(13/6,0) = ROUND(2.166667) = 2 = rounds to the closest unit.

MAX(VAR1,VAR2) : Returns the greater value between VAR1 and VAR2.

MAX(VAR1,MAX(VAR2,VAR3)) : Returns the greater value between VAR1, VAR2 and VAR3.

MAX(VAR1,MAX(VAR2,MAX(VAR3,VAR4))) : Returns the greater value between VAR1, VAR2, VAR3 and VAR4.

MAX(VAR1,MAX(VAR2,MAX(VAR3,MAX(VAR4,VAR5)))) : Returns the greater value between VAR1, VAR2, VAR3, VAR4 and VAR5.

And so on if there are more variables to take into account to achieve the greatest value.

MAX_PP: This function, contrary to the MAX function only takes a single parameter, either the variable name of the parameter produced in the Additional Fields of the item in the Catalogue Management.

MAX_PP(VAR_PJ): This one will return a maximum PJ value of all the items selected for the VAR variable.

MAX_PP: by only passing it as the name of the parameter produced (ex: PJ) for having the maximum of PJ regardless of the variable which the item is attached.

NOTE: The «name of parameter produced» is actually the «name of Additional Fields » created for the item in Catalogue Management.

SUMEX: Calculation of a quantity from a SUMEX formula.

SUMEX{<variable to add>,<division>,<section>,<phase>,<activity>, <assembly>,<item type>,<filter>}

 

Element

Detail

Possible values

<Variable to add>

Sum variables.

QTE, COSTAMOUNT, UP, SELLAMOUNT

<Division>

Divisions to browse.

CURRENT (default), ALL, a division code.

<Section>

Sections to browse.

CURRENT (default), ALL, a section code.

<Phase>

Phases to browse.

CURRENT (default), ALL, a phase code.

<Activity>

Activities to browse.

CURRENT (default), ALL, an activity code.

<Assembly>

Assemblies to browse.

CURRENT, ALL (default), an assembly number.

<Item type>

Item types to include.

M, S, R.

NOTE: M for material, R for resource and S for sub-contractor. All combinations are possible. No value is equivalent to all the values.

<Filter>

Filters to apply.

The variables possible are UNIT, ACTIVITY, GROUP, CODE, QTY, COSTAMOUNT, UP, SELLAMOUNT and must be entered between <>

NOTE: The possible operators are: =, >, <, NOT, AND, OR, /, \, *, +, -. The parentheses are also permitted

NOTE: The value must be entered between quotes ("value") if the filter is not numeric (UNIT, ACTIVITY, GROUP, CODE).

Example: <UNIT>="foot".

NOTE: UP signifies «Unit Price» and is linked to the unit price column; QTY is the variable linked to the Quantitycolumn; COSTAMOUNT is the variable linked to the Cost column; SELLAMOUNT is the variable linked to the Selling column.

 

Here are a few examples:

 

The formulas in the following examples can be modified to be applied in quotations (or quotation templates) as well as in the assemblies as needed.

EXAMPLE 1

SCENARIO

IIF(VAR1<VAR2,VAR3,VAR2): displays the value of the variable VAR3 if VAR1 is lesser than VAR2, otherwise will display a value of VAR4.

EXAMPLE 2

SCENARIO

You want to use the duration of the project in months and weeks in order to calculate the amount for the construction site trailer and for the signalman.

Given that this data is also used for the calculation of other elements of the quotation, it is preferable to create variables in the Variables tab. Thus, if the duration of the project changed in the course of quotation, it will be very easy to adjust all calculations by a change in a single location.

Formula: UNIT(NBR or EXPRESSION,WHOLE NBR)

UNIT(CALCULATION_DURATION,1): displays, in the WEEK_DURATION variable, the value of the variable CALCULATION_DURATION rounded to the higher unit.

OR, more directly:

UNIT(MONTH_DURATION*4.33,1): displays, in the variable WEEK_DURATION, the result of the expression rounded to the higher unit.

In the quotation:

Result in the quotation:

EXAMPLE 3

Formula: SUMEX{<variable to add>,<division>,<section>,<phase>,<activity>, <assembly>,<item type>,<filter>}

SCENARIO

The labor time of the day labourer who vibrates and smooths the concrete depends on the amount of concrete cast in place. The ratio is 1 hour per cubic meter. So, the objective is to automate the calculation in order to save time.

SUMEX{QTY,CURRENT,CURRENT,CURRENT,CURRENT,,M,} or SUMEX{QTY,,,,,,M,}: displays, in the Quantity column for the current element, the sum of the values of the Quantity column of all the other elements, of the current level, which the Type column indicates that it is a material and the value for the column Group is M.

Result:

EXAMPLE 4

Formula: SUMEX{<variable to add>,<division>,<section>,<phase>,<activity>, <assembly>,<item type>,<filter>}

SCENARIO

The calculation of the bond guarantee is always the same then you want this calculation to be automated. The bond is for $7.50 /1000. The calculation is performed on the basis of the selling amount of the quotation.

 

This example may also apply to the calculation of the amount of the insurance, transportation, and any other element which depends on the value of another element of the quotation.

SUMEX{SELLAMOUNT,ALL,ALL,ALL,ALL,ALL,MRS,}/1000 or SUMEX{SELLAMOUNT,ALL,,,,,MRS,}: displays, in the Quantity column of the current element, the value resulting from the sum of the selling amounts of all the items, resources and sub-contractors of the quotation.

Result:

EXAMPLE 5

Formula: SUMEX{<variable to add>,<division>,<section>,<phase>,<activity>, <assembly>,<item type>,<filter>}

SCENARIO

You are a sub-contractor for coatings (floor, walls, and ceilings); that is to say that you manufacture and install yourself the coatings in the buildings. So you must calculate the transport of your materials from your factory to the site. The calculation of the transport varies from one material to the other. So you decide to create a quotation template that uses a quotation structure of which the first level is used for materials.

Over time, with your experience you have learned that the transportation of wood cost you 15% more than the cost of the material, the transport of the ceramic costs you 1.25 times more than the same material, and the transport of the carpet costs you 2 times more than the cost of the material, because of the weight and other indirect factors. In all cases, the cost of transport by the km is $1.20 there/back, and the quantity of transports depends on the total cost of the materials to carry.

SUMEX{COSTAMOUNT,,1,,,,M,<GROUP>=''M''}: displays, in the Quantity column of the current element, the result of the sum of the cost amounts of the items in section 1 which the Type is «Material» and the Group is «M».

Result:

 

Formerly, How-to no. 22

Last modification: September 20, 2024