Exporting employee opening balances allows you to then perform a bulk import to update all employee initial values at once. The purpose of this function is described in more detail here.
Exporting Opening Balances
You can bulk export employee opening balances by navigating to the payroll dashboard. From there, click on the Payroll Settings tab > Opening Balances (listed under the Business Settings header).
Click on Export > Opening Balances:
You will then be redirected to the following screen where you can choose from three data type options:
- Template with employee data: this template will include only active (non-terminated) employees setup in the payroll system;
- Template with employee data (including terminated employees): this template will include ALL employees in the payroll system. This is a handy when you're transitioning all your employees across from a previous payroll system and want all data contained in the one system;
- Empty Template: this template does not include any employees, only the relevant opening balance fields.
Choose from cvs or excel as the file format and then click on "Download".
What are each of the columns in the Opening Balances template?
This list explains what each column represents. You'll find further clarification of the information provided below if you use it when looking at the different tabs on the Opening Balances page of one of your employee's records.
Columns A to K are always constant regardless of your payroll setup. They are as follows:
Column A - ExternalID: this relates to an employee ID from another external system used in conjunction with the payroll system. This is captured from the "External Id" field in the employee's Details screen.
Column B - IRDNumber: this is captured from the "IRD Number" field in the employee's Tax Code Declaration screen.
Column C - EmployeeName: this is captured from the employee's "Preferred Name", "Middle Name" and "Surname" fields in the employee's Details screen.
Column D - TotalHours: only required if you want to add total hours paid in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (Refer to the Earnings tab of the opening balances page of the employee's record).
Column E - GrossEarnings: the total of gross earnings paid in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This is displayed at the bottom of the Earnings tab on the opening balances page of the employee's record). NB. You can just enter a total gross earnings figure in this column if you wish but if you choose to enter amounts for specific pay categories (see other columns in the spreadsheet labelled PCxxx, explained below), do NOT enter a total here. The system will calculate the total of all of the Pay Category columns in the spreadsheet once imported and so will automatically calculate the total for this column.
Column F - PAYE: the total PAYE deducted from gross earnings paid in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This field is displayed on the KiwiSaver & PAYE tab on the opening balances page of the employee's record). Please ensure that if you want to report the ACC Levy Amount separately that it is not also incorporated in the amount entered in this column.
Column G - AccLevyAmount: total ACC Earner Levy amounts deducted from gross earnings paid in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This field is displayed on the KiwiSaver & PAYE tab on the opening balances page of the employee's record). If your previous payroll system does not report separate amounts for PAYE and ACC Earner Levy, then leave this field blank as the ACC Levy Amount amount will be incorporated in the PAYE column.
Column H - StudentLoan: total student loan repayments deducted from gross earnings paid in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This field is displayed on the KiwiSaver & PAYE tab on the opening balances page of the employee's record).
Column I - KiwiSaverEmployeeContributionAmount: total employee contribution amounts calculated in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This field is displayed on the KiwiSaver & PAYE tab on the opening balances page of the employee's record). You must ensure the amount entered here EXCLUDES employer contribution amounts.
Column J - KiwiSaverEmployerContributionAmount: total employer contribution amounts calculated in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This field is displayed on the KiwiSaver & PAYE tab on the opening balances page of the employee's record). You must ensure the amount entered here EXCLUDES employee contribution amounts.
Column K - EsctAmount: total amount of employer superannuation contribution tax deducted in the initial financial year, up to the day before the period start date of the first pay run in this payroll system. (This field is displayed on the KiwiSaver & PAYE tab on the opening balances page of the employee's record).
The following column numbers will vary for each business and depends on the business setup but the category orders are the same for everyone. They are as follows:
Leave Categories: The next group of columns represent all the leave categories set up in your Leave Categories' payroll settings. In the template they are labelled as LC, then a number (which is the system generated ID code for the leave category), then the name of the leave category, eg. "LC262968_Annual Holiday Leave". For these fields you will enter the employee's leave balance in units. If the employee does not have a leave balance or the leave category is not tracked or set to accrue leave automatically, then leave the figure as "0". Negative leave balances need to be entered as "-12.657" (for example).
Deduction Categories: The next group of columns represent each of the deduction categories set up in your Deduction Categories payroll settings. In the template they are labelled as DC, then a number (which is the system generated ID code for the deduction category), then the name of the deduction category, eg. "DC141225_ChildSupportPayment". Deduction amounts should be entered accurately and in the correct deduction category so that the employees' YTD net amounts on their pay slip are correct.
Employer Liability Categories: The next group of columns represent each of the employer liability categories set up in your Employer Liability Categories' payroll settings. In the template they are labelled as EL, then a number (which is the system generated ID code for the employer liability category), then the name of the employer liability category, eg. "ELC628_ACCLevy". For these fields you will enter the employee's liability as a dollar amount. If there are no EL columns in your template, this means that no Employer Liability categories are set up in your business.
Pay Categories: The next group of columns represent each of the pay categories set up in your Pay Categories payroll settings. In the template they are labelled as PC, then a number (which is the system generated ID code for the pay category), then the name of the pay category, eg. "PC959199_Ordinary Hours". For these fields you will enter the employee's pay category breakdown as a dollar amount. You only need to enter amounts in these columns if you want the employee to see a breakdown of their YTD pay by pay category or if you want to report total wages broken down by pay category. Otherwise, entering an employee's total gross wage in Column E "GrossEarnings" is sufficient.
Importing Opening Balances
Once you have completed the spreadsheet, you can then import the employee opening balances in bulk, as follows:
- From the payroll dashboard, click on the Payroll Settings tab > Opening Balances (listed under the Business Settings header)
- Click on Import (top right hand side) > Opening Balances
- Click on "Select file" > select the file from your computer > click "Open" > click "Confirm upload?".
Once the file has imported, a status summary will be displayed:
Matching Criteria
When importing opening balances, employee records are matched according to the following criteria (in order of priority):
- If there is an IRD number specified, 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 appropriate field 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.
If you have any questions or feedback please let us know via support@nzpayroll.co.nz.
Comments
Article is closed for comments.