Because the data have these critical uses, it is essential that states report their activities accurately and uniformly. Data validation is intended to assure accurate reporting of employer contribution activities. Two principles underlie a comprehensive data validation process:
Exhibit 1.2 Form ETA-581
These two principles have been used to develop this system for validating data that states report to the U.S. Department of Labor on the ETA-581 form. The comprehensive data validation program described in this handbook ensures the accuracy of the UIRR data. It validates most items on the ETA 581 report using a process that is highly automated and complements existing quality control programs such as the Tax Performance System (TPS).
This handbook explains in detail how to do data validation. In addition to the handbook, DOL provides a software application that processes state-produced extract files, then uses them to validate the ETA 581 report.
The basic approach used in data validation is to reconstruct the numbers that should have been reported on the ETA 581 form. Because state UI records are highly automated, states can develop computer programs that go through their electronic databases and extract all transactions that should have been on the report. Automation reduces the burden on validators and state data processing staff as they extract records from state files, assemble those records for analysis, and assess validation results. Once transactions are extracted, they are subjected to a series of “logic rules.” These rules test the accuracy of the reconstructed data, assuring that states have used the most definitive source of information and have adhered to Federal definitions. After it is determined that the extract data meet the logic rules, they are used to produce validation counts that are compared to what the state has reported. If the validation counts confirm what was reported, within plus or minus 2 percent, then the reporting system is judged valid.
Modules 1, 2, and 3 of this handbook explain how to create and test reconstruction files. Although the basic approach is standard for all states, the instructions are state-specific in that they present many details of the validation process using terminology and data elements familiar to each individual state.
The instructions guide the state as it goes back to its automated databases to select transactions for analysis. Following the specifications in the handbook, the state extracts all records that should be counted on the Federal report.
Once the state has developed an accurate reconstruction of the transactions that should have been reported on the ETA 581, the handbook guides the state in the use of UI Tax Data Validation software to compare the reconstructed counts to the values that were actually reported.
Modules 4 and 5 of this handbook provide instructions for two validation tasks that do not use an automated reconstruction approach. Module 4 tests the procedures for selecting samples used in the Tax Performance System. Module 5 describes procedures for validating counts of wage items processed.
Validation is intended to ensure that data on the ETA 581 report are error free. Thus the design of the data validation system is grounded in an understanding of likely sources of reporting error — both systematic errors and random errors. Systematic errors involve faulty design or execution of reporting procedures or the automated programs that generate reported counts. Random errors are mistakes in judgment or data entry that corrupt the information entered in data systems or recorded on reports. The validation design addresses both types of error.
Many of the more common random, judgment, and definitional errors can be detected through existing Tax Performance System (TPS) reviews. TPS acceptance samples for status determinations and field audits are used to evaluate the accuracy of transaction classification and posting. TPS system reviews look for strong supervision, up-to-date documentation, and other controls that limit human error. Data validation does not attempt to repeat TPS reviews. Rather, it supplements TPS with a review of systematic errors while remaining alert to additional sources of random error.
Consistent and accurate reporting requires both good data and accurate systems for reporting the data. Data validation and TPS together test whether data are entered accurately and whether they are counted correctly.
States use different methods to prepare the ETA 581 report. Some states produce the Federal reports directly from the employer contribution database. Computer programs scan the entire database to select, classify, and count transactions. Other states produce a database extract or statistical file as transactions are processed, essentially keeping a running count of items to be tabulated for the report. Still other states use a combination of these methods. The basic approach to data validation is the same no matter how the report is developed — states reconstruct the transactions that should have been reported and do so using standard national criteria.
The validation methodology is flexible in accommodating the different approaches used by states. However, validation is most effective when validation data are produced directly from the employer contributions database. For cost reasons and to minimize changes in data over time, some states prefer to use daily, weekly, or monthly statistical extract files instead. When extract files are used, other types of system errors may occur. Reportable transactions may be improperly excluded from the employer master file. Furthermore, the statistical file may contain corrupt data. The statistical file is not used as part of the daily tax system and, therefore, errors may not be detected and corrected through routine agency business.The only way to test for these problems is to independently reconstruct or query the employer master file. States that produce validation data from the same extract files used to produce the ETA 581, rather than directly from the database, must ensure that the extract files contain all the appropriate employers or transactions. The way to do this is to recreate the logic used to produce the reports. This handbook includes a validation tool, “independent count validation,” specifically for this purpose. The specific type of independent count (simple query, multiple queries, cross tabulation) must be determined by state programming staff.1
Exhibit I.3 outlines variations in the validation methodology, based on typical state approaches to ETA 581 reporting and data validation reconstruction. To identify the specific validation methodology to be implemented, the state validator or Federal representative should identify the state’s ETA 581 report source and validation reconstruction source for each population to be validated.
States themselves perform validation and report the results to DOL. The UI Tax Data Validation software provided by DOL processes data extract files produced by the state and generates all required validation reports.
Data validation provides a reconstruction or audit trail to support the counts and classifications of transactions that were submitted on the ETA 581 report. Through this audit trail, the state proves that its UIRR data have been correctly counted and reported. For example, if a state reports 5,000 reimbursable employers at the end of the quarter, then the state must create a file listing all 5,000 employers as well as relevant characteristics, such as the Employer Account Number (EAN), employer type, liability date, number of liable quarters, and sum of wages in those quarters. Analysis of these characteristics can assure validators that the file contains 5,000 correctly classified employers. The reported number is proved and the report is considered valid.
To assure that the reconstruction of report counts has been done correctly, UI Tax Data Validation software tests the accuracy of the reconstruction process:
There are two additional data edits that are only partially automated:
These checks build validators’ confidence in the reconstruction count. Thus, when they tabulate the numbers of transactions in the validation file and compare them to the reported numbers, they can feel sure that they are accurately testing the validity of the reports.
VARIATIONS IN VALIDATION METHODOLOGIES BASED ON STATE
APPROACHES TO REPORTING AND RECONSTRUCTION
|Scenario||Transactions Overwritten on Database||ETA 581||Data Validation||Independent Count Required||Source Documentation Review Required||Comments|
|Program Type||Source||Timing||Program Type||Source||Timing|
|1||No||Count||Database||Snapshot||DRE||Database||Snapshot||No||No||Best scenario because comparing snapshots eliminates timing discrepancies|
|2||No||Count||Statistical file||Daily||DRE||Database||Snapshot||No||No||Database is only reconstruction source. There could be changes in transaction characteristics(but will find all transactions).|
|3||No||DRE||Database||Snapshot||DRE||Database||Snapshot||Yes||No||Reporting and validation are the same program. Independent count may mirror that program.|
|4||No||DRE||Statistical file||Daily||DRE||Statistical file||Daily||Yes||Yes||Since transactions are not overwritten, states should be able to do Scenario 2 instead.|
|5||Yes||DRE||Statistical file||Daily||DRE||Statistical file||Daily||NA||NA||No alternative validation source. Cannot reconstruct from the database. Not thorough validation.|
|6||Yes||Count||Statistical file||Daily||Must create daily extract a||NA||NA||NA||NA||Cannot reconstruct from database. Must change reporting process to Scenario 5.|
NOTE: Snapshot is oft the last day of the reporting period.
DRE= Detail Recocrd Extract
NA= Not Available
After states complete validation, the Federal partner may audit the results. To facilitate the Federal audit, the state must prepare and maintain a validation package. This package enables the Federal auditor to easily follow the validator’s work, without requiring the state to print out entire reconstruction files. The components of the package are discussed in more detail in Modules 1 and 2.2
There are 50 ETA 581 report items to validate.3 Each item has its own set of definitions, rules, and validation requirements. To minimize the burden of reconstructing item counts, the validation process is streamlined by breaking it down into manageable components and providing a software package that does much of the analysis. The data to be validated are grouped into mutually exclusive populations and subpopulations. The validation process itself is organized into a series of modules that are the logical steps in the process. This structure lets the state validation team focus on one type of data at a time, and validate each type of data one step at a time.
A single employer account transaction may be counted in several different ETA 581 report items. For example, a contributions report that is filed timely is counted in two items for the current report quarter (timely reports and reports secured) and in one item in the following report quarter (reports resolved).
Validation reconstructs and analyzes each transaction only once, even if it is counted in multiple cells on the report. Employers or transactions are classified into mutually exclusive groups — specifically, five types of employers or transactions (populations), which are further divided into 46 mutually exclusive groups (subpopulations). All validation counts are built from these subpopulations. The five populations are: (1) Active Employers, (2) Report Filing, (3) Status Determinations, (4) Accounts Receivable, and (5) Fields Audits.
Exhibit I.4 lists the ETA 581 populations and subpopulations that are reconstructed and the number of report items being validated for each. It also describes the dimensions along which populations are sorted as they are divided into subpopulations.
ETA 581 REPORT, BY TRANSACTION POPULATION
|Dimensions Used to
|1. Active Employers||101||Employer status
|2. Report Filing||201||Timing of report receipt and resolution
• secured within the quarter
• resolved within two quarters
|3. Status Determinations||301||Type of status determination
Time lapse of the determination
|4. Accounts Receivable||401
|Type of receivable processing
• amounts established
• declared uncollectible
• removed from the report
• outstanding debt.
|5. Field Audits||501
• no change
|Wage Items Processed||101||1||N/A|
State staff produce a series of extract files that contain all relevant data for each population to be validated. These extract files are imported to the UI Tax Data Validation software which reads these files, checks for errors, eliminates invalid records, sorts populations into subpopulations, counts transactions, compares validation counts to reported counts, and displays the results in spreadsheet formats. The UI Tax Data Validation software also produces a list of invalid records and the type of error that caused each record to be rejected.
To assure that reported data are accurate and meet Federal reporting definitions, there are five validation processes or “modules.” These modules provide all the tools to be used in validating the quantity and quality of Federally reported data.
Exhibit I.5 illustrates the data validation process detailed in the handbook modules and appendixes, using ETA 581 active employers as an example.4
From time to time this handbook provides recommendations for managing the validation process. These recommendations are described in “Task” exhibits. For each task the handbook provides a listing of activities to be completed and the staff who are likely to take the lead on each activity. Staff roles and responsibilities for preparing for data validation are summarized in Exhibit I.6 below. Staff roles will be divided among:
TASK 1: PREPARING FOR DATA VALIDATION
|Assemble data validation team.||Managers|
|Review handbook.||Validators, ADP|
|Attend training. Share training with staff who did not attend.||Validators, ADP|
|Review and update state-specific information in Module 3 of the handbook. Send U.S. DOL and its contractor a copy of the module with any needed changes clearly marked.||ADP, with help from validators|
|Develop a data validation plan with: • Schedule for completing data processing and validation review for each population. • Staff assignments for each step in the data validation process.||Managers, validators, ADP|
The remainder of this handbook guides users through the data validation process. Modules 1, 2, and 3 describe the major steps that states must follow when conducting data validation. Appendices provide the forms and specifications needed as the state proceeds through these steps. The general process is that the state produces a series of files that list all transactions that are to be counted on the ETA 581 report. The files are imported into UI Tax Data Validation software which reads transactions, compares validation counts to reported counts, and displays the results in a spreadsheet format.
1 There is no way to accurately reconstruct the report count when the statistical file contains transactions that are no longer present in the database (e.g., when it includes status determinations deleted from the main database after a corrected status determination for the same employer).
2 This handbook provides detailed validation instructions for each state to ensure that state and Federal staff understand all relevant aspects of the state’s employer contributions reporting system. In specifying how to reconstruct reported transactions, the methodology explains the criteria that states should use in their Federal employer contributions reporting. Thus in addition to guiding the states through the validation process, this handbook provides technical guidance on Federal ETA 581 reporting requirements.
5 Given the highly automated nature of tax data validation, database screens are generally the only supporting documentation needed. Therefore, this handbook refers to screens, rather than to supporting documentation, throughout. To prevent inconsistencies due to timing, screens to validate the accuracy of transfer of data from the database should be printed at the same time as the reconstruction file is created.
Module 1, Item Count, describes the process that state staff use to produce reconstruction/extract files. These files contain all transactions from the state Unemployment Insurance database that should be counted for a single quarter on the Federal report.
Reconstruction files are created for five different categories of data, referred to as data populations. The populations are listed in Exhibit 1.1 along with their corresponding sections of the ETA 581 report.
SUMMARY OF REPORT VALIDATION POPULATION FILES
|File Specification||Population||ETA 581 Line Number|
|4||Accounts receivable||401, 402, 403, 404|
|5||Field audits||501, 502|
Once created, the reconstruction files are imported into the UI Tax Data Validation software. That software reconstructs counts of transactions or employers and compares those counts to the numbers that were submitted on the ETA 581 report for the comparable quarter.
Before the data counts and validation results are judged to be final, the validation files are subjected to both automated and manual error-checking procedures. These checks eliminate invalid cases, ensuring that the reconstruction is based on an accurate and unduplicated count of transactions.
Appendix A contains instructions that specify how extract files are to be created for each population and subpopulation:
To create the report validation file, the state must go back to its source files (database) and select every transaction that meets the criteria for inclusion. These criteria can be found in Appendix A, Part I. An example of the inclusion criteria for Population 1 appears in Exhibit 1.2.
The narrative in this section describes which transactions or employers to select for inclusion in the extract file for each population. Inclusion criteria should be used in conjunction with the information in the column entitled “Module 3 Reference.” That column refers the reader to a “step” in Module 3 of this handbook. If you turn to that step, you will find more detailed information regarding the definition of the data element being used by the inclusion criteria.
Example (see Exhibit 1.2):
The instructions tell the state that the extract file for Population 1 should include only employers that are active as of the last day of the report quarter (RQ). If the state needs more information on what is meant by an active employer, this is provided in Step 3A of Module 3.
Transactions must be extracted from the state database in the specified standard format. For each transaction selected, the state will list many data elements. Those elements are the characteristics of the transaction that are necessary to determine the subpopulation (and hence reporting item) to which it should be assigned.Record layouts for each of five populations are included in both the software and the User Guide. The User Guide also provides instructions for using the record layouts. Inclusion criteria and record layout for Population 1 are presented here to illustrate the process. States should create an ASCII file with one column for each field listed in the record layout. The fields should be listed in the order they appear in the layout. Commas should be entered to separate each field.
POPULATION ONE INCLUSION CRITERIA
|Population 1 should include all employers who were active as of the last day of|
the report quarter.
|To be included:|
end of the quarter and must be later than any inactive/terminated date
that appears in the employer’s file (Steps 4 & 5).
wages paid in the eight consecutive quarters ending with RQ. (Step
An example of the second part of the record layout specifications for Population One is found in Exhibit 1.3. To help you understand the exhibit let us discuss each column.
Number and Field Name identify the characteristics or fields that should be listed for each transaction.
Module 3 Reference guides state staff to a portion of the handbook where they can find more detailed instructions for the information to be entered in the field. Module 3 of this handbook is organized by steps. Turn to Module 3 and you will see step numbers prominently displayed at the bottom and top of each page.
When you turn to the step indicated in the record layout, two types of information will be provided regarding the field. First, there will be an annotated version of the Federal definition for the field. Second, at the start of the step will be a section labeled File Integrity Validation. In that section there will be a document and rule that indicate where the information for the field can be found in the state database. The location will be identified by reference to fields in query screens used in the state’s UI tax automated system (“document”) and particular fields on each screen (“rule”). .
Field Description provides a narrative explanation of the data to be recorded in the field.
Data Type/Format specifies how the data should be displayed. Where the data are numeric, the word “ Number” will appear. Numerical fields will accept ten digits. Fields containing dollar amounts will accept numerical entries of any size. Of dollar amounts are listed showing cents, the decimal points should be included. All other numerical fields should be whole integers with no decimal points.
If the field is alphanumeric the word “Text” will appear. The listing also indicates the alpha codes that must be used. The alpha codes are the generic indicators that allow common coding across states. States should also list their own indicators. The listing should include both the generic and state codes separated by a dash, for example, C-01, where ‘C’ is the generic code and ‘01' is the state code for employer type that is listed in the database for this transaction.
Exhibit 1.4 displays a sample printout of a portion of an extract file in the prescribed format.
SAMPLE RECORD LAYOUT FOR POPULATION ONE
Extract file type is: ASCII, comma delimited columns. The record layout should be used to create a reconstruction file of all active employers on the last day of the Report Quarter (RQ) covered by the ETA 581 being validated. Reconstruction should be done at the end of the RQ being validated (when the ETA 581 report program is run). The module 3 reference indicates the step where the state-specific values are documented. Data must be in the order and format listed in the Data Type/Format column. The Data Type/Format column also indicates the generic values for text fields. These must be followed by a dash and the statespecific value. All fields listed as mandatory are required fields. All other field values are required only for specific types of employers as noted. If a state declares employers inactive after less than eight liable quarters of zero wages, the header of the reconstruction file should indicate the maximum number of liable quarters of zero wages that are permitted.
|Field Description||Data Type/Format|
|1||OBS||Sequential number, start at 1.||Number - 00000000 (Mandatory)|
|2||EAN||Step 1A||Employer Account Number||Number - 00000000 (Mandatory)|
|Step 3A||Indicate that the employer is an active employer.||Text – A (Mandatory)|
|4||Employer Type||Step 2A
|Indicate whether the employer type is
contributory or reimbursable.
|Text – C; R (Mandatory)|
|Step 4A||Indicate the date on which the employing unit
meets the state’s definition of an employer
and is registered and required to file reports.
|Date – MM/DD/YYYY (Mandatory)|
|Step 4B||Indicate the date an employing unit which
was previously inactivate or terminated again
meets the definition of employer in the state
unemployment compensation laws.
|Date – MM/DD/YYYY
(Mandatory if Liability Date
“as of” Date
|Step5||Indicate the effective date for the termination
or inactivation status of the employer.
|Date – MM/DD/YYYY|
|Step 15||Indicate the date on which an account was
established on state’s system for an
“employer,” under the state unemployment
|Date – MM/DD/YYYY|
|Step 7B||Indicate the number of consecutive quarters
between the date the employer was last
activated on state’s system and the report
quarter. If the number of liable quarters is
eight or more, the value should be reported as eight.
|Number – 0
|Step 7A||Total wages for the employer in the quarter
prior to the report quarter. Field is unlimited
|Number – 000000000000.00
|Step 7A||Total wages for the employer in the second
quarter prior to the report quarter. Field is unlimited length.
|Number – 000000000000.00
|Step 7A||Total wages for the employer in the third quarter prior to the report quarter. Field is unlimited length.||Number – 000000000000.00
|Step 7A||Total wages for the employer in the fourth quarter prior to the report quarter. Field is unlimited length.||Number – 000000000000.00
|Step 7A||Total wages for the employer in the fifth quarter prior to the report quarter. Field is unlimited length.||Number – 000000000000.00
|Step 7A||Total wages for the employer in the sixth quarter prior to the report quarter. Field is unlimited length.||Number – 000000000000.00
|Step 7A||Total wages for the employer in the seventh quarter prior to the report quarter. Field is unlimited length.||Number – 000000000000.00
|Step 7A||Total wages for the employer in the eighth quarter prior to the report quarter. Field is unlimited length.||Number – 000000000000.00
|18||User Field||User-defined field.||Text (Optional)|
SAMPLE EXTRACT FILE FOR POPULATION FOUR
Once the file is arrayed according to the standard format it can be imported into the UI Tax Data Validation software. Instructions for how to import the file can be found in the User Guide. The software will screen each record in the file, reject it if it is invalid and assign it to a proper counting category if it is valid. Module 2 discusses the data tests that occur as the file is imported.
Once the extract file for each population has been imported, the UI Tax Data Validation software organizes each population into its component subpopulations. These subpopulations are designed to correspond to the basic counting categories for reporting. For example, the extract file imported into the software for Population 5 will contain all field audits completed during a report quarter. The population will be automatically divided into subpopulations such as large employer audits with “no change” because the Federal report counts the number of large employers audited and the number of those audits that resulted in changes to an employer’s contribution report.
Even though states can allow the UI Tax Data Validation software to sort populations into subpopulations, it is important that they understand how the software makes those assignments. Subpopulations are the basic counting categories that will be used to judge whether state reports are valid.
Understanding the concept of subpopulations will also help you understand the population’s contents. Remember that the records included in each subpopulation are mutually exclusive. Each transaction is to be listed in one and only one subpopulation. The population is a combination of the records from all subpopulations. By examining the specifications for each subpopulation, state staff can understand the pieces that make up the population as a whole, and they can do so one piece at a time.
The structure of the subpopulations is specified in tables contained in Appendix A, Part II. Exhibit 1.5 is a copy of the first page of the reconstruction file specifications for population 1, Active Employers.2 It may be helpful to walk through the key features of the specifications, by the numbers.
Reconstruction File Specifications, Active Employers
Timing. The ETA 581 report is a snapshot of performance at one point in time. Like the well-advertised “Kodak moment,” if you miss the moment, the picture may be gone. Depending on the state’s system, employer records may be continually changing as employers are terminated or added to the rolls, payments and adjustments modify account balances, long-delinquent reports finally show up, and so forth. When this happens, state data systems may overwrite earlier records, making them inaccessible.
EXHIBIT 1.6 QUARTERLY TIMELINE
States should produce the RV reconstruction files (i.e., extract files) at the same time they produce the ETA 581 report. This will eliminate the possibility that validation counts will differ from the report simply because transactions were added to or removed from the employer account in the interval between running the ETA 581 report and the data validation reconstruction. If there is a slight difference in the timing of the two runs, the data “as of” the time when the ETA 581 report was run can sometimes be reconstructed if the state has a complete audit trail. Theoretically the validator could use the audit trail to verify that a transaction was correct at the time of reporting. However, this would be cumbersome. It is better to download data simultaneously for reconstruction files and the ETA 581 report.
Capture files. There is another, more troublesome problem related to the production of the RV files — certain transactions will be overwritten or changed in some state databases. Thus, if the original record is not captured and saved before it is overwritten, it will disappear and will not be available to validators. In data validation, overwritten transactions are a problem primarily in two populations:
It may be necessary to create special capture files to ensure that the reconstruction files have access to all transactions. These new files will capture and store all transactions for each employer account. By capturing this information, states can maintain records of status determinations or other overwritten transaction types even if the transactions are later canceled, adjusted, or superceded on the main tax system.
If states need to develop special files to capture an audit trail of all transactions, the capture program must run for one full reporting period before validation can be done.
Listing the reconstruction files. The UI Tax Data Validation software will produce complete reports for each reconstruction file. The state validator and the Federal validation auditor may view these reports on screen.
At this point in Module 1, you have completed the instructions for producing report validation (RV) files. Before continuing with the discussion of Module 1, it may be helpful to review the steps the validation team will need to complete as the RV files are constructed. That review is presented as Exhibit 1.7.
TASK 2: CREATING RECONSTRUCTION (RV) FILES
|Review specifications, product requirements, and schedules.||ADP, Validators|
|Convert handbook specifications into programming specifications.||ADP|
|Develop “capture” programs if needed.||ADP|
|Develop and run file extract programs.||ADP|
|Import extracted files into the UI Tax Data Validation software. Review errors both by referring to error reports and by inspection of listings.||Validators|
|Modify programs to correct any problems identified.||ADP|
|Schedule data validation extract programs to run at the same time ETA 581 programs are executed. Also arrange to print any screens needed for Module 2 at the same time.||Managers, ADP, Validators|
The UI Tax Data Validation software produces five reports in spreadsheet format comparing ETA 581 reported counts and validation findings. The spreadsheets compare subpopulation counts with Federal report item counts and the sort counts for each of the five transaction populations.4
The spreadsheet automatically calculates validation counts from the report validation files. The values from the Federal reports must be entered manually by state staff. They do so using the Enter Reported Counts screen available through the UI Tax Data Validation software (go to File – Report Validation). An example of that screen is shown in Exhibit 1.8.
a. Using the Enter Report Counts screen, state staff enter reported counts and dollar amounts from the ETA 581 Contribution Operations report.
b. The UI Tax Data Validation software fills in the counts or dollar totals from each subpopulation on the RV file in the Validation Count/$ column of the RV spreadsheet.5
c. When there is not a one-to-one relationship between the validation counts and the ETA 581 counts, the UI Tax Data Validation software automatically adds or subtracts validation counts for different subpopulations as necessary to make the proper match. For example, counts for subpopulations 2.1 through 2.8 must be added together to match the reported count for resolved reports for contributory employers during a report quarter (ETA 581 item 8).
e. The Count Difference and the Count % Difference between the count from the ETA 581 report and the comparable validation count(s) are automatically calculated at the subpopulation and the population levels. If the Count % Difference is greater than plus or minus 2 percent, the Count Pass/Fail column will indicate “Fail.”6
Exhibit 1.9 is a sample of a portion of an RV file for population 1 (in the UI Tax Data Validation software, go to Report Validation – View Source Table) . Population 1 has two subpopulations and the RV file shows the transactions (employers) that the state programmer included in subpopulations 1.1 and 1.2. Subpopulation 1.1 represents active contributory employers and subpopulation 1.2 represents active reimbursing employers. The software assigned employers to their appropriate subpopulations. There are 38,222 valid transactions listed in subpopulation 1.1 and 793 valid transactions in 1.2.
Exhibit 1.10 is a sample RV spreadsheet generated by the UI Tax Data Validation software for population 1 (go to Report Validation –Report Validation Summary) Here the software has calculated the validation counts of 38,222 for subpopulation 1.1 and 793 for subpopulation 1.2. State staff have entered the reported counts of 38,222 for ETA 581 item 1 and 793 for ETA 581 item 2. When the validation counts are compared to the reported counts for items 1 and 2 on the ETA 581, the UI Tax Data Validation software calculates that there is a count difference of zero percent, and displays, a “Pass” next to each ETA 581 report item.
The RV spreadsheet generated by the UI Tax Data Validation software documents, by type of error, discrepancies between the RV files and the Federal ETA 581 report. This allows the validator to identify trends and systematic errors. The validator should further research any “Fail” indicators to determine the source of errors and should document findings in the Comments column on the RV spreadsheet.
See Module 2.4 for a discussion of required corrective action when validation identifies errors.
4 States should treat the final results as provisional until they have completed Module 2, verifying the accuracy of the reconstruction. The provisional entry will identify any large differences between validation counts and reported counts, thereby alerting validators to potential errors in the program that the state has created to produce extract files. Once Module 2 is completed and the validation data are fully checked, the validator will return to the spreadsheet to enter final values.
Before validators use reconstruction files to assess reported counts, they must assure themselves that those files are as accurate as possible. Module 2 focuses on the elimination of errors that occur as the State builds its reconstruction (extract) files. It discusses the processes used to test that individual transactions have been correctly selected for inclusion in the validation counts.
Module 2.1, Error Detection, reviews key data tests used by the UI Tax Data Validation software as it screens transactions for inclusion in the validation counts.
Module 2.2, File Integrity Validation (FIV), checks that the correct information was brought over from the database to build the reconstruction file.
Module 2.3, Range Validation, presents additional validity tests that examine whether characteristics associated with a transaction are in the correct range for the particular population in which the transaction has been placed.
Once these checks are complete and any problems resolved, the state validation team can be confident that the validation counts are correct. If, in the end, there are differences between the validation counts and the report counts, states will conclude that the reported counts are not valid.
Module 2.4, Corrective Action Plan (CAP), reviews the actions states must take when reported data prove to be invalid.
Module 2.1 tests that all transactions contain complete and usable information, and that all transactions meet the specifications for inclusion in the population extract file to which they have been assigned. All tests described in this section are done by the UI Tax Data Validation software, which automatically eliminates records of any transactions that do not meet established standards. This section describes the basis of each test to assist the state in anticipating and resolving any problems.
Data validation software tests the data in four ways and eliminates data that fail any one of the tests. It tests that:
If a transaction fails to pass any of these tests, it will be removed from the analysis file and not included in the count of transactions. A report will be provided identifying the transactions that failed each test and the reason for failure (See example in Exhibit 2.1). If the UI Tax Data Validation software generates large or systematic exclusions of transactions from the original extract file, the state should review and revise the procedures used to produce the extract file. Any problems must be corrected and another version of the file produced. The new version of the extract file should again be loaded into the UI Tax Data Validation software for testing.1
Similarly, after the extract file is successfully loaded into the UI Tax Data Validation software, a large or systematic difference between the validation count and the count reported on the Federal report may indicate that the extract file needs refinement. In this instance as well, states should review and revise the procedures for creating the extract file, then begin the extract process again.
States should work to correct systematic problems identified by data validation even when the amounts in question appear small. It is expected that the procedures developed now will be used again for data validation in future years. It is easier to correct the problems now while design decisions are fresh in mind.
This section provides details on the parameters used in the screening tests that the UI Tax Data Validation software uses when it determines which transactions belong in each data validation population. The section reviews two sets of parameters: (1) data elements used to identify duplicates, and (2) parameters used to determine whether transactions are included in the file.
A basic tenet of the data validation design is that no transaction or entity should be counted more than once. The UI Tax Data Validation software provided by the U.S. Department of Labor identifies any duplicate transactions as it creates the report validation file. When state ADP staff first build the extract files for data validation, they are instructed to exclude duplicate records. Thus it is not expected that many duplicates will need to be excluded by the analysis software. The test for duplicates is included mostly for completeness.
As the extract file is loaded into the UI Tax Data Validation software, a check for duplicates is conducted.2 After this is completed, the software produces a report that counts the number of potential duplicate cases found and lists each duplicate record. The report lists all data elements present in the extract for each duplicate record. States may want to examine this report closely for two reasons. First, it will help identify the source of duplicates so they can be eliminated systematically in the future. Second, the review may show records that are identical on the test data elements but different elsewhere. This will allow the state to identify apparent duplicates that are really legitimate separate records.
Exhibit 2.2 lists the data elements that are examined to determine whether transactions are duplicates. For each set of elements, the exhibit also lists the criteria for review of duplicates.
CRITERIA FOR IDENTIFYING DUPLICATE TRANSACTIONS
|1. Active Employers||Employer Account Number (EAN)||No employer should be counted twice. Since each employer should have a unique EAN, no EAN should appear twice. (Multi-unit employers are counted as one employer.)|
|2. Report Filing||EAN,
Employer Report Quarter (ERQ)
|Each employer owes only one report for each quarter. The EAN should not appear twice for a single ERQ. (If an EAN has reports for multiple employer report quarters (ERQs), only the report for the ERQ immediately preceding the report quarter (RQ) is countable. Reports from multi-unit employers are counted as one report.)|
|3. Status Determinations||EAN,
Status Determination Type Indicator,
Status Determination Date,
Status Determination Processing Date
(if different from determination date),
Predecessor Account Number
|No single status determination transaction should appear twice. Individual EANs may appear more than once. For example, there might be two transactions listed for a single EAN if an employer acquires two businesses at different times during the quarter, resulting in two successorship determinations. Multiple determinations may be legitimate, as long as they do not reflect clerical errors.|
|4. Accounts Receivable||Established Date, Employer Report Quarter (cont.), Balance at end
of Quarter, Due Date (reimb).
Transaction Date, Transaction Type,
(established, liquidated, uncollectible)
|No transaction should be listed more than once. ADP staff should ensure that the extract file does not include duplicate transaction. Currently, the UI Tax Data Validation software does not check for duplicates in Population 4.
Two transactions of the same amount, type, date and original quarter due will be assumed to be duplicates. No employer’s account balance should be listed more than once. Two transactions established in the same quarter, for the same quarter, and with the same balance will be assumed to be duplicates.
|5. Field Audits||EAN,
|The same employer ID and Audit Identification Number should not appear twice.|
As it loads information from the extract file that the state has produced, the UI Tax Data Validation software checks that transactions are within allowable ranges. Most often this involves tests that reported transactions took place within the report quarter. The software will produce a list of all transactions that do not fall within the allowable range. States should review the report and, as necessary, correct their procedures for creating their extract files.
Exhibit 2.3 lists the parameters that determine whether transactions are included in each population. The UI Tax Data Validation software checks that all transactions meet these parameters and then assigns each transaction to its subpopulation. The specifications for assignment to subpopulations appear in Appendix A, Part II.
PARAMETERS FOR INCLUSION
TESTED BY UI TAX DATA VALIDATION SOFTWARE
|Population 1: Active Employers|
|Population 2: Report Filing|
|Population 3: Status Determinations|
|Population 4: Accounts Receivable|
|Population 5: Field Audits|
Module 2.1 reviewed data checks that were automatically performed on the data validation extract files produced by the state. Module 2.2 presents several additional tests. Completion of these tests requires action by state validators. The primary test is File Integrity Validation (FIV). State staff examine data items in the validation files to assure that they accurately represent information in the state data system.
Module 2.2 checks whether each transaction in the extract file, and each data element listed with the transaction, is an accurate representation of the information in the state UI contributions database. Remember, this is a test of whether the extract file has been created accurately. State validators compare each data element in the transaction record to the source information in the state database. This ensures that the correct data have been used to assemble the record. The review is done in a series of simple steps.
1. Obtain a copy of the FIV Samples Worksheet listing the data items for two transactions in each subpopulation.
The sampling function of the UI Tax Data Validation software should be used to select two transactions from each subpopulation (Go to FIV/DEV – FIV Samples worksheet). For each of these transactions, the software will provide an FIV Samples Worksheet listing all data items for that transaction (see example in Exhibit 2.4). For each data element in these two transactions, the validator will compare the entry to source documents. Based on that comparison, the validator will record whether the entry matches what is in the state database.
The FIV validation check can be done using a very small number of transactions because the process that states use to build the extract files is highly automated. Automated processes are repetitive. If, for example, a certain field in the employer history file is extracted and placed in the fifth column of the reconstruction file for one transaction, that same field will be used for the fifth column of every transaction. Thus, if we know that all data elements have been transferred correctly for the two transactions, we can be reasonably assured that all similar transactions are done correctly.
2. Validate the selected transactions on the worksheet by checking each item (column) against the corresponding field on the database screens printed from the employer master file.
The validator compares each characteristic listed for the two selected transactions to supporting documentation in the state’s database. By checking the data in every column the validator ensures that the full complement of data in the reconstruction file is accurate. The source data can be found by referring to query screens from the state data system. These screens display information on transactions and the status of employer accounts. It is strongly recommended that the necessary screens be printed at the time the reconstruction file is originally created.3
3. Follow the “step” number in the column heading of the reconstruction printout to find the appropriate page in the state-specific segment of this handbook (Module 3).
Exhibit 2.5 is a sample page of Module 3. For each step listed in Module 3, File Integrity Validation Instructions are provided. These instructions help the validator locate and compare specific pieces of information from the supporting documentation with the corresponding data on the reconstruction file, and to determine the validity of the information (pass or fail).
The instructions for each step or substep identify the supporting documentation (screens and fields) that the validator will need to examine. A set of logic tests, called validation rules, determines the accuracy of each characteristic of a given transaction. A subsection, called function, explains the purpose of each rule.
Definitions listed within each step in Module 3 give the Federal definition of the item being validated. This definition is followed by further information on the data element – examples, includes (situations falling within the definition), and excludes. Where state and Federal definitions differ, be sure to follow the Federal rules as required by the reporting instructions.
Definitional Issues describes known discrepancies between state and Federal definitions. This section serves an important role in systematically documenting validation issues in advance, letting validators and auditors know when problems are anticipated. State staff were interviewed during the design of data validation. Known issues were listed at that time and additional issues will be added by states as they are identified during the validation process.
Comments provide additional information that state staff or Federal auditors may need in order to handle unusual situations.
4. Using the Pass/Fail column of the FIV worksheet record whether each data element passes (matches the source file) or does not pass.
Put a zero (0) on the review sheet next to each data element that successfully passes a step. Place a one (1) if a data element does not pass the step.
Based upon the pass/fail entries, the worksheet will provide an item-by item count of the number of data elements that failed.
5. If the FIV process shows errors, reprogram the report validation file.
The reconstruction file is the basis of all validation exercises and must be proved valid before proceeding any further.
6. Save the printout with 0 or 1 (pass/fail) entered next to each validated item.
They will be used during the Federal monitoring review of the validation process.
Module 2.2 provides tools to test that the data used to create RV files accurately reflect the information in the state’s UI database. Exhibit 2.6 summarizes the steps in the File Integrity Validation Process.
TASK 3: FILE INTEGRITY VALIDATION
|Secure File Integrity Validation Samples Worksheet listing all data elements for two records from the RV file for each subpopulation.||Validator, ADP|
|Produce necessary query screens at the same time reconstruction file is created.||Validator or ADP|
|Following the steps indicated in Module 3, review and validate every item (column) on the printout for the two selected records. The review compares information listed in the reconstruction file to source documentation, typically query screens on the UI database.||Validator|
|Record the results on the File Integrity Validation screen of the UI Tax Data Validation software.||Validator|
|If invalid data were used in the creation of the reconstruction file, correct the file and begin this task over again. This is obviously a step that should be done well before the scheduled date for the actual validation.||ADP|
|If the first Report Validation file is incorrect, conduct File Integrity Validation for corrected versions of the RV file.||Validator|
Module 2.3 presents additional validity tests. They examine whether characteristics associated with a transaction are in the correct range for the particular population and subpopulation in which the transaction has been placed.
The UI Tax Data Validation software assigns transactions to subpopulations. It does so using the generic codes used by all states, e.g. ‘C’ to mean contributory. As discussed in Module 2.1 the software also conducts automated edit checks. The tests ensure that, based on these generic codes, all transactions are listed in the correct population.
In some states there are additional data that can be used to determine whether data elements are in the correct range.
Subjectivity Reason Codes
This exhibit is an actual listing, now out of date, of codes a state used to indicate the reason employers were subject to the provisions of unemployment insurance law. In data validation these would be the codes to which the state would refer to identify that a status determination was as a ‘new’ employer or as a ‘successor.’ This state had an equally long list of codes indicating inactivations and terminations.
|02||Employment 13th week|
|04||Whole Successor allowed|
|07||Revived with new number|
|11||Whole successor denied (No notice)|
|12||Whole successor denied (Predecessor delinquent)|
|13||Consolidation denied (No notice)|
|14||Consolidation denied (Predecessor delinquent)|
The additional data can be examined as another test of whether the data validation files have placed the record in the correct subpopulation.
This additional information can help validators determine whether the validation files are built correctly. However, the DOL-provided software cannot use this information since the meaning of codes is different in each state. States must conduct the range validation tests following the methodology listed below. Range validation is to be done only in states that have relevant codes that provide more information than the generic codes.
The validation approach involves sorting the records in the extract file so state validators can easily see if data are in the correct range. Once the records are sorted it is easy for the validator to spot records that are out of range. Sorting is easy to do since it can occur at a click of a button once the file is imported into the UI Tax Data Validation software.
Methodology.1. Examine the range validation criteria in Exhibit 2.8. For each potential sort look at the column entitled “When to Do this Range Validation Sort” to determine if the sort is applicable in the state. The sort will only be used when the state data provide more information than the single generic indicator. 2. To begin range validation, log into the UI Tax Data Validation software using the population to be reviewed. Click the tab labeled “Report Validation” then the tab labeled “View Report Validation Table.” A summary report validation table will appear. 3. Using Exhibit 2.8, identify the subpopulations to test (under the “Subpopulations Sorted” column) and the data element by which the file will be sorted (under “Test data element”).
4. Apply the “test criterion” from Exhibit 2.8 to identify whether or not any transactions are out of range.
5. If problems are identified, correct the extract file.
6. Record the results of each sort using the UI Tax Data Validation Software. To do so click the "FIV/DEV" button on the menu bar. Then click the button that indicates "Enter Data Element Validation Counts." Enter the number of records you have sorted and the number of errors found. The results will be displayed in the Summary and Analytic Report.
7. Save a copy of the first two pages and last page of each sort conducted. These materials will be used in the Federal review of the validation process.
Validation is not an end in itself; it is a means toward correct reporting. If validation identifies reporting errors, the state should correct them as soon as possible.
Corrective Action Plan. To document the steps required for corrective action and the timetable for completion, the state must provide to its ETA Regional Office a brief Corrective Action Plan (CAP) in accordance with the annual State Quality Service Plan (SQSP) containing the following information on every validated report element found to be in error by more than validation limits:
Timing of CAP. The CAP should be submitted within one month of submitting the Validation Summary Report. CAPs are considered additions to the SQSP. If the state is conducting the validation in segments, e.g., Benefits first, then Tax, and a CAP is required based on a segment’s validation results, the CAP should be prepared within a month of the completion of that segment.
Revalidation. Every element in error by more than the stated validation limit must be revalidated the following year. A “clean” validation confirms the success of the corrective action, or, if the state has not completed corrective action, identifies the current extent of error.
Errors Discovered Outside the Validation Process. During the validation process, errors in reporting may be identified that are outside the scope of the validation program. Such errors should be included in the state’s Validation Summary Report in the comments section and included in the CAP if warranted.
Range Validation Criteria
|When to Do Range Validation||Test Data
|Test Criteria||Module 3 References|
|1||S1.1||1.1||When the value of an employer’s account number indicates whether it is contributory or reimbursing||EAN||All EANs must be in ranges allocated to contributory employers||Step 1A|
|1||S1.2||1.2||When the value of an employer’s account number indicates whether it is contributory or reimbursing||EAN||All EANs must be in ranges allocated to reimbursing employers||Step 1A|
|1||S1.3||1.1 and 1.2||When different employer status codes are used to represent different ‘active’ employer statuses||Employer
|All status codes must represent active employers||Step 3A|
|1||S1.4||1.1||When states have more than one code that represents a ‘contributory’ employer type||Employer
|All employer type codes must represent contributory employers||Step 2A|
|1||S1.5||1.2||When states have more than one code that represents a ‘reimbursing’ employer type||Employer
|All employer type codes must represent reimbursing employers||Step 2B|
|2||S2.1||2.1- 2.8||When the value of an employer’s account number indicates whether it is contributory or reimbursing||EAN||All EANs must be in ranges allocated to contributory employers||Step 1A|
|2||S2.2||2.9-2.18||When the value of an employer’s account number indicates whether it is contributory or reimbursing||EAN||All EANs must be in ranges allocated to reimbursing employers||Step 1A|
|2||S2.3||2.1-2.8||When there are multiple codes, other than EAN, to indicate whether the employer type is contributory||Employer type||All employer type codes must represent contributory employers.||Step 2A|
|2||S2.4||2.9-2.18||When there are multiple codes, other than EAN, to indicate whether the employer type is reimbursing||Employer Type||All employer type codes must represent reimbursing employers.||Step 2B|
|3||S3.1||3.1, 3.3||When the state uses more than one code to indicate that status determination type is new.||Status Determination Type||All status determination type codes must represent ‘new’ status determination type||Step 11A|
|3||S3.2||3.4, 3.6||When the state uses more than one code to indicate that status determination type is successor.||Status Determination Type||All status determination type codes must represent ‘successor’ status determination type||Step 11B|
|3||S3.3||3.7||When the state uses more than one code to indicate that status determination type is inactivation.||Status Determination Type||All status determination type codes must represent ‘inactivation’ status determination type||Step 11C|
|3||S3.4||3.8||When the state uses more than one code to indicate that status determination type is termination.||Status Determination Type||All status determination type codes must represent ‘termination’ status determination type||Step 11D|
|4||S4.1||4.1, 4.9||When the state uses more than one code to indicate that the transaction type is establishment||Transaction Type Indicator||All transactions must be establishment of accounts receivable||Step 21A|
|4||S4.2||4.2, 4.10||When the state uses more than one code to indicate that the transaction type is liquidation||Transaction Type Indicator||All transactions must be liquidations of accounts receivable||Step 21B|
|4||S4.3||4.3, 4.4, 4.11, 4.12||When the state uses more than one code to indicate that the transaction type is declared uncollectible||Transaction Type Indicator||All transactions must be accounts receivable declared uncollectible||Step 21B|
1 Building the extract file may require more than one iteration. In preparing the file, states must follow the record layout precisely. To produce final and correct extract files, states may need to make several trial runs.
3 Elements requiring data from multiple fields pose a greater risk of reconstruction error. For example, the reactivation date for status determinations may not come directly from one field in a state’s database, but instead from a combination of a transaction code and a transaction date field. There may be a series of applicable transaction codes representing reactivations. In these instances, the state or region may want to examine the elements in greater detail.
FEDERAL DEFINITIONS AND STATE-SPECIFIC
Tax Performance System (TPS) validation reviews sample selection procedures used by TPS (formerly RQC, Revenue Quality Control). It ensures that the samples drawn to assess status determination and field audit quality are randomly selected from the correct populations.
There are two basic approaches to selecting samples. The first is a conventional interval sample: the programmer (or a utility program) divides the size of the desired sample (say 30) into the size of the population (say 300) and derives the sample interval (every 10th observation). The programmer or the utility program then selects a random start point (in this instance) between 1 and 10 and selects every tenth case from that point. The second approach is to use a sampling utility program that randomizes the file and selects the first 30 observations. This approach is somewhat more difficult to validate, but could involve a review of the sample against the source file or review of the utility program specifications.
Upon completing the review the validators should record their results. Forms for entering findings are provided as part of the UI Tax Data Validation software on the Enter TPS comments screen. There is one form for each of the four universes. An example can be found in Exhibit 4.1.
On the form the validator will find room to enter:
If the sampling method was not correct or was not implemented properly, the validator should discuss the problems with the programmer. If the programmer confirms that the process was incorrect, the validator should record the problems on the TPS comments form. The Enter TPS Comments screen is found on the FIV/DEV menu.
Wage item validation verifies that wage item transactions processed in the report quarter are accurately reported on the ETA 581. This helps ensure equitable funding when this item is used to determine state workload. A wage record is the listing of an individual’s earnings in covered employment. Each individual employee’s earnings are listed by social security number (SSN) and are submitted by employers each quarter. Employers may submit wage records as paper records or as computerized files stored on magnetic tapes, diskettes, CD-ROMs, or files transmitted over the Internet.
Wage item validation tests that the ETA 581 report contains a correct count of wage items processed during the report quarter. Validators test that every wage item is counted and that the count does not include:
Exhibit 5.1 shows an example of a Wage Item Validation Worksheet, listing a number of possible modes of wage item processing in the first column. In this particular state, the validator has chosen only the applicable modes and selected an appropriate time period for each mode. The column labeled “581 Count for Batch” has been filled in with the number of wage items processed in this batch as reported on the ETA 581. Once the validator has recounted the wage items for each of the modes, this number is reported in the column labeled “Recount for Batch.” In this example, the wage items that were electronically transferred and processed using CD-ROMs show no discrepancies between the two counts and are therefore proven to be valid. The recount of the magnetic tape processing, however, indicates a duplicate wage item, and therefore the counts do not match. This requires further research to establish the reason for the miscount and to correct any other errors caused by the use of this mode of processing.
Inclusion Criteria for
Extract Files by Population
Tables for Each Population
REPORT VALIDATION SPECIFICATIONS
As described in Module 1 of the handbook, the first step in the data validation process is to create report validation (RV) files (also referred to as extract or reconstruction files). These files list all transactions of a single type that are to be reported on the ETA 581 report. Each transaction is listed in a single population and in only one subpopulation within the population.
Part I of this appendix defines the inclusion criteria for each population. It specifies the type of employer or transaction that should be included in the population. Tables 1 through 5 in Part II of Appendix A specify how the populations are to be divided into subpopulations. Each row of the table is the specification for a single, mutually exclusive subpopulation. At the end of each table is a written description of each subpopulation. This should help readers orient themselves to the information in the table.
Each column header includes a step number that refers to the state-specific portion of the handbook in Module 3. Validators and programmers should refer to the indicated step number for detailed instructions on how to validate the data in that column, as well as for the definition of the data element. Each specification includes a column and/or row entitled “ETA 581 Item #’s,” which indicates the Item number on the ETA 581 that the count or dollar amount in the column or row is compared with on the RV spreadsheet.
States should reconstruct each population as specified for a recent ETA 581 report quarter (RQ). In addition, states that administer unemployment insurance together with other taxes should capture tax type, to distinguish between the taxes being validated on the ETA 581 and others which are not countable on the report.1
1 Some states may have other unique types of data elements which should be captured in the reconstruction file to facilitate validation. For example, some states may have an indicator for seasonal employers which would be helpful in validating subpopulations 2.7 and 2.15 on Table 2.
RQ ETA 581 report quarter
ERQ Employer Report Quarter (quarter covered by employer’s contribution report)
FDRQ First day of the report quarter
LDRQ Last day of the report quarter
FD(RQ+1) First day of the quarter after the report quarter
FD(RQ-1) First day of the quarter before the report quarter
DD Report due date
A & nbsp; Active
N Newly Liable
C Contributory Employer
R Reimbursing Employer
OBS Observation number
> After the date or quarter specified, e.g., >RQ means “after the report quarter.”
< Before the date or quarter specified, e.g., < RQ means “prior to the report quarter.”
Calculating quarters with a time line: in the example below, if the report quarter being validated is the fourth quarter of 2001, then RQ-8 is the fourth quarter of 1999.
Population 1 should include all employers who were active as of the last day of the report quarter.
To be included:
States should create a reconstruction file of all employers owing contributions or required reports for employer report quarter (ERQ), due in RQ, which were received timely or secured in the RQ, or reported as resolved in RQ+1. The entire population extract can be run at the end of RQ+1.
The reconstruction file includes:
Population 3 includes all status determinations made during the quarter. More than one status determination may be made and reported during the report quarter for a single employer. To be included:
Population 4 includes all accounting transactions made during the report quarter that establish or modify a receivable (past due taxes owed) for an employer account. There may be more than one such transaction for a single employer during the report quarter. To be included:
Population 4 also includes all receivable amounts in accounts that have a balance due at the end of the quarter (Step 26). Amounts in this population include those that were ‘removed from the report’ during the report quarter. Included are accounts that:
Population 5 includes all field audits completed during the reported quarter. To be included an audit must have an audit completion date during the report quarter (Step 30).
Table 1: Reconstruction File Specifications, Active Employers
These subpopulations constitute the unique subgroups of all active employers on the last day of the Report Quarter (RQ) covered by the ETA 581.
Reconstruction should be done at the end of the RQ being validated (when the ETA 581 report program is run).
|1 (Step 1A)||2 (Step 3A)||3 (Step 2A)
|4 (Step 4A)||5 (Step 4B)||6 (Step 5)||7 (Step 15)||8 (Step 7B)||9 (Step 7A)|
581 Item #’s
|Employer Account # (EAN)||Employer
Terminated “as of” Date
|Sum of Wages
(Last 8 Q’s)
|1.1||1||A||C||<=RQ||<=RQ||>RQ, or < liability date (reopen),or none||(If col. 8=8) >$0|
|1.2||2||A||R||<=RQ||<=RQ||>RQ, or < liability date (reopen),or none||(If col. 8=8) >$0|
1) Analysis will treat columns 4 and 5 as a single date, using the most recent. One of the two must be a date earlier than the end of the report quarter.
2) Column 8 reports the consecutive number of liable quarters ending with the report quarter. If the number is > 8, simply list 8.
3) In column 9 add together the reported wages for the last 8 quarters. The record layout for the software specifies the states list all 8 quarters. The software does the addition automatically.
1.1 Active contributory employers liable by the end of the report quarter.
1.2 Active reimbursable employers liable by the end of the report quarter.
Table 2: Reconstruction File Specifications, Report Filing
Report Quarter (ETA 581):
Employer Report Due Date: The Due Date (DD) is defined as the date after which the state can impose penalty and/or interest, whichever is first applicable. It is a state-specific date.
These subpopulations constitute the unique subgroups of all employers owing contributions or required reports during the same ETA 581 report quarter (RQ), which were received timely or secured in the RQ, or reported as resolved in RQ+1. Programmers and validators should note that timely, secured, and resolved are here defined as discrete filing statuses, whereas the ETA 581 reports cumulative counts for these categories. Because of the static nature of the received date, which is a key data element for subpopulations 2.1, 2.2, 2.3, 2.9, 2.10, and 2.11, the entire population extract can be run at the end of RQ+1. The validation counts in subpopulations 2.1, 2.2, 2.9, and 2.10 are compared with ETA 581 counts for the RQ; all subpopulation validation counts are compared with reported counts for RQ+1 (see 581 item # references below.)
581 Item #’s
|Employer Report Q (ERQ)||Employer
|Received Date||Final Assessment
“as of” Date
“as of” Qtr.
|2.1||6,7,(8 in RQ+1||RQ-1||C||<=DD||none|
|2.2||7,(8 in RQ+1)||RQ-1||C||> DD but within RQ||none|
|2.3||8 in RQ+1||RQ-1||C||within RQ+1||none|
|2.4||8 in RQ+1||RQ-1||C||none||w/in RQ or RQ+1|
|2.5||8 in RQ+1||RQ-1||C||none||< RQ-1||within RQ or RQ+1|
|2.6||8 in RQ+1||RQ-1||C||none||>=RQ||>RQ and >liability date, or none|
|2.7||8 in RQ+1||RQ-1||C||none||(RQ-1)|
|2..8||8 in RQ+1||RQ-1||C||none||=col 8 date||=col 6 date||Within RQ or RQ+1|
|2.9||9,10,(11 in RQ+1)||RQ-1||R||<=DD||none|
|2.10||10,(11 in RQ+1)||RQ-1||R||> DD but <=LDRQ||none|
|2.11||11in RQ+1||RQ-1||R||within RQ+1||none|
|2.13||11in RQ+1||RQ-1||R||none||within RQ or RQ+1
|2.14||11in RQ+1||RQ-1||R||none||>=RQ||>RQ and >liability date, or none|
|2.16||11in RQ+1||RQ-1||R||none||=col 8 date||=col 6 date||Within RQ or RQ+1|
1) A few states resolve reports for seasonal employers by suspending the report filing requirement in off seasons (subpopulations 2.7 and 2.15). Other states will have no entry in this column.
2) States may identify all contributory and reimbursing employers who were subject to file a required report covering the quarter prior to the ETA 581 report quarter, on the last day of the quarter prior to the ETA 581 report quarter. That data file can then be used in the validation reconstruction, even though not every report owed will be resolved. (If this approach is workable for states, it can also be done every quarter to program the ETA 581.)
3) Some states may use a delinquency flag instead of the preferred received date; this creates audit trail issues to be reviewed on a statespecific basis.
4) If an employer has more than one resolved date under columns 4, 5, 8, or 9, the software assigns the record to the first subpopulation for which it meets the criteria.
The software assigns records to the first subpopulation for which it meets the subpopulation criteria. Each record is compared to the requirements
for subpopulation 1 and the software determines if the record meets the subpopulation 1 criteria. If it does, the record is assigned to subpopulation
1. If it does not, the software then compares the record to the requirements for subpopulation 2 and determines if the record meets the
subpopulation 2 criteria. This process continues as necessary comparing each record to the requirements for each successive subpopulation.
2.1 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who filed contribution reports timely during the report quarter.
2.2 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who filed untimely contribution reports by the end of the report quarter (secured, but not timely).
2.3 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who filed contribution reports during the quarter after the report quarter (resolved, neither secured nor timely).
2.4 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who did not submit a report but received a final assessment by the end of the quarter after the report quarter (resolved, neither secured nor timely).
2.5 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who were made inactive during the report quarter, or during the quarter after the report quarter (resolved, neither secured nor timely).
2.6 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, whose liability date (met threshold) was changed from prior to the report quarter, to during or after the report quarter (resolved, neither secured nor timely).
2.7 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, who were suspended from filing contribution reports due in the report quarter by virtue of being seasonal employers, an administrative decision not to pursue report filing, for domestic employers who elect to file annually, or for other reasons (resolved, neither secured nor timely).
2.8 Contributory employers owing contributions reports for activities in the quarter prior to the report quarter, whose accounts were withdrawn by making the liability date and the inactive/terminated “as of” date equal (resolved, neither secured nor timely). This includes canceled, withdrawn, closed, dropped, etc. accounts.
2.9 Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who filed required reports timely during the report quarter.
2.10Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who filed untimely required reports by the end of the report quarter (secured, but not timely).
2.11Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who filed required reports during the quarter after the report quarter (resolved, neither secured nor timely).
2.12Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who did not submit a report but received a final assessment by the end of the quarter after the report quarter (resolved, neither secured nor timely).
2.13Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who were made inactive during the report quarter, or during the quarter after the report quarter (resolved, neither secured nor timely).
2.14Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, whose liability date (met threshold) was changed from prior to the report quarter, to during or after the report quarter (resolved, neither secured nor timely).
2.15Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, who were suspended from filing required reports due in the report quarter by virtue of being seasonal employers, an administrative decision not to pursue report filing, for domestic employers who elect to file annually, or for other reasons (resolved, neither secured nor timely).
2.16Reimbursable employers owing required reports for activities in the quarter prior to the report quarter, whose accounts were withdrawn by making the liability date and the inactive/terminated “as of” date equal (resolved, neither secured nor timely). This includes canceled, withdrawn, closed, dropped, etc. accounts.
Table 3: Reconstruction File Specifications, Status Determinations Entered within Report quarter (RQ)
These subpopulations constitute the unique subgroups of all status determinations made by the state during the ETA 581 Report Quarter (RQ). States that overwrite status determinations on their master tax file may use the TPS universe for reconstruction. Programmers and validators should note that time lapse categories are discrete subpopulations, whereas the ETA 581 reports time lapse cumulatively.
(Step 2A) (Step 2B)
|Sub- population||ETA 581 Item #’s||EAN||Employer
|3.1||14,15,16||New||<=90 days||within RQ||within RQ, or < column 9 date||within RQ, or none||< active/ reactivation date, or blank||< active/ reactivation date, or blank|
|3.2||14,16||New||>=91 but <=180 days||within RQ||within RQ, or < column 9 date||within RQ, or none||< active/ reactivation date, or blank||< active/ reactivation date, or blank|
|3.3||14||New||>=181 days||within RQ||within RQ, or < column 9 date||within RQ, or none||< active/ reactivation date, or blank||< active/ reactivation date, or blank|
|3.4||17,18,19||Successor||<=90 days||within RQ||<= successor- ship date||<= successor- ship date, or none||within RQ||non-blank|
|3.5||17,19||Successor||>=91 but <=180 days||within RQ||<= successor - ship date||<= successor - ship date none||within RQ||non-blank|
|3.6||17||Successor||>=181 days||within RQ||<= successor - ship date||<= successor - ship date none||within RQ||non-blank|
|3.7||20||Inactivations||n/a||within RQ||n/a||n/a||n/a||n/a||n/a||within RQa||blank|
|n/a||within RQ||n/a||n/a||n/a||n/a||n/a||blank||within RQ|
a There is the same issue as under Population #1, where the employer could be inactive based on 8 quarters of no wages (or fewer depending on the state’s threshold), but for some reason the inactivation date/flag was not triggered. We may be able to cross-reference by EAN (by programming or on the printout) the employers identified as falling in this category from the Population #1 specifications, since they are identical, as long as the same RQ is validated.
1) States that prefer to validate contributory and reimbursing employer status determinations separately may do so by replicating the eight subpopulations (one set of eight subpopulations for each type of employer). States may prefer to validate the two types of employers separately if they are processed in very different ways.
2) Time Lapse is the difference, in days, between the last day of the liable quarter and the status determination date.
3.1 Status determinations of new employers made during the report quarter, which were made within 90 days of the end of the quarter in which
the employer became liable. (Employers changing from contributory to reimbursing status and vice versa are included in subpopulations 3.1 -
3.2 Status determinations of new employers made during the report quarter, which were made between 91 and 180 days of the end of the quarter in which the employer became liable.
3.3 Status determinations of new employers made during the report quarter, which were made 181 days or later from the end of the quarter in which the employer became liable.
3.4 Status determinations of successor employers made during the report quarter, which were made within 90 days of the end of the quarter in which the employer became liable.
3.5 Status determinations of successor employers made during the report quarter, which were made between 91 and 180 days of the end of the quarter in which the employer became liable.
3.6 Status determinations of successor employers made during the report quarter, which were made 181 days or later from the end of the quarter in which the employer became liable.
3.7 I Inactivations of employers made during the report quarter.
3.8 Terminations of employers made during the report quarter.
Table 4: Reconstruction File Specifications, Accounts Receivable
|Sub- popula- tion||EAN||Employer
Established in RQ
end of RQ
|ETA Item #||22||23||24||25||26|
|ETA Item #||34||35||36||37||38|
Values in column 8 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #22.
Values in column 9 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #23.
Values in column 10 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #24.
Values in column 11 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #25.
Values in column 12 for all observations in subpopulations 4.1 - 4.8 should be totaled, for comparison to ETA Item #26.
Values in column 8 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #34.
Values in column 9 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #35.
Values in column 10 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #36.
Values in column 11 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #37.
Values in column 12 for all observations in subpopulations 4.9 - 4.16 should be totaled, for comparison to ETA Item #38.
4.1 Receivable amounts established as past due in the report quarter for contributory employers.
4.2 Receivable amounts liquidated during the report quarter for contributory employers.
4.3 Receivable amounts declared uncollectible during the report quarter for contributory employers where the receivable is less than eight quarters old.
4.4 Receivable amounts declared uncollectible during the report quarter for contributory employers where the receivable is more than seven quarters old but was established within the report quarter or the two preceding quarters. The establishment date parameter is used to confirm that these transactions have not yet been removed.
4.5 Receivables removed during the report quarter for contributory employers where the receivable is eight quarters old and was established prior to two quarters before the report quarter.
4.6 Receivables removed during the report quarter for contributory employers where the receivable was at least eight quarters old and was established two quarters prior to the report quarter.
4.7 Receivable balances at the end of the report quarter for contributory employers which were less than eight quarters old. (The receivable was not yet old enough to be removed.)
4.8 Receivable balances at the end of the report quarter for contributory employers which were more than eight quarters old but which were established within the report quarter or the preceding quarter. (The receivable is old enough to be removed but is not removed because it has not yet sat for 2 quarters in the ‘greater than 15 months’ aging category.)
4.9 Receivable amounts established as past due in the report quarter for reimbursable employers.
4.10 Receivable amounts liquidated during the report quarter for reimbursable employers.
4.11 Receivable amounts declared uncollectible during the report quarter for reimbursable employers where the receivable is less than eight quarters old.
4.12 Receivable amounts declared uncollectible during the report quarter for reimbursable employers where the receivable is more than seven quarters old but was established within the report quarter or the two preceding quarters.
4.13 Receivables removed during the report quarter for reimbursable employers where the receivable is eight quarters old and was established prior to two quarters before the report quarter.
4.14 Receivables removed during the report quarter for reimbursable employers where the receivable was at least eight quarters old and was established two quarters prior to the report quarter.
4.15 Receivable balances at the end of the report quarter for reimbursable employers which were less than eight quarters old. (The receivable was not yet old enough to be removed.)
4.16 Receivable balances at the end of the report quarter for reimbursable employers which were more than eight quarters old but which were established within the report quarter or the two preceding quarters. (The receivable is old enough to be removed but is not removed because it has not yet sat for 2 quarters in the ‘greater than 15 months’ aging category.)
Table 5: Reconstruction File Specifications, Field Audits
These subpopulations constitute the unique subgroups of all field audits completed during the ETA 581 Report Quarter (RQ). Data elements specified on the file specification may not be captured on the state’s system when they are not reported on the 581. They are however included in the auditor’s file. When states cannot capture such information automatically, the column can be completed from the auditor’s paper files during the validation for the selected cases.
|Total Wages||Taxable Wages||Contributions|
|5.1||45, 46, 47||L||Y||within RQ||T1$||T2$||T3$||T4$||0||X1$||X2$||X3$||X4$||0||C1$||C2$||C3$||C4$||0|
|5.2||45, 47||L||N||within RQ|
|5.3||46, 47||S||Y||within RQ|
1) Some states may want to capture and store in the data file the number of employees pre-and post-audit. Some states allocate a percentage of their UI receipts to special funds or programs; if so, the employer’s discount rate and amount discounted should be included on the printout.
2) Post audit figures for total wages, taxable wages and contributions reflect the net increase or decrease of under and over reporting identified during the audit, even though the netted figures are not reportable on the ETA 581. Referring to the specification:
Subtract the positive net of (T3 - T4) from the positive net of (T1 - T2). The result in column 10 should be zero.
Subtract the positive net of (X3 - X4) from the positive net of (X1 - X2). The result in column 15 should be zero.
Subtract the positive net of (C3 - C4) from the positive net of (C1 - C2). The result in column 20 should be zero.
If TI = $10,000, T2 = $14,000, T3 = $5,000, T4 = $1,000, then ($10,000 - $14,000) - ($5,000 - $1,000) = 0.
Also, if TI = $10,000, T2 = $6,000, T3 = $1,000, T4 = $5,000, then ($10,000 - $6,000) - ($1,000 - $5,000) = 0.
Dollar values in column 6 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #49.
Dollar values in column 7 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #50.
Dollar values in column 8 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #53.
Dollar values in column 9 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #56.
Dollar values in column 13 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #54.
Dollar values in column 14 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #57.
Dollar values in column 18 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #55.
Dollar values in column 19 for all observations in all four subpopulations should be totaled, for comparison to ETA Item #58.
5.1 Large employer audits completed during the report quarter, which were change audits.
5.2 Large employer audits completed during the report quarter, which were not change audits.
5.3 Small employer audits completed during the report quarter, which were change audits.
5.4 Small employer audits completed during the report quarter, which were not change audits.
APPENDIX C IS ONLY APPLICABLE TO POPULATIONS FOR WHICH THE STATE HAS PRODUCED THE RV FILE FROM THE SAME
EXTRACT FILES USED TO PRODUCE THE ETA 581 REPORT.
The validation exercises described in Modules 1.1 and 1.2, and those outlined in Module 2, address the validation of all UI contributions transactions that have been included in the ETA 581 report. However, it is also important to confirm that no transactions have been improperly or systematically excluded from the Federal report. Although this problem is a difficult one, it is important to ensure that funding, economic statistics, and performance outcomes are not biased by the systematic elimination of particular types of transactions.
This module is not applicable for states that produce the RV file directly from the employer contributions database, because the RV process itself constitutes an independent count through the process of reconstruction. When the RV file is produced from the same file used to produce the ETA 581 report, it is necessary to conduct an independent count in order to identify any errors that may have occurred in the ETA 581 report since these errors will be duplicated in the reconstruction file.
It is also not possible to perform an independent count when the database does not contain all of the reported transactions. In these circumstances, the statistical file is the only source of data to reconstruct reported counts on the ETA 581 report. It is unlikely that any state will need to perform an independent count for 581 validation as explained in Exhibit C.2 (it is more relevant to validating Federal benefits reports). This procedure is included in this handbook to ensure that states are aware of the possible problems with using statistical files for both reporting and validation when database files could be used.
B. MATERIALS (ADP STAFF)
1. Independent Count Files
ADP staff create independent total counts of transactions from the main database for comparison with counts generated on the extract files used to create the ETA 581. In general, the independent count is created opposite to the way the RV file is created. The RV file should be programmed from the bottom up, by selecting only the codes and criteria indicated on the file specification in Appendix A. However, the independent count should be programmed from the top down, by including all codes relevant to a population and then subtracting observations related to those not indicated on the file specification.
Exhibit C.1 indicates when independent count validation is required. There are six typical scenarios for how states produce the ETA 581 report and reconstruct counts for validation. The ETA 581 Report Source column indicates for each scenario the source files that states use to generate report counts. States may use different source files for different types of transactions. The Validation Reconstruction Source column indicates for each scenario the source files that states use to reconstruct lists of transactions for validation.
The Independent Count Required column of Exhibit C.1 indicates whether the state should conduct independent count validation for transaction types that match the report and validation scenario.
Exhibit C.2 describes independent count criteria for each population.
Exhibit C.3 shows a spreadsheet to record the results of an independent count if one proves necessary.
ETA 581 REPORTING AND VALIDATION CONFIGURATIONS
|Scenario||Transactions Overwritten on Database||ETA 581||Data Validation||Indepen- dent Count Required|
|Program Type||Source||Timing||Program Type||Source||Timing|
|1||No||Count||Database||Snapshot (for reporting period)||Detail Record Extract (DRE)||Database||Snapshot||No|
|3||No||DRE||Database||Snapshot (for reporting period)||DRE||Database||Snapshot||Yes|
|4||No||DRE||Stat file||Daily||DRE||Stat file||Daily||Yes|
|5||Yes||DRE||Stat file||Daily||DRE||Stat file||Daily||NA|
|6||Yes||Count||Stat file||Daily||must create a daily extract||NA||NA||NA|
EXHIBIT B.2 INDEPENDENT COUNT CRITERIA, BY POPULATION (USING QUERY CAPABILITY)
|Population Description||Independent Count Criteria|
|1 Active Employers||States should not use statistical files to validate active employers because the count should be taken from the database as a snapshot at the end of the month. If states do not use this approach for reporting (if they instead derive the number from changes in status over the quarter), they must use it for validation (they cannot recreate the active employer population from the status changes). Therefore, there is no situation which would require an independent count.|
|2 Report Filing||States generally use data files containing a record for each employer quarter for both reporting and reconstructing counts of employer report statuses. Therefore, there is not likely to be a situation where statistical files are used for reporting or validation. If a state uses a statistical file for validation, it should create a frequency distribution of received dates for every employer with a received date for the quarter being validated. This count can be used to validate that the statistical file data matches the data base for all timely and secured reports and for all reports which are resolved by receipt of report. This will validate subpopulations 2.1, 2.2, 2.3, 2.9, 2.10 and 2.11, which will be sufficient to demonstrate that the statistical file is valid.|
|3 Status Determinations||States often use statistical files for reporting status determinations when their system stores only the most recent status determination for each employer account and thus overwrites or overlays some status determinations. These files are often called “RQC files” because they were developed to provide a universe of determinations from which to derive the RQC sample. These states cannot perform an independent count from the database to validate the statistical file because the database will not contain records for all of the status determinations. Therefore, an independent count is not required for status determinations, because it is not possible to create such a count in states which use statistical files.|
|4 Accounts Receivable||All states must use a transaction history file or audit trail to correctly reconstruct payments (amounts liquidated), because only such files show the date that each payment was made. Transaction history files are also the source for receivable amounts established and amounts declared uncollectible in some states. There is only one source file for such transactions, so an independent count is not relevant. All states must use “employer quarter files” to reconstruct balances for reporting amounts removed and amounts outstanding at the end of the quarter. Some states use such balances for reporting amounts declared uncollectible. These balances are always captured as a “snapshot” at the end of the quarter from the database, so an independent count is not relevant.|
|5 Field Audits||States do not maintain more than one file with field audit results, thus an independent count is not relevant.|