Question 1

Smart Formulas sourced from UpSheets

Accepted Answer

Before You Begin

Please read this entire article before starting to use this functionality in order to understand how it works and what changes will be made in Xero.

Smart Formulas are not a substitute for payroll or legal advice, nor are they intended to relieve you of your obligation to comply with the Legal Requirements applicable to your business.

Because this functionality creates pay items and assigns them to employees in Xero, we recommend for testing purposes to use the Xero Demo Company (it is free and can be reset at any time).

Settings

To be able to assign UpSheets Smart Formulas to Employees you must first make sure you have enabled this in your settings:

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

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/q8VvcQvWz7/settings.png (settings)

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/btzqWLq0CC/enable_smart_formula.png (enable_smart_formula)

The following screen will then be available by selecting Smart Formulas from the menu:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/c4lyp0QLpc/smart_formulas_screen.png (smart_formulas_screen)

Assign Smart Formulas

To assign a smart formula to employee(s) simply select the rows in the table for each employee. You may use CTL or Shift to assign multiple rows a once. The following screen will be displayed and allow you to select the appropriate award and enter the base rate.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/L3Kr7Q7fjc/smart_formulas_assign.png (smart_formulas_assign (1).png)

After assignment, all of the rates and allowances are displayed for each employee.A new pay item will be created for the award in Xero and will also be added to the pay template for each employee and set as the ordinary earnings rate.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/tMQ7dg3kOV/smart_formula_assigned.png (smart_formula_assigned (1).png)

In Xero, the employee pay templates and ordinary earnings rates will be updated:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/VVA6lzUQMU/smart_formuls_xero_pay_template.png (smart_formuls_xero_pay_template)

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/Tg1MbJb5oa/smart_formula_xero_oer.png (smart_formula_xero_oer)

Updating Rates

Base Rates and Allowances may optionally be updated for each employee individually by selecting a row and clicking Edit Rate. The pay template for the employee will then be updated with the updated rate.

Rates that are set as a multiple of the base rate may not be updated as these are calculated automatically.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/adyBdkdDGy/smart_formulas_update_rate.png (smart_formulas_update_rate (2).png)

Public Holidays

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

Importing Timesheets

You are now ready to import your timesheets from a CSV file using the normal process from the Import screen. If you need a sample file with your own data to test, select the following options after clicking Download Sample File (along with tracking codes if you use them):

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/IayPHqZNpz/smart_formula_download.png (smart_formula_download)

Removing Smart Formulas

To remove a smart formula from the employee(s) simply select the employees in the table and click Clear. UpSheets will attempt to remove all assignments from their pay templates and reset the Ordinary Earnings rate. If there is any problem doing this, it will need to be updated manually in Xero. Any Pay Items created are not automatically removed and should also be inactivated/deleted from Xero via the Payroll Settings as required.

Question 2

Smart Formulas

Accepted Answer

What are Smart Formulas?

Smart Formulas can be used to calculate earnings rates based on the dates and times worked by an employee.

They can calculate different rates for such things as:

different times of the day
daily overtime
weekly overtime
minimum shift engagement
split shifts
public holidays

Enabling Smart Formulas

Option 1: Custom Smart Formulas Sourced from Xero

This option lets you define your pay items in Xero with a specific format that describes the rules and apply them to employees via their pay template.

For more information and setup, please see these instructions

Option 2: Pre-defined Smart Formulas Sourced from UpSheets

This option lets you select pre-defined Smart Formulas and assign them to your employees from within UpSheets. It updates the employee pay template and creates the required pay items in Xero automatically.

This functionality is currently in development and a limited number of awards are available:

Hair and Beauty Industry Award (HBIA) [MA000005]
Hospitality Industry General Award (HIGA) [MA000009]
Cleaning Award (CleanA) [MA000022]
Amusement, Events, and Recreation Award (AREA) [MA000080] (CASUAL)
Social, Community, Home Care and Disability Services Industry Award (SCHADS) [MA000100] (Home Care)
Restaurant Industry Award (RIA) [MA000119] (CASUAL)

Click here to request the addition of a new award to the Smart Formula library

For more information and setup, please see these instructions

Availability

SMART FORMULAS ARE CURRENTLY AVAILABLE IN AUSTRALIA ONLY

Please contact us if you need this in the UK/NZ

Question 3

Smart Formula Pay Item Defaults

Accepted Answer

Pay Item Defaults

When UpSheets is used as the source for Smart Formulas, it may need to create new Pay Items in Xero the first time they are used.

The following default values may be set:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/0NR9qGhulL/pay_item_defaults.png (pay_item_defaults (1).png)

These values will then be used for any new Pay Items created. Omce created they will not be updated, so any pay Items that have been created prior to these settings being changed will need to be manually updated in Xero.

For the same reason, if you need to be update these settings on existing Pay Items, this can be done directly in Xero after they have been created.

Question 4

Error Saving Pay Item

Accepted Answer

Xero can occasionally have issues saving new Pay Items when some of the existing pay items have invalid setup.

In order to resolve this:

In Xero navigate to Payroll> Payroll Settings> Pay Items
Check all pay items to make sure every pay item has a valid expense account (i.e. the account is active and has a type of Expense). If not then update it to one that is.
Be sure to click Show Inactive and also check/update these as required.

Question 5

Using Public Holidays with Smart Formulas

Accepted Answer

Background

Smart Formulas can be used to select different earnings rates on public holidays. In order to do so, they must first be configured in UpSheets (even though Xero has public holidays configured, they are not available publicly).

Basic Setup

To use public holidays, it must first be enabled:

Smart Formulas> Smart Formula Settings> Holidays

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/MXaz7Onx3j/enable%20smart%20formulas.png (enable smart formulas)

Then click Setup Holidays and then Add each holiday to the default calendar

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/jgcT0Zk7dv/add%20holiday.png (add holiday)

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/zro1FGCMN5/calendar%20setup.png (calendar setup)

Once complete click Back to Settings to exit

Default Calendar

As standard, a single Default Calendar is available and automatically assigned to all employees.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/11KhSBg2hM/default%20calendar.png (default calendar)

Using Additional Calendars

If you require more calendars, simply click Add Calendar, give it a name and click Save. You can then add holidays in the same way as usual.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/ah4ZgsVVVe/add%20calendar.png (add calendar)

NOTE: You must add all required dates for each calendar.

When multiple calendars are defined, you can optionally select a calendar for each employee. If some employees do not need a calendar, simply set up a calendar with no holidays and assign it as required.

Question 6

Smart Formula Allowances (from Xero)

Accepted Answer

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:

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

Other Allowances

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:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/FMpop3v7tb/add_line.png (add_line)

Clicking the link will display all manual allowance to be selected and applied to the employee on that date:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/YGG357leyO/add_line_modal.png (add_line_modal)

Question 7

Using Ignore Types with Smart Formulas

Accepted Answer

Ignore Types

A CSV file often has additional rows that you don't want to import and Upload to Xero.

By entering values in the Ignore Types field, these rows will not be imported or validated in UpSheets and will also not be uploaded to Xero.

The list can have multiple values separated by a comma. A wildcard* can also be used to match other patterns.

All values are Case Sensitive

Example

For example, with the below setup:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/5xVV1sivfQ/ignore%20types.png (ignore types)

Any lines with the following types would be excluded:

Break
Public Holiday
Week
Week 1
Weekly

Any types that don't match would be included, such as:

Ordinary Hours
Breaks
Annual Holiday
WEEK 1

Note - In order for this to work, a Type column must be selected when importing the file.

Question 8

Using Smart Formulas with RDO / Rostered Days Off

Accepted Answer

In some situations, it may be necessary to automatically remove time from a shift for the purpose of an RDO.

Below is an example of how this can be done using Smart Formulas.

In this scenario, employees usually work 8 hours, but should only be paid for 7.6 hours. Any time above 8 hours should be paid as overtime as usual.

This can be handled using three rules as follows:

Note - the number is essential as it determines the order the rules are applied.

The setup of the second rule in Xero is as a multiple of 0 x Ordinary Earnings Rate:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/sepaxXzHeB/RDO%20Setup.png (RDO Setup)

When the pay items are added to the Employee Pay Template and the Smart Formulas are refreshed in UpSheets they are as below:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/hDtxk8EsLo/RDO%20Smart%20Formulas.png (RDO Smart Formulas)

After validating a file, the shifts are broken into the components:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/9jKMDiR4tF/RDO%20Processed.png (RDO Processed)

The resulting payslip has the RDO total and no pay:

Question 9

Uploading Smart Formulas with other Types (Earnings Rates)

Accepted Answer

Setup

The default behaviour when using smart formulas is to treat all lines as smart formulas. This is shown in the smart formla settings as Allow Types:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/8jCYN3OMxx/allow%20types.png (allow types)

If you wish to upload nother earnings types along with this, select the option Allow All Types and Save

Types

For this feature to work, you must supply a Type column in your file to match against your Xero pay items. Any Types that match this column (or have been mapped to one) will be treated normally and not processed as a Smart Formula.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/KoSXHAVvpw/type%20selection.png (type selection)

Hours

You can optionally select an Hours column to use values from another column than the start/end times.

If a value is present in the hours column, this will be used for the non smart formula types. If no value is present, the start/end times will be used to calulate it. If they are missing an error will be given.

Question 10

Missing Xero Smart Formulas

Accepted Answer

In order for Smart Formulas to be imported into UpSheets from Xero the pay item in Xero must:

Match the format described here;
Be assigned to the pay template of an active employee; and
Be valid for timesheets.

The easiest way to check if a pay item is valid for timesheets is to add a new timesheet in Xero (Payroll> Timesheets> Add Timesheet) and check that it is available in the dropdown.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/AmR1nN5Vqi/timesheet%20dropdown.png (timesheet dropdown)

If they are not visible it is usually because they are not active, or because they are set up as an allowance. Allowances may be uploaded as timesheets if they:

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)

See here for more information