sage 100 erp aca reporting worksheet

Layout Overview

General Information

Part II

Part III

Instructions • We have spoken to many customers with 1,000+ employees, who have requested the ability to get started on entering the

employee information needed for ACA reporting. This worksheet is intended for that purpose.

• For the vast majority of customers, we recommend waiting for the release of the product update with the new ACA enhancements for the Payroll module. The functionality provided in these enhancements will streamline data entry.

• It is not necessary to use this spreadsheet, but it can be useful to help compile employee information required for the 1095-C form. You will be able to import the information after installing the product update. See knowledgebase article 48622 for more details.

Reminder: In order to import data into Sage 100 ERP the Visual Integrator module is required.

As you look at the spreadsheet, keep in mind that there are three different sections to the form, and three different row formats that you can enter. For each row, you must enter a “record indicator” in column C that reflects the type of information in the row. The three types are:

• 1095-1 (General information that mostly correlates with Part I of the form) • 1095-2 (Information that goes into Part II of the form) • 1095-3 (Information that goes into Part III of the form)

The image below provides an overview of the spreadsheet layout. Beneath that are more detailed views of the form and the corresponding sections of the spreadsheet. Please note that the form images are from a draft version of the form. The final version was not available at the time this document was created.

Additional instructions appear in the spreadsheet. On the “Sample with Instructions” tab, you can hover over a cell with a comment for additional information.

Spreadsheet Record Definitions: Spreadsheet and cell comments originally created based on the 2014 Instructions for Forms 1094-C and 1095-C (Draft as of 08/28/14)

Record for 1095-I info Record No Indicator = 1095-1 Year Dept No Employee No

1095-C Electronic Consent Signed

(Y/N)

Provided Self Coverage (Y/N)

Record for 1095-II info for monthly detail Record No Indicator = 1095-2 Year Dept No Employee No Month

(01-12)Offer of Coverage

(1A - 1I)Employee Share of

Lowest Cost

Applicable Section 4980H Safe Harbor

(2A - 2I)

Record for 1095-III info for covered individuals Record No Indicator = 1095-3 Year Dept No Employee No Entry No

(00001-99999) Covered Individual SSN Date of Birth

Covered All

Months (Y/N)

Covered Jan (Y/

N)

Covered Feb (Y/

N)

Covered Mar (Y/

N)

Covered Apr (Y/

N)

Covered May (Y/

N)

Covered Jun (Y/

N)

Covered Jul

(Y/N)

Covered Aug (Y/

N)

Covered Sep (Y/

N)

Covered Oct (Y/

N)

Covered Nov (Y/

N)

Covered Dec (Y/

N)

****This is the sample spreadsheet below. DO NOT delete or move any of the existing columns. You can add columns to the end if needed, but they will not be imported into Sage 100 ERP. ****Sample Spreadsheet:

Record for 1095-I info 1095-1 2015 11 0000001 Y Y

Record for 1095-II info for monthly detail 1095-2 2015 11 0000001 01 1A $ 60.00 2C






Record for 1095-II info for monthly detail 1095-2 2015 11 0000001 07 1B $ 150.00 2C






Record for 1095-III info for covered individuals 1095-3 2015 11 0000001 00001 Employee Smith 123-45-6789 1/1/80 Y Y Y Y Y Y Y Y Y Y Y Y Y

Record for 1095-III info for covered individuals 1095-3 2015 11 0000001 00002 Spouse Smith 234-56-7890 1/1/85 Y Y Y Y Y Y Y Y Y Y Y Y Y

Record for 1095-III info for covered individuals 1095-3 2015 11 0000001 00003 Newborn Smith 3/1/15 N N N Y Y Y Y Y Y Y Y Y Y

Record for 1095-I info 1095-1 2015 11 0000002 Y N

Record for 1095-II info for monthly detail 1095-2 2015 11 0000002 01 1C $ 50.00 2A








Record for 1095-II info for monthly detail 1095-2 2015 11 0000002 09 1D $ 60.00 2A




Record for 1095-III info for covered individuals 1095-3

Record for 1095-III info for covered individuals 1095-3

Spreadsheet Record Definitions:

Record for 1095-I info Record No Indicator = 1095-1 Year Dept No Employee No

1095-C Electronic Consent Signed

(Y/N)

Provided Self Coverage (Y/N)

Record for 1095-II info for monthly detail Record No Indicator = 1095-2 Year Dept No Employee No Month

(01-12)Offer of Coverage

(1A - 1I)Employee Share of

Lowest Cost

Applicable Section 4980H Safe Harbor

(2A - 2I)

Record for 1095-III info for covered individuals Record No Indicator = 1095-3 Year Dept No Employee No Entry No

(00001-99999) Covered Individual SSN Date of Birth

Covered All

Months (Y/N)

Covered Jan (Y/

N)

Covered Feb (Y/

N)

Covered Mar (Y/

N)

Covered Apr (Y/

N)

Covered May (Y/

N)

Covered Jun (Y/

N)

Covered Jul

(Y/N)

Covered Aug (Y/

N)

Covered Sep (Y/

N)

Covered Oct (Y/

N)

Covered Nov (Y/

N)

Covered Dec (Y/

N)

****This is the sample spreadsheet below. DO NOT delete or move any of the existing columns. You can add columns to the end if needed, but they will be ignored by Sage's import. ****Sample Spreadsheet:

Record for 1095-I info 1095-1 2015

Record for 1095-II info for monthly detail 1095-2 2015 01












Record for 1095-III info for covered individuals 1095-3 2015 00001


Record for 1095-I info 1095-1 2015















sage 100 erp aca reporting worksheet

Technology