Each week we must make contribution rate updates for participants based on the correspondence we have with clients regarding the weekly Deferral Verification Report. This is an overview of the process, for more details please see the Activity in PC2. 


  1. Gather Data
    1. Using the DV Response Tracker, copy all items that have a "No" in the "Action Complete" column into a new Excel Workbook. Be sure to mark those as "Yes" with today's date.
  2. Prep Data
    1. Keep all of the original data on the first sheet as a Master. Create new tabs for "CR Updates", "No BlueStar Record", "Corrections/Reviews", "Duplicate SSN", and "Import File". 
    2. Using filters, copy the appropriate data from the master tab to its corresponding tab for scrubbing. 
  3. Scrub Data
    1. No BlueStar Record
      1. Clean up the data to make it easier to work with by deleting unnecessary columns.
      2. Using PlanConnect>Database Wizard>Participant>Contribution Rates, search for each participant using first and last name. *Note* Using the SSN to search will not work on this step. It must be done using first and last name.
      3. If the participant is in our system with a different SSN, move the record to the "Duplicate SSN" tab
      4. Verify if the plan allows Roth contributions and the change schedule for contribution rate changes
      5. Add paired rate for all participants. *Not required if plan does not allow Roth contributions or if participant is only adjusting one of their contribution types.
      6. Change the "Effective Date" column to reflect the appropriate date instead of the change schedule abbreviation.
      7. Copy/Paste to Import File tab. *Be sure to match data with correct headers*
    2. CR Updates
      1. Clean up the data to make it easier to work with by deleting unnecessary columns.
      2. Using PlanConnect>Database Wizard>Participant>Contribution Rates, search for each participant using SSN. 
        1. Verify there has not been a recent update made as we do not want to overwrite any changes the sponsor or participant may have entered.
      3. Verify if the plan allows Roth contributions and the change schedule for contribution rate changes
      4. Add paired rate for all participants. *Not required if plan does not allow Roth contributions or if participant is only adjusting one of their contribution types.
      5. Change the "Effective Date" column to reflect the appropriate date instead of the change schedule abbreviation.
      6. Copy/Paste to Import File tab. *Be sure to match data with correct headers*
    3. Corrections/Reviews
      1. We currently use this as a way to catch as many future DV Issues as possible that we know are related to expected corrections from the client. Our hope is to not send multiple DV Reports to a client for an issue they have already resolved. There is no scrubbing necessary for this data. 
    4. Duplicate SSNs
      1. Any duplicate SSNs that have been identified during the scrub process can be sent to Recordkeeping for correction. 
  4. Import Data
    1. A Contribution Rate import template can be found on the BlueStar Hub>Procedures and Forms>Plan Setup page under the PC Load File Templates section.
    2. Ensure all data is under the correct headers. 
    3. Reformat SSNs to identify any potential leading zeros. 
    4. Ensure all date formatting is correct and no change schedule abbreviatons were missed.
    5. In the "Origin" column, enter "B" for each row to identify the rate as a "Backdated Takeover" in our system. The "B" code is utilized in the Contribution Change Report logic to prevent a report being sent to the client when the update is complete.
    6. Save as a Tab Delimited Txt file
    7. Import using PlanConnect>Utilities>File Import>Import Contribution Rate
    8. Review the Output for potential errors. *The header row will always show as an error*