When a business migrates their payroll over to a new payroll system, they need to import historical gross earnings (HGE) from their previous payroll system for the correct calculation of leave rates such as ADP (average daily pay), AWE (average weekly earnings) and OWP (ordinary weekly pay).
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.
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.
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 two ways of accessing the Historic Gross Earnings export from the payroll dashboard:
- Go to Payroll Settings.
- Under Business management, select Data Extracts.
- Select Import/Export according to which action you wish to perform.
- You will see a drop-down menu offering two HGE options: Historic Gross Earnings - AWE and Historic Gross Earnings - OWP. These are two different spreadsheet formats.
Previously, we only had the provision to import the 52 week gross earnings but these figures included non-discretionary bonuses and other non-discretionary payments which wouldn’t be included in the OWP 4 weekly so we have made the provision to also import the OWP 4 weekly gross earnings as an additional import.
We have also changed the format of the spreadsheet that is used for the imports. Previously an employee had one row which needed to be populated with up to 52 weeks of gross earnings on a per pay period basis, including the hours and days for those pay periods which was not user friendly to populate and if these figures needed to be added so that calculations could be checked it was difficult.
We have changed the format so that for a weekly employee, they will now have a separate row for each pay period which makes it easier to populate and add if the need arises. The old format can still be used to import but when the user extracts the spreadsheet it will be extracted in the new format.
The system will now use the figures provided in the OWP 4 week import to calculate OWP 4 weekly during the first 4 weeks of pay runs in the KeyPay system and if these have not been imported but the 52 week gross has been imported, it will fall back on that import for the OWP 4 weekly calculation.
- Select Data type and File format
- Click Download.
- The downloaded file will show Historic Gross earnings.
Or
- Go to Payroll Settings tab.
- Under Business Settings, select Opening Balances.
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.
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/OWP is
calculated correctly.
After the Period Ending column, the next column is Period Gross. In this column you must enter the employee's gross earnings that would constitute AWE/ADP or OWP. 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/OWP 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, the less of the information that 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 or 1 week of historic gross earnings plus 3 weeks of pay runs
for the OWP.
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 Hours. In these columns you must enter the number of hours worked/paid by each employee for each relevant pay period. This field is crucial for employees working irregular hours so that Average Hours reports can be populated in order to calculate their leave pay.
The last column is Period 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 follows: One half day = 0.5 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. Further information on how the system calculates pro rata historic gross earnings can be found here.
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(business settings) screen.
- Click on the Import button located on the top right hand side.
- Select Historic Gross Earnings - AWE or Historic Gross Earnings - OWP from the drop down list.
- This will take you to the Import Historic Gross Earnings – AWE screen where you can toggle between Import Historic Gross Earnings – AWE and Import Historic Gross Earnings - OWP - 4 Weekly by clicking on the Import OWP – 4 weekly button to the right.
- Click Select file then select the file from your computer and click Open and click Confirm upload.
If your file includes employees on each worksheet (pay frequency), make sure 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.
When you choose to clear the Historic Gross Earnings, upon clicking the Clear button you are given the option to clear All or you can choose to only clear one of the AWE or OWP 4 weekly.