If you are setting up a new plan the first step on this page is to select the census grid for the participant data that you will be uploading. Click on the “Change” link to select, edit or change the Current Census Grid. From the drop-down box highlight the grid you want and click the “Set Census Grid” button. You will see that each of the grids available has a unique code - a combination of letters and numbers - that the software uses to identify it. You will see this code when you download the grid as described below.
We’ve created several sample “primary census grids” that we suggest you use to upload your census data. Appendix I displays a chart to help you choose the grid that is right for your plan.
In addition to the individual characteristics shown in Appendix I, all of the sample grids contain the following recommended fields:
- Last Name
- First Name
- Social Security Number
- Date of Birth
- Date of Hire 1
- Date of Termination 1
- Termination Reason 1
- Date of Hire 2
- Date of Termination 2
- Termination Reason 2
- Hours
- Statutory Compensation
- Elective Deferral Contribution
- Roth Contribution
- Match Contribution
- Safe Harbor Non-Elective Contribution
- Non-Elective Contribution
- Officer Status
- Ownership Percentage
- Family Group (for ownership attribution)
- Family Group Relationship
- New Comparability Group
If additional columns are needed or you would like to delete columns that you will not be using, it is a best practice that you copy the primary census grid that most closely matches your needs, rename that grid then make the edits.
To view what fields are available in a particular grid or to custom-design your own census grid, see the "Edit Data Entry Grids" options below. Note that only grids that contain either the SSN (Social Security Number) or Employee Number field (or both) will be available as an option to select as a data entry grid. Users can elect to use either field as the key for uploading census data via the Plan Specifications Menu, General Features/Accounts and Sources, in the General Plan Features box.
To edit a grid, click on the “Change” link next to current Census Grid or from the Compliance Menu, hover over Census and select the Create/Edit Grids option. This will open the Compliance Grids pop-up box. From here, select the grid you wish to edit (or copy, then edit) from the drop-down box, then press either the “Edit” or “Copy” button, as applicable. Users can also create their own grids from scratch by pressing the “Add New” button or delete existing grids by pressing the “Delete” button.
When the user selects the “Edit”, “Copy” or “Add New” option, the selected grid (or a blank grid if adding new) is opened.
To change an existing field, use the drop-down box under the “Field” column to select a different field, then modify the heading and help text as applicable. Be sure to press the “Update” button to save changes.
To delete a column, select “None” in the “Field” column and click “Update”.
To add a column, enter a new sequence number designed to place the new column where you want it located. e.g. if you want the new column between items with sequence numbers 180 and 190, you would give it a sequence number from 181 to 189; when you click the “Update” button the numbers will be resequenced to 10 units apart. Select the column type from the Field drop-down box; the Heading and HelpText boxes will auto-populate. If you add a new field and leave the Seq ("sequence") information blank, the system will default the new field to have a Seq of 100 and place the new field at the top of the grid. Note that although the Heading and HelpText boxes do auto-populate they are editable and you can enter your own descriptions. These descriptions will be used on the spreadsheet that you download.
Please note that if you modify an existing grid or create a new grid, the first two lines (sequence numbers 100 and 110) must be last name and first name. These equate to the first two columns on the actual grid. Also, when you modify a grid it will have a different unique identifier that is automatically assigned.
The system can custom-map fields and do simple calculations on fields. To get to the mapping information for a field, select the census grid that will be used for the plan from the "Data Entry Grids" page. Once you have the census grid selected, you should be at the "Edit Data Entry Grid" page and will see a button to "Update and Edit Mapping". Once you select the button to "Update and Edit Mapping", you will be taken to the "Update and Edit Mapping" page. Here, you will see most fields available in the system (under "system Field") and you will be shown any default field mapping if any. For example, as a default most compensation fields are mapped from the field "Compensation_Statutory".
You may only map fields on the selected census grid. The census data scrub does the mapping as specified in the selected census grid. No other mapping is ever done. A grid used in the Other Import/Export/Report Menu does NOT map.
When mapping on the “update and edit mapping” screen, the only data fields available for mapping in the “grid field” drop-down are those fields that are listed on the “edit data entry grid” screen. For example in the screen shot shown, the “Service_EligibilityHours” is a column that’s on the census grid. The drop-down boxes below indicate other fields that are being mapped from it, i.e. “Service_EligibilityHoursInitial”, “Service_VestingHours” and “Service_ParticipationHours” are all fields that are being mapped from “Service_EligibilityHours”. To remove this mapping, select “None” from the Grid Fields drop-down box; to customize the mapping, select “Custom Calculation” from the Grid Fields drop-down box and enter the field names that you want to map in the Custom map column. The field names need to be separated by “+” or “-”.
Custom calculations can be set up on the “update and edit mapping” screen by selecting “custom calculation” from the grid field column drop-down list and then copying data elements (from the system field column) to the custom map column and separating the fields with a “+” or “-” sign. For example, if you collect "Census_UDFNumber1" on the census grid and it represents a form of "other pay" that is excluded for the purposes of Nonelective contributions, a custom calculation for "Compensation_NonElective" could be done where, on the "Update and Edit Mapping" screen, the "Grid Field" selected would be "Custom Calculation" and "Compensation_Statutory-Census_UDFNumber1" would be entered in the custom map column. When the census scrub is run, the data collected in the grid will be used to calculate the Nonelective Compensation.
Having selected the format for uploading/viewing your participant census data, you are ready to load your data.
Edit Census
Data can be edited directly within the census grid - to update a field, simply double-click on it and enter the new information. The system automatically saves your edits. To add a participant, enter the SSN or Employee ID number in the box above the grid and click on "Add New". You will then be able to enter data in each of the fields for the new participant. Generally however you will want to upload data into the grid. The system will try to ensure the formatting is within certain limits for certain fields. If you enter data with incorrect formatting you may see the error "Not Valid" and the field will not be updated. If you enter a date as "1/1/01", the system will update the date to the correct formatting ("1/1/2001").
Download Census
Pressing the “Download” link from within the census or from the Compliance Menu under the Census heading will bring up a pop-up box with three download options.
Current Census
In general, before uploading a census, you will need to download the blank census so that your spreadsheet will have the correct formatting. The field names must match those on our system and be in the same order. The ID number of the selected census grid also must match the number in the first column of third row of the spreadsheet (cell A3) that will appear in the downloaded spreadsheet file. This means that if you add, remove, or move a column from the selected data entry grid you generally must download a new Excel file for your census. In addition, in order for the file to be re-uploaded by the system, it must be saved in .csv format.
Another use for downloading census would be after census scrub or after other changes have been entered into the system via a portal or the "Edit Census" link. Items in the census may be updated by the system (during a data scrub - you will be shown errors if a census item is updated) or by the user. You can download the latest version of the census by clicking on "Download Census".
Note that if changes were made to the census on the system you can still get a copy of the census that was last uploaded to the system (would not include any subsequent changes made by the system or the user) by clicking on the "Print Reports" link across from the "Do Census Data Scrub" link on the Compliance Menu page. This will open a "Report Menu" screen where the last option "Download Most Recent Census" will generate a copy of the census that was last uploaded to the system.
NOTE: even if you plan to enter data on the excel sheet and upload data back to the software, you may want to consider clicking Edit Census and on the Data Entry Grid page that appears add a participant’s SSN. This will give you a drop down lists showing permissible entries if applicable as well as access to the help buttons for each column.
Download Prior Census
Clicking on the “Prior” link will generate a copy of the census prior to any changes last created by the "Do Census Data Scrub" link.
Last File Uploaded
The system saves the most recent census uploaded. Even if changes are made to the census on the system (via a data scrub or user entry), you can download a copy of the census as it was last uploaded.
Upload Census
When initially adding census data to the software it is generally easier to upload it. It is a best practice to download the grid to Microsoft Excel format, enter your data then upload the spreadsheet.
Prior to uploading the census, it is highly recommended that the data you receive from your client is reviewed for completeness and accuracy. For example, if the census has both statutory compensation and statutory from plan entry compensation, both fields must be completed for all participants.
To upload the census click the “Upload Census” link, select the file to upload, enter the total number of rows in the census file (if the last row ends on row 50, enter 50), then press “Submit”. Note: Users may enter more rows than there are census records, but not less. For example, if the last row ends on row 50, users may enter 100.
Upon uploading the census, the Spreadsheet Upload Results screen will:
- Show the number of rows selected for upload
- Show the total number of records to be added
- List participants with new records
- If no census data has been uploaded for the plan year, all records in the spreadsheet will be listed as new records.
- If the user has already uploaded data for a plan year, and needs to re-upload the data, the user will only see new records listed if additional participants were added to the census after the initial census data upload.
For first census year, indicate whether eligibility dates should be reset
If this is the first plan year on the FT William system, you will have the option to have the system clear out all of the status fields that the system calculated after the census was previously uploaded and the data scrub was run. This is a handy tool to use if invalid data, such as an incorrect termination date or hire date was previously uploaded.
Retain prior year codes
For years subsequent to the first plan year on the system, users have the option to either copy the following status fields from prior year or upload with the census:
- Ownership percentage
- Family code
- Family relationship
- Officer status
- New comparability group number
- Employee class
- Employee class other
- Note: The system defaults to “Yes” to have the prior year data pulled instead of the census data. If “Yes” selected, data for the seven fields from the prior year will overwrite data for those seven fields even if that data is in the current census being uploaded. To have the system use the data in the census, simply set the override to “No”.
Upload file name
Click the browse button to search your computer for the census file you wish to upload. Please note that the census file must have been saved as a .csv file. Also note that the format must match the format in the grid selected from the Work with Data Entry Grids screen.
Number of rows to ignore
The number of rows to ignore defaults to 5 indicating that the participant data starts on Row 6; if you have made any changes to the top of the spreadsheet you need to adjust this number accordingly. Please note that there is a number in the third row that must not be removed or changed. This number tells our system what data will be in each column.
Number of rows containing data
You must also enter the approximate number of rows containing data - you may over-estimate the number of rows but not underestimate. (Once the system reaches the number of rows you entered a blank row or an invalid Social Security/employee number, the system will stop uploading).
Submitting the file and reviewing results
After clicking on "submit", you will be taken to the "Spreadsheet Upload Results" page. If your upload was successful, the system will display the number of records added. If your upload was not successful, you will see an error message. The most common error involves not having the correct census ID number in cell A3 of the spreadsheet (see the section "Download Census" above for more information).
If you click on the "return" link from the "Spreadsheet Upload Results" page, you will be taken back to the main “Compliance Menu” page and be prompted to run the data scrub.
For some fields only a certain number of entries will be valid. These fields appear as drop-down boxes in data entry grids on the system. Upon upload, the system will try to match the text in a particular field to the correct field value. For example: the field "Officer" has two possible values: "Yes" or "No". If you upload a census with "yes" (all lower case) or even simply "y", the system should identify that the value of that field is "Yes". Of course, if there are multiple values that begin with the same letter, you will have to enter more of the beginning letters of the field to have the system recognize the correct value.
You can effectively start over for a plan year by uploading a new census. This will clear out all the "R" (regular) field types on the system for the applicable plan year end. This will also clear out any errors that appeared after a census data scrub. Master file data will not be cleared out by uploading a new census unless you are directly uploading master file information (i.e. participant’s address or earliest date of hire).
Other Import/Export/Reports can be accessed from the Compliance Menu by hovering over Census and selecting Other Import/Export/Reports or from within the Census screen, by clicking the Other Import/Export/Report link.
Click here for more information on adding historical data
Most plans will need other information entered into the system beyond the census data, at least in the first plan year. For example, if you are running the Top Heavy test you will need to add information about prior year in-service distributions using a supplemental grid in the Other Import/Export/Reports menu. There are several * ftw grids that we suggest you use to view or edit supplemental data. You can import or directly enter data that does not appear in your census. In general you should first upload a census for the plan year, then enter other information under the Other Import/Export/Reports menu and then do a census data scrub.
Note that "Upload" will not be an option if the selected grid does not have SSN or employee ID information and you will instead see "N/A - no SSN" where "Upload" would normally appear. If you have a data grid that you would like to use for upload and need to add a SSN or employee ID field, you can edit the grid in the Work With Data Entry Grids menu.
If you wish to add additional historical data to your census, a useful grid to add for a first year plan is “Sample Census Supplemental with Disagg No OR” – this grid is generally added from the “UD Grid 1” drop-down box; it allows you to enter supplemental data for prior years of service and in-service distribution amounts for Top Heavy testing. There are two options to upload data into the system (see Mapping for more info on mapping):
- Upload No Map: Data uploaded will not be mapped. The system will only use the data fields uploaded.
- Upload with Map: The system will use the data fields uploaded and will do any mapping as set up under the Mapping options for the census grid selected.
Data Review / Data Export
You can export/download information in any selected grid to an Excel file or simply review the data in a grid online.
For example, after doing a census scrub, the system will determine whether an employee is a key employee. You may wish to view more information about a particular employee to better understand why they were determined to be / not be a key employee than is provided in the standard Census Data Scrub HCE/KEY Status report. All of the fields that are relevant to a key determination are provided in the default "Key Data" grid. By entering the Other Import/Export/Report Menu after a census scrub and selecting the "Key Data" grid, you can see whether an indicator used for the key determination was correctly entered (for example, family status may not have been entered correctly and this information does not appear on the Census Data Scrub HCE/KEY Status report but does appear in the Other Import/Export/Report Menu "Key Data" grid).
User-Defined Reports
You can add a grid to create and print custom reports to a Word document or Excel file. Firstly you would need to set up a grid in the Work with Data Entry Grids menu so that it has the information you want reported in the order you would like it to appear. You can then print the report from the Other Import/Export/Reports Menu.
Several sample grids are supplied with the Compliance software. All grids can be customized under the Work with Data Entry Grids menu. All grids for your company code’s log-in (and division, if applicable) will be available in this menu. Before you can work with a particular grid, you will have to select the grid from the drop-down box and click on "select grids". This allows you to set up variations of the allocation grid, for example, that you can select for different plans.
NOTE: We strongly suggest that you make a copy of any sample grid you wish to change (select the grid, click "Copy This Data Entry Template" and give the new grid a custom name).
Appendix I contains suggestions for supplemental grids that are available for extracting additional data on the Other Import/Exports/Reports menu.
Participant data
This link will open the "Participant Menu" window with the following additional options described in more detail below (each will open a window with a check-list of fields and their corresponding values).
- Master File: Address, entry dates for contributions and overrides, and vesting years of service (YOS).
- General Status: Hire and termination date(s), employee class and status, ownership, key/HCE determinations, in-service distributions for past 4 years
- Eligibility: Years of service for contributions, catch-up eligibility
- Compensation: Shows compensation for contribution types and other purposes
- Contribution: Contribution amounts for all types of contributions, excess elective deferrals
- Other: Actual deferral and contribution rates, ADP/ACP failure data
The Participant Menu also has options to "Print Master File Data" or "Print all Participant Data" to a Word document (printing all Participant data will create a checklist-like Word file with all the participant data for a participant - typically creates a 7-page Word document in a check-list format).
Eligibility / Vesting / Benefit History
These three grids all work in a similar manner. The grid shows the years of service and hours of service earned for each computation period year in the system as well as status change dates. The following example will illustrate how these grids operate:
A sample Eligibility history link is shown below for the 2013 testing year. The first field shown is the "Comp Period year End" which shows the computation period year end for which the status was calculated. This person was hired 1/1/2001 and for the computation period ending 12/31/2013, he completed one year of service.
The next three columns taken together show the status, status date and type of record shown. The field "Type" indicates the type of status. There are two main categories of status types:
- A: General status change with a status of hire or term. "A" status types will not have corresponding hours of service.
- E, V or B: "E" = eligibility; "V" = vesting; and "B" = Benefit. These status types will have corresponding Statuses to indicate what type of contribution they pertain to ("ED" - elective deferrals, "MT" - Matching, "NE" - nonelective, or "ALL" - all contribution types).
The hours of service is displayed, typically taken from the census grid, and the service earned will indicate the years of service earned for the computation period.
