Case 4 Use the 2_Somerville_ XXXXXXXXXX download Excel data file that contains payments data for 2013 to 2016 for the City of Somerville in Massachusetts. This data was introduced in Case 2.1. There...

Case 4

Use the
2_Somerville_2013-2016


download
Excel data file that contains payments data for 2013 to 2016 for the City of Somerville in Massachusetts. This data was introduced in Case 2.1.

There are 55,746 records (check payments) and a header row. The 15 check amounts equal to $0 were checks that were lost in the mail and that were later reissued for the correct amount. Do not treat them as missing values. Use Excel and show your screenshots or text for the requirements.

There guidance video is titledForensic Analytics Second Edition, Guidance Case 8.1, Abnormal Duplications

Required

1. Use Excel. Run the same-same-same test on the payments data. Define a duplicate to be the same vendor, the same date, and the same amount. Delete amounts under $100 since our audit resources are scarce. You should expect that such duplicates are rare because we are looking at the payment date and not the invoice date. Show a screenshot of the first 20 rows of your results sorted by count and then amount descending.


2. Review your results in (1) above. Given limited audit resources which one duplicate payment would you audit? You can assume that payments to the state and payments to the bank are not errors, and that if they are errors that the recipient will give you a credit for the overpayment. The audit need not be an intensive review of all the supporting documents.


3. The data does not contain the invoice numbers. Assume that if an invoice is paid twice that the second (erroneous) payment will be made 30 days or less after the first (correct) payment. Define a duplicate payment to be a payment to the same vendor, for the same amount, more than one day later and less than 31 days of the first payment. Assume that the payments will be two successive payments to the vendor. In other words, payments of $1,805.00, $1,983.00, and $1,805.00 should not flag the $1,805.00 as duplicates even if the payment dates are less than 31 days apart because of the $1,983.00 in the middle. Run the same-same-same test looking for such duplicates. Delete all amounts under $1,000.00 from the dataset before running the test. Show a screenshot of the first 24 rows of your results sorted by vendor name ascending.


4. Review your results in (2) and (4) above. Given limited audit resources in is probably not feasible to audit the “almost” duplicates. Based on the payments and the results would you be inclined to review the organizations controls to prevent duplicate payments?


5. Use Excel. Run the subset number frequency test. The subsets in this case are the vendors. Delete amounts under $100 from the dataset before running the test. Show a screenshot of the first 20 rows of your results sorted byNFFdescending andCountYour results need only show the vendor’s name, the count for the vendor, and the vendor’s NFF if the vendor has an NFF greater than 0.


6. Review theCase Submission Checklistand type a statement as follows “I reviewed the case submission checklist before submitting this case.”

Deliverables for Module 4" style="float: left;">Previous
Idea Case and Quiz 2" style="float: right;">Next