Smart Formula Allowances (from Xero)
Introduction
Smart Formulas can be used to create allowances in addition to determining the earnings rates for employees.
These allowances can be either automatically or manually applied depending on how they are set up.
Getting Started
The following steps must be completed to use Smart Formulas for allowances:
- Set up Pay Items in Xero
- Assign Pay Items to Employee Pay Templates in Xero
- Enable Smart Formulas in UpSheets
- Refresh Smart formulas from Xero to UpSheets
- Import your file using Smart Formulas
This document explains Step 1. Set up Pay Items in Xero for Allowances. Click here for detailed instructions on steps 2-5
1. Set up Allowance Pay Items in Xero
The steps below are for loading smart formulas to set up automatic and manual allowances with Smart Fomulas.
Pay Items are set up in Xero by navigating to Settings> Payroll Settings> Pay Items
To be used for timesheets the pay items must:
- Have a Rate Type = Multiple; or
- Have a Rate Type = Rate per unit and Type of units = hours
- Have a Rate Type = Rate per unit and Type of units = The same time unit used for the Ordinary Earnings Type of the Employee (Days/Weeks etc)
To enable a pay item for use as an allowance Smart Formula, the name must use the following format:
NUMBER NAME [DAY][PERIOD][TYPE][MINMAX]
Example:
001 Daily Allowance [WEEKDAY][10~24][DAY]
For more information on defining NAME, NUMBER, DAY please see Smart Formulas (from Xero)
Period
The Period element can be used to define the minimum and maximum hours for applying the allowance, e.g.
[8~24] will apply the allowance only if 8 or more hours are worked.
[0~24] or [] will apply the allowance if any hours are worked.
This can be used to create different allowances for different periods e.g.
[0~10] Will be applied if the hours are less than 10
[10~24] Will be applied if the hours are 10-24
For allowances that require a time range, this can be defined as below and also limited by using the MINMAX element. To do so use the start_time - end_time option. Each time must be set using four digits and 24 hours clock.
Examples:
[0000-1300] = midnight to 1pm
[1400-1600] = 2pm to 4pm
[2000-0000] = 8pm to midnight
MinMax
The MinMax defines the number of hours when a time range is used for the period (e.g. [0000-0600].
The format is:
-
from_hour ~ to_hour.
Examples:
[0~5] = the first 5 hours
[5~24] = From 5 to 24 hours
[0~24] = the first 24 hours
This element is only available for certain allowance types as defined in this guide.
Type
Determines the type of allowance. The code value below should be entered into the formula to define the type of allowance.
Time-Based Allowances
| Code | Type |
|---|---|
| Hour | Hour |
| SHIFT | Shift |
| DAY | Day |
| WEEK | Week |
Other Allowances
| Code | Type |
|---|---|
| BS | Broken/Split Shift |
| ME | Minimum Engagement |
| TU | Top Up |
| MAN | Manual (not applied automatically) |
Examples
Time-Based Allowances (HOUR / SHIFT / DAY / WEEK)
All of these allowances work in a similar manner and can be used with the DAY/PERIOD elements to determine when they are applied.
The main difference is how they are applied to imported lines:
- HOUR - Applies the allowance to every hour worked in the period
- SHIFT - Applies the allowance to every shift (i.e. once for every timesheet line imported even on the same day)
- DAY - Applies the allowance once per day (i.e. once even if multiple shifts are imported on the same day)
- WEEK - Applies the allowance once per week (i.e. once per week so long as a line is imported during that week). The DAY element is not used for this type)
001 Allowance [WEEKDAY][0000-0600][HOUR]
Will apply the allowance for every hour worked between midnight and 6am on a Monday, Tuesday, Wednesday, Thursday or Friday. This can be used to add an additional hourly allowance to a base rate. If an emplyee worked from 02:00 - 03:30 they would be allocated 1.5 x this allowance. This allowance is only configured to work for periods between set hours.
001 Allowance [WEEKDAY][1~24][SHIFT]
Will apply the allowance for every timesheet line imported for a Monday, Tuesday, Wednesday, Thursday or Friday and the employee has worked at least 1 hour (and less than 24). This could potentially apply multiple times in a single day.
002 Allowance [WEEKDAY][8~24][DAY]
Will apply the allowance if one or more timesheet lines are imported for Monday, Tuesday, Wednesday, Thursday or Friday and the total hours each day are between 8 and 24. This can only apply once per day, but could apply up to 5 times if each weekday is worked.
003 Allowance [WEEKEND][][DAY]
Will apply the allowance if one or more timesheet lines are imported for Saturday or Sunday. The number of hours is not a factor as it is left blank. This would apply 2 allowance lines if both days are worked.
004 Allowance [EVERY][40~168][WEEK]
Will apply the allowance if the total hours worked in a week is over 40 (but below 168). This would apply a maximum of once per week.
Broken/Split Shift (BS)
A Broken/Spilt Shift allowance will be applied when an employee works multiple shifts in a day that have a gap between them. The size of the gap between shifts is defined using the period element.
The fourth [MinMax] element can be used to determine the maximum number of allowances to be applied in a day or week. e.g. [1~3] is once per day and 3 per week. This is optional and if left out it will apply the allowance every time the condition is met.
Examples
001 Split Shift [EVERY][1~3][BS][1~7]
Will apply the allowance if the gap between two shifts is more than one hour but less than 3. This will be applied a maximum of once per day and 7 times per week.
002 Split Shift [EVERY][3~21][BS][2~3]
Will apply the allowance if the gap between two shifts is more than three hours but less than 21. This will be applied a maximum of twice per day and three times per week.
003 Split Shift [WEEKDAY][1~23][BS]
Will apply the allowance if the gap between two shifts worked on a weekday is more than one hour but less than 23. There is no limit to how many times this will be applied each day or week.
Minimum Engagement (ME)
A Minimum Engagement allowance is applied when a shift is less than a set number of hours. The number of units is automatically calculated as the difference between the hours worked and the set number of hours. In this way, it can be used to pay an employee a minimum number of hours even when fewer are worked.
The MINMAX element is used to determine the minimum and maximum hours where the allowance should be applied.
Examples
001 Minimum Engagement [EVERY][][ME][0~4]
Will apply the allowance if any shift is worked for less than 4 hours. The number of units applied will be equal to 4 less the number of hours worked. If 1 hour is worked, the allowance will be for 3 hours. If more than 4 hours are worked, no allowance is applied.
002 Minimum Engagement [EVERY][][ME][1~2]
Will apply the allowance if a shift is worked for more than 1, but less than 2 hours. The number of units applied will be equal to 2 less the number of hours worked. If 1.5 hours are worked, the allowance will be for 0.5 hours. If more than 2 hours or less than one hour are worked, no allowance is applied.
Top Up (TU)
A Top Up allowance applies one unit of the allowance whenever an employee works at a particular time.
Examples
001 Top Up [MON-FRI][1900-0000][TU]
Applies the allowance if a shift is worked that starts, finishes or encompasses the period of 7pm to midnight on a Monday - Friday. This is usually set up In Xero as a rate per unit allowance to pay a set amount as an extra payment for a shift.
Manual (MAN)
A Manual allowance is not applied automatically, but can be added in UpSheets after importing your file and then uploaded to Xero.
Please note, these allowances must be added to all employee pay templates in order for them to be applied.
Examples
100 Manual Allowance [MAN]
When assigned to an employee an Add Line link will be displayed within UpSheets:
Clicking the link will display all manual allowance to be selected and applied to the employee on that date:
