Exporting Options
There are a number of exports available via the Payroll settings > Opening balances page.
If you click on the 'Export' button, located on the top right hand side of the page, you then have the option to export:
- Opening balances;
- Payroll history;
- Tax reliefs: See this article for information about importing/exporting tax reliefs;
- Benefits in kind: See this article for information about importing/exporting benefits in kind
Payroll history
It is important that the employee's pay roll history data from previous employment (via TP3 form) is provided to the current/new employer where the employee has started employment part way through the year so that the amounts can be included in the calculation of taxable income for PCB tax purposes.
If previous payroll history data are not imported into the system for the employee, this will incorrectly calculate the employee's PCB.
From the data type drop down, you can choose three options:
- 'Template with employee data' - this template will list only active/current employees in the payroll system;
- 'Template with employee data (including terminated employees)' - this template will list all active and terminated employees in the payroll system;
- 'Empty template' - this template does not list any employees; only the relevant opening balance fields.
Once you have selected the appropriate Data type and file format, click 'Download'.
What are each of the columns in the payroll history opening balances template?
Within the Payroll history export, there are two worksheets, the first being the 'Total summary' and the second sheet is for 'Tax relief items'.
The 'Total summary' worksheet, this list contains the employee's total gross earnings, taxes, EPF, SOCSO amounts paid by the previous Employer.
Column A & B: Employee name - this is captured from the employee's 'Preferred name', 'Middle name' and 'Surname' fields in the employee's Details screen;
Column C: Income tax number - This is captured from the Employee file > Statutory details page;
Column D: ExternalID - this relates to an employee ID you may be using that comes from another external system. This is captured from the "External Id" field in the employee's Details screen.
Column E: Last period ending - This will always be 31/12/xxxx of the relevant tax year.
Column F: Period 1 ending - This will always be 31/12/xxxx of the relevant tax year.
Column G: Period 1 gross salary - The total of gross earnings and bonuses paid.
Column H: Period 1 BIK taxable - The total of benefits in kind taxable.
Column I: BIK tax exempt - The total of benefits in kind tax exempt. This is only for information purposes and is not used for reporting or any calculations in the system.
Column J - tax (MTD)- The total tax (MTD) paid.
Column K: Period 1 allowances - The total taxable allowances paid.
Column L: Period 1 EPF employer - The total EPF employer paid.
Column M: Period 1 EPF employee - Total employee EPF amount paid.
Column N:Period 1 SOCSO employer - Total employer SOCSO amount paid .
Column O: Period 1 SOCSO employee - Total employee SOCSO amount paid.
Column P: Period 1 CP38 - Total CP38 (if any) paid.
Column Q: Period 1 Zakat - Total Zakat paid.
The 'Tax relief items' worksheet, this list contains the total amount per tax relief items claimed with the previous Employer. The reason why this is required so that it accurately reflects for the tax calculations as well as ensuring that the tax relief thresholds limits are tracked for the relevant tax year. The tax relief items are as follows:
- Medical/ care for parents
- Medical/ care for father only
- Medical/ care for mother only
- Basic support equipment for self/family
- Degrees - studies/higher education fees
- Courses - studies/higher education fees
- Medical expenses for self/family
- Complete medical examination
- Lifestyle - expenditure for personal use
- Lifestyle - sports activities
- Breastfeeding equipment
- Child care fees
- Skim Simpanan Pendidikan Nasional (SSPN)
- Life insurance
- Private retirement scheme/ annuity
- Education and medical insurance
- Social security organisation (SOCSO)
- Payment for alimony
- Domestic tourism expenditure
What are each of the columns in the opening balances template?
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 Leave". For these fields you will enter the employee's leave balance in the relevant unit type that the leave category has been configured to, for example, if the leave category has been set up with the unit type = 'days' then the balance entered must be in days. If the employee does not have a leave balance or the leave category is not set to accrue then leave the figure as "0". Negative leave balances need to be entered as "-2" (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_Community Chest".
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_WorkCover". 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.
Lump sum payments: The last group of columns relate to any lump sum payments processed in the basis period for terminated employees in the previous payroll system. This information will be displayed on the Lump sum payments tab on the opening balances page of the employee's record.
Detailed information on each of these fields can be found in this article.
Importing Opening Balances and Payroll History
To import the employee opening balances, go to Payroll settings > Opening balances.
Next, click the Import button to the right of the page heading. The following Import Opening Balances page will be displayed:
Select the file that you wish to upload and then click the Upload button. You will then be asked to "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 ITN/TRN number specified, then we'll use that 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 ITN/TRN 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 use the value "(clear)" without the quotes in the appropriate field on your import file to remove it from the matching field on the opening balance page of the employee record.
If you have any questions or feedback please let us know via support@yourpayroll.io.