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.