UDS “D” Record Training

UDS “D” RecordTraining • History and Overview 1

Credits • Project Manager – Julie Snyder • UDS “D” Record Training Subcommittee • James Hamilton (Home Ins. Co., in Liquidation) – co-chair • Kathy Lavin (GFMS) • Mark Might (Ohio & West Virginia IGA’s) – co-chair • Darin Miller (Ohio & West Virginia IGA’s) • Paul Radel (Reliance Ins. Co., in Liquidation) • Dale Stephenson (DFS Consulting) • NCIGF Support Staff • Nick Crews • Maureen Sciame • Andrew Holladay

Mission Statement • Simplify Reporting in a Standardized Format versus Multiple Formats Currently Utilized • Standardize financial reporting • Reduce the number of report formats • Simplify the reporting process • Move to an Electronic Filing (preferred) versus Hard Copy • Ease of creating reports • Speed in creating reports • Reduction of calculation errors 3

“D” Record Overview • History/Background • The formation of property/casualty guaranty funds began in the early 70’s. • By the mid 80’s, when guaranty funds were becoming a factor in the insolvency process, most still maintained books and records which were largely manual. As insolvencies became larger and encompassed multiple states, guaranty funds started to automate their systems. • After implementation of the UDS claim format (April 1, 1995), it was also recognized that the financial reporting by guaranty funds to receivers should be matched to the standardized claim reports, with the added elements of expense that were administrative in nature and therefore not included in the transaction based claim reports. 4

“D” Record Overview • History/Background (cont’d) • The “D” Record was designed to standardize the claims-related and administrative expense data requested, providing for efficiency, reconcilability and ease of use by both guaranty funds and receivers. • In December 2007, the new “D” Record for financial reporting was approved by the NAIC with reconciliation capabilities for historical, non-detailed segments and with new features for future reporting. 5

“D” Record Overview • Benefits • Guaranty Funds • Standardized reporting reduces the variety of formats required to be completed • Increases the opportunity to automate the process • Reduces manpower needs to compile the report • Speeds up the preparation and issuance of the financial report • Receiverships • Facilitates the compilation of financial data from multiple GFs. • Speeds up reconciliation with the “C” Record and Revenue sections • Allows for review and analysis of claim costs • Allows for review and analysis of individual GF Proofs of Claims 6

“D” Record Overview • Costs • Review and changes to general ledger accounts • Initial costs for the change over and time to reconcile between prior reporting formats and the new “D” record 7

“D” Record Overview • Reconciliation • Guaranty Funds • Prior to submitting the “D” Record to the receiver: • The guaranty fund should reconcile the “D” Record, Section 1 to the “C” Record. Any differences found during this reconciliation need to be corrected and an itemized report should be sent to the receiver. This will assist the receiver in expediting their reconciliation. • The guaranty fund should reconcile Year-to-Date and Inception-to-Date amounts in Section 2 and the Revenue Section to the prior quarter report. 8

“D” Record Overview • Reconciliation • Receiverships • The receiver should reconcile the “D” Record, Section 1 to the “C” Record. • The receiver should reconcile Year-to-Date and Inception-to-Date amounts in Section 2 and the Revenue Section to prior quarter report. • If any differences are found during this reconciliation, the receiver should contact the guaranty fund. 9

Section IGuaranty Fund IT StaffOther ConsiderationsSystem Considerations 10

General Ledger (G/L) Export G/L Data Create UDS “D” Records Liquidator Receives Records Transmit Process The Guaranty Fund exports information from its General Ledger and presents it within the appropriate “D” Record categories. The resulting “D” Record is then transmitted to the Liquidator. 11

Other Considerations As stated in the Mission Statement, the short term goal relates to standardization and simplicity. The long term goal relates to automation. In instances where the guaranty fund provides an electronic UDS “D” Record and the receiver elects to utilize the electronic file, the following other considerations apply: • Record mapping will have to identify the corresponding G/L Account Mapping • Consideration must also be given to multiple G/L accounts which roll into one UDS code or category. • Excel spreadsheet templates will be available to assist in creating a hard copy “game plan.” 12

Other Considerations (cont’d) • Consideration must be given to cut-off dates, with the ultimate goal of having the financial cut-off being consistent with the claims cut-off. • Allows automation of reconciling the “D” Record with the “C” Record. • Eliminates the need for manually reconciling any gap between cut-off dates. 13

Preparing the Data • Data is collected for: • Current Quarter • Year-to-Date • Inception-Date • Every effort should be made to use the same cut-off period for both Financial Reconciliation and Claims Reconciliation. This will eliminate the need for manual reconciliation. 14

Establish an Account Map that links organization’s accounting codes to their UDS counterparts • Utilize template to accomplish mapping • Extra rows are provided in the event that multiple G/L Accounts roll into one UDS code 15

UDS Financial Manual Detail Records Header Trailer System Considerations Format specifications are defined in the UDS Financial Manual 16

System Considerations As stated in the Mission, the short term goal relates to standardization and simplicity. The long term goal relates to automation. In instances where the guaranty fund provides an electronic UDS “D” Record and the receiver elects to utilize the electronic file, the following system considerations apply: • “D” Record Format specifications are spelled out in theUDS Financial Manual • Accounting software must be able to export data. • System software must be developed to allow exported data to be entered into the appropriate UDS “D” Record fields and store the data electronically. 17

System Considerations (cont’d) • Quality Control Checks • Consideration needs to be given to utilization of front end edits to screen invalid and/or illogical data. • Utilization of Header and Trailer Records helps ensure data is consistent with the expectations defined in the “D” Record format specifications. • Upload Data to Receiver • Consideration must be given to the secure transmission of the UDS “D” Record from Guaranty Fund to Receiver. 18

Section IIReceiver IT StaffSystem ConsiderationsOther Considerations 19

Imports Claims and G/L Data Into Liquidator’s Loading Program Run Pre-load Quality Control Reports and Check Trailer Report Totals Match Section 1 Totals to the “C” Record Totals Create Analysis Reports Liquidator Receives Records Load Data into Liquidator’s Database Process The Liquidator receives “D” Records from GFs and loads them into G/L, Administrative Database or Proof of Claim System.. 20

System Considerations • “D” Record Format specifications are spelled out in the UDS Financial Manual • Consideration will need to be given to design and development of an IT facility to accept the electronic file and possibly load the information into a receivership’s administrative database. • System software will need to be developed to read the file and store the data electronically. • Front-end Edits/Header and Trailer Records • Consideration needs to be given to utilization of front end edits to screen invalid and/or illogical data. • Consideration needs to be given to utilization of header and trailer records for reconciliation and balancing routines. 21

System Considerations (cont’d) • Quality Control Checks • Consideration needs to be given to design of controls to ensure that data per the UDS “D” Record is accurately populated into the Receiver’s G/L or Administration database. • Reconciliation and balancing routines need to be developed. 22

Other Considerations Receivers will have varying processes and procedures for receipt, review and utilization of UDS “D” Record information, whether they obtain the information via electronic file, electronic spread sheet or paper format. The following are some other considerations that may apply to their situation: • Identify UDS “D” Record mapping to corresponding Receiver’s G/L Account mapping • One or multiple fields on the UDS “D” Record may need to be mapped into a single field on the Receiver’s G/L or Administration Database. • Consideration will need to be given to programming requirements should this mapping be performed electronically. 23

Other Considerations (cont’d) • Load data into G/L, Administrative Database or Proof of Claim System • Consideration needs to be given as to whether data per the UDS “D” Record will be loaded into the Receiver’s G/L, Administrative Database or Proof of Claim System. • Consideration needs to be given to IT requirements including identification of selected fields of UDS “D” Record data that will be populated into the Receiver’s Administrative Database. • Once the database is populated, consideration needs to be given to IT requirements to program ad hoc reports for data analysis purposes 24

Header 25

Detail Records 26

Detail Records (cont’d) 27

Trailer 34

Trailer (cont’d) 35

Trailer (cont’d) 36

Questions? udshelp@udstsg.com 37

UDS “D” Record Training