It is important that each conversion file that you create follows the PensionSoft standard conversion file structure. By keeping each file similar it will be easy for not only you, but any other user to look at this file and determine the answer to any question that may arise after the conversion is completed.

If it does not already exist, you should first create a folder within your client's working folder and name it "DBP". You should then create a sub-folder for this newly created folder and name it "Conversion". Within this sub-folder, you should then create a new Excel Workbook named "Conversion_yyyy.xlsx" where yyyy is the year of the conversion.

If you were creating a conversion for 2017, you should have something that looks like the following in Windows Explorer:

Each conversion file should then have the following as separate tabs of the conversion workbook:

Use the notes tab to document anything that could not be matched between what was imported into DB Precision and what was extracted. These differences generally are due to inconsistencies in the data being imported. The notes should be written to the person reviewing the conversion (usually the plan's actuary or valuation analyst). Examples of notes include the following:

• Invalid Social Security Numbers: you should document the original invalid SSN as well as the dummy SSN that was used. On the import tab, the SSN should be highlighted in yellow for the person.
• Inconsistencies between Employment Dates and Salary or Hours: hours and salary must be for periods that a person was employed. Differences can result when amounts are reported for periods that a person was not employed (oftentimes prior to one's original employment period reported). On the import tab, these amounts should be deleted, and the cell should be highlighted in yellow.
• Missing Payment Start Date: retiree and beneficiary data imported oftentimes is missing actual payment start dates. These should generally be imported with a payment start date equal to the conversion date. For forms of payment that have a certain period, you should make every effort to obtain the actual payment start date so that DB Precision is then able to properly determine the certain period end date. Any payment start date that has been substituted with the conversion date should be highlighted in yellow.

The import tab should include all of the data being imported as well as data elements that had been previously stored but which will be derived by DB Precision going forward. You should import data for all participant status types using one import (rather than having separate imports for actives, terminated vested, etc.). Setting up this tab generally involves taking the data provided and separating out derived fields and moving them to the right side of this worksheet tab.

• Participants to Import: you should import data for all participants with a liability due from the plan as well as ineligible participants that might someday become a participant. It is not advised to import data for non-vested terminateds, cashouts, or deaths.
• Layout: all data being imported should be on the left side of the worksheet, while all derived elements should be on the right where a column highlighted in yellow should then be used to separate the columns.
• Freeze Panes: you should freeze the top row of the import tab (View - Freeze Panes - Freeze Top Row).
• Formatting and Fonts: data being imported should be copied in without any formatting (using Paste Special when copying data into this sheet).
• Reported Amounts: for any reported amounts being imported, the header should include a comment showing the effective date and stop date for that amount ("01/01/2016 - 12/31/2016" for example).
• Columns with Conversion: for any column that requires a conversion when importing into DB Precision, the column header should include a comment that details the conversion. For example, for the gender column, your comment might read "M: Male. F: Female". This helps document what was done without needing to then go into the Plan Sponsor in DB Precision.
• Status Column: status column should be translated into DB Precision status codes to simplify your compare. For a list of the DB Precision status codes, click here.
• Terminated Vested Benefit Start Date: this should be set equal to the plan's Normal Retirement Date. If this was not already determined, it should be calculated now and included in the import.
• Service Amounts: for elapsed time service amounts, these should not be imported. For service amounts that use reported amounts (hours based formulas), if you have any hours history, you should not initially import service amounts but rather only import for participants where you have an incomplete history.
• Dates of Participation/Credited Service Date: these should only be imported when there is not enough data to otherwise allow DB Precision to calculate.
• Benefit Import: when importing benefit information, you should have the following columns. When importing benefit information, make sure that your conversion file does not contain any benefit information for active participants. Such amounts will cause DB Precision to treat these people as either terminated vested or retired (even if your file only has payment start date). Also, if your file includes NRD for active participants, make sure that you do not treat these dates as payment start dates.
  • Annual Benefit
  • Payment Start Date
  • Payment Form Information: including certain period and J&S fraction if applicable.
  • Benefit Type: retirement, beneficiary, disability, QDRO, etc. This column will likely need to be determined based upon status code reported.
  • Deferred Indicator: this column will need to be created and is used to separate terminated vested participants from participants in pay-status. Note that terminated vested and participants in pay status are very similar except the terminated vested have a payment form equal to the plan's single normal form and have a deferred indicator that is checked.
  • Plan Number: if a sponsor has multiple plans, you will need a column to indicate from which plan a benefit is paid.
• Derived Columns: the derived columns that are preserved on the right side of the import tab but not actually imported will usually include the following:
  • Status Code
  • Accrued Benefit
  • Service Amounts
  • Credited Service Date
  • Participation Date
  • Normal Retirement Date
• Beneficiaries: for participants who are beneficiaries, make sure that the initial import data does not include any employment or termination dates. If such dates exist, they should be deleted from the import data. If a participant has both employment dates and a beneficiary benefit, DB Precision will treat this person as both a beneficiary and an active participant. Any other data stored for the original participant such as earnings Reported Amounts should also be deleted.
• Descriptors in Participant Name: oftentimes, descriptors are included in the data being imported (information regarding former spouse for example). In such situations, the descriptor should be removed from the name and information contained in the descriptor should be reflected in the other data being imported (change benefit type to survivor for example).
• Dual Status Participants: when a participant has two statuses (active and beneficiary for example), oftentimes, the data will include two separate SSNs for that person. For the initial import, this data should be combined into a single row. It is important to note however, that you can only import one benefit within a single import. Therefore, if a participant has two benefits (retiree and survivor for example), you can only import one benefit and will then need to either import the second benefit separately or else manually enter this second benefit after import.
• One Row Per Person: you should only have one row per person as you are importing data. This will simplify the compare and lifecount reconciliation process.
• Extra Rows at End of File: make sure that you do not have blank rows of data at the bottom of your import sheet as this will result errors upon import into DB Precision.

The snapshot tab will include all data extracted out of DB Precision as part of the conversion. This data will come from a snapshot that is run in DB Precision on the conversion date (Valuation - Valuation Snapshots - Add).

When the data from the snapshot is pasted into the conversion tab, it should then be sorted by the SSN column (column A). The first time pasting the data in, this sorted range should be highlighted and given a name, "tableSnap".

Examples of other tabs include the following:

• Statements: if you have benefit statements that were run as of the conversion date, the information shown on these statements (SSN, Name, accrued, projected benefit, etc.) should be pasted into a tab named "Statements". This information will then be compared to the results from a benefit statement export run within DB Precision.
• Data from Other Sources: sometimes, you will need to import data from other sources. For example, you might have a separate data source that includes hours history for participants. If you have such an additional data source, this should be put into its own tab (named "Hours" for example) and will then be imported separately. Do not use lookups to try to get all of the data initially being imported into a single import on the Import tab described above.

For an example of what a conversion file should initially look like, click here.