ACC 427 Module 2 Data Acquisition Assignment Example

Before the First Query: Acquiring and Validating 22 Months of Landfill Scale Data

[Student Name]

Southern New Hampshire University

ACC 427: Investigating with Computers

Module Two Assignment

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

Before the First Query: Acquiring and Validating 22 Months of Landfill Scale Data

Introduction

The landfill investigation has a clear question: are cash payments at the scale house being collected and then removed from the records? Answering it requires every scale ticket, every void and the identity of the person who made each change. Before any analysis, the investigators must obtain that data from a source the suspects cannot influence and prove that it is complete. Nigrini (2020) treats this step as the foundation of forensic analytics, because every later test inherits whatever gaps the data contain.

The Data Request

The scale software is hosted by its vendor, which keeps 24 months of records. The investigators, through the company's controller and counsel, asked the vendor directly for four tables covering the last 22 months: the ticket table, with ticket number, date, time, truck, customer type, weight, fee and payment type; the void log, with ticket number, void time, user and reason code; the user table, linking user IDs to employees; and the shift schedule exported from the payroll system. The request specified a delimited text format and a vendor-signed control report giving record counts and fee totals by month. The request did not go through the scale-house supervisor or any operator. Routing it through the controller and counsel also documents that the investigation, not a suspect, controlled what was asked for and what was received, which matters if the data are later challenged.

Receipt and Integrity

The vendor delivered the files by secure transfer. On receipt, the investigator calculated a hash value for each file and recorded it, with the date and time, in the evidence log. Analysis is performed on working copies; the originals are stored unchanged, so anyone can later confirm by recalculating the hash that the data were not altered (Casey, 2011).

Data Dictionary

Table 1. Data Dictionary Extract

Validation Tests

Table 2. Validation Results

Each test addresses a different risk. Matching record counts to the vendor's own report shows nothing was dropped in the export. The sequence check shows no tickets were deleted outright, which matters because a deleted ticket would leave a gap while a voided one does not. Date coverage shows no days are missing. The completeness test finds blanks that could distort later analysis. And the reconciliation to bank deposits, an independent outside record, shows that cash recorded net of voids matches cash banked. That last result is important: it means the drawer balanced, so any money taken was taken in a way the system recorded as legitimate, most likely through voids.

Why the Reconciliation Points to Voids

Because deposits match recorded cash net of voids, the scheme, if there is one, does not leave the drawer short. That narrows the analysis. Kogan et al. (2014) describe continuous auditing systems that compare transaction data with independent records automatically, the kind of control that would have flagged an unusual void pattern as it developed. In its absence, the void log is where the investigation must look, and the validation has shown that the log is complete and trustworthy.

What Validation Cannot Show

The five tests prove that the data are a complete copy of what the system recorded. They cannot prove that the system recorded everything that happened. If an operator waved a cash customer across the scale without creating a ticket at all, no record would exist to void, and every test above would still pass. Two checks address that risk. The scale's hardware log, kept separately by the vendor, records every weight event on the scale plate, and a comparison of weight events with tickets will show any crossings without tickets. The camera footage, preserved for the last 30 days, can be counted against tickets for a sample of days. Both are planned before the analysis is reported, so that the investigation does not conclude the scheme is limited to voids when it might be larger.

Preparing the Data for Analysis

Before testing, three preparation steps are documented. The ticket and void tables are joined on ticket number, so each void carries the original ticket's time, fee and payment type. User IDs are linked to employee IDs and then to the shift schedule, so each void can be attributed to the person on duty. And all timestamps are converted to local time, because the vendor stores them in a universal time zone and a five-hour offset would misplace every void against the shift schedule. Each step is written down so that another analyst could repeat it and get the same result, and the joined table is saved with its own hash value as a new working file.

Conclusion

The investigators obtained 22 months of scale data directly from the vendor, recorded hash values on receipt, documented each field and validated the data against the vendor's control totals, the ticket sequence, the calendar and the bank deposits. All exceptions were explained, and the two checks for tickets never created are scheduled before any findings are reported. The data are complete enough to analyze, and the reconciliation result directs the analysis to the void log.

References

Casey, E. (2011). Digital evidence and computer crime: Forensic science, computers, and the Internet (3rd ed.). Academic Press.

Kogan, A., Alles, M. G., Vasarhelyi, M. A., & Wu, J. (2014). Design and evaluation of a continuous data level auditing system. Auditing: A Journal of Practice & Theory, 33(4), 221-245. https://doi.org/10.2308/ajpt-50844

Nigrini, M. J. (2020). Forensic analytics: Methods and techniques for forensic accounting investigations (2nd ed.). Wiley.