You're starting to make a name for yourself in the internal audit department. Your manager liked your analysis of the audit plan and now would like you to see what other ways data analytics could be applied beyond the existing audit action sheets.

As you've been reading about risk and fraud, you learned that one common risk is that employees may be tempted to create fictitious suppliers that they use to embezzle money. The premise is simple enough. an employee with access to create master data adds a supplier record for a spouse. She then submits an invoice for "learning services" that were never performed and is promptly paid, assuming there isn't good follow-up from the accounts payable department. The employee is smart enough to know that an exact address would raise red flags, so she alters it slightly to avoid detection. Other suspicious addresses may include PO Box addresses because they can obscure the identity of a fictitious supplier.

You know that one way to detect this issue is to look for fuzzy matches, and you're eager to show your manager what you know.

For this assignment, using Excel, (1) Identify the questions, (2) Master the employee and vendor data, (3) Perform the analysis, (4) Address the results.

Master Audit Files

Audit Action Sheets.docx Download Audit Action Sheets.docx

Audit Plan.docxDownload Audit Plan.docx

Data Dictionary.xlsxDownload Data Dictionary.xlsx

UML System Diagram.pptxDownload UML System Diagram.pptx

Current Audit Files

Goods_Received.xlsxDownload Goods_Received.xlsx

Invoices_Received.xlsxDownload Invoices_Received.xlsx

Payments_Made.xlsxDownload Payments_Made.xlsx

Purchase_Orders.xlsxDownload Purchase_Orders.xlsx

Supplier_Listing.xlsxDownload Supplier_Listing.xlsx

User_Listing (Employee File).xlsxDownload User_Listing (Employee File).xlsx

1: Identify the questions

Q1. Given what you know about vendor addresses, what types of addresses would be the most suspicious?

Q2. How could a vendor be added to an enterprise system with a suspicious address?

2: Master the Employee and Vendor Data in Excel

1. Use the Current Audit File folder.
2. Create a new Excel workbook and call it User-Supplier Match.
3. Rename the Sheet1 to Users and add a new sheet called Suppliers.
4. Open the User_Listing and Supplier_Listing files.
5. Copy the data from the User_Listing file to the Users sheet in your new spreadsheet.
6. Copy the data from the Supplier_Listing file to the Suppliers sheet in your new spreadsheet.
7. From your User-Supplier Match spreadsheet, click Open in Excel to load it in the desk-top version of Excel.
8. Take a screenshot and label it Assignment 6-Master Data-Last Name_First Name
9. Perform the Analysis in Excel

There are a couple ways to look for suspicious addresses. You could look for specific values or use tools to help you link records.

1. Begin by narrowing down addresses with the word "box." This should include "PO Box," "P.O. Box," and "Box".
2. Select the data in the Supplier sheet, and format it as a table of your choosing.
   • (Home > Styles > Format as Table)
3. Click the drop-down arrow next to the Supplier_Physical_Street_Address1 field to show the sort and filter menu.
4. Choose Text Filters > Contains....
5. Enter box and click OK.
6. Take a screenshot and label it Assignment 6-Analyze Data-Last Name_First Name.

Now look for fuzzy matches in Excel.

1. Click the drop-down arrow next to the Address field, and choose Clear Filter From "Supplier_Physical_Stree_Address1"
2. Perform a fuzzy match on the Supplier_Physical_Stree_Address1, and Supplier_Physcial_ZipPostalCode from the Suppliers sheet and the User_Physcial_Street_Address1 and User_Physcial_Street_ZipPostalCode from the Users sheet.
3. Take a screenshot and label it Assignment 6 - Fuzzy Match-Last Name_First Name
4. Q5. How many fuzzy matches appeared?

Q6. Which of the matches are suspicious?

Q7. Which of the matches are normal?

1. Address the Results

Q8. Are there any limitations to the way you just evaluated addresses?

Q9. What other data values would indicate that there may be fictitious suppliers in the system?