Once you have matched all participants' statuses, you will need to match all other numeric columns. Matching text fields and names can be done simply by spot checking a few people to make sure that the columns were imported in the correct order.

• Basic Information: to compare columns such as DOB and hire date, you should use a formula similar to what was used for matching status where a lookup is used against the table, tableSnap, based upon the SSN column (IF(D2<>VLOOKUP(A2,tableSnap,2), 1, 0)) where D2 contains imported DOB for example) to check for matching values. You should then copy this formula down for each row of imported data and confirm that all cells equal zero.
• Retiree/Terminated Vested Benefit Information: if values do not match when comparing benefit amounts, check that you are comparing annuals amounts to annual amounts and not to monthly amounts. If for example your import file contains monthly amounts (that you then multiplied by 12 in your import), make sure that you adjust for this in your comparison formula. Also, make sure that the imported amounts do not contain more than two decimals. If they do, you will need to truncate to two decimals in the import column prior to doing the comparison.
• Earnings: if your plan includes an earnings component, earnings extracted for valuation are based upon the parameters defined for the Average Earnings Definition that has been designated to be used for valuation extract. If amounts are different for all of the plan's participants, there is most likely an incorrect parameter specified such as an invalid Averaging Unit Determination Calendar Anniversary. Please note that when comparing earnings amounts, it is best to only compare for active participants.

  When extracting earnings, DB Precision will force earnings amounts into employment periods. If an earnings amount is reported before hire date, that amount will be assumed to have been fully earned on hire date. Similarly, if an amount is reported after termination date, that amount will be assumed to have been earned on termination date.

  If earnings amounts for individual participants are not matching, such differences are most likely caused by inconsistencies between employment dates and these earnings amounts. If your plan has such inconsistencies, you should create a new note which lists such individuals along with all amounts that are reported outside of these employment periods. Then delete the reported amounts from both DB Precision and the conversion file. Do this until all earnings differences have been resolved.

  After the conversion is completed, the client should be notified of these differences and new employment dates and earnings can then be added back in to DB Precision.

• Service Start Dates: when service such as vesting service, credited service, and participation begin on hire date, there is nothing to check for service start dates. However, if service begins on some other date (age 21 and 1 year of service for example) you will need to code Service Eligibility (including the rounding of eligibility dates) and then compare.

  When eligibility is based upon service that is elapsed time, you should always be able to match dates reported to the eligibility dates calculated by DB Precision. However, when service eligibility references service that is based upon hours history, you will need to use override dates for any periods prior to the earliest date that hours history was reported. To override eligibility dates, you will need to do the following:

  • Code Service Eligibility in DB Precision: code each Service Definition's eligibility based upon the age and service rules specified in the plan.
  • Create a Compare Column in the Conversion File: create a comparison column that compares service date reported to the date calculated by DB Precision. When doing the comparison however, make sure to return zero if the date reported is blank. For example, if comparing dates of participation where participation date reported is in column M of your import, your formula would be as follows: IF(M2="", 0, IF(M2=VLOOKUP(A2,tableSnap,108), 0, 1)).
  • Filter On Compare Column: filter the import data such that only rows where the compare column equals 1 are displayed. (This step identifies all of the people where there was not enough information to calculate eligibility).
  • Copy the SSNs and Participation Dates: select and then copy the SSNs and reported service start dates for the filtered range and then paste these two columns into a new Excel file (your new file should only have two columns).
  • Save the File as a .csv: save the new file as a .csv file.
  • Create Import Definition: in DB Precision, open the Plan Sponsor, right-click Import Definitions, and select Add from the resulting menu. Give the new Import Definition a name such as ImportDOP. Then double-click Single Row. Add a new Row Item by pressing on the Add button. Then change the Item Type for the newly created item to Historical Dates. Double-click the item and pick either Credited Service Override, Vesting Service Override, or Participation Service Override depending upon which of these you need to fix. Press OK and save the Import Definition.
  • Import the Dates: right-click the Import Definition and select Import from the resulting menu. Find the .csv file created above and then press OK to process the import. On the Import Results screen, you should see new Historical Dates but no new people or other changes. Save the results to the database.
  • Code Service to Use the Override Date: swap to the Plan Document and then go the Service Eligibility screen (you need at least one Eligibility Provision on the Service Definition screen). Under Eligibility Override, check the override box and then select the appropriate date classification as chosen when setting up the Import Definition (Credited Service Override, Vesting Service Override, or Participation Service Override).
• Service: if the plan's service definitions are all elapsed time, to get the correct service to calculate in DB Precision, you would select the calculation method and rounding type on the Service Provision screen until you matched service for all participants.

  However, if service is instead determined based upon a Reported Amount such as hours, you will need to use a service override for participants for whom you do not have a full history.

  For example, if a sponsor has provided hours history back to 1/1/2000, any person hired prior to that date would not have the correct service calculated in DB Precision because there would be no hours prior to that date to use when calculating service accruals. Assuming that you are unable to get a full hours history, to handle service for these plans, you should do the following:

  • Code Service in DB Precision: go into each Service Provision screen and define how each Reported Amount should be used towards service accrual.
  • Create a Compare Column in the Conversion File: create a comparison column that compares service amounts reported to the service amount calculated by DB Precision.
  • Filter On Compare Column: filter the import data such that only rows where the compare column equals 1 are displayed. (This step identifies all of the people where there was not enough information to calculate service).
  • Copy the SSNs and Service Amounts: select and then copy the SSNs and reported service amounts for the filtered range and then paste these two columns into a new Excel file (your new file should only have two columns).
  • Save the File as a .csv: save the new file as a .csv file.
  • Create A New Reported Amount: in DB Precision, open the Plan Sponsor, right-click Reported Amounts, and select Add from the resulting menu. Give the new Import Definition a name such as serviceCreditedOverride. Then double-click the newly created amount. The Amount Type should be set to Service Reported and the Reporting Frequency should be set to Non-recurring.
  • Create Import Definition: in DB Precision, open the Plan Sponsor, right-click Import Definitions, and select Add from the resulting menu. Give the new Import Definition a name such as ImportSvcOverride. Then double-click Single Row. Add a new Row Item by pressing on the Add button. Then change the Item Type for the newly created item to Reported Amounts and select the newly created reported amount at the bottom of the list to the right. Change both the effective date and stop date to be equal to the day before the conversion date. Press OK and save the Import Definition.
  • Import the Amounts: right-click the Import Definition and select Import from the resulting menu. Find the .csv file created above and then press OK to process the import. On the Import Results screen, you should see new Reported Amounts but no new people or other changes. Save the results to the database.
  • Code Service to Use the Override Amount: swap to the Plan Document and then go the Service Provision screen. Check the box Override accumulated service with and then select the Reported Amount that you just created/imported.