KB14-014
ACA Compliance Solution – Hosted/Cloud import information
Excel Templates:
- Excel Employee MasterTemplate 07-28-20
- Excel Transactions Template 7-28-2020
- Excel Dependents Template 7-28-2020
The system requires employee and transaction data to be imported. If your organization is self-insured, then you will need to import the dependent information as well. If your organization is fully insured, the dependent information is not required. Click Here
To download sample files, log onto the ACA cloud solution and go to Utilities & Maintenance>Upload & Import. Under this page there are options to download each type of upload file. These files are text files that can be opened in Microsoft Excel.
Open Microsoft Excel and click “Open” and then go to downloads.
Select “All Files” and click on the template file.
The next step is to format the sample files. When you click on open excel will prompt you to format the text file. When this comes up, simply click “Finish.”
Once this is opened the correct data can be added in. After the data is in the spreadsheet, it is wise to Save the file as Excel and then you will need to Save As Text (Tab delimited). Always open your Text file in Notepad before uploading. Remove extra lines by using Ctrl/End on the keyboard and then backspacing until the cursor is next to the last character. Also use the Edit button next to File and go down to Replace. Then enter ” and Remove All. These operations will prevent an error message from occurring.
Employee import: this file includes the employee data required to print the 1095-C and run the reports. It is important you format the fields and enter the data exactly as required. This import is a add/edit type of import where a record will be added if the employee information is not present and if it is present, that record will be modified based on the most current information being imported.
It is important to include full time employees as well as part time employees in the employee and transaction files. The Standard Measurement Period reports tests all employees and the law requires you to track their hours even if you offer them coverage. The Initial Measurement report only tests part time employees.
For the Employee Type field, the system requires entering Fulltime, Parttime. It will not import if FT or PT is entered. The system uses these fields to determine which employees to run for the reports.
The Department is not required for ACA purposes but it is used in the system to help you find/sort employees. We recommend importing information that is meaningful for your organization in this field.
The Adjusted Hire Date is important for rehired employees. You can enter their most recent rehire date in this field. This field cannot be empty. If an employee does not have an adjusted hire date, then enter the hire date in this field.
Termination Date – You will want to include employees that worked in the filing year. If an employee has been terminated, you will need to enter the termination date. If they have not been terminated, enter 01/01/1900. The system will not import if this field is empty.
Company ID – Because employees need to be sorted by company, put the Company ID in this column. This ID must match the Company ID in Configuration on the ID ACA solution.
Transaction import: This file includes all the transactions required to calculate the ALE and run eligibility reports based on the hours worked to determine whom is offered coverage. This information is needed in order to provide reporting on eligibility through either the standard or initial measurement periods. Depending on the method used for determining eligibility, the information imported will be used to fill in the monthly reporting codes for the 1095-C. All data must be imported either by pay period or at the most by the month.
It is important to enter all the transactional data for each Record type. There are three key record types. Pay Codes, Benefits and Health Deductions. The system uses the pay codes to track hours and calculate eligible employees. The system uses both Deductions and/or Benefits to print the 1095-C forms. Benefits are only needed if the company pays 100 percent of the health coverage or for a W-2 Safe Harbor.
Record Type Details:
Pay codes
We only want to import pay codes for transactions that represent hours worked. These hours will be included in the calculation of reported hours worked. In the spreadsheet a pay code should entered as Record Type 1.
Employee hours for the purpose of the ACA are defined as follows: (1) each hour for which an employee is paid, or entitled to payment, for the performance of duties for the employer; and (2) each hour from which an employee is paid, or entitled to payment by the employer on account of a period of time during which no duties are performed due to vacation, holiday, illness, incapacity (including disability), layoff, jury duty, military duty or leave of absence. Not all Pay code IDs are included. Commissions, bonuses and profit sharing are not pay codes that are used for tracking hours; they are usually entered as dollars. Keep in mind, if you use the W-2 Safe Harbor you do need to import all taxable compensation including commissions, bonuses, tips, and profit sharing.
Avoid double-counting hours. An example is an employer that pays an hourly base rate and a shift premium. If these are separated into two pay codes, you only want to include one, usually the base hour pay code. Including both could double-count the hours. Remember, if you upload the same data more than once it will duplicate hours and skew the reports.
The units to pay should have an amount entered if they were actual hours worked. The law requires tracking the hours worked. Salaried employees need to be assigned Full Time hours per week (usually 40 hours) if your pay role system does not pull hours for this classification.
Benefits
Health insurance benefits will need to be imported into the system depending on how you track your health insurance premiums. You do not need to include other benefits (e.g. Life, Dental, and Retirement) for ACA Reporting. If you use the Federal Poverty Line or Rate of Pay safe harbor, then you can enter either benefits or deductions to determine if an employee had coverage for a certain month. If you use the W-2 Safe Harbor, benefits and the amount are required for this safe harbor. Benefits can be imported as monthly transactions. You can now import a single benefit spanning the entire year or part of a year by going to the company setup in Configuration and doing as below. An example would be to place on the excel spreadsheet a period start date as 2/1/2021 and the period end date as 8/31/2021 or if covered for the entire year enter 1/1/2021 to 12/31/2021.
If you currently are not tracking health insurance with a benefit code in your payroll system and only have a deduction, then you can import only the deduction transactions even if you use rate of pay or W-2 Safe Harbor.
The units to pay will be 0 for Benefits.
In the spreadsheet, a benefit transaction should be entered as Record Type 3.
Deductions
For all the Safe Harbors, you will need to import only your health insurance deduction and remove other non-health insurance codes.
The Units to Pay and Rate Amount will be 0 for Deductions.
The fields for check number and check date are for information only and are designed to be beneficial for auditing and tracking hours. They can be empty or filled.
The pay period start and end dates are important for running the hours calculations. The system allows you set setup calculations based on a monthly start date and end date or spread over the pay period. You can now import a single benefit spanning the entire year or part of a year by going to the company setup in Configuration and doing as shown in Benefits above. An example would be to place on the excel spreadsheet a single row entry for an employee with period start date as 2/1/2021 and the period end date as 8/31/2021 or if covered for the entire year enter 1/1/2021 to 12/31/2021.
In the spreadsheet, a deduction transaction should be entered as Record Type 2.
Dependents import: this file includes the dependent information required for the system to fill out Part III of the 1095-C correctly. You only need to import dependent information if you are self-insured. Your provider/carrier should be able to provide you the dependent information if you have not been tracking it.
The Spouse field cannot be empty, if the record is for a spouse then enter a 1. If the record is not a spouse, enter a 0.
You can enter the start date and end date as the date they started and ended coverage.
Here are some editing tips:
- If you need to delete a row, right click and select delete to prevent any extra rows appearing as null values in the file. If you use the Delete button on the keyboard, then sometimes Excel keeps the row when the file is saved as a Tab Delimited Text file.
- Once the data is entered in Excel save the file as a Tab Delimited Text file. Note: once it is a tab delimited text file the headings and columns may be off. This is ok.
- Employees in the transaction file must also be in the employee file.
- If you try to import transactions for employees who are not in the Employee Import or not setup as an Employee in the ACA Compliance Solution then the system will reject those transactions and not import the transactions for that employee. However, all the other transactions do go into the cloud solution for those employees previously imported into the cloud.
- As of 2019, the excel headers are not case sensitive but the first letter must be capitalized and the spelling and no spacing correct. Example: Address1. Also, the order of the headers is no longer critical.
Questions / Resources
If you have questions or comments please email Support@integrity-data.com.
If you would like to search for information and resources for Integrity Data products check out the following sites:
- Videos: https://www.integrity-data.com/video-library/
- Knowledge Base: https://www.integrity-data.com/knowledge-base/
Last Review: 8/10/2022 – Revision: 4.2
Applies To: ACA Compliance Solution
Categories: Import of Data, Monthly Actions
Keywords: Import, Templates