Inventory Fraud Analysis using IDEA software

Objective

Auditing Procedures

1--Inventory

Import Inventory Supplies and Location file and Comm Supplies Inv and Sales file.

 The Inventory Warehouse Location file can help you with identifying where supplies are located for interpreting your results of where identified obsolete items are stored.

Identify parts that are obsolete. The definition of obsolescence is:

Inventory with a turnover of less than 3 times a year, or no sales in the last 6 months

 This will be tested using the following steps (print your results after each step and explain each print out as you create your report):

a. Summarize the Inventory Supplies and Location file by ITEMNUM. Indicate that the summarization should be on inventory quantity and inventory cost.

Call the report "Comm Supp Inv Sum by Item."

b. Summarize the Comm Supplies Inv and Sales file by ITEMNUM. Indicate that the QTYSOLD is the field to total. Call the report "Comm Supp

Sales Sum by Item."

c. Select the Comm Supp Inv Sum by Item and perform a join databases with the Comm Supp Sales Sum by Item file. The match key should be ITEMNUM and

the join option should be "all records in primary file." Name the result

"Inv with Sales."

d. Perform a field manipulation on the Inv with Sales file, appending a field called

"TURNRATE," a virtual numeric field with one decimal calculated as "QTSOLD_SUM/ INV_QTY_SUM." Report your findings.

e. Make sure the Comm Supp Inv and Sales file is the active file.

Identify the last date of sale to identify obsolete inventory items (see definition above) by performing a data extraction of the "Top Records".

(Anaysis/Top Records Extraction). The TOP RECORDS FOR

is “INVDATE” and GROUP is “ITEMNUM/A” (ascending). Name resulting file “Last Sales Date.”

f. Join Inv with Sales and Last Sales Date files. Match on ITEMNUM and join "All records in primary file." Name the result "Inv turn with last sales date."

g. Under the Field Manipulation function (right click into the data), rename the INV_DATE field "LAST_

INV_DATE" and the QTY_SOLD field to "LAST_SALE_QTY."

h. Extract obsolete inventory by setting the criteria as "TURN_RATE < 3.0 .OR. LAST_INV_DATE < “20050701””.

I Print the history report

Discuss your findings based on the data analysis performed along with the additional parts required for this project.

2--Travel

Import the Expense Reports April 25 05 through Dec 31 05.xls (Print your results after each step and explain each printout as you create your report):

(a) Perform a Duplicate Key Detection test (under Analysis) using the keys DATE, EXPTYPE, AMOUNT. Output duplicate records. Index the resulting databases on “AMOUNT” in descending order. Name the result “Duplicate Expenses”. Discuss the items you would conduct further tests on and why they appear suspicious.

(b) Make sure the Expense Reports April 25 05 through Dec 31 05 is the active file. Perform a summarization on EXPTYPE and include the AMOUNT, statistics: Sum, Average. Call your file “Exp Report Sum by Type with Average”. Discuss any examples of car rentals and mileage that took place on the same day (which might be an indicator of double-billing).

(c) Make sure the Expense Reports…file is the active file. Perform an extraction EXPTYPE = “Airfare” (note that airfare is generally the highest avg dollar). Name the resulting file “Expenses Airfare”.

(d) Import the Mastercard April 05 through March 06 file. Summarize the Mastercard fiile by PAYEE with AMOUNT checked. Name the resulting file “Mastercard Sum by Payee”.

(e) From the Mastercard Sum by Payee file, Right click into the data and select FIELD MANIPULATION and append a new field “EXPTYPE” that is an “editable character” with a length of 12 and a parameter of “ “ (just the blank quote marks to type in here). Manually type “Airfare” beside those payees who represent airlines (i.e. American, Continental, Delta, NWA, Southwest and United).

Perform an extraction on EXPTYPE = “Airfare”. Name the file “Airline Vendors”.

(f) Join Mastercard April 05 through March 06 file (primary) with Airline Vendors (secondary) (matches only). Match on PAYEE and call the resulting file “Mastercard Detail Airline Charges Only”.

(g) To facilitate matching Mastercard charges with Expenses, perform a Field Manipulation on the Mastercard Detail Airline Charges only by adding a field for the absolute value (ABSAMT) of the AMOUNT (virtual numeric, 2 decimals, parameter: @abs(AMOUNT)).

(h) Perform a join on the Expenses Airfare (primary) and Mastercard Detail Airline Charges Only (secondary), records with no secondary match. Name the result “Expensed Airfare not on Mastercard”. Match on AMOUNT (primary) and ABSAMT (secondary). Print report and discuss further investigative testing.

(i) Make sure the Mastercard Sum by Payee file is the active file. Review the payees and type in “suspicious” by any vendor name that appears questionable. Remember you are only looking at vendo names at this point. Perform an extraction (“Suspicious Vendors”) by EXPTYPE = “suspicious”.

(j) Print the history of what you have done for this assignment

Discuss your findings based on the data analysis performed along with the additional parts required for this project