Attachment_1_-_FY19_PIMS_Data_Entry_Manual.pdf

Filename: https://pbgcgov.sharepoint.com/sites/FY2019/Shared Documents/Staging Area/PIMS Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page i

Pension Insurance Modeling System

Data Entry Program (FY19.0) Manual

Version FY19.0 3/27/2019

Pension Benefit Guaranty Corporation Policy Research and Analysis Department (PRAD)

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page ii

Table of Contents

1 OVERVIEW

2 INITIAL PROCEDURES

2.1 PIMS MANAGER

2.2 PIMS DATA ENTRY PROGRAM

2.3 COMPARE NECESSARY SCHEDULE SB/MB SECTIONS

2.3.1 Missing Schedule R in the Filing

2.3.2 Check the Plan’s Eligibility for PIMS

2.4 CHECK PLAN’S BASIC INFORMATION

2.4.1 New EIN or PN

2.4.2 Determine Plan Kind

2.5 USE PIMS MANAGER TO UPDATE DATABASE

3 DATA PREPARATION AND ENTRY

3.1 ENTER AMORTIZATION DATA (ALL SE & NEW ME PLANS)

3.2 ENTER ACTIVES AND HIRE DATA

3.3 ENTER ACTIVES SALARY DATA (SE-PIMS)

3.4 ENTER ACTIVES ACCOUNT BALANCE DATA FOR CASH BALANCE PLANS (SE-PIMS)

4 ENTER PLAN BENEFIT PROVISIONS AND DECREMENT RATES

4.1 CAREER AVERAGE AND EAN ADJUSTMENTS

4.1.1 Convert Career Average Plans to Final Pay Formula

4.2 DOCUMENT PLAN PROVISIONS AND/OR MODIFY PLAN BENEFIT FORMULAS

4.2.1 Determine the Benefit Formula Type for Plan

4.2.2 Benefit Formula Parameters - Flat Plans (stored in “bfflat” table)

4.2.3 Benefit Formula Parameters - Salary Plans (stored in “bfsalary” table)

4.2.1 Benefit Formula Parameters – Cash Balance Plans (stored in “bfcashbal” table)

4.2.2 Benefit Formula Parameters - Hybrid Plans (stored in “bfflat” & “bfsalary” tables)

4.3 ENTER DEMOGRAPHIC AND EARLY RETIREMENT REDUCTION RATES

4.3.1 Entering Decrement Rates (if there are new rates in an existing plan or it’s a new plan)

4.3.1.1 Enter Plan’s Retirement Rates for Actives

4.3.1.2 Enter Plan’s Separation Rates

4.3.1.3 Enter Plan’s Disability Rates

4.3.2 Enter Benefit Reduction Rates and Supplemental Benefit

4.3.2.1 Enter Plan’s Benefit Reduction Rates

4.3.2.2 Supplemental Benefit

4.4 UPDATE PROGRESS REPORT ON RATES TEMPLATES

5 DIFFERENCES IN DATA ENTRY PROCEDURES FOR MULTIEMPLOYER PLANS

6 PREPARE FINAL DATABASES

6.1 PREPARE FINAL PIMS DATA FOR PBGC

6.2 COMPARE TWO SUB DATABASES/VALIDATE DOUBLE ENTRY

6.3 COMPARE WITH PREVIOUS YEAR DATA

7 DEMOGRAPHIC PROJECTIONS AND CALIBRATION – FINAL STEP

7.1 CREATE RETIRED AND TVESTED POPULATION TABLES (100-YEAR PROJECTIONS/INACTIVE CENSUS, SE-

PIMS ONLY)

8 APPENDICES

APPENDIX 1 – EXAMPLE OF NON-PIMS PLANS

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page iii

APPENDIX 2 - SAMPLE AMORTIZATION TABLE

APPENDIX 3 - SAMPLE ACTIVES MATRIX

APPENDIX 4 - SAMPLE SUMMARY OF ACTUARIAL ASSUMPTIONS

APPENDIX 5 - SAMPLE SUMMARY OF PLAN PROVISIONS

APPENDIX 6 - PIMS SALARY BENEFIT FORMULA PARAMETERS

APPENDIX 7 - SAMPLE SALARY BENEFIT FORMULA SUMMARIES

APPENDIX 8 - DATA TRANSFER AND MANIPULATION RULES

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 4

Revision History

Modified 2018 by PRAD

Notes Regarding (FY 2016)

A decision was made by the PBGC, for FY 2016 and moving forward, a new program, PIMS Data Entry Program (DEP), will be used, which uses a more comprehensive, self-contained, and automated process for data entry. The principal difference between the new process and the previous one is that a large portion of the data will be imported directly from the 5500 FOIA dataset and that the inventory is set ahead of the fiscal year’s dataset. This transition was made in order to not only maximize the overall efficiency, but also to simplify the PBGC’s review process. The PIMS Manager will be used to view and update the PIMS tables throughout the process, as well as to load some data that the new program does not yet handle. In addition, both ME and SE calibrations will be performed by the PBGC as part of the review processes. The PIMS tables to be updated by the data entry are as follows:

• Acctbal - Manually to the Cash Balance Account Excel file (SE-PIMS)

• Active & Hire– using the Active.hire Excel template

• Assetret – automated download using the DEP

• Bfflat – Manually via PIMS manager

• BfSalary - Manually via PIMS manager (SE-PIMS)

• BfcashBal - Manually to the Cash Balance Formula Entries Excel file (SE-PIMS)

• Benereduc – using the benreduc Excel template

• BenereducTV – using the benreducTV Excel template (ME-PIMS)

• Disable – using the Disable Excel template

• Partic – automated download using the DEP

• Plan – automated download using the DEP

• Plan_amort – using the SE amortization Excel template (SE-PIMS)

• Retire using the Retire Excel template

• Retireds - automated using the DEP (SE-PIMS)

• Salaries - using the Salary Excel template (SE-PIMS)

• Separate - using the Separate Excel template

• Tvesteds - automated using the DEP (SE-PIMS)

To gain better control of the data entry processes and operational efficiency, all data entry will be performed within PBGC’s development network. Contractor personnel are required to obtain “dev id” (development environment) in order to perform the work.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 5

Notes Regarding FY 2017 & FY 2018

The PIMS Data Entry Program is continuously being enhanced to maximize the overall efficiency.

For FY 2017 (and FY 2018), the DEP incorporates the capability of comparing and verifying the double entries for various tables. It is anticipated that for the FY 2019 release, most of the Excel templates will be replaced by direct entry screens within the DEP. In addition, a functionality to compare the 5500 download information to prior year’s data will also be incorporated into the FY 2019 release. The path to the FY 2017 work is shown below.

Notes Regarding (FY 2019)

A new release of the PIMS Data Entry Program (DEP II) will be used, which uses a more robust self-contained, and improved system than the original release of the data entry program. The principal changes are:

• Data Input Screens that replaced the Excel templates for the following tables o Active & Hire o Benreduc & BenreducTV (ME only) o Decrement tables – Disable, Retirement and Separate o Salaries (SE only) o Amortization (all SE plans and new ME plans) o Benefit formula tables and miscellaneous data variables

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 6

• Enhanced features to validate double entry

• Comparison tool to compare data from prior year

• Integrated Data Entry Program with PIMS Manager

• Enhanced data import and data entry validation rules

This release is expected to be a production release. Defects should be logged and PRAD will determine if the program should be updated. Lastly, new processes have been incorporated to simplify the annual update process.

For FY2019, data entry work will be split into 2 separate projects (ME and SE). The DEP II has been verified for SE plans. It is also anticipated that the number of plans to be updated will be reduced to about 450 (total for both ME and SE) for the FY2020 as a result of PRAD’s “No Data Entry” analysis.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 7

1 Overview

The Pension Insurance Modeling System (PIMS) is an actuarial/econometric model that is used by the PBGC to help examine the impact of potential legislative, regulatory and economic changes on the PBGC net positions. As a normal part of the model operation, PIMS completes actuarial valuations on a large number of ongoing plans. In order to complete these valuations, a considerable amount of plan-specific data must be extracted from governmental filings (Form 5500, Schedule SB/MB, R, H/I and the attachments), and then loaded into the standardized tables for use by PIMS. This document outlines the steps necessary to complete this data extraction and loading processes.

2 Initial Procedures

Starting FY2016, instead of entering data from a Schedule SB/MB into a database table, user will use the DEP for most part of the data entry process. With this new program, Schedule MB/SB, R, and H/I data is automatically downloaded from the 5500 FOIA dataset to the PIMS database. The DEP will download the fillings for the last (most recent) 6 years and update the fields in various tables (Plan, Partic and Assetret). Also, with the new program, the inventory will be set ahead of the fiscal year’s dataset. Prior year’s inventory will be adjusted based on the PIMS applicability, availability of sponsors’ data, and other relevant information. Note that the inventory, prior year’s final databases and the data “reset” functions will be completed by PRAD and/or PIMS contractor.

The PIMS Manager will be used to view and update the database tables. At any point of the process, user can make manual adjustments to the downloaded 5500 data and upload it back to the database using this application.

This section outlines the initial plan acceptance process and the procedures needed to run the PIMS Manager and the PIMS Data Entry Program. The general data processes for PIMS involve the following steps:

Sequence SE-PIMS ME-PIMS

1 (1) Reset PIMS data and load Form 5500 schedules SB, H/I and R data (utilizing DEP). This step will be performed by PRAD.

Review and correct errors as appropriate. Compare current and prior year’s schedule SB entries and make changes as needed. Settle on information year to be used for each plan.

Reset PIMS data and load Form 5500 schedules MB, H/I and R data (utilizing DEP). This step will be performed by PRAD.

Review and correct errors as appropriate. Compare current and prior year’s schedule MB entries and make changes as needed. Settle on information year to be used for each plan.

2 Print and organize applicable Form 5500 schedules and attachments (via efast.dol.gov)

Print and organize applicable Form 5500 schedules and attachments, including cash flow and withdrawal payments pages of the audit report

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 8

(via efast.dol.gov) 3 (2) Complete data entries: active/hire, salary, cash balance account/formula & Plan_amort, and for new plans;

decrements [disable, retirement and separate] & Benreduc

Complete data entries: active/hire and for new plans; decrements [disable, retirement and separate], planUAL & Benreduc

4 & 5 (2) For plans that existed in prior year, review plan provisions for changes in benefit formula and early retirement reduction factors for actives, update appropriate benefit formula tables, and complete the benreduc data entries via

DEP.

Note: If professional judgements have to be made during this step, appropriate supporting comments should be noted on the Weekly Progress Report and if needed, supporting files should be created and saved as needed.

For plans that existed in prior year, review plan provisions for changes in benefit formula and early retirement reduction factors for actives, update appropriate benefit formula tables, and complete the benreduc data entries via

DEP.

Note: If professional judgements have to be made during this step, appropriate supporting comments should be noted on the Weekly Progress Report and if needed, supporting files should be created and saved as needed.

4 & 5 For plans that existed in prior year, review actuarial assumptions changes in the disable, separate/withdrawal and retirement decrements, and complete the appropriate data entries as needed.

Note: If professional judgements have to be made during this step, appropriate supporting comments should be noted on the Weekly Progress Report and if needed, supporting files should be created and saved as needed.

For plans that existed in prior year, review actuarial assumptions changes in the disable, separate/withdrawal and retirement decrements, and complete the appropriate data entries as needed.

Note: If professional judgements have to be made during this step, appropriate supporting comments should be noted on the Weekly Progress Report and if needed, supporting files should be created and saved as needed.

6 Enter additional data items via DEP. Enter additional data items via DEP.

7 Validate double entries via DEP . Validate double entries via DEP.

8 Generate inactive census (100-year projection) via DEP (tvesteds and retireds)

Generate inactive census (3)

(1) The functionality to compare prior year’s data to this year’s is available with the FY 2019 release. For FY 2018 and prior years, the comparison was completed using Excel and the PIMS Manager.

(2) As noted earlier in this manual, the active/hire, benreduc, benreducTV, salary, plan_amort, disable, separate, and retire Excel templates are replaced by the direct entry screens within DEP for FY 2019.

(3) Inactive census data for ME-PIMS will be generated by PRAD.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 9

2.1 PIMS Manager

Using PIMS Manager, you can view, edit, and (re)upload the data. See Step 2.5. The PIMS Manager is the primary tool for correcting downloaded Form 5500 data and reviewing prior’s year data.

In order to run the PIMS Manager, open the “PIMS Data Entry Application” folder.

To start the PIMS Manager, double click the highlighted batch file. Close the Command window after it is run. Select the database you want and click “Use Selected”.

Note that for FY 2019, the initial focus is on SE only.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 10

The databases will be generated by PRAD each year. The “_INITIAL” database is the baseline database from the prior fiscal year. It will be used as a reference point throughout the data entry process. All data for PIMS requires double entries, and the “_A” and “_B” are the two separate databases for this purpose.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 11

Double click any of the databases and the following screen will pop up, which shows all tables within the selected PIMS database. (Note that the list of tables is different between SE-PIMS and ME-PIMS. Screen below is from SE-PIMS).

Note that primary keys are in bold, both in the tables list and in the fields list, as shown below. A primary key is the number of fields that are needed to identify records in a unique way in each table. For example, there should be only one record of service=20, age=55, with id=101, in a table where these fields are the primary keys.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 12

If you open the “plnref” table, you will see all plans in this year’s inventory. There are three columns you should pay close attention to: in_pims_data, terminated, and use_plan.

a) A check under the “in_pims_data” field means the plan has been in PIMS before.

If the plan has been terminated, update the “terminated” field via Export and Import (See Step 2.5).

For data entry purposes, ALL data for each plan should be updated to the extent possible. Thus, whether a plan is terminated, is missing information, or has insufficient active counts (as stated below in Appendix 1 to be “non-PIMS” plans), all available data should be entered and verified.

If data is missing from the 5500 dataset or EFAST, please enter prior year’s data and make a comment in the Weekly Progress Report. For case-specific questions, please contact PRAD for additional guidance.

2.2 PIMS Data Entry Program

1. To run the PIMS Data Entry Program, go to the “Tools” menu of the PIMS Manager and select “PIMS Data Entry Program”. Click on the “Run” button.

Once DEP starts, the following screen will appear. If this is the first time logging into the DEP for the fiscal year, update the configuration as User A, and “Save Config for this User”. User B configuration also needs to be saved before using the “Validate Double Entry” functionality.

Make sure to check that the PIMS Year and Destination Pseudo-Database are correct. See PRAD staff for additional guidance.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 13

The “PIMS Flavor” determines whether the data is for SE-PIMS or ME-PIMS, and the “Destination Pseudo Database” determines the version of the two double entry databases. Note that the program allows for multiple concurrent sessions. Thus, multiple users can work on the same set of data simultaneously. In addition, the configuration is saved under an individual profile and will not affect the configuration of other users.

Log Files All program activities will be recorded in the log files. Users should review the log files upon completion of each process to ensure data integrity.

In the “Log” directory (Q:\PIMS Data Entry Application\PIMS Data Entry Program\Log), you should see the program log files in the HTML format. Log file will be named as “Log_DATE_xxx_SE (or ME)”, where xxx is the index number and DATE is the date the specific log file was created on. This log file includes entry of all activities during the session. The program will create a log each time it runs, incrementing the corresponding index number by one. User should review the log file and correct errors and warnings as needed. Log files contain the following information: Time, User ID with the name of the computer used, and details of the log.

Errors will be shown in red and warnings in yellow. See the screenshots below.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 14

It is highly recommended that users exit and restart the DEP before any large-scale process (e.g. updating PIMS tables, download 5500 data etc.) to avoid lengthy and cumbersome log files.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 15

All working folders are pre-set for the PIMS Data Entry Program. You should see five text files in Q:\PIMS Data Entry Application\PIMS Data Entry Program\SE: EIN_PN_Changes, FieldsToPopulate, FieldsToReset, TablesToEmpty, and FieldsToCompare, and the “Sch” folders.

Before stating to work on the dataset, please check/do the following:

1) In Q:\PIMS Data Entry Application\Data Entry Team\Templates, you should see

“Account_Balances_FY2019” and the “Cash Balance Formula” files.

2) In Q:\PIMS Data Entry Application\PIMS Data Entry Program\SE\Sch, you should see all the data files downloaded from the FOIA website: Schedule H/I, SB, and R. Each schedule will have three files—a WinZip file, Microsoft Excel Comma Separated Values File (.csv) file, and .txt file for the layout. These files are created by DEP.

3) Saved in the same directory, “EIN_PN_Changes” describes the rules for the users to follow when there is a change in EIN and/or PN in a plan. Create a record in this text file, if there is an EIN/PN change. Note that this function is not available for DEP II.

4) “FieldsToPopulate” lists the rules the program will implement to populate the tables. This is performed by the PBGC.

5) “FieldsToReset” lists the fields the program has reset in the “plan” table, when the user initiates the process. This is performed by the PBGC.

6) “TablesToEmpty” lists the tables the program will clear before populating new tables. This is performed by the PBGC.

7) “FieldsToCompare” lists the fields that will be compared to last year’s data values for reasonability.

Next, in the PIMS Data Entry Program main screen, click on the “Import 5500 Data” button.

You should see the following screen.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 16

The following steps 1 through 7 will be performed by PRAD. However, users need to review the log files and address the warnings and errors as needed. Please consult with PRAD for further guidance.

1. Click on the “Import 5500 Data” button to import the data (Schedule H/I, R, and SB/MB) from the FOIA website to the tables (data is downloaded to the “ttF_SCH_H_YEAR, ttF_SCH_I_YEAR, ttF_SCH_R_YEAR, ttF_SCH_B_YEAR” tables in the PIMS Manager).

YEAR corresponds to the schedule years being downloaded. The program will download the data from the last 6 years’ filings, 2011 through 2016 for FY 2018, for ALL plans in ‘plnref’ table (checking the boxes for individual plans will not impact the download), extract the files, read layout of the schedules, and fill in every applicable field in the tables (also discard non-applicable data that is in the 5500 filings). The log files will describe this process in detail. In these tables, a “-88” indicates a blank field (where it should be numeric) in the downloaded schedules and a vertical bar denotes a blank text field.

You should see the progress bar at the bottom of the screen.

The “Import 5500 Data” process will take a few minutes. When finished, you will see a message box with a result. If there is an error, the program will tell you to check the log. Find and check the log file with the most recent date and time stamp in the “Log” folder.

Note: Under normal circumstances, this step should only need to be performed once each year.

PBGC recommends initiating this step in no later than late March but no earlier than late January in order to capture all the calendar year plans. Note that prior year’s data may be used for the initial year, but plan data will catch up beginning in the next cycle.

2. When the importing process is finished, the “Year” and “Delta” columns will be filled in. The program will read the most recent filing year available on the FOIA website and show it in the “Year” column. A “Delta” is the difference between the year used for this inventory and the one used for the last inventory. In other words, it gives the value of “New year–Old year” for that plan. Check for plans with a Delta 0 or a negative number. In the log files, you will see the “Delta” values being calculated for each plan. Plans ready to be loaded to PIMS should have delta values from 1 to 4. If other values are produced, such as 0 or a negative number, the log files will mark them as errors. If there was an EIN/PN change, follow the instructions in Step 2.4.1. Carefully review all warnings and errors.

If the plan’s most recent Schedule B is too old (ME plan must NOT be older than 5 years past the current year, SE plan must NOT be older than 3 years past the current year), the plan should NOT be copied to the current version of PIMS. The Data Entry Program will indicate the plans whose last filings are 4 years old with warnings (color-coded in yellow) in log files. When the user initiates the “Import 5500 Data”, the program will automatically check off the “Use Plan?”

column for these plans and the user has the option of loading (or not loading) them.

Do not load ME plans with the most recent filing older than 5 years (3 years for SE plans) past the current calendar year and check off the “Use Plan?” column on the screen. Also, write the reason it is dropped in the Weekly Progress Report (See Appendix 1 for various reasons). If the plan has been terminated, use the Export/Import function in PIMS Manger to update the

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 17

“terminated” field in plnref. Such plans will no longer appear in the main screen.

Also, on the “Import 5500 Data” screen, you can unselect all plans by using the “Toggle” button. To select all plans again, use “Check All” or “Check All Used”. You can also sort plans by their EIN, PN, PIMS ID, and Name.

Note: Once you check off any non-PIMS ready plans on this screen and populate PIMS Tables in Step 5, you will not see them on the other three screens: “actives and hire”, “disable, retire, and separate”, and “tvesteds and retireds”. Please consult with PRAD BEFORE checking off plans. It is recommended to update ALL plans with all available data during the data entry process. PRAD will make a final decision whether to include or exclude individual plans before finalizing the inventory for the fiscal year.

3. Some plans will have an incomplete year (less than 12-month period) or duplicate filings in an one-year period. You can find these plans as warnings in the log. Employer identification number (EIN) and plan number (PN) are listed with the warning and error messages.

Note: Import 5500 Data process changes the following data in PIMS: year and use_plan in plnref table and schb_year and planyear0 in plan table. The year, schb_year, and planyear0 for most plans are set to the most recent full year available for import and the use_plan in plnref is set to TRUE for these plans. For other plans that have errors (either missing information or short plan year), the year is set to -77 and the use_plan is set to FALSE.

4. When you finish investigating all errors in the log and checked off non-PIMS ready plans, check “Load?” column for the plans for which you want to import the applicable data from the “ttF_SCH_YEAR” tables into the applicable PIMS tables. Populating PIMS tables will overwrite any manual entries in the database tables, so do NOT start adjusting data in PIMS until tables are populated (see step 5 below). If a plan’s filing is missing some important information, you should search the DOL EFAST database for the missing information and later enter it manually in the appropriate table(s) or check off the “Load?”

column. Add comments to the Weekly Progress Report as needed.

Click on the “Reset PIMS Data” button to clear the applicable PIMS tables and set the applicable PIMS variables (in the Plan, Partic and Assetret tables) to -77. This step prepares the PIMS databases for the updates from the Form 5500 and the data entry. Users can reset individual plans as needed. See “FieldsToReset” and “TablesToEmpty” text files, located in PIMS Data Entry Program\SE (or ME), which describe what tables and fields are being reset. Sample screen shot from the plan table after the reset is shown below.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 18

5. Click on the “Populate PIMS Tables” button to import the data from the “ttF_SCH_YEAR” tables to the applicable PIMS tables.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 19

6. The “Success” column (last column) will show the results. Read the log file and make changes as needed (e.g. Change the data in PIMS Manager, if there has been an update since you have imported the data from the EFAST website into the PIMS Tables) to fix any errors. Warnings should also be fixed. Most warnings are for when the data field is blank (NULL) and it should not be. It is better that blank fields are also fixed using PIMS Manager. After making necessary changes, repeat the Step 2 through 5. Please note that PRAD anticipates numerous errors for the Schedule R information and it is appropriate for the users to IGNORE all errors from the Schedule R.

7. Exit out of the “Import 5500 Data” screen.

2.3 Compare Necessary Schedule SB/MB Sections

Beginning with the 2008 Schedule SB/MB, a (signed) copy of a plan’s filing can be printed out from the following website: www.efast.dol.gov. Download and print out the following documents based on the year indicated in the plnref table from the EFAST website:

• Signed copy of the Schedule SB/MB,

• Attachments to Schedule SB/MB. This includes:

o amortization table, o summary of plan provisions, including a description of any benefit reduction for early retirement and a description of any supplemental benefits, o summary of the actuarial assumptions, including rates of retirement, separation, and, in some cases, disability, and o schedule of active participant data (“actives scatter”) o cash balance account (SE only) o salary scatter (SE only) o Schedule H o Pages with cash flow and withdrawal liability payment information for ME plans from the audited financial statements (income/expense page and any related pages from Financial Notes)

Note: Beginning FY 2018, the amortization schedule for ME will be rolled forward by an Excel template. The amortization table should only be needed for new ME plans. In addition, if items 32(a) and 32(b) on Schedule SB or Items 9c(1), 9c(2), 9c(3), and 9(h) on Schedule MB are not 0, and that the amortization schedule is missing for the plan, the filing should be reviewed by an actuary, who may be able to use the available information to prepare the missing table. Make a note of anything missing in the Weekly Progress Report.

Compare the newly downloaded Schedule SB/MB data to prior year’s data using the “Compare with Previous Data” function in DEP. The following variables should be compared; MVA, AVA, Participant counts, Target Liabilities/Current Liabilities, Target Normal Cost/Current Liabilities Normal Cost, expected benefit payments and the Contributions amount. If changes are more than expected (use 25% as a threshold), manually compare the data in the signed copy of the schedule SB/MB to the downloaded data. Make corrections as necessary. Actuarial judgement may need to be made under certain scenarios.

http://www.efast.dol.gov/

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 20

The EFAST database could potentially contain few errors, since there is no official checking done for this electronic system. Thus, it is up to the user’s discretion to determine whether a mismatched data is a simple error on the electronic copy and can be ignored (make sure it is correctly shown on the signed hard copy). In cases like this, user should still proceed with the plan and make a note in the Progress Report.

If you find any discrepancies in the filing(s), make changes to the applicable table via PIMS Manager, based on the signed copy of Schedule SB/MB.

2.3.1 Missing Schedule R in the Filing

PIMS obtains a plan’s asset return information from its Schedule R. When the Data Entry Program downloads all applicable schedules from the EFAST (explained in Step 2.2), Schedule R is considered optional. The program only creates “assetret” records if there is a Schedule R record in the filing. If there is not, no record will be created and the default records will be populated in the “assetret” table. A warning will show in the log file, if Schedule R does not exist in the filing. Note that orphan records could exist in the database due to previous year’s leftovers and/or the user deciding not to use a plan in the middle of the process.

Again, Schedule R generates the records in the “assetret” table in PIMS. There are special “assetret” records with IDs (“planid”=0, which needs to be inside PIMS), which conflict with “plan IDs”. We have shifted all “assetret" IDs” that relate to a plan via schedule R records by 10,000.

For example, plan ID + 10,000 = “assetret ID”.

2.3.2 Check the Plan’s Eligibility for PIMS

There are other reasons a filing cannot be used in PIMS. Among those reasons are:

• Short Plan Year: The first line on Schedule B may show the beginning and ending dates of the fiscal plan year. The filing cannot be used in PIMS if the fiscal plan year is shorter than 12 months. If no dates are shown in the first line on Schedule B, the fiscal plan year is 12 months. (This was also captured by the “Incomplete and Duplicate Report” button in the Data Entry Program)

• Terminated Plan: If Item B on Form 5500 indicates that the filing is the final filing for the plan, the plan has terminated and cannot be used in PIMS. Note that all terminated plans will remain in the plnref table, but with the “terminated” field indicating FALSE.

This ensures PIMS inventory have unique assigned IDs for each plan.

• Active Participants: There are two tests concerning the number of active participants:

o Too Few Active Participants: Item 3c(3), column (1) on Schedule SB or Item

2b(3)(c), column (1) on Schedule MB, shows the number of active participants in the plan. If the plan has very few active participants, it cannot be used in PIMS.

o Low Ratio of Actives to Retirees: Item 3a, column (1) on Schedule SB or Item 2b(1), column (1) on Schedule MB, shows the number of retirees in the plan. If the ratio of the plan’s active participants to its retirees is very low, it cannot be used in

PIMS.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 21

• Ineligible Plan Sponsors: Plans sponsored by private or foreign companies or not-for-profit organizations cannot be used for PIMS. These plans are identified by PRAD.

2.4 Check Plan’s Basic Information

A determination should be made whether the filing is:

• A more recent filing for a plan already in PIMS (e.g., PIMS has 2014 data and there is a 2015 filing for the same EIN/PN combination), or

• A new plan that has never been in PIMS before (new EIN/ PN combination).

2.4.1 New EIN or PN

This function is still under construction in the FY 2019 release.

The plan’s Employee Identification Number (EIN) and the Plan Number (PN) are in Items D and B on Schedule B. Sometimes, a plan changes its EIN or PN. Since FY 2016, a single Plan ID will be used throughout the databases to help simplify PBGC’s review process. Compare the new filing with the prior years’ filings to see whether the plan has changed its EIN and/or PN. If so, make a note following the instructions below (must be recorded in the .txt file using the format provided by the PBGC):

; There is one line per change. The format is as follows:

; old_ein,old_pn,new_ein,new_pn,eff_year ; old_ein - the old EIN for the plan ; old_pn - the old PN for the plan ; new_ein - the new EIN for the plan ; new_ein - the new PN for the plan ; eff_year - the first year the new EIN and/or PN is effective ; example:

; 128928198,001,291818281,002,2013 ; Notes:

; - There should be no spaces in each line ; - Lines that are blank or start with a semi-colon are ignored ; - There is one file for SE and another for ME

As noted earlier in the manual, it is recommended that users update ALL plans with ALL available information. PRAD will make the appropriate determination whether to include or exclude individual plans before finalizing the fiscal year’s inventory.

Note: The printed-out filing should be the same year as the year used in the 5500 download function. It is not necessary to chase the latest information. The printed-out copy is used to verify the data being downloaded from the 5500 FOIA databases.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 22

; - At this point, only one change per plan is supported

The instruction above is also provided in PIMS Data Entry Program\Log\ EIN_PN_Changes. Also, make sure to update the plnref with the new EIN/PN.

Note: For any new plans in plnref that have never been in PIMS before, the program will automatically add and show records for them in the “plan”, “partic”, and “assetret” tables in ME-

PIMS.

2.4.2 Determine Plan Kind

For PIMS purposes, there are four kinds of plans:

• Flat (F): A flat plan provides a benefit based on a flat dollar amount times years of service (e.g., $42.00 per month for each year of service). This should always be the case for all multiemployer plans.

• Salary (S): A salary plan provides a benefit based on a percentage of pay or average pay.

• Hybrid (H): A hybrid plan has both flat and salary properties (e.g., a pay-related formula with a flat dollar minimum benefit). Note: A hybrid plan in PIMS is not the same thing actuaries refer to when they use the expression “hybrid plan.”

• Cash Balance (CB): The benefit under a cash balance plan is usually described as an account balance or a lump sum.

Sometimes a plan has different benefits for different groups of employees. For example, a plan may include a flat plan benefit to union employees and a salary plan benefit to non-union employees. In such cases, a decision should be made by an actuary whether to treat the plan as a flat or salary plan, based on factors such as which group of employees is larger/dominant. Often the filing is consulted with a PBGC actuary before making that determination.

Note: Once a plan is in PIMS, the PLANKIND usually doesn’t change from year to year. If it has changed, update the plan table. If this is a new plan that has never been in PIMS before, also enter the PLANKIND in the plan table, using PIMS Manager. See Step 2.5.

In general, Plan Kind should be selected based on this hierarchy:

1. Based on the ongoing formula;

2. If there is more than one ongoing formula, based on the early retirement provision that covers most of the plan participants (age 55 & service 5); and

3. If all coverages are the same, based on the salary related formula.

2.5 Use PIMS Manager to Update Database

Follow these steps, if you find any changes in this year’s filings and need to update the database:

1. Click on the applicable table in PIMS Manager.

2. Click on the “Export” button at the top of the screen and “Yes”. This will open an Excel export file that includes all data in this table.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 23

3. Update the applicable data onto the Excel file, based on the signed hard copy. All exported Excel files are located in PIMS Manager\export. Save the file.

4. Click on the “Import” button. Find and double click the file you just made a change(s) to.

5. The program will read the revised spreadsheet and you will see a pop-up message box with a summary of how many records would be updated, inserted, or deleted, as shown in the following screenshot.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 24

6. Click “OK”.

7. If the changes were correctly read from the file, click on “Apply Import”. Otherwise, click on “Cancel Import”. If you cancel, you should see the following error message:

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 25

8. If you applied the export, verify the change(s) are successfully populated in the table.

Note: Blank fields are not allowed when you are uploading data to the PIMS Manager.

If you try to import a wrong export file, you will see the following message box. Confirm you are uploading the correct file.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 26

Also, if you need to update the database of a table with many records (note you can see the record count in the upper right hand corner of the screen), using the “Filter” button may be very helpful.

1) Click on the “Filter” button;

2) You can filter by each of the primary keys that are applicable to the table;

3) Type in the number you want to filter with. As an example, the screenshot below filtered the retire table by ID=195.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 27

4) Click “Apply”;

5) You should see a table filtered by the primary key you chose. User will be able to make changes to the table without having to go through many records.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 28

You can also use the “go back” button (circled in the screenshot below) if you want to go back to the previous table (or screen) you were looking at. You can also “go forward”.

Note: Using the PIMS Manager to update data is would be recommended for mass changes.

Individual tables where data entry screens are available in the DEP, it is preferred that users utilize the DEP to update the data.

3 Data Preparation and Entry

This is a data-entry step. With this new PIMS Data Entry Program, Schedule MB/SB, R, and H/I Data are automatically downloaded from the 5500 FOIA dataset to the program. The program will download the fillings for the last (most recent) 6 years and update the fields in various tables. Also, with the new Data Entry Program, the inventory will be set ahead of the fiscal year’s dataset.

Four items continue to require manual data entry, which are:

1) Amortization Bases (all SE and new ME plans)

2) Active Scatters

3) Salary Scatters (SE Only)

4) Cash Balance Account Scatters (SE Only)

For all other tables, the program will carry forward entries from prior years and the users will revise the entries if there are any changes (which will be noted in the Weekly Progress Report).

When using “filter” function, please be mindful that it is the most appropriate for changes for a single plan. Utilizing this to add or remove data should be used with caution.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 29

Note: User may need to update the “bensuppl” table later at the PBGC’s discretion. Please consult with PBGC if there is an updated supplemental benefit formula.

3.1 Enter Amortization Data (All SE & new ME plans)

Overview: This is a data-entry step. The user enters plan amortization data from the attachment to the Schedule SB/MB into the “amortization input screen”. This input screen resides under the decrement entry screens set. To start the “amortization input screen”, click the [disable], [retire], [separate] button (third button on the right hand side) of the main menu in the DEP.

The amortization table is one of the attachments to Schedule SB/MB. See Appendix 2 for an example of an amortization table. Schedule SB/MB may not include an amortization table if Items 32(a) and 32(b) on Schedule SB or Items 9c(1), 9c(2), 9c(3), and 9(h) on Schedule MB are all 0.

If the filing doesn’t include an amortization schedule, and Items 32(a) and 32(b) on Schedule SB or Items 9c(1), 9c(2), 9c(3), and 9(h) on Schedule MB are not 0, the filing should be reviewed by an actuary, who may be able to use the available information to prepare the missing table.

Follow these steps below to enter the amortization schedule for all new plans:

1. Start the input screen by clicking the [disable], [retire], [separate] button in the DEP main menu. The following screen will appear.

2. Scroll to the plan and click the “Yes” or “No” under the Amort column to bring up the input screen.

Note: In general, for all the screens in the DEP, it will usually show “No” to show data has not been entered and will turned Yes” once data is entered and saved.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 30

Sample SE data entry screen shot

Sample ME data entry screen shot

3. Enter the information provided on the hard copy of the schedule. Please note that the

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 31 remaining balance and the 2 interest only columns (for a SE plan) are not needed.

4. Users need to right click and select “insert” to add a row.

5. Use the [Load] to load previously entered data. This would be used to correct data entry errors only and is not used to retrieve data from the PIMS databases.

6. Click the [Save] button when completed.

7. As a final step, in the Weekly Progress Report, check “Prepared” if you saved the data and

“N/A” if missing the attachment. Please consult with PRAD for any further guidance.

Amortization Table Notes

1. For SE, please use negative numbers for credit entries. For ME, the input screen is set up only to take positive values under the “Credit” entries. Users need to right click and select “insert” to add a row. To remove a row, highlight the row and press [delete].

2. If an item in the amortization table is illegible or there is a question about an item, user should just use the prior year’s data, but notify the PBGC about such plans. Further examination will be done on the missing data.

3. If there is no amortization table and Items 32(a) and 32(b) on Schedule SB or Items 9c(1), 9c(2), 9c(3), and 9(h) on Schedule MB are all 0, enter 0 in the amortization template and the plan will not be loaded into the database.

3.2 Enter Actives and Hire Data

Overview: The census data for the plan’s active population must be entered into the PIMS database. This information is normally contained in an attachment to the Schedule SB/MB and the population data is normally recorded in a quinquennial age/service scatter. This step allows the user to enter the data in a format similar to the Schedule SB/MB attachment. The data is then saved in celled data format and loaded into the PIMS database. Note that the active population is used to develop data for both the active and hire tables in PIMS.

The actives matrix is one of the attachments to Schedule SB/MB. The actives matrix is a table that shows the number of active participants at various combinations of ages and years of service. See Appendix 3 for an example of an actives matrix.

Starting FY 2019, data for the active population will be entered via the input screen.

The instructions for Schedule SB/MB require that years of service appear in columns that represent five-year increments (i.e., 5-9 years, 10-14 years, etc.) except that:

• the first two columns represent (a) “Under 1” and (b) 1-4 years

• the last column represents “40 & Up.”

The instructions also require that ages appear in rows that represent five-year increments (i.e., 25- 29, 30-34, etc.) except that the first row represents “Under 25” and the last row represents “70 & Up.”

Note: For PIMS, it is assumed that no employee is hired before age 20 or after age 60. It is also assumed that no employee is older than 65. Everyone is assumed to retire at 65/5 (all decrement rates, except retirement, are automatically cut off at 65/5).

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 32

Follow the steps below to enter active population for each plan:

1. Download Schedule SB/MB attachment: Active Participant Data.

2. Start the input screen by clicking the [actives], [hire] and [salaries] (or [actives], [hire] for ME) button in the DEP’s main screen. The following screen will appear. Note that [salaries] table is for SE only. Thus, the salary column will not appear in the screen below in ME.

3. Scroll to the plan and click the “Yes” or “No” under the Active/Hire column to bring up the input screen

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 33

4. Enter the numbers from the actives matrix onto the “Partial #1” screen (set by default). If the plan provides more than one matrices, click the [add scatter] button to add another scatter. The DEP is set up to automatically sum all the entries and save to the “Cumulative” scatter, which will be used to update the PIMS tables.

5. The [Remove Scatter] button remove the entire scatter entries.

6. The [Reset Scatter] clear out all the entries in the scatter

7. The [Clear All] button resets ALL SCATTERS

8. The [Load] brings up previously entered data. This will be used for correction purpose only

9. Click the [Save] button once the data entries are completed

10. As a final step, in the Weekly Progress Report, check “Prepared” if you saved the data and

“N/A” if missing the attachment. Please consult with PRAD for guidance.

Note: For PIMS, it is assumed that no employee is older than 65. PIMS also has a minimum service requirement of 5 years in order to retire. Thus, it is assumed that at age 65, the employee has at least 5 years of service. Thus, the greyed-out cells are not used by PIMS.

They are there for users’ convenience purposes. For plans that provide the totals by each column and/or row, users can enter all cells and then use the columns/rows totals to verify the data entries.

Note: Users can run the “actives and hire” and “disable, retire, and separate” steps in any order, since these steps are independent of each other. However, the “tvesteds and retireds” must be the last step and be run AFTER validating the double entry, since the inactive census program depends on actives and hire data and decrement rates for SE. For ME, the inactive census program is independent of the decrements and the actives census, thus can be completed once the 5500 downloaded information has been verified.

Modernization/2) On-the-Street and Evaluation/Tab 6 - RFP/RFP Sections/Attachments/Attachment 1 - FY19 PIMS Data Entry Manual.docx Page 34

In the screen above, a “Yes” for the “Loaded?” column means the plan has data in the database.

When user uses the “actives and hire” or (“Salary” for SE) functionality for the first time, all plans should indicate “No”, since the program has cleared out the two tables when the user initiated the “Reset PIMS Data”. All plans require new annual entries every fiscal year.

Please note that if you have checked off any non-PIMS ready plans on the “Import 5500 Data” screen and populated the PIMS Tables, you will not see them on the other three screens: “actives and hire”, “disable, retire, and separate”, and “tvesteds and retireds”.

Actives Matrix Notes

1. The Quinquennial Scatter…