Case Analysis: Analytics mindset

P-card

Part I:

Background

You work as an internal auditor for Oklahoma State University (OSU). You were asked to perform an audit of the purchasing cards (P-cards) that are used on campus. P-cards are a business credit card that some employees are permitted to use to purchase necessary goods and services. If employees agree to certain rules, they can then use a P-card to make appropriate business purchases rather than using their own credit card. This allows the employee to avoid spending personal funds and seeking reimbursement. It also provides the business with greater control because the business can institute internal controls to limit types of purchases and avoid inefficient and fraudulent transactions. Organizations also can track spending using detailed P-card records provided by the credit card companies.

You have been assigned the task of auditing all of OSU’s P-card transactions. To perform this audit, you received a file of all P-card transactions for the entire state of Oklahoma (the state collects all transactions for state and higher education institutions).

The purpose of this case is to help develop your analytical mindset. An analytics mindset is the ability to:

· Ask the right questions

· Extract, transform and load (ETL) relevant data

· Apply appropriate data analytics techniques

· Interpret and share the results with stakeholders

For this case, you will perform the following task

Task 5 – Perform a test of various P-card internal controls.

* Adapted from original case

Analytics mindset

P-card

Part V:

Task 5

One of the key objectives of your internal audit is to determine if employees of OSU are following internal controls.

Required

· Using the data provided, perform the tests listed below to analyze whether there are potential internal control violations. Use the following matrix to guide your analysis. The matrix is set up as follows:

· The first column lists the internal control that should be operating.

· The second column identifies the test you should perform to evaluate the control.

· The third column lists how you should name the query or file with your results so your instructor can review your work (if you use multiple queries, use the query that contains your final results).

· Note that, although something is flagged as a potential internal control violation, it does not mean there has been a violation. There may be acceptable reasons for internal control deviations. The goal of this assignment is to identify higher-risk transactions, employees and vendors that should be targeted for additional testing.

Internal control

Test to perform and desired output

What to name query/file with output

Import the Excel data into IDEA (be sure you import all 3 tabs, the first row is the column headings, and 0 for blank fields). This data will be used again later in the course

Import

1. User shall not spend more than $50,000 per year.

Display the cardholder name and total amount spent during the year for all employees using summarization. Sort by the total amount spent with the larger amounts listed first.

Summarization

2. User shall not spend more than $10,000 per month without approval.

Display the cardholder name, total amount spent, and transaction date using summarization. Sort by larger amounts listed first.

Summarization 1

3. User shall not spend more than $5,000 per transaction.

Display all transaction details for transactions greater than $5,000 using the direct extraction command. Sort by the total transaction amount. Using field statistics identify the total amount of these transactions

Import- Transactions

4. Purchases should not be made out to the vendor or generic card holder

Be sure to use the original data set. Find all transactions where the vendor is the same as the cardholder last name (Question4) using direct extract. Again back to the original data set, see if the cardholder last name is named “Cardholder” (Question4a) using direct extract.

Duplicate

Question 4a

5. Purchases should not have the same transaction number, these are generated automatically by the system

Display duplicate transactions numbers (Question5), then using this data summarize the amount by transaction date (Question5a). Run the duplicate command again and summarize by cardholder (Question5b)

Question5, Question5a, and Quesiton5b

Duplicate 3

Summarization 2

Summarization3

6. Transactions are prohibited for expenses for food while traveling. A per diem for food expenses may be claimed using a travel voucher.

Find all transaction details about any transactions for FOOD in the description field (Question6). Sort them in ascending order by cardholder and then by the amount. Then look in the merchant category code field for anything related to GROCERY purchases (Question6a). Summarize by cardholder, sorted by highest amount first (Question6b).

Question6, Question6a, Question6b

Display a copy of the IDEA History File for this case

History

Answers

1.-

image1.png

2.-

image2.png

3-

image3.png

image4.png

4-

image5.png

4.a)

Nothing

image6.png

5.-

image7.png

5.a

image8.png

5.b

image9.png

6.- not found which means that everyone is complying with the policy

? The data for this case comes from the Purchase Card (P-card) Fiscal Year 2015 database and is made available under the Open Database License: ? HYPERLINK “http://opendatacommons.org/licenses/odbl/1.0/” ?http://opendatacommons.org/licenses/odbl/1.0/?. Any rights to individual contents of the database are licensed under the Database Contents License: ? HYPERLINK “http://opendatacommons.org/licenses/dbcl/1.0/” ?http://opendatacommons.org/licenses/dbcl/1.0/?. The data for this case is made publicly available at ? HYPERLINK “https://data.ok.gov/dataset/purchase-card-pcard-fiscal-year-2015” ?https://data.ok.gov/dataset/purchase-card-pcard-fiscal-year-2015?. Do not use the data set posted on the web. Use the one included by your professor because some of the transactions may have been changed or updated. Also, some data may have been altered, added or deleted to enhance learning objectives and, thus, you should not use the data to infer good or malicious intent by anyone listed in the data set.