KB14-014

ACA Compliance Solution – Hosted/Cloud import information

Excel Templates:

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.

 

ap0413

Open Microsoft Excel and click “Open” and then go to downloads.

Select “All Files” and click on the template file.

snap0414

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.”

snap0416

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 a ” 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 system does not import if this field is empty.

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.

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.

**If employees need to be sorted by company, put the Company ID in the Department field or User Defined field to add the ability to sort and filter this way.

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 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.

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 cannot import a single benefit spanning the entire year.

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. If an employee had coverage the entire plan year, you will need to enter at least a monthly transaction. Then, enter the start date as the beginning of the plan date and end date as the end of the plan year. If an employee accepted coverage due to a life change or move to Full Time status mid-year, then enter the start date as the date they were covered and the end date as the plan year-end date.

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. If an employee had coverage the entire plan year, you will need to enter at least a monthly transaction. Then, enter the start date as the beginning of the month and end date as the last day of the month. If an employee accepted coverage due to a life change or move to Full Time status mid-year, then enter the start date as the date they started covered and the end date as the last day of the month for every month the employee took insurance..

The fields for check number and check date are for information only and are designed to be beneficial for auditing and tracking hours. They cannot be empty but if you do not have the check number, you could enter a constant number for all transactions. If you do not have a check date, then enter the pay period end date.

The pay period start and end dates are important for running the hours calculations. The system allows you set setup calculations based on start date, end date or spread over the pay period. The law requires the tracking of hours, thus it is important to import the actual pay period start/end dates for the corresponding transactions.
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.
  • Verify the headings in the final import files match exactly letter for letter, spacing and capitalization the sample Excel import file sent from Integrity Data.
  • Verify the headings in the final import files are in the same order as the sample file sent.
  • 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 them.
  • Email Support@inegrity-data.com if you have any questions.

 

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:

Last Review:      8/22/2017 – Revision: 4.0

Applies To:   ACA Compliance Solution

Categories:  Import of Data, Monthly Actions

Keywords:  Import, Templates