Question 1

Create a Mapping for Employee, Type or Tracking Code

Accepted Answer

When uploading a file you may see an error that has a link to Create a Mapping for an Employee, Type or Tracking Code.

This option enables you to map the value in your file to a valid value in Xero and will be applied to the current upload and any future uploads (until the mapping is edited or deleted).

Create a Mapping

To create a mapping simply click the link and select the desired value to map it:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/HhZb4wX1s7/map1.gif (map1)

Edit a Mapping

To edit a mapping simply click the mapped value, select a new value and click Save Mapping. The new mapping will be used for the current and all future uploads (or until the mapping is edited or deleted).

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/mtXMKtRHQb/map2.gif (map2)

Delete a Mapping

To delete a mapping simply click the mapped value and click Delete Mapping.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/5oe5RZKn3R/map3.gif (map3)

Download All Mappings

To download a file with all of your defined mappings, Click Manage from the Organisations screen and click Download Mappings.

Question 2

Uploading files with multiple columns with hours and earnings rates

Accepted Answer

Background

In order to upload data to Xero using multiple earnings rates, the upload file must have a different line for each one. Sometimes the data extracted from another system will have a column for each type, rather than multiple rows.

We need data like this:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/mcdZFMTVhJ/row%20data.png (row data)

But data sometimes looks like this:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/Yqg5Yvad6Q/column%20data.png (column data)

Getting your data in the right format

There are a couple of options to get your data into a usable format:

Using Excel to transpose your Data

Download the Excel Template
Follow the steps on the Instructions tab
Adjust the data tabs and formulas to match your own data as required

If you need any help with getting your data into shape contact us for some help or advice.

Question 3

Using Smart Formulas

Accepted Answer

SMART FORUMLAS ARE CURRENTLY AVAILABLE IN AUSTRALIA ONLY

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

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:

There are two ways to use this feature in UpSheets

Define your pay items in Xero with a specific format that describes the rules
Select a pre-defined Pay Rule from UpSheets

To enable Smart Formulas Sourced from Xero (Option 1)

In your user settings, enable Smart Formulas
In your smart formula settings, set your Smart Formula Source to Xero
Set up your formulas and import them using these instructions

To Smart Formulas Sourced from UpSheets (Option 2)

In your user settings, enable Smart Formulas
In your smart formula settings, set your Smart Formula Source to UpSheets
Assign formulas to Employees using these instructions

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]
Restaurant Industry Award (RIA) [MA000119] (CASUAL)

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

Question 4

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 5

Uploading Allowances to Payslips

Accepted Answer

This article is intended for Australian Xero users as the setup and features are specific to that region. NZ allowances can be uploaded as usual using Adjustments. UK users can not currently upload Adjustments until it is enabled by Xero (why?).

The way Allowances are uploaded using UpSheets depends on how they are set up as Pay Items in Xero.

Allowances can be uploaded to an employee payslips using the Adjustment option.

Allowances may also 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)

There is no right or wrong way - it depends what makes sense for your business/process. If your pay items are defined in a way to use Timesheets and you don't currently upload payslip Adjustments, then it is likely to be easier to upload them as timesheet.

Pay Item Setup

Below are the various ways Allowance Pay items can be set up in Xero:

Download a sample CSV file

Fixed Amount

A fixed allowance allows you to upload a fixed dollar amount to the payslip for an employee.The upload file must contain the following columns/values:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/WcCu1A8nX7/payItem1.png (Fixed Amount Allowance Setup)

Rate per Unit

A rate per unit allowance allows you to upload a rate and a unit to the payslip for an employee.The upload file must contain the following columns/values:

Rate Per Unit Allowance Setup

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/kILL7ZuFaF/payItem2.png (Rate Per Unit Allowance Setup)

Rate per Unit (Preset)

A rate per unit allowance allows you to upload the number of units to the payslip for an employee when the rate is set against the Pay Item in Xero.The upload file must contain the following columns/values:

Rate Per Unit Preset Allowance Setup

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/qEcFmL8JA6/payItem3.png (Rate Per Unit Preset Allowance Setup)

Multiple

Multiple Allowances allow you to upload the number of units that are a multiple of the ordinary earnings rate for each employee. These must be uploaded as timesheet lines or a validation will likely occur on the payslip adjustment upload.

Multiple Preset Allowance Setup

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/HeYmhhnPpO/payItem4.png (Multiple Preset Allowance Setup)

Upload/Validation

The Adjustment option is required to upload to a payslip and the payslip must already exist

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

When validated, the above formats will appear as below:

Validated

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

Payslip

Upon upload to the payslip in Xero, the adjustments will appear as below:

Question 6

Video: How to Upload a File

Accepted Answer

Watch how easy it is to upload your file to Xero using UpSheets:

Question 7

Assign UpSheets Smart Formulas to Employees

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. 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 Fomrulas

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 8

Overwriting Existing Xero Payslip Adjustments

Accepted Answer

Default Behaviour

The default behaviour when uploading payslip adjustments to Xero is to overwrite all existing adjustments on the payslip, so whatever you load will be all that exists for any given category (other than earnings created from timesheets and system generated lines such as tax).

For example, if you are uploading earnings, then any existing earnings will be overwritten, but the remainder of the payslip remains. So if you load 'Ordinary Hours' you will also overwrite any 'Overtime Hours' or 'Allowances' etc that aren't loaded via an approved timesheet.

For example - loading an Ordinary Hours line (10 Hours x $100)
Payslip Before

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

Payslip After

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

Alternative Behaviour

It is possible to modify this in your user settings so that only the specific types you are uploading will be overwritten. To do so, select the option below and revalidate your file:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/AnA8dKFAip/Dont%20Overwrite%20Payslip%20Setting.png (Dont Overwrite Payslip Setting)

By changing this setting, if you load 'Ordinary Hours' you will only overwrite existing 'Ordinary Hours', not any 'Overtime Hours' or 'Allowances' etc (or any other categories such as reimbursements deductions etc).

For example - loading an Ordinary Hours line (10 Hours x $100)

Payslip Before

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

Payslip After