Menu
Home
Log in / Register
 
Home arrow Business & Finance arrow Fraud and fraud detection
< Prev   CONTENTS   Next >

DATA AND DATA FAMILIARIZATION

For accounts payable data analytical tests, we will mainly be using a large data set downloaded from the state of Oklahoma's website.1 The data set is the state of Oklahoma Vendor Payments Fiscal Year 2013, which contains over 2 million records from the different agencies in Oklahoma. There are 29 fields, of which 15 are relevant to our discussion:

- AG ENCY_NUMBE R

- AGENCY_NAME

- VENDOR_NAME: There are no vendor numbers in this file and some of the names are redacted and replaced with the words Protected Information

- VOUCHERJD

- VOUCHERJTYPE

- VOUCHER_DESCRIPTION

- INVOICE_ID

- INVOICE_DATE: The dates are in three different formats as a character field; the author normalized the dates and then converted the character field to a date field called INVOICE_DATE_2

- TRANSACTION_TYPE

- PAYMENT_DATE: The dates are in three different formats as a character field; the author normalized the dates and then converted the character field to a date field called PAYMENT_DATE_2

- ACCOUNT

- EXPENDITURE_DESCRIPTION

- PAYMENT_AMOUNT

- PURCHASE_ORDER_CONTRACT_NUMBER

- PURCHASE_ORDER_CONTRACT_ITEM_DESCRIPTION

Where this payment file is not appropriate for demonstration purposes, the sample files that are included with the IDEA software or IDEA course material will be used.

To get an overall view of the data contents of the "State Vendor Payments" file, we review the field statistics. Of the numeric fields, only the PAYMENT_AMOUNT field provides any interest, as shown in Figure 8.1.

We note that all 2,066,536 records have valid values in this field but 34,388 of them contain the amount of zero. There is a wide spread among the data amounts, ranging from a negative $5,996,550.22 to a positive value of $48,907,078.15. To obtain a better insight of the amounts, you should perform a stratification of the PAYMENT_AMOUNT field.

More interesting are the field statistics for the INVOICE_DATE_2 and the PAYMENT_DATE_2 fields.

We can see that the INVOICE_DATE_2 field contains 76,461 errors and that there are issues with date inputs, as can be seen in both the record number of the earliest and of the latest dates as displayed in Figure 8.2.

The PAYMENT_DATE_2 field appears to be reliable. There are no data errors and the dates seem to be in line with what is expected. Of special interest are the 41 payments made on Sundays and the 64 payments made on Saturdays.

Both the INVOICE_DATE_2 and the PAYMENT_DATE_2 fields contain zero or were originally blank in these fields of 17,919 and 30,742 respectively.

Summarizing on TRANSACTION_TYPE tells us that our main focus should be on type P, as they appear to be payments actually made. See Figure 8.3.

From the review of the data the following are noted:

- B—The voucher type for all the records is JRNL with PAYMENT_AMOUNT as zero; it seems that these are journal entries

- C—Contains both positive and negative amounts in the PAYMENT_AMOUNT field

Field Statistics of Payment Amount

FIGURE 8.1 Field Statistics of Payment Amount

- H—Contains negative amounts and are noted as Regular Voucher - P—Paid amounts

- R—Refunds

- W—Negative amounts

We are not sure of all the coding as the data file was downloaded from an Internet public source.

From the "State Vendor Payments" file, we can extract to a new file called "Payments trans type P" by using the equation of TRANSACTION_TYPE = P.

Field Statistics of Dates

FIGURE 8.2 Field Statistics of Dates

Transaction Types

FIGURE 8.3 Transaction Types

 
Found a mistake? Please highlight the word and press Shift + Enter  
< Prev   CONTENTS   Next >
 
Subjects
Accounting
Business & Finance
Communication
Computer Science
Economics
Education
Engineering
Environment
Geography
Health
History
Language & Literature
Law
Management
Marketing
Philosophy
Political science
Psychology
Religion
Sociology
Travel