ACCT 432 Week 6 Individual Project 6 Example

Extracting the Vendor and Payment Files: A CAATT Mini-Case on Duplicate Payments, Employee-Vendor Matches and Split Invoices

Data Structure and Extraction

Mercy Valley Health, a composite three-hospital system, records payables in a relational database with three tables. The vendor table holds each vendor's number, name, address, bank account and tax identification number. The invoice table holds invoice number, vendor number, date, amount and approver. The payment table holds payment number, invoice number, date and amount. Hall (2016) explains that relational databases link tables through keys, here vendor number and invoice number, and that the auditor must understand those links to extract complete and accurate data. The extraction should be a read-only copy for the fiscal year, taken by the auditor or under the auditor's observation, and its record counts and payment totals should be reconciled to the accounts payable subledger and general ledger before any test is run. If the counts or totals do not agree, the extraction is incomplete, and every test run on it would understate the exceptions. The auditor should also check the field definitions, for example whether invoice amounts include tax and whether dates are stored as invoice dates or entry dates, because a test built on the wrong field produces confident but meaningless results.

Choice of Technique

Generalized audit software suits this engagement, because the auditor needs to test a full year of data once, independently of the client's programs. An embedded audit module would capture transactions as they occur, which is valuable for continuous assurance, but Debreceny et al. (2005) found that embedded modules in ERP environments were used less than their potential suggested, partly because of the cost of implementing and maintaining them inside complex systems. For Mercy Valley, the module would also require IT cooperation the auditors do not yet have. Querying the live system directly is also undesirable, since it puts the auditor's work at the mercy of client programs and risks slowing a hospital's production systems during business hours.

Tests

The duplicate test uses a range of dates and normalized invoice numbers because people rarely duplicate a payment exactly; a second entry often has a hyphen removed or a digit transposed. The employee-vendor test requires joining the vendor table to the payroll master file, which the auditor must extract separately and reconcile to payroll totals. The split-invoice test targets the approval threshold, since a person who cannot approve a $15,000 purchase may enter it as two invoices of $7,500. Each test is run as a script in the audit software so that it can be repeated at year-end or in the next audit without rebuilding it.

Evaluation of Results

In this illustration, the duplicate test flags 47 pairs, of which 39 are legitimate recurring charges, such as monthly service contracts of identical amounts, and 8, totaling $61,300, are true duplicates. The employee-vendor test finds two matches; one is an employee's spouse who supplies interpreter services under an approved arrangement, and one is unexplained and is referred to the fraud specialist. An exception is a question, not a finding, until someone traces it to the documents. Kogan et al. (2014) emphasize distinguishing rule violations from anomalies for exactly this reason. The confirmed duplicates indicate a weakness in the invoice-entry control, so the auditor would recommend a system edit that rejects invoices matching an existing vendor, amount and date, and would extend the duplicate test to the prior year to estimate total exposure.

Conclusion

The exercise shows why chapter 8 matters to the audit. Data extraction is not a clerical step; the completeness of the extracted file, the accuracy of the joins and the logic of each rule determine whether the tests find anything at all. At Mercy Valley, five scripts run over a full year of payments produced eight confirmed duplicates and one unexplained vendor relationship, findings a sample of fifty invoices would very likely have missed. The results also give management a concrete reason to add preventive edits to the payables system, which would reduce the work needed in future audits. It is the kind of result that justifies the time spent learning the data structure first.

References

Debreceny, R. S., Gray, G. L., Ng, J. J.-J., Lee, K. S.-P., & Yau, W.-F. (2005). Embedded audit modules in enterprise resource planning systems: Implementation and functionality. Journal of Information Systems, 19(2), 7-27. https://doi.org/10.2308/jis.2005.19.2.7

Hall, J. A. (2016). Information technology auditing (4th ed.). Cengage Learning.

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