When you receive information from the sponsor for actuarial valuation, it is generally in the form of a returned data request, payroll feed, and/or a payment register from the plan's benefit payment provider.

Regardless of the form that you receive client data however, the process for importing this data into DB Precision is the same. For each different type of layout that you receive data, you should create an Import Definition and use that to import the data.

When importing data, consider the following:

• Repeatedly Receiving Data in the Same Format: if you repeatedly receive data in the same format from a client, you should re-use the same Import Definition already created to import the data last year. When doing this however, you should check to see if hard-coded reported amount effective and stop dates need to be incremented by one year.
• Folders: if you have many different Import Definitions, consider grouping them using folders, possibly with a separate folder for each year.

When you receive data from the client, you should do as little manipulation to the files received as possible. Each time you make a change to a file, you increase the risk of a mistake. When importing data, it is important to be aware of the following:

General File Issues

• Original Files: preserve the original files received from the client on your network without manipulation.
• File Headers: if the file received contains headers, reduce the header to one row and remove any spacing between the header and the actual participant data.
• Invalid Cell Values: if your file contains invalid values such as #NA or #VALUE!, these should be corrected prior to import as DB Precision will check for these values and stop the import if encountered.
• Extra Rows at Bottom of File: highlight several rows at the bottom of the file after the data and delete these rows to remove any invalid characters or spacing that is sometimes found in data files.
• Comment Columns: if your file includes a comment column, each comment should be reviewed and the information contained should be either set up as importable data or entered manually into DB Precision. By simply importing such information, key items usable in calculations might not be properly reflected. In addition, you may overwrite an existing comment upon import.
• Columns Not Being Imported: if your file contains columns that are not being imported, do not delete these columns but rather leave these in the file for documentation purposes.
• Status Not Imported: remember that status is not imported but rather is determined based upon rules. For a list of those rules, click here.
• Status Change Column: if your file includes a column for status changes, you may need to move the data in this column to other columns. For example, a status column might have dates of death, termination date, payment start date, and hire date all in one column. You may then need to move these data items into other columns if there is not also some other column that includes status code for this column.
• Importing One Person in Multiple Imports: if a person is listed on multiple import files, it is important to be aware that the last information imported will govern. This is only important however if the data is inconsistent across each file.
• Re-save Files as .csv Files: when you have completed any initial work on the file being imported, save the file as comma delimited where each worksheet and file received is its own csv file.



SSN

• Invalid SSN: check the file for invalid SSNs (000000001 for example) and try to obtain the true SSN or replace using the standard method for developing fake SSNs.
• SSN Changes: if your file includes changes to SSN for people, the new SSN should be in the file with all other SSNs while the prior SSN should be in a separate column. This column for prior SSN can then be specified in the Import Definition (Prior Social Security Number column).



Reported Amounts

• Estimated Hours for Full Time Employees: if the plan sponsor does not report actual hours but rather indicates full time and the plan uses hours for service accrual, import hours amounts as 2,080 rather than 1,000. Not doing so could result in understated projected service accruals.
• Hours and Earnings: for Reported Amounts being imported that are either for earnings or hours, go into each of these columns in the Import Definition and check the box Treat zero as blank (ignore).

  Alternatively, you can modify the file to remove all zeros by highlighting each of these columns and selecting Find & Select in Excel. Choose Replace. Then enter 0 for Find what: and, press the Options button, and check the box Match entire cell contents. Then press the button Replace All. This replace all cells that contain a zero.

  Importing zeros for either earnings or hours can cause problems with benefit projections and is therefore discouraged.




Benefit Payments

• Benefit Changes: when importing benefits, DB Precision will look at payment start date and if there is none, benefit amount to try to identify if the amount being imported is a new amount or simply overlaying an existing benefit amount.

  Therefore, if importing benefits for a COLA, it important that your file include payment start date so that DB Precision can identify and then modify the existing benefit rather than add a new benefit. Do not specify the payment start date to be the date of the COLA, this is specified separately upon import.

• Retirement Date Versus Termination Date: sometimes a plan sponsor may refer to termination date as being a person's retirement date rather than the date that payments commenced. In DB Precision however, termination date and payment start date are two separate events. You should scan the data to make sure that termination dates and payment start dates are in separate columns and are not equal.
• Benefit Information: when importing benefit information, you need to make sure that your file includes benefit type, a deferred indicator (for all terminated vested participants), and plan number if there are multiple plans. If you do not include this information, DB Precision may not have enough information to properly determine status.

  It is also important to make sure that you only import one benefit per person per import. If a person is being paid more than one benefit, each benefit needs to be imported with a separate import.

• Monthly Benefit Amounts and Lump Sums: if the plan sponsor reports benefit amounts as monthly amounts, you can specify in your Import Definition that the benefit column should be multiplied by 12. However, if that same column includes lump sum amounts, you cannot do this as you would then overstate the lump sum amounts in DB Precision. If lump sums are in the file, you should instead multiply the monthly amounts by 12 in your import file.
• Deceased Participant with Surviving Beneficiary: if a person dies and has a surviving beneficiary, import their date of death and add a new row for the beneficiary (including the SSN for the new beneficiary). Do not overwrite the deceased participant's information with the beneficiary's information and overlay that information onto the deceased participant's SSN.

To import data, open the Plan Sponsor, right-click the Import Definition name, and select Import from the resulting menu. Press the Find button to select the full path of the .csv file created above. Then press the OK button. For more detailed information on the items found on this screen, click here.

After you import your data, an import results screen will be displayed. At this point, the import results are stored in your machines memory and are not yet stored in the database. It is not until after you check the box Store Results to Database and press OK that the results will be stored. Therefore, you should first review the results. If you close the Import Results screen without saving, nothing will be saved to the database.

Here are some things to watch out for:

• Skipped Rows: the rows indicated as being skipped should only include header rows. If you have other skipped rows, this most likely occurred because DB Precision did not have enough information to add a person. This occurs when a person has no employment dates or benefit information. If you have rows skipped for this reason, you may need to go back to the plan sponsor, ask for either a hire date or benefit payment information, and then re-import.
• New People: check this listing to see if the number of new participants seems reasonable (were you expecting any new people?).

  Also check to see if anybody has a warning icon. If they do, DB Precision thinks that the person being imported might already be in the database but with a slightly different SSN (in which case you have an SSN change rather than a new person). If you double-click the name, you will see a message of who DB Precision thinks this person is similar to. Confirm that the new person is not already in the database. If you do not fix this, this person will be double-counted in the database.

  To fix any SSN changes, put the new SSN in the main SSN column and add a column with the prior SSN. Then modify the Import Definition to include this prior SSN column.

• New Items: check the amounts, dates, and benefits shown on this listing to make sure that the amounts seem reasonable and also that the effective dates match the dates of the amounts you were expecting to import and are not off by a year.
• People in Other Sponsors (crossovers): for people who are also in other sponsors, check for changes to basic items such as name or date of birth. If there is a difference between data items reported by two sponsors, you may need to coordinate with the actuary or administrator to determine which information provided is correct before saving the import to the database.
• Changes Tab: check this tab to see if the changes seem reasonable. If you see effective dates for reported amount changing or changes in first name or gender, this is usually an indication of a problem.