Smart Formulas sourced from Xero

Introduction

Using Xero as the source of Smart Formulas allows you to define the custom rules you need to support your payroll processes. Once set up they will automatically be applied when importing and uploading your data.

Smart Formulas can be used to calculate the number of hours and earnings rate for employees using start_time, end_time and date rather than the default method of providing the date, earnings rate and hours.

Smart Formulas also support a number of allowances click here to read more.

Getting Started

The following steps must be completed to use import files using Smart Formulas:

1. Set up Pay Items in Xero
2. Assign Pay Items to Employee Pay Templates in Xero
3. Enable Smart Formulas in UpSheets
4. Refresh Smart formulas from Xero to UpSheets
5. Import your file using Smart Formulas

1. Set up Pay Items in Xero

The steps below are for loading smart formulas to be used with ordinary earnings rates. You can also set up automatic and manual allowances.

Pay Items are set up in Xero by navigating to Settings> Payroll Settings> Pay Items

To enable a pay item for use as a Smart Formula, the name must use the following format:

Number Name [DAY][PERIOD][][]

Example:

001 Ordinary Hours PT L1 [WEEKDAY][0800-1600][][]

Number

The Number should be three characters and zero filled between 1 and 999 (001, 010, 100 etc).

Name

The Name should describe the pay item and can contain any characters (excluding [].) The length of the name is limited by the total length of the entire pay item being 50 characters.

Day

Determines which day(s) the pay item applies to. Valid values are:

• MON
• TUE
• WED
• THU
• FRI
• SAT
• SUN
• WEEKDAY
• WEEKEND
• EVERY
• Ranges (eg [MON-WED], [THU-FRI], etc)
• Selections ([MON,WED,FRI], [FRI,SAT] etc)

Period

The period can be defined with either:

• start_time - end_time; or
• start_hour ~ end hour.

Generally the start_time-end_time should be used for normal hours and the start_hour~end_hour for overtime and rates that are based on the number of hours worked rather than the time of day.

To define a period of time for which an earnings rate is applied, use the start_time - end_time option. Each time must be set using four digits and 24 hour clock.

Examples:

[0000-1300] = midnight to 1pm

[1400-1600] = 2pm to 4pm

[2000-0000] = 8pm to midnight

To define the number of hours for which an earnings rate is applied, use the start_hour ~ end hour. Each hour must be defined as an integer or a float of up to 2 decimal places.

Examples:

[0~7.6] = 0 to 7.6 hours

[7.6~9] = 7.6 to 9 hours

[9~24] = 9 to 24 hours

The above rules are applied on a daily basis and so can be used to determine rates for daily overtime.

Weekly Overtime

To create a formula that is applied based on weekly hours instead of daily, a WOT parameter can be added to indicate this:

020 Weekly Overtime [WEEKDAY][38~168][WOT]

The above rate will be applied when the employee works more than 38 hours in a week (168 is the total hours in 7 days).

Multiple levels can also be defined if required:

020 Weekly Overtime < 2hrs[WEEKDAY][38~40][WOT]
020 Weekly Overtime > 2hrs[WEEKDAY][40~168][WOT]

The first rate will be applied for the first 2 hours. The second rate will then apply for all remaining hours.

The start day for the week defaults to Monday, but can be set in Smart Formulas> Smart Formula Settings

RDO / Rostered Days Off

Period rules can also be used to deduct hours from a shift to handle RDOs. See here for more information.

Other

The last two fields [][] are only used for allowances or where specified. They don't don't need to be provided when not used. So you can use:

001 Ordinary Hours [WEEKDAY][0800-2000]

instead of

001 Ordinary Hours [WEEKDAY][0800-2000][][]

Start/End Period Only

As shifts can often span multiple formulas, you can optionally choose to apply only the rule that the shift ends on to the whole period. e.g. for some awards, if a shift ends after 8pm, the after 8pm rate should apply to the entire shift, not just the part that was actually worked after 8pm. For others, if a shift starts before 6am, a rate may apply to the entire shift.

Start Period Only

To enable this feature, simply add a '-' sign to the day of the shift you wish to be considered final (e.g. [WEEKDAY-] not [WEEKDAY])

So for the following rules:

001 Ordinary Hours < 8am [WEEKDAY-][0000-0800]
001 Ordinary Hours [WEEKDAY][0800-2000]

If an employee worked from 07:00 - 19:00, the following would be generated:

12 Hours x 001 Ordinary Hours < 8am [WEEKDAY-][0000-0800]

MinMax Period

A MinMax period may optionally be used in this scenario as follows:

020 Before 6am [WEEKDAY-][0000-0600][0~8]
021 Before 6am [WEEKDAY-][0000-0600][8~24]

In this case, the final element provides the min~max hours for each rate. So two lines would be created as follows if an employee worked from 5am to 5pm:

8 Hours x 020 Before 6am [WEEKDAY-][0000-0600][0~8]
4 Hours x 021 Before 6am [WEEKDAY-][0000-0600][8~24]

End Period Only

To enable this feature, simply add a '+' sign to the day of the shift you wish to be considered final (e.g. [WEEKDAY+] not [WEEKDAY])

So for the following rules:

001 Ordinary Hours [WEEKDAY][0800-2000]
001 Ordinary Hours > 8pm [WEEKDAY+][2000-0000]

If an employee worked from 18:00 - 22:00, the following would be generated:

4 Hours x 001 Ordinary Hours > 8pm [WEEKDAY+][2000-0000]

MinMax Period

A MinMax period may optionally be used in this scenario as follows:

010 Ordinary Hours > 8pm [WEEKDAY+][2000-0000][0~4]
020 Ordinary Hours > 8pm [WEEKDAY+][2000-0000][4~24]

In this case, the final element provides the min~max hours for each rate. So two lines would be created as follows if an employee worked from 8am to 8pm:

4 Hours x 010 Ordinary Hours > 8pm [WEEKDAY+][2000-0000][0~4]
8 Hours x 020 Ordinary Hours > 8pm [WEEKDAY+][2000-0000][4~24]

Minimum Shift Gap

Sometimes a different rate may need to be applied if there has not been a minimum number of hours between the start of the current shift and the end of the previous one. This differs from a Broken Shift as it changes the rate used and also considers shifts from the prior day when contained in the upload file.

In The following example, the rate will be used for all standard hours in the shift when the shift starts within 10 hours of the end of the previous shift.

020 Less than 10 Hour Gap [EVERY][10][GAP]

Public Holidays

Public holidays can be enabled by setting the day to PH, for example:

001 Public Holiday [PH][0000-0000]

In order for public holidays to be used, they must be enabled in your smart formula settings. and dates must be set for your organisation.

Breaks

If your data contains lines with break times these can be removed automatically by Smart Formulas.

Full Example

Below is a full configuration for a Cleaning award (part-time level 1)

The above rules are applied in the following order of precedence:

1. Hour Periods
2. Time Periods

If there are multiple rates for the same period, the earliest start time will be used, otherwise, the lowest number will be used.

Examples: If an employee works 01:00 - 13:00 (1am - 1pm) on a Monday, the following rules will apply:

• 7.0hrs x 002 Mon - Fri PT L1 [WEEKDAY][0000-0800][][]
• 0.6hrs x 001 Ordinary Hours PT L1 [WEEKDAY][0800-1600][][]
• 2.0hrs x 006 Overtime < 2hrs PT L1 [WEEKDAY][7.6~9.6][][]
• 2.4hrs x 007 Overtime > 2hrs PT L1 [WEEKDAY][9.6~24][][]

If an employee works 01:00 - 13:00 (1am - 1pm) on a Saturday, the following rules will apply:

• 7.6hrs x 004 Sat PT L1 [SAT][0.0~7.6][][]
• 2.0hrs x 008 Overtime < 2hrs PT L1 [SAT][7.6~9.6][][]
• 2.4hrs x 009 Overtime > 2hrs PT L1 [SAT][9.6~24][][]

2. Assign Pay Items to Employee Pay Templates in Xero

Before setting up the Pay Template in Xero, the Ordinary earnings rate should be set for the employee (Payroll> Employees> Employment Tab)

All relevant pay items should then be assigned to each employee pay template (Payroll> Employees> Pay Templates Tab)

3. Enable Smart Formulas in UpSheets

Select Settings from the menu and enable the Smart Formula Option, then Save.

If you have formulas that need to use public holidays - please follow these instructions to set them up in UpSheets.

4. Refresh Smart Formulas from Xero to UpSheets

Select Smart Formulas from the menu

Click Refresh Smart Formulas from Xero to synchronise the two systems.

Review the table below to ensure all Smart Formulas have been created correctly. If any are missing, simply correct the name in Xero and click the button to refresh again.

Formulas only need to be refreshed if there is a change to either the:

• configuration of a pay item used in a formula; or
• pay items that are assigned to an employee pay template.

5. Import your file using Smart Formulas

You are now ready to import files using your smart formulas. To do so, simply select the Timesheets and Use Smart Formulas option

Your file should contain the following fields:

• first_name + last_name OR full_name
• date
• start_time
• end_time

Alternatively, if your file has only the total hours and no start_time and end_time, a default start time may be set instead