Employee Data Take On Details
- Mark Holland (Unlicensed)
Introduction
The file (current version EmployeeDataTakeOn.xltm) should be opened from a link rather than from within excel. Once open the Data Input worksheet should be selected and the fields entered as described below. Mandatory columns are marked below with an (M) and have a Bold header in the file. Once completed the Control worksheet tab should be selected and the Validate Data button clicked.
If errors are found the user will be asked whether they wish to correct the errors prior to export (at this point yes should be clicked unless sure that the validation can be ignored).
On the Data Input sheets any error will be highlighted in red in the right hand corner of the field, hovering over this will give details of the error.
Once corrected the Validate Data button should be re-clicked. The user will get the message that 3 separate csv files will be created and is asked to specify the location where the files will be created. The 3 files created are EmployeeStarter.csv, EmployeeYTDNIBalances.csv and EmployeeYTDPayBalances.csv. In the case of the two balances files these will be empty unless this is a mid-year migration where YTD values are being imported (if empty they do not require import).
The created files should be imported via the Import/Export -> Import Data screen. Selecting the relevant matching import format
File Details
Key Details
A: Employee Number
- Enter if already allocated.
- If a mid-year migration, this needs to match the reference number for the employee previously submitted via the FPS.
- If left blank, System Generated Employee number must be selected through the Company -> General screen.
B: Employee Forename (M)
C: Employee Surname (M)
D: Employee National Insurance Number
E: Payroll Name (M)
- Weekly
- Monthly
- Quarterly,
- Annual.
Payroll Names outside of this are allowed in InPay. For example a Company may have multiple weekly payrolls named Weekly1, Weekly2 and Weekly3. Therefore the validation on this field is only a guide and can be ignored where necessary. Payroll Name should match the Payroll Number field on the Payrolls -> Payroll page in InPay.
F: Sex (M)
- M/F
G: Date of Joining Company (M)
- This is used as both the Date of Joining and Service Start Date. If the Service Start Date is different because of TUPE rules, the date should be edited in the Starter file created by this macro
H: Date of Birth (M)
I: Title (M)
- Mr/Mrs/Miss/Ms/Dr/Rev/Rt Hon/Sir.
- Further titles can be added through the Company -> Company Lookup screen.
- The file will only validate against the list (Mr/Mrs/Miss/Ms/Dr/Rev/Rt Hon/Sir). If additional titles have been added to InPay the validation can be ignored.
J: Employee Leave Date
- Leavers in previous financial years should be excluded from the file.
K-P: Employee Address
- If populated a minimum of Address1, Town and Post Code should be entered
Q: Method of Payment (M)
- BACS/Cash/Cheque/Auto Pay
R: Tax Code
- Not required if the worker is a starter (details will be obtained from Start Statement or P45)
S: Tax Basis
- Required if Tax Code is entered. 0 = Cumulative, 1 = Wk1\Month 1 Basis
NI Details
T: Employee National Insurance Table Letter (M)
- A/B/C/H/J/M/X/Z – This is the national Insurance category Letter, not the last letter of the NI number.
U: Director Start Date
- If Director
V: Director Non-Cumulative status for calculation of NIC
- If Director
Previous Employment
W: Previous Taxable Pay
- Supplied via HMRC P6 or P45
- Is only required and will only be used in calculation if Date of Joining is in current tax year
X: Previous Tax Paid
- Supplied via HMRC P6 or P45
- Will only be used in calculation if Date of Joining is in current tax year
Starter Statement
Y: Statement (M - If employee is a starter)
- A/B/C
Z: Student Loan
- Should only be set if Statement entered
- Will default to N
AA: Student Loan Plan Type
- 1 or 2
- Should be set only if Student Loan is set to Y and Student Loan Repay Directly is set to N or Migrated is set to Y and worker has a student loan
AB: Student Loan Repay Directly – Y/N
- Should only be set if column Student Loan is set to Y
- Will default to N
AC: Student Loan before last 6th April – Y/N
- Should only be set if column Student Loan is set to Y
- Will default to N
AD: PG Loan Not Repaid
- Should be set if worker has current Postgraduate Loan
AE: PG Monthly Payments – Y/N
- Should only be set if column AD is set to Y and a Starter Statement is entered
- Will default to N
AF: PG before last 6th April – Y/N
- Should only be set if column AD is set to Y and a Starter Statement is entered
- Will default to N
Location
AG: Department
- Must match Description in Company -> Company Lookup screen
- If the worker has multiple Departments the Departments must be included in the Department 1, Department 2 or Department 3 field and will be used as the home department.
AH: Division
- Must match Description in Company -> Company Lookup screen
AI: Cost Centre
- Must match Description in Company -> Company Lookup screen
- Will populate the Cost Centre with 100% allocation. If the worker has a more complicated costing it can be entered directly in the Starter csv file created by the macro
AJ: Department 1
- Must match Description in Company -> Company Lookup screen
- Only required if worker is split between multiple departments.
AK: Department Percent 1
- Required if Department 1 is entered.
- Percent 1, 2 and 3 field must add up to 100
AL: Department 2
- Must match Description in Company -> Company Lookup screen
- Only required if worker is split between multiple departments.
AM: Department Percent 2
- Required if Department 2 is entered.
- Percent 1, 2 and 3 field must add up to 100
AN: Department 3
- Must match Description in Company -> Company Lookup screen
- Only required if worker is split between multiple departments
AO: Department Percent 3
- Required if Department 3 is entered.
- Percent 1, 2 and 3 field must add up to 100.
Payments
AP: PayElement1
- Will create a permanent payment against the worker for this Pay Element. This should match the Pay Element field in the Company -> Pay Elements screen and be of type payment.
AQ: PeriodAmount1
- If PayElement1 is populated then either this field or AnnualAmount1 must also be populated (fields are mutually exclusive). If populated should contain the value that is paid to the worker each pay period.
AR: AnnualAmount1
- If PayElement1 is populated then either this field or PeriodAmount1 must also be populated (fields are mutually exclusive). If populated should contain the value that is paid to the worker annually.
AS: PayElement2 (See AP)
AT: PeriodAmount2 (See AQ)
AU: AnnualAmount2 (See AR)
AV: PayElement3 (See AP)
AW: PeriodAmount3 (See AQ)
AX: AnnualAmount3 (See AR)
Deductions
AY: Deduction1
- Will create a permanent deduction against the worker for this Pay Element. This should match the Pay Element field in the Company -> Pay Elements screen and be of type deduction.
AZ: PeriodAmountD1
- If Deduction1 is populated then either this field or AnnualAmountD1 must also be populated (fields are mutually exclusive). If populated should contain the value that is deducted from the worker each pay period
BA: AnnualAmountD1
- If PayElement1 is populated then either this field or PeriodAmount1 must also be populated (fields are mutually exclusive). If populated should contain the value that is deducted from the worker annually.
BB: LoanBalance1
- If the deduction is for the purpose of paying back a loan to the employer the balance remaining should be entered here. InPay will cease the deduction when loan balance has been paid
BC: Deduction2 (See AY)
BD: PeriodAmountD2 (See AZ)
BE: AnnualAmountD2. (See BA)
BF: LoanBalance2 (See BB)
Bank Details
BG: Bank Sort code
- 6 numeric digits, less than 6 will be padded with zeros. Must be populated if BH or BI is populated
BH: Bank Account Number
- 8 numeric digits, less than 8 will be padded with zeros. Must be populated if BG or BI is populated
BI: Account Name
- Maximum 50 Characters. Must be populated if BG or BH is populated
BJ: Roll / Building society reference
- if applicable
Pension Scheme
BK: Pension Scheme Name / description to show on Pay advice
- Must match description field on Company -> Pension Schemes screen
BL: Employee Pension Value or Percentage
- Numeric
- Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data
BM: Employee Pension indicator Value or Percentage
- V or P
- Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data
BN: Employer Pension Valve or Percentage
- Numeric
- Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data
BO: Employer Pension indicator Value or Percentage – V/P
- V or P
- Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data
National Insurance Breakdown
BP to BU should only be entered for mid-year migrations. The figures are used as the opening balances for the first period that is calculated by the InPay software. The figures will be entered against the current NI Letter. On the rare occasion the worker has changed NI Letter during the tax year changes should be manually made against the worker in the Employee → YTD NI Balances screen.
BP: To Lower Earnings Limit
BQ: Lower Earnings Limit to Primary Threshold
BR: Primary Threshold to Upper Earnings Limit
BS: Niable Pay this Employment
BT: Employees National Insurance Contributions
BU: Employers National Insurance Contributions
Year to Date Values
BM to BR should only be entered for mid-year migrations. The figures are used as the opening balances for the first period that is calculated by the InPay software.
BV: Taxable Pay this Employment
- Excluding SMP and SPPb
BX: Tax paid this Employment
BY: Student Loan Paid this Employment
BZ: Postgraduate Loan Paid this Employment
CA: SMP Paid This Employment
CB: SPPb Paid This Employment
RTI Compliance
CC: Irregular Payment Indicator
- Y = Paid on irregular basis
- N = Paid every pay period
CD: Hours Worked:
- A = 0-15.99 hours per week
- B = 16 – 23.99 hours per week
- C = 24 – 29.99 hours per week
- D = 30+ hours per week
- E = Other
Miscellaneous
CE: Contracted Hours -
- Weekly contracted hours (will be used in rate and occupational absence calculations if applicable)
CF Pay Type (will pick up company default selected in Company → General screen if not entered)
- Salaried
- Grade
- Hourly
CG: Work Email – This is required for Integrations with InTime and if the worker requires access to the payslip portal
CH: Migrated
- Y/N – For mid-year migrations from another payroll Y would be entered here, the employee will be import as “current” status and not a starter, in which case starter declarations are not required.
CI: Off Payroll Worker
- Y/N - If left blank, the worker's off payroll status will be set to the default off payroll status for the entered payroll.
CJ: RTI Employee Number
- The unique employee reference which is included in submissions to the HMRC.
Employee Spinal Point (Academies)
CK1: Grade1
- Must match a Grade showing through the Company -> Grade Screen.
CL1: Point1
- Must match a Point ID showing through the Company -> Grade Screen for the Grade entered in BW1
CM1: FTE1
- 0 -100
CN1: Full Time Hours1
- Weekly Hours
CO1: Working Hours1
- Weekly Hours
CP1: Working Weeks1
CQ1: FTE Working Weeks1
CR1: Academy Pay Element1
- This should match the Pay Element field in the Company -> Pay Elements screen, and the element must be of type payment.
CS2: Grade2 (See CK1)
CT2: Point2 (See CL1)
CU2: FTE2 (See CM1)
CV2: Full Time Hours2 (See CN1)
CW2: Working Hours2 (See CO1)
CX2: Working Weeks2 (See CP1)
CY2: FTE Working Weeks2 (See CQ1)
CZ2: Academy Pay Element2 (See CR1)
Miscellaneous 2
DA: OSP Scheme
- The name of the occupational sickness scheme the worker is to be added to, as specified by the scheme name on the Company → OSP page
DB: Schedule Override
- Override the worker's schedule must be whole weeks Sunday - Saturday (example 1 week on 1 week off NYYYYYNNNNNNNN)
DC: Schedule Start Date
- If the schedule is for multiple weeks it is possible that a date other than the employee's date of joining may be entered