Historic Gross Earnings - Importing & Exporting

Payment for leave is generally paid at:

• the greater of the employee's ordinary weekly pay at the start of the holiday or the employee’s average weekly earnings (AWE) for the 12 month period just before the end of the pay period before the annual holiday is taken; and
• the employee's average daily pay (ADP) when it is impossible or impracticable to determine an employee’s relevant daily pay (RDP), or where their daily pay has varied within the pay period where are any day based leave is taken. In addition, this could be the case when the employee does not have any historical data and little or no earnings in the new payroll system.

The purpose of importing historic gross earnings is to assist in determining the AWE and ADP when an employee takes leave, especially where the business has moved to a new payroll system.

N.B. The Historic Gross Earnings export has no impact or relationship at all with employee YTD earnings for the current financial year. If you wish to import employee YTD earnings refer to the Opening Balances import. 

There are 2 ways of accessing the historic gross earnings export:

1. Management > Payroll > Payroll Settings > Data Extracts) > Data type = Historic Gross Earnings; or
2. Management > Payroll > Payroll Settings > Opening Balances > Export = Historic Gross Earnings.

Regardless of the method used, you will be given the option of exporting the template containing the column headers and either:

• active employees only,
• active and terminated employees, or
• no employees.

Once you have selected the appropriate data type and file format, click "Download".

Understanding how the Historic Gross Earnings template works

When you export the template, you will notice there are 4 worksheets that cover each payroll frequency: weekly, fortnightly, half monthly and monthly:

Your employees will be assigned to the relevant worksheet based on their assigned pay schedule. Although you may not have each pay schedule set up in the business, the reason we include all four pay frequencies is to cater for scenarios where employees may have been paid at a different frequency during the 52 week period. 

If you have exported the template containing employees, the first 4 columns will be pre-populated using the data contained in the system.

The next column "Last Period Ending" relates to the period end date of the last pay run that was processed in the business's other payroll system. Once this date is entered, each "Period x Ending" column will pre-populate based on the value of the "Last Period Ending" field and the pay run's frequency. Any manual override of these values will cause an import error as follows:

This will need to be rectified and reimported to ensure the employee's AWE/ADP is calculated correctly.

The next column is "Period x Gross". In these columns, you must enter the employee's gross earnings that would constitute AWE/ADP. As per legislation, AWE does NOT include (unless the employment agreement says otherwise) the following:

• reimbursement for expenses incurred on the job;
• discretionary or ex gratia payments (it is recommended you seek further information or advice before deciding a payment is discretionary or ex-gratia and therefore should not be included in gross earnings;
• payments for cashed-up holidays;
• payments made by ACC (excluding the first week paid by the employer under s97 of the ACC Act 2001); 
• payments when an employee is on voluntary military service; and
• payments the employer is not contractually bound to make.

Each gross earnings amount MUST be separated by pay period otherwise the employee's AWE/ADP  will not be correctly determined. This is important because as each pay period passes where the business has been using this payroll system to process pays, less of the information will be needed from the historic gross earnings export. For example, assuming the business runs a weekly pay run. The first pay run in this payroll system has an employee that has taken annual holidays - to properly calculate the higher of AWE and OWP, the system will require the full 52 weeks of historic gross earnings. Then, when the fourth pay run processed in this payroll system has an employee taking annual holidays, the system will require 49 weeks of historic gross earnings to accurately calculate the employee's AWE.

The only exception to the above point is where an employee has worked with the company for less than 52 weeks. In that case, you would only enter data for each pay period the employee has been paid.

The next column is "Period x Hours". In these columns, you must enter the number of hours worked/paid by each employee for each relevant pay period. This field isn't compulsory so you can leave it blank.

The last column is "Period x Days". In these columns, you must enter the number of days or part days worked by each employee for each relevant pay period. N.B. Part days are entered as a full day, ie 1 part day = 1 day. Entering correct data in this field is extremely important due to the following reasons:

• The total number of days worked in the 52 week period is used as part of determining an employee's ADP when taking any day based leave;
• The system uses the total number of days worked when calculating an employee's pro rata gross earnings to then determine AWE, ADP and/or 8% holiday pay. The pro-rata earnings apply when an employee's leave year start date or leave entitlement falls mid-pay period.

If this field is left blank the system will assume the employee has worked a 5 day week.

 

Importing the Historic Gross Earnings template

Once you have completed all the fields, you can import the template from the Opening Balances screen. Click on the "Export" button located on the top right-hand side and then select the "Historic Gross Earnings" option from the dropdown list.

Then, click on "Select file" > select the file from your computer > click "Open" > click "Confirm upload?".

If your file includes employees on each worksheet (pay frequency), you must ensure all worksheets are completed. Otherwise, you will receive the following error:

If you want to import each worksheet separately, you can do so by moving the worksheet to a new spreadsheet.

Once the file has been imported, a status summary will be displayed:

Matching Criteria

When importing historic gross earnings, employee records are matched according to the following criteria (in order of priority):

• If there is an IRD Number specified and it is not '111111111', then we'll use the IRD Number to match the employee;
• If there is an External ID specified, then we'll use the External ID to match the employee;
• Otherwise, we will use the combination of IRD Number AND name to match the employee.

Deleting Data

If you want to use this import file to remove data from the opening balances, in bulk, you'll need to enter the value "0" (without the quotes) in the "Period x Gross", "Period x Hours" and "Period x Days" fields on your import file. This will revert the figure back to zero once the import is complete.

Alternatively, you will notice an option to clear the opening balances / historic gross earnings you have entered for the employee:

This works on a 'per tab' basis. If you choose to clear the data within that particular tab keep in mind the relevant reports will be updated. This feature is especially helpful if you have a high level of data entered.