Resource Rate

Calculators II
User Manual

1 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
1. Introduction

The Resource Rate Calculators II is designed to give the user the ability to calculate rates for resources in a
spreadsheet like format environment and then use these rates within a Candy estimate.

These calculated rates can be complex build up rates (e.g. Cost to Company for Job Occupation including
all allowances, overtimes and statutory requirements) or even existing rates from the resources list that
can be revised to calculate a new rate and then the revised rate can be exported back to the Candy
Estimate.

This process uses 6th Base documents and allows the user to do analysis that is not possible in a Candy
worksheet environment.

The Resource Rate Calculators II can be found under the tab > Advanced menu dropdown >
Resources rate calculators II.

2. Rate Calculator Documents

When Resource Rate Calculators II has been selected, you will be directed to the Resource Rate Calculators
II Document Manager.

Herewith options to creating a document:

2 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
2.1 Importing 5th Base Resource Rate Calculator Documents

Previously created 5th base documents can be imported into 6th base.

This would be recommended to do as 5th base is a legacy system and will soon become obsolete.

Do this

Select New > Import 5th Base Calculator Documents >

Choose between:
➢ Import Job Calculator Document
➢ Import My Template Documents
➢ Import My Reference Documents
➢ Import Candy Template Documents
➢ Import Candy Reference Documents

These documents are sourced from resource rate calculator found under Estimating > Advanced >
Resource Rate Calculators.

3 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
All information including: Heading Descriptions, Resource Coding and Descriptions, numeric values
and formulae is carried over into the 6th Base document.

2.2 Select Resource Rate Calculator Template Document

Selecting any of the available existing Template documents as a starting point is another option.
These documents provide guidance and can be customized to your required specifications.

Do this

Select New > New Template Document >

Choose between:
➢ Plant Lookup List – Reference Document
➢ Structural Steel Tables – Reference Document
➢ Plant Rate Calculator – Manual Document
➢ Plant Rate Calculator – Full Document
➢ Labour Rate Calculator – Manual Document
➢ Labour Rate Calculator – Full Document
➢ Materials OnSite Calculator – Manual Document

2.3 Create New Calculator Document

Choosing this option, you will create a new calculator document from scratch.

Do this

Select New > New Calculator Document >

4 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
3. Rate Calculator Documents Heading Structure

The Resource Rate Calculators II Document Manager is structured in accordance to the following:

Rate Calculator Reference Documents - holds documents which contain static or slow-changing
data. (E.g. Structural Steel Tables or Plant Purchase prices, life of equipment, etc.) This information
would be used frequently in calculations.

Rate Calculator Documents - holds documents which contains the final calculated rates which will be
used to export to the resource list for the use in the Candy estimate.

Addition headings and sub-headings can be included / removed based on your requirements.

Do this

Right-click on the document manager select New > New Heading or New Sub-Heading

To remove a heading, right-click on the heading and select Delete Heading

5 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
4. Using Resource Rate Calculator Documents

4.1 Setup a New Rate Calculator Document

When a new rate calculator document has been created, select the document by double-clicking on it
or highlighting and using the select function button in the bottom right-hand corner of the document
manager.

The change column layout box will appear. Within this box is where definitions, settings and styling of
the rate calculator document will be done based on your requirements and desired outcome.

1. Document header: Include a left justified, centered and/or right justified header. Header
descriptions are limited to 160 characters.

2. Sample column headers: Populated information will be displayed in this environment based
on what has been defined within the column headers section. Information displayed is column
number, description and width of column.

3. Column name: The column header name is created here, information can be structured in 3
lines (Top, middle and Bottom) and each line is limited to 50 characters.

4. Column width: The column width is set here. Column width length can be set to a maximum
of 255 characters.

5. Column type: Here we can define the type of information that will be contained within the
column. With the drop down functionality, you can choose the required type:

➢ Free Format: Columns can contain any information and is seen as text only

6 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
 information.

➢ Numeric Format: Displays numeric values with thousand separators and decimalization up
to 4. No text can be included in these columns.

➢ Date Format: Displays date format only. No text or numeric values are displayed in this
type column. Dates are entered 01 May 18 or 01 05 18

➢ Formula Format: This is a calculated column and displays numeric value up to 4 decimals.
This is also a non-editable field and can only be changed by changing either the format or
the formula contained for the column.

6. Decimals: Choose to display decimals up to 4. Only available in Numeric and Formula formats.

7. Column formula: Various formulae can be included in this field in order to calculate the
column requirements. All formula functions and operators are displayed below in section 6.
Formulae is limited to 248 characters after the = ‘equal’ sign.

8. Columns: These function buttons allows you to create a new column, delete a column, copy
an existing column and/or move column.

9. Lookup code: In this section, codes with a value can be created. Same as a local variable in a
worksheet. Codes are limited to 5 alpha numeric characters and 1000 codes can be added.
When recalling a code the [] square brackets are used with the lookup code in the formula
field.

10. Column styles: In this section there are various tick options:

➢ Indent Column data: Data will be indented from left based on under what heading level
they are displayed.

➢ Lock Column data: Lock data to prevent changes in the calculator document.

➢ Column totals: Display numeric or counter values for the column.

➢ Hide Column: Select if the column should not be displayed in the calculator document.

➢ Column Lines: Choose to have a single, double or triple line to be displayed after or before
and after the column.

➢ Text Inset: Choose text to be displayed normal, medium or wide.

4.2 Importing Resources Information from Resource List

When importing resources information from the resource list, only the resource code is imported.
Making use of the standard 6th base formulae, any other field in the resource list corresponding to
that resource code can be imported.

Note!! When creating a new resource code in the resource rate calculator document, a corresponding
resource code must be created in the resource list in order to export information from the rate
calculator document to the resources list.

Once our rate calculator document has been setup in term of columns, we can now import information
from our resources list.

Do this

7 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Open selected rate calculator document > go to Tools > Import/Export > import Resource

This will bring up a dialogue box where you need to select the Column number where you want to
insert the Resource codes. The Resource codes will be imported into Column 1 in the example below.

Once the destination Column has been selected, a further dialogue box is displayed (see below).

You now have an option to:

1. Select all codes or a range of codes.
2. Filter to selected resource types.

Resource Codes have now been imported from the resource list based on our selection.

We can now include the required 6th base formulae to import the resource information relating to the
resource codes imported.

Do this

8 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Formulae is setup in our change column layout document. Go to view > Add/remove columns or use
shortcut key Ctrl+F1

Ensure to select column 2 and include the formulae to recover the resource description relating to
resource code in column 1.

Let’s analyze the formula we included.

C2 = CANDY ( RES ( C1 , DESC ) )

Current Recover Match Return

Column This from information description
formula is Candy Job resources contained of Match in
applied to list in column 1 column 1

Full list of formula functions and operators are displayed below in section 5.

Now include formula for column 3, 4 and 5

Column 3: C3=CANDY(RES(C1,UNIT))
Column 4: C4=CANDY(RES(C1,RATE))
Column 5: C5=CANDY(RES(C1,UTIL))

9 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Once we have finished including the formulae, click

You will be returned to the rate calculator document, click

Information as per your resources list have now been imported into the rate calculator document for
the selected resources.

4.3 Heading Level Structuring

The rate calculator document allows to create up to a level 9 heading structure.

Do this

By placing the cursor in any column in the first line, use shortcut key functionality Ctrl + Insert. This
will include a blank line above the first line item.

Provide the new line(s) a description.

We can now set a level heading to these new line(s)

By right-clicking on the row selector for the applicable line, go to > set line(s) level

You can now choose the level heading u wish to assign. Up to level 9 headings can be created.

10 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
A structured document can now be created with the ability to collapse and expand to desired heading levels to
view information.

4.4 Export Resource Rates back to Resources List

We will be adding additional columns to the document in order to establish a modified rate which we wish to return to
the resources list.

Do this

Utilisation Value column: C6 = C4 * C5

Rate Back to Candy column: C7 = (C6 * 1.25) / C5

To send modified rates back to the Candy Resource list, select Tools > Import/Export > Export all rates. This will open
up a dialogue box where you select whether to copy to existing, base or final Resource rate. You will also have to
identify the Column numbers of the Resource code and Resource rate which are to be exported.

11 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Click OK.

The new rates from the document are then written back to the resource list in Candy to the selected destination rate.

12 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
5. Rate Calculator Reference Documents

Rate Calculator Reference Documents - holds documents which contain static or slow-changing
data. (E.g. Structural Steel Tables or Plant Purchase prices, life of equipment, PPE requirements, etc.)
This information would be used frequently in calculations.

Any other document created can be used as a reference document. Only one reference document can be used
for a rate calculator document.

Do this

We will create a new document for our total PPE requirements per labour category. The total PPE inclusive rate will
then be used as a reference to our already built labour rate document. A unique code is required in both documents
as this will be the identifier for what value needs to be allocated against what resource.

The resource code will be used in the reference document as the unique code for identification purposes. With this
example we have created a total rate for PPE per hour.

13 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
In our rate calculator document we have now included a column to bring in the total PPE rate per hour from our
reference document.

Let’s analyze the formula we included.

C2 = REF ( C1 , 06 , C1 , C12 )

Column
Current code is in to Corresponding Return
make Document
Column column Column
reference to Number
formula is Reference For code in that
in current to make
applied to reference contains
document reference to
document value

14 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
6. Formula Functions and Operators

Formulae are used extensively in the Rate Calculators for manipulating the columns of a document and for
getting information from Candy and other documents. Formulae are entered on the column formula field on
the change column layout document.

Arguments are:
• Any number
• Column reference - C1
• Look-up - [C1]
• Function - SUM(1,2,C1)
• Expression - (1+C1/10+5%)

Group 1: Arithmetical operators

Symbol Name Examples Remarks

C1+C2
+ Plus Add the arguments on either side of the plus sign
(5*C1)+99
C1-C2
- Minus Subtract the arguments on either side of the minus sign
C1-SUM(C2,C9)
C1/C2 Multiply the arguments on either side of the multiply
* Multiply
C1*ABS(-99) sign
C1/C2 Divide the argument on the left by the argument on the
/ Divide
C1/INT(C2+21) right
Unary -C1+C2 If preceded by an open bracket, the minus will negate
-x…
Minus ABS(-99) the argument
C1+10% Divide the argument on the left of the % by 100 and
% Percent
C1*(C2+2)% apply this fraction to the preceding argument

Group 2: Logical operators

A logical operator is any operator which resolves to either TRUE or FALSE.

The definitions of TRUE and FALSE are as follows:

-1 or any non-zero value represents TRUE zero represents FALSE

Symbol Name Examples Remarks

Logical C1 AND C2 If both arguments are non-zero the result is TRUE.
AND
AND C1 AND INT(C2) Otherwise result is FALSE
C1 OR C2 If either argument is non-zero the result is TRUE.
OR Logical OR
C1 OR INT(C2) Otherwise result is FALSE
Logical If the arguments within the brackets resolve to TRUE,
NOT() NOT(C1=5)
NOT the result is FALSE, and vice-versa
C1 = C2 If the 1st argument is equal to the 2nd, the result is
= Equal to
IF C1=2 THEN... TRUE, otherwise FALSE
Greater C1 > C2 If the 1st argument is greater than the 2nd, the result is
>
than C1>SUM(C2..C9) TRUE, otherwise FALSE
C1 < C2 the 1st argument is less than the 2nd, the result is TRUE,
< Less than
IF C1<2 THEN... otherwise FALSE

15 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
 Greater
C1 >= C2 If the 1st argument is greater than or equal to the 2nd,
>= than or
C1>=SUM(C2..C9) the result is TRUE. Otherwise the result is FALSE
equal to
Less than C1 <= C2 If the 1st argument is less than or equal to the 2nd, the
<=
or equal to C1>=INT(C2) result is TRUE. Otherwise the result is FALSE

Group 3: Functions

Symbol Name Examples Remarks

Square SQR(C1) Calculates the square root of the argument between the
SQR(n)
root SQR(C1^2+C2^2) brackets
Absolute ABS(C1) Returns the absolute value of the argument between the
ABS(n)
value ABS(C1-100) brackets
Integer INT(C1) Returns the integer portion of the argument between
INT(n)
value INT(C1/100) the brackets
Rounds the argument between the brackets, to the
RND(C1)
nearest whole number, using 4/5 rule
RND(n) Round RND(C1/100)
Rounds the argument to the nearest 3 decimals using
RND(C1,3)
the 4/5 rule
Returns the trigonometric sine of the argument between
SIN(n) Sine SIN(C1)
the brackets
Returns the trigonometric cosine of the argument
COS(n) Cosine COS(C1)
between the brackets
Returns the trigonometric tangent of the argument
TAN(n) Tangent TAN(C1)
between the brackets
IF C1 > 0 THEN C1 If C1 is non negative, print the value of C1. If not, blank
CLR() Clear
ELSE CLR() ENDIF the field
IF C1 > 0 THEN C1
BLANK() Clear Same as CLR()
ELSE CLR() ENDIF
Do nothing in the current column
STET() Stet STET()

Group 4: Functions that use a List or Range as Parameters

Symbol Name Examples Remarks

SUM(C1,C5,3)
SUM(list) Sum Add the list of arguments
SUM(C1^2,[C2])
AVG(C1,C5,3)
AVG(list) Average Calculate the average of the list of arguments
AVG(C1^2,[C2])
MIN(C1,C5,3)
MIN(list) Minimum Calculate the minimum of the list of arguments
MIN(C1^2,[C2])
MAX(C1,C5,3)
MAX(list) Maximum Calculate the maximum of the list of arguments
MAX(C1^2,[C2])
Add the values from the range of column references
SUM(range) Sum SUM(C1..C5)
Calculate the average of the range of column references
AVG(range) Average AVG(C1..C5)
MIN(C1,C5,3) Calculate the minimum of the range of column
MIN(range) Minimum
MIN(C1^2,[C2]) references
MAX(C1..C5) Calculate the maximum of the range of column
MAX(range) Maximum
MAX(C1^2,[C2]) references

16 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Group 5: Conditional structures

Structure Remarks
IF arg THEN arg ENDIF

IF arg THEN arg ELSE arg ENDIF IF SUM(C1..C9) > 1,000 THEN
100 ELSE 200 ENDIF
IF arg THEN Arg ELSE IF arg THEN
arg ENDIF ENDIF

Group 6: Text functions and operators

Symbol Name Examples Remarks

Get the data from column 5 as text
Tx Get column T5
The result is the text between the quotes
“ “ Text “House”
Joins two text values
& Concatenate “House” & T4
If the left text argument is exactly the same as the
Text
= T4 = “House” right text argument, the result is TRUE Else the result
compare
is FALSE. Is not case sensitive.
The result is the numeric value of the text argument.
VALUE() Value VALUE(“123”)+4 An invalid number will evaluate as far as possible. The
formula: VALUE(T1) is equivalent to the formula: C1

Group 7: Miscellaneous

Symbol Name Examples Remarks

PI is defined as 3.14159
PI Pi 2*PI*C3
Calculates the square of the argument
X^2 Squared PI*C5^2
Calculates the cube of the argument
X^3 Cubed C4^3+2
Everything after the back slash is treated as comments
\ Comment PI*C5^2 \ Area
Cx =
Spreads a rate over a given duration, giving the total
Spread Spread SPREAD(StartCol,
i.e. Duration*Rate
Duration, Rate)
Display the current row number in column 3
Row Current Row C3=ROW()
Current
Col C3=COL() Display the current column number (3) in column 3
Column
Display the value in column 2, row 3 in column 3
Cell Cell Value C3=CELL(2,3)
C3=CELL(COL()- the value in the cell to the left of the current row, in
Cell Cell Value
1,ROW()) column 3
Display the text in column 2, row 3 in column 3
Celltext Cell Text C3=CELLTEXT(2,3)
Set Line Sets the level of each line to the level given in C2.
Setlevel C3=SETLEVEL(C2)
Level Returns the new level to C3

17 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Group 8: Links to Candy

8a: Linking to Candy Global Variables

Retrieves the Global Variable Value for a code used in the Global Variables

CANDY(GLOBALVALUE(GlobalCodeCol))

Example: C3 = CANDY(GLOBALVALUE(C1))

Parameter Meaning
GlobalCodeCol The column which contains the variable code as defined under Global Variables in
Candy

8b: Linking to Candy Resources

Retrieves the Value for a code used in the Resources List for chosen property to be returned

CANDY(RES(ResourceCodeCol, ResourceProperty)[,Job])

Example: C3 = CANDY(RES( C1 , DESC ) )

Parameter Meaning
ResourceCodeCol The column which contains the resource codes which will be matched with
Candy resource codes
ResourceProperty The required Candy item. This may be one of the following:

o DESC - Resource Description

o RATE - Resource Rate
o TYPE - Complex resource value
o UNIT - Unit
o UTIL - Utilisation
o GRUP - Resource group
o CLAS - Resource class
o REMS - Remarks
o BASE - Base rate
o SUB% - % by which the base rate is decreased
o ADD% - % by which the base rate is increased
o CURC - Currency in which the base rate is priced
o TAXC - Tax code

Job Job file extension code. If the document is from another job, the job file
extension must be specified. If the job name is omitted, the current job is
assumed.

Note: If a job extension is used, and the source document is backed up and
recovered into another job, the job extension will be different, and the remote
document will not be found.

8c: Linking to Candy Group Codes for Description

Retrieves the group description for a given group code used by a Candy resource

CANDY(GROUPDESC(GroupCodeCol))
Example: C3 = CANDY(GROUPDESC(C1))

18 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
 Parameter Meaning
ClassCodeCol The column which contains the group code, as defined in the resource group
definitions in Candy

8d: Linking to Candy Work Codes for Description

Retrieves the work description for a given work code used by a Candy Op Code

CANDY(WORKDESC(WorkCodeCol))

Example: C3 = CANDY(WORKDESC(C1))

Parameter Meaning
WorkCodeCol The column which contains the work code, as defined in the work code
definitions in Candy

8e: Linking to Candy Task Codes for Description

Retrieves the task description for a given task code used by a Candy Op Code

CANDY(YACDESC(YacCodeCol))

Example: C3 = CANDY(YACDESC(C1))

Parameter Meaning
YacCodeCol The column which contains the YAC code, as defined in the Bid Scroller in Candy

8f: Linking to Candy Currency Exchange Rates

Retrieves the exchange rate for a given currency code used by a Candy resource

CANDY(EXCHANGERATE(CurrencyCodeCol))

Example: C3 = CANDY(EXCHANGERRATE(C1))

Parameter Meaning
CurrencyCodeCol The column which contains the currency code, as defined in the currency
definitions in Candy

8g: Linking to Candy Tax Codes

Retrieves the tax % for a given tax code used by a Candy resource

CANDY(TAX%(TaxCodeCol))

Example: C3 = CANDY(TAX%(C1))

Parameter Meaning
TaxCodeCol The column which contains the tax code, as defined in the tax definitions in
Candy

19 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com
Group 9: Candy Rate Calculator Specific

Connects numeric data in a "Rate calculator reference document", to the Rate Calculator document
containing this formula

REF( KeyColumn, RefDocCode, RefDocKeyCol, RefDocDataCol )

Example: C3=REF( C1, AG, C1, C4 ) - Match column 1 in this document with column 1 in the
Reference document AG, and return the data in its 4th column to column 3 of this documents

Parameter Meaning
KeyColumn The column which contains the key which must be matched with the
Reference Document key column
RefDocCode The reference document code as it it appears on the Reference
Document selector
RefDocKeyCol The column in the Reference document, which must be matched with
records in the key column
RefDocDataCol The column in the Reference document, which contains the data
required

REFTEXT( KeyColumn, RefDocCode, RefDocKeyCol, RefDocDataCol )

Connects text data in a "Rate calculator reference document", to the Rate Calculator document
containing this formula. See REF() above for explanation of the parameters.

Operator Precedence

The following shows the order in which operations are performed in a formula. The precedence can be
overridden by grouping operations with brackets.

Operator Precedence
Exponentiation (^) First - Highest
Negation (-) Second
Multiplication and division (*, /) Third
Addition and subtraction (+, -) Fourth
String concatenation (&) Fifth
Comparison (=, <>, <, >, <=, >=) Sixth
AND Seventh
OR Eighth - Lowest

20 | Resource Rate Calculator II – Candy User Manual – v1

www.constructioncomputersoftware.com