DEPARTMENT OF THE TREASURY BUREAU OF ALCOHOL, TOBACCO AND FIREARMS FEB 9 1996 S:A:DVG 5320 MEMORANDUM TO: Chief, Firearms and Explosives Division FROM: Chief, Audit Services Division SUBJECT: National Firearms Act (NFA) Database Audit This to convey to you the results of the audit done of the NFA database. Attached documents are: Attributes Sampling Data Summary An Explanation of Sampling - 3 pages Attributes Sampling Data Summary - Non Critical Name Errors Summary NOTE:CONTAINS 6103 MATERIAL Four pages statistical sampling parameter tables The audit of the NFA database was designed by Audit Services Division personnel, in conjunction with Gary Schaible and Terry Cates of F&E Division, and John Elliot, a contractor with Information Services Division, and performed by NFA Branch personnel. We reviewed all workpapers and reperformed the comparison of names from the database to the registration documents for all 350 sample elements. Our conclusion is that the Test performed conclusively supports the reliability of name searches in the database and related searches for supporting registration documents. [signed] Dexter V. Gould, CPA [page 2] NFA Owner Name Review Attributes Sampling Data Summary Client: ATF Period Covered: N/A Sampling Objective: Test if an NFA name search would find the name being searched and then if the search would find supporting registration documents. Population Size: 982,471 Population Description: The number of weapons in the NFA database as of 2/2/96, based on the Weap_Serial field. Sampling Unit: Each database record. Random Sample Size: 325, plus 25 replacement samples, for a total of 350 items sampled. Selection Procedure: Download the current NFA database into an Access database for sequential numbering. Select the samples, re-sort the database, and print a list of the samples selected. Attributes Tested Planned Sampling Parameters Definition of Error Confidence Acceptable Expected Sample Level UPL (a) Error Size Rate 1. Search would not find 99% 2% 2% 325 a record because of a mis-entered name, searching on the first 3 characters of a name, considered a critical error. 2. Search found non- 95% 10% 5% 114 critical error, other than the first 3 name characters, such as errors in other portions of the name field. 3. Search did not find 99% 5% 2% 325 a supporting document. Attributes Tested Actual Results Definition of Error Sample Occurrence of Errors Size No. Rate (b) CUPL (a) 1. Search would not find 350 0 0.0% 1.5% a record because of a mis-entered name, searching on the first 3 characters of a name, considered a critical error. 2. Search found non- 350 13 3.7% 6.8% critical error, other than the first 3 name characters, such as errors in other portions of the name field. 3. Search did not find 350 0 0.0% 1.5% a supporting document. Sampling Results Attribute 1: No critical errors found, based on a search using the first three name characters. We have a 99% confidence that the NFA database name searches would result in finding records with no more than a 1.5% error rate, based on the sampling performed. The actual rate of critical errors was zero. however the statistically computed maximum rate of occurrence is 1.5%. Attribute 2: Thirteen non-critical errors were found, which were 3.7% of the 350 samples reviewed. The NFA staff's search found supporting registration documents for these 13 with non-critical errors during our review, which are summarized on an adjacent document. We have a 95% confidences that the rate of non-critical errors is between 2.2% and 6.8%. Attribute 3: The review found supporting registration documents for all of the 350 samples. We have a 99% confidence that similar searches would be equally successful, with no more than a 1.5% error rate. Conclusion: We consider this name review test as conclusively supporting the reliability of name searches in the database and related searches for supporting registration documents. Notes: File: NFASam.WK4 (a) Abbreviations: Updated: 02/07/96 AUPL: Acceptable Upper Precision Level. 12:26 PM CUPL: Computed Upper Precision Level. Obtain CUPL from a statistical results table. (b) Rate = Actual mathematically calculated rate. Initials Date WP Done by: RJH 02/07/96 Reviewed by: DVG 2/7/96 [page 3] An Explanation of Sampling by Robert J. Holland Sr., CPA Audit Services Division Until the twentieth century, it was not uncommon for an auditor to examine each and every entry and record of an organization. However, it is apparent to all involved that such a 100% review was quite uneconomical and downright wasteful, and more and more difficult to complete as organizations grew in size and complexity. Therefore, early in the 1900's, auditors started examining a portion of records with spot checking as the only efficient solution for still providing a reasonable assurance about the validity of records. Audit testing has evolved into using judgmental sampling, and where appropriate, statistical sampling, which auditors have been commonly using since the 1960's, as shown by The American Institute of Certified Public Accountants' 1962 publication of a special report entitled: "Statistical Sampling and the Independent Auditor," and the Institute of Internal Auditors 1987 manual on statistical sampling entitled: "Sampling Manual for Auditors." Statistical sampling allows one to objectively make quantified statements about a group, or series of items, known as "the population" on the basis of the sample results. The statistical sample is defensible, even before a court of law, since the sample is objective and unbiased by using a random selection, as all entries and records in the population have an equal opportunity for inclusion in the sample. One expects that the sample will be representative of the population, reflecting approximately the same qualities that are present in the population as a whole. However, a sample does not provide absolute assurance that the sample results are exact; the economy of sampling has a risk that can be alleviated only by inspecting 100%, or every item in a population. If an auditor decides to review a sample rather than 100% of an audit population, the auditor then determines the minimum sample size needed to limit the risk of an incorrect conclusion. That audit risk is the risk that an incorrect conclusion may be reached about the population, and what it represents. In statistical sampling, the risk is measured in quantitative terms, based on the level of reliability and desired level of precision. Statistical sampling is used to save considerable time and expense, rather than verifying 100% of the records by direct review. The auditor plans the overall objective, population and sample parameters before performing the actual sample testwork, including deciding what risk is reasonable, and defining what is considered a testing error. The objective is first defined, to help identify the desired sampling unit that will be tested. For instance, the NFA Owner Name Review's objective is to test if an NFA name search would find the name being searched and then if the search would find supporting NFA registration documents. Next, the population and its size is carefully defined, since it contains the sampling units from which the sample will be drawn. For instance, the NFA database population of 982,471 records is the number of weapons in the NFA database as of February 2nd, 1996, based on the count of Weap_Serial data fields. The NFA population was defined and obtained by John P. Elliot, an Information System Division contractor, who was working with the NFA Branch staff. Page 1 of 3 [page 4] The auditor then decides what type of attributes will be tested, and how an error is defined. The attributes tested vary depending on the review's objective, and the nature and type of population being reviewed. For the NFA review, three attributes were tested: o Name search using the first 3 letters of the name field. An error would be where the search would not find a record because of a mis-entered name, searching on the first 3 characters of a name, and is considered a critical error. o Name search using the entire name. An error would be where the search found a noncritical error, other than the first 3 name characters, such as errors in other portions of the name field. o Supporting registration documents. An error would be where the search did not find a supporting registration document. The auditor then specifies and records the planned sampling parameters, which are: o The Confidence Level, known as the reliability, which measures the risk that the actual but unknown error rate will exceed the computed upper precision level. Since in the NFA review, the beginning three characters of the name field is the basis of the search and has a high impact if it is inaccurate, we selected the highest confidence level, 99% for attribute 1. Auditors generally select a 90% or 95% confidence level, since a higher level requires a significantly larger sample size, however, since we were more concerned with critical errors, we used a 99% confidence level. Had we chosen a 90% or 95% instead of 99% confidence level, our sample size would have been 133 or 189, respectively, instead of a sample size of 325, which would have expended a considerably smaller audit effort. o The Acceptable Upper Precision Level (AUPL), which is the maximum tolerable error rate that the auditor will reasonably accept, and is a question of professional judgment, related to the materiality of the type of error. For the NFA review, we considered 2% as an acceptable name search AUPL for attribute 1, since we had only limited experience with the database, yet at the same time considered any errors of the first three name characters to be critical errors for purposes of the test. o Expected Error Rate, needed to select the initial sample size, by using a statistical sampling chart for attribute sampling. For the NFA review, the sample size with a Confidence Level of 99% and an Expected Error Rate of 2% for attributes 1 and 3, and a precision of +/- 2%, is 325 samples, as shown on Table D-3A, page 354 of Herbert Arkin's Handbook of Sampling for Auditing and Accounting Third Edition, copyright 1984. To be conservative, we assumed that we would find some samples that were void entries or otherwise not valid samples that would need to be replaced with another sample, so we added 25 to the sample size, for a total of 350 samples. Page 2 of 3 [page 5] The samples are then randomly selected, using one of many different type of random selection tools, such as a random number table, a sampling program, or a computer program's random generator function. We used the Lotus 1-2-3 computer program's @RAND function to select our samples, after obtaining the population size from John Elliot, and after identifying our sample size as described above. For the NFA review, for instance, the population size was 982,471, so we selected 350 samples between the population range of 1 to 982,471, inclusive. John Elliot then obtained our 350 random samples records from his Access database copy of the NFA database, after he had sequentially numbered each record. The samples were printed in order of selection, and included the random sample numbers. The samples were then re-sorted in microfiche reel order to help the NFA staff search for them. The staff found all 350 samples. We reviewed each of them, and then tallied the critical and noncritical errors. We found that there were no critical errors, attribute 1, as all 350 name search samples were successfully traced to their name in the database. Also, for attribute 3, we successfully traced all 350 samples to their supporting registration documents. However, for attribute 2, testing the entire name field, we found 13 instances where there was a mis-spelling or mis-coding of the name. We consider these as non-critical errors, since both the database name entry and the supporting registration documents were successfully found. We evaluated each of the 13 errors to determine its cause, and its implications on the remainder of the review. We consider these errors non-critical, and have summarized both the errors and how the names should have been entered on an adjacent document entitled: "Non-Critical NFA Name Errors Summary." We summarized the sample results on the Attribute Sampling Data Summary sheet. We then calculated actual rate of occurrence of the errors, by dividing the numbers of errors by the respective sample size. Since attributes 1 and 3 had zero errors, their rate is 0.0%. Attribute 2 had 13 errors, so 13/350 = 3.7% rate of occurrence. We then looked up the CPL, or computed precision level, in Table F of Herbert Arkin's Handbook of Sampling for Auditing and Accounting, Third Edition, copyright 1984. For attributes 1 and 3, Table F-27 at page 469, for a 99% Confidence level, a population greater than 100,000. with a 0% rate of occurrence (no errors found), the CLPL precision is 1.5%. For attribute 2, Table F-4 at page 399, for a 95% Confidence level, a population greater than 100,000, with a 4% rate of occurrence (since 3.7% errors were found), the CPL precision range is 2.2% to 6.8%. This expression of sample precision level gives the maximum, and minimum where applicable, rate of expected errors, at the stated confidence level. The confidence rate is, as stated previously, the risk that the true error rate exceeds the computed upper or lower precision level. In other words, an upper precision level of 1.5% for attributes 1 and 3 means that we are 99% confident that there is no more than a 1.5% error rate for that attribute in the population, or only a one in one hundred chance that there are more than 1.5% errors. For attribute 2, we are 95% confident that there is no more than a 6.8% error rate, and no less than a 2.2% error rate. Based on these results, we concluded that the review test conclusively supported the reliability of name searches in the NFA database. File: Samplexp.96 Updated: February 7, 1996 12:51 pm Page 3 of 3 [The document also includes 4 pages of tables copies from the cited statistics textbook, showing error rates for various populations. The supplement to this report, indicating the nature of the name errors was not supplied, presumably because it contains the names of persons to whom NFA weapons are registered.]