Create a Word document, to write your responses and paste required screenshots.

Create a Word document, to write your responses and paste required screenshots.

Use the Pomona Payment Data_F23.xlsx Excel data file that contains payments data for 2013 to 2016 for the City of Pomona. Use Excel for the requirements. Save the file as LastName FirstName Pomona Payment Final.xlsx.

There are 55,629 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 Word to show your screenshots for the requirements.

Required:

Use NigriniCycle template to run the required tests. Save NigriniCycle.xlsx as LastName FirstNameNigrini Final.xlsx. Write your responses in your Word document.

Run the First Digit test on payment amount. Does the distribution conform with Benford’s Law? Provide support of your answer by either statistical evidence and/or chart.

Run the First-Two Digits test. Does the distribution conform with Benford’s Law? Provide support of your answer by either statistical evidence and/or chart.

Run the Summation test. Does the distribution conform with Benford’s Law? Provide support of your answer by either statistical evidence and/or chart.

Run the Duplication test on the payments data. Provide a screenshot of the highest 10 duplicated payment amounts and the duplicated counts.

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 $50 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. Provide a screenshot of the first 10 rows of your results sorted by count and then amount descending

Review your results in Question 2 & 3 Given limited audit resources which one or two duplicate payments 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.

Run the largest subset test on the payments data to show total payments of each merchant. Rank results based on total payments. Provide a screenshot of the top 10 merchants with largest total payments.

Run the largest subset growth test on the payments data to show merchants with the largest growth in total payments from 2013-2014 (Period1) to 2015-2016 (Period2). Use “conditional formatting” to highlight top 10 merchants with largest subset growth in total payments from Period1 to Period2. Provide a screenshot of the results.

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 in the current dataset to be a payment to the same vendor, for the same amount, more than one day later and within 30 days. Run the same-same-different test looking for such duplicates. Run the test on all amounts over $1,000.00. Provide a screenshot of the first 20 rows of your results sorted by vendor name ascending

Given limited audit resources it 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?