Data Validation Module 4 Benefits
Overview • Concept • Tasks • Universe Files • 9052 /9054L Reports • Randomization methods • Reporting Results
Concept • Correct Sample Size • Correct Universe • Selection was Random • BTQ non-mons & appeals selection • Tax has similar Mod 4 for TPS DV Module 4 ensures that the samples selected for nonmonetary determinations and lower authority appeals quality have been randomly selected from the correctly defined universe.
ADP (IT) Staff Creates Universe Files • ADP (IT) staff creates universe files • Non-Mons & Appeals • Plain delimited text file • Observation numbers and ssn’s • Seps / Non-Seps • Run at the end of the quarter • Same timing as the 9052 / 9054L • 3 years if passing, otherwise 1 year • Population 5 & 8 must pass Data Validation
Sample Size • Verify correct sample size • 100 or 60 • Non-mon count for last calendar year • 100,000? • Add totals of Sections A & B • Intra and Inter-State • Obtain values from the State Menu • Or use SQL Select sum(c1 + c5 + c97 + c101)From ar9052Where rptdate between “01/01/2009” and “12/31/2009”
Correct Universe • Compare number of non-mons reported on the 9052 for the quarter to the number in the universe file. • Compare number of appeals reported on the 9054L for the quarter to the number in the universe file.
Correct Universe, cont. • Determine what was reported • Obtain values from the State Menu • 9052 for non-mons • 9054L for appeals • Or use SQL Select sum(c1 + c5 ) seps , sum(c97 + c101) nonsepsFrom ar9052Where rptdate between “07/01/2009” and “09/30/2009”
Correct Universe, Part 2 • Did populations 5 & 8 pass Data Validation? • Determine the number in the universe file • Open the file in a spreadsheet • Count the rows / exclude headers The appeals universe file may have some appeals removed. Some ADP (IT) shops remove the appeals that don’t belong in the sample but are counted on the 9054L such as when no testimony was taken. The programmer must then obtain a count for you of those excluded for this reason.
Correct Universe, cont. • Compare the 2 values • Within 2% of reported • Formula: • Example Universe - reported = difference Difference / reported = Percent Different from reported 14672 –14650 = 22 22 / 14672 = .0014994 = .015%
Step 3 • Ask ADP (IT) staff how the random selection is made • Randomized file • Interval
Step 3, Randomized • A random number is assigned to each transaction. • File is sorted by the random number • Look for non-random patterns
Step 3, Randomized File • No systematic review process is possible • Observe the file and look for non-random patterns • Consecutive numbers • All even numbers or all odd numbers • Other patterns in the columns • Compare to file before randomization
Step 3, Interval • Determine sample interval • Universe size / sample size =N • Determine starting number • Random number provided by DOL in December • Random num * sample interval • Round to nearest integer • Select every Nth transaction starting with the random starting number
Step 3, Interval Sample • See the that the correct cases were selected • First Case randomly selected • Every Nth case selected • Match up observation numbers with those in the sample section; prior to importing to the 9056/9057
Step 4 • Mainly done in Part B • For Appeals: • Were withdrawals, dismissals and no-shows removed from the universe? • Will not match the 9054L • Add the number of those excluded
Report Results • DOL Template: Obtain an MS Word template at: www.tc.state.mn.us/online/dvmod4/mod4template.doc
Results Example Email Results to National Office at email@example.com.
Correcting Failures • Problems with the universe • Reconstruct universe • Re-do Mod 4 next year • Problems with the random selection • Correct and re-do selection prior to BTQ • Re-do Mod 4 in three years
Review • Obtain universe file from ADP • Learn how the random sample was selected • Compare what was reported on the 9052/9054L to the number in the universe file • Verify selection was random • Report Results. Email to the National Office at firstname.lastname@example.org. • DOL’s Module 4 website: http://www.ows.doleta.gov/dv/pdf/benmod4.pdf