Instructions and Requirements Please access and download the following file for Module 6 and the Portfolio Project in Module 8: P-card Student Case Study InstructionsDownload P-card Student Case Study

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 for 2014. 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).1 This file will be described more in Part II.

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 tasks, as assigned by your professor. Each task focuses on a different part of developing an analytics mindset.

• Task 1 – Gain an understanding of OSU P-card guidelines and internal controls. Start generating questions that would evaluate employee compliance with internal controls and guidelines and that would test organizational costs and benefits of using P-cards.

• Task 2 – Gain an understanding of the data used for testing.

• Task 3 – Transform the data and load it into the appropriate tool for analysis.

• Task 4 – Review or learn basic querying techniques.

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

• Task 6 – Develop an audit tool to search for possible noncompliant purchases.

• Task 7 – Perform forensic tests of transactions to look for fraudulent transactions.

Task 1

This task focuses on the first bullet point of developing an analytics mindset — Ask the right questions. We strongly emphasize the importance of this principle before any data is analyzed. To apply data analytics correctly, it is critical to understand the business setting and your objectives in using data analytics.

The OSU P-card guidelines stipulate that “It is the intent of Oklahoma State University (OSU) to use a commercial purchasing card (P-Card) to facilitate the acquisition of lower dollar goods and services needed for conducting official University business.”

Required

• Gain a more in-depth understanding of the OSU P-card guidelines and objectives by reviewing the P-card guidelines in the appendix following. As you review the guidelines, consider the risks OSU faces and what controls it has implemented around those risks.

• Prepare a list of questions that you would like to test to see if employees are following OSU P-card guidelines. Your questions should be informed by thinking about the risks OSU faces and the internal controls it has implemented. Make sure you focus on questions that can be answered using data.

Using the P-Card

The cardholder is the only person authorized to make purchases using his/her card. Loaning a P-Card to another person may result in revocation of the card.

The fact that the cardholder has been issued a card does not imply prior approval of all purchases. The cardholder must follow applicable University policies and procedures and departmental procedures, including any departmental pre-approval procedure.

Many companies provide discounts through their Education Sales Department, so ask for that department.

There should be no up-charge by merchants to use the P-Card except where there are specific contractual arrangements with OSU to do so.

The following procedures should be followed for all purchases made by P-Card:

Decision to Use P-Card for Purchase

When making the decision whether to use the P-Card for a purchase, the cardholder should:

• Review the lists of prohibited and restricted purchases to ensure the purchase is allowable on the P-Card.

• Be sure the total amount will not exceed the cardholder’s single transaction and/or cycle limit.

• Give fair treatment to all merchants and determine if the price obtained is reasonable.

Making the Purchase Using a P-Card

There are three main methods of making purchases:

Over the Counter – When making an over the counter purchase, the cardholder should:

• Verify the vendor accepts VISA. If the merchant accepts VISA, provide the P-Card for payment and make certain the merchant understands the purchase is exempt from sales tax. (The cardholder must have a copy of the Oklahoma Tax Commission Sales Tax Exemption Certificate for most merchants to exclude sales tax. This certificate can be downloaded from the Purchasing Department’s website.)

• Verify no sales tax is included in the final purchase total before signing the sales receipt.

• Obtain a receipt at the time of purchase. (This receipt must be maintained as documentation with the cardholder’s Bank Statement.)

Mail, Phone, or Fax – When placing an order by mail, phone, or fax, the cardholder should:

• Provide the merchant with the requested card information.

• Indicate to the sales representative or on the order form that OSU is a tax-exempt institution. While OSU is not necessarily exempt from sales tax in other states, some vendors will not tax OSU.

• Provide the merchant with detailed shipping instructions to include your name, department name, phone number, and appropriate campus address.

• Obtain a confirmation number from the merchant and request that a receipt with itemized descriptions and pricing information be sent with the purchase.

• Retain appropriate documentation, including receipt / invoice and packing slip, of the purchase. (The documentation must be maintained with the cardholder’s Bank Statement.)

Internet – When placing internet orders, the cardholder should:

• Use a reputable merchant and ensure purchases are made from a secure site or a site that provides account number encryption.

• If available, use the Educational section of the merchant’s website.

• Complete the necessary order process and provide cardholder information to include the billing address that appears on the cardholder’s bank statement.

• Verify no Oklahoma sales tax is included in the final purchase total before completing the order process.

• Print appropriate screens to include vendor name, date, item description(s), itemized cost, and total cost including shipping and handling.

• Retain appropriate documentation, including receipt / invoice and packing slip, of the purchase. (The documentation must be maintained with the cardholder’s Bank Statement.)

Delivery Address

Items purchased with the P-Card should always be delivered to a University address. Any exception must be approved in advance. If circumstances require goods to be shipped to an address other than a University address, a Request for Exception – Goods/Items to be Shipped to a Non-University Address Form must be completed and approved by the Associate Vice President and Controller. (This form can be found at http://controller.okstate.edu/exceptions-forms-guidelines.) The approved form must be maintained as a part of the purchase documentation and attached to the cardholder’s Bank Statement.

Returns, Damaged Goods, and Credits

Boxes, containers, special packaging, etc. should be retained until you have determined you are going to keep the materials. Some items, such as software or fragile pieces, cannot be returned without the original packaging material.

If the cardholder determines materials purchased with a P-Card need to be returned, the cardholder should:

• Work directly with the merchant.

• Carefully read all instructions enclosed with the order. A phone number of and/or instructions for returning the materials are usually included on the receipt and/or packing slip.

• Request a Return Authorization Number from the merchant if required.

• Request a credit receipt for returned items. Some merchants may not provide this receipt unless it is requested.

• Determine if a restocking fee will be charged. If the merchant is responsible for the error or problem, you should not have to pay a restocking fee. If the merchant is not responsible, you may have to pay the restocking fee. A P-Card may be used to pay this fee provided it does not exceed your limits or violate policies.

• Check your monthly Bank Statements to ensure the charge for the returned item(s) is credited properly.

• Retain appropriate documentation of the return and associated credit. (The documentation must be maintained with the cardholder’s Bank Statement.)

Backorders

No charges should be incurred for back orders. Charges may only be applied for material that has been received by the University or shipped from the merchant’s dock.

Card Denied

If the P-Card is denied for any reason, the cardholder should contact Bank of America at the number on the back of the P-Card. Bank of America can provide the cardholder with the reason the card was denied. The cardholder may be asked to provide the name embossed on the card, the address listed on the P-Card application form, or the phone number listed on the P-Card application form. The cardholder can also check the Authorization Log in the Works system to determine the reason for the decline.

Transaction Flow

A typical P-Card transaction consists of the following steps:

• Cardholder follows his/her department’s pre-approval procedure.

• Cardholder makes a purchase from a merchant using the P-Card and obtains an itemized receipt.

• Merchant delivers the goods or service and submits the transaction to the credit card company.

• The bank pays the merchant.

• Cardholder reviews his/her transaction in Works, enters a detailed description and the purpose of the purchase, and signs off electronically on his/her transaction.

• The transaction is routed to the cardholder’s approver. The approver reviews and electronically approves the transaction.

• The transaction is routed to the cardholder’s accountant. The accountant reviews and electronically approves the transaction.

• Bank of America furnishes cardholder with a Bank Statement of purchases at the end of the billing cycle.

• Cardholder reconciles his/her receipts with the Bank Statement and forwards the Bank Statement, receipts, and other required supporting documentation to the cardholder’s accountant.

• Accountant reviews the Bank Statement and all supporting documentation for completeness and compliance, and signs and dates the Bank Statement in a timely manner. Original records shall be maintained in a central location within the department. Within five (5) days of the end of the billing cycle, all transactions are reviewed, approved, and the account numbers and subcodes are updated. At the end of the five (5) day period, University Accounting downloads all transactions into the financial accounting system and makes a single payment to the bank on behalf of all OSU departments.

NOTE: The cardholder, approver, and accountant must be three different people and may only sign off in one role per transaction. Approvers and accountants may not sign off on their own transactions.

Spending Controls

Cardholder Spending Limits

Because OSU, not the individual employee, will pay for purchases made with the P-Card, authorization controls have been added to the P-Card accounts. These limits are imposed at the point of sale when the card is swiped or applied. The available limits on a P-Card include, but are not limited to:

• Credit Limit (dollar amount per cycle) – shall not exceed $50,000

• Single Transaction Limit (dollar amount per transaction) – shall not exceed $5,000

The single transaction limit includes shipping and handling charges or any applicable allowable transaction fee.

Individual cardholder limits are set by departmental administration and indicated on the P-Card application form. Departments will be required to provide justification for any monthly credit limit over $10,000 for a cardholder. (For student employees, justification will be required for a credit limit over $2,500 and a single transaction limit over $500.)

Merchant Activity Type Limits

OSU prohibits the use of P-Cards for certain types of transactions. Businesses are identified by Merchant Category Codes (MCC), a standard code the credit card industry uses to categorize merchants based on the type of goods or services provided by the merchant. MCC groups are defined for OSU by the Purchasing Department and are used to control whether a cardholder may purchase from a particular type of merchant. If an otherwise allowable P-Card purchase has been denied due to an MCC, contact the P-Card Administrator.

Cardholder Limit Recertification

Cardholder limits will be reviewed annually to determine whether actual usage is consistent with a cardholder’s spending limits.

• If the usage is consistent with the spending limits, there will be no change to the card limits.

• If the usage is not consistent with the spending limits, the P-Card Administrator will recommend an adjustment be made to the card limit(s), or recommend an inactive card be cancelled. If the department does not agree with the recommendation, departmental administration may file an appeal. The appeal must be in writing to the P-Card Administrator and should provide justification for why the card limit(s) should not be adjusted (or why the card should not be cancelled). Any appeal that cannot be resolved at that level will be forwarded to the Chief Procurement Officer for final determination.

Cardholder, Approver, and Accountant Responsibilities

Cardholder Responsibilities

When accepting a P-Card, the cardholder becomes an authorized purchasing agent for the University and has certain responsibilities. These include:

• Protection of the Card – The cardholder must protect the security of the P-Card and the card number. The cardholder is the only person authorized to make purchases using his/her card. If the card information is compromised or if the card has been lost or stolen, the cardholder must contact the card provider and also email the P-Card Administrator.

• Limitations on Merchants – Purchases from friends or family, from a company owned by any University employee, or from companies where the cardholder has a financial interest are prohibited using a P-Card. The cardholder must not accept any gift or gratuity from any merchant when it is offered, or appears to be offered, to influence the cardholder’s decision regarding a P-Card purchase.

• Card Changes – The department is responsible for completing a Change Form if there are changes to card information, i.e. name change, credit limit change, change to the default account number, etc.

• Purchase Limitations – The cardholder accepts the responsibility for ensuring unallowable, prohibited, or restricted items are not purchased.

• Receipt Maintenance – The cardholder must ensure appropriate documentation, including the original invoice/receipt and packing slip, if applicable, is received and maintained for each purchase. The documentation is to be matched with the cardholder’s Bank Statement and forwarded to the cardholder’s accountant for review.

• Transaction Processing – The cardholder is required to complete P-Card training. The cardholder is responsible for timely review of his/her transactions in Works to verify the purchases are legitimate and in compliance with policy and procedures, the required supporting documentation is present, and a description of the purchase and business purpose has been entered in the description field in Works. The cardholder may be required to update account numbers and subcodes. Once the verification process is complete, the cardholder must sign off on the transaction in Works. At the end of each billing cycle, the cardholder must reconcile the individual receipts and supporting documentation with his/her Bank Statement to verify the purchases and returns are accurately listed, and forward the Bank Statement and all supporting documentation to his/her accountant.

Prohibited Purchases

Certain types of purchases are prohibited by the State/State statutes. Certain purchases may be allowable if processed on a requisition, but may not be made with a P-Card.

A P-Card may not be used for the following:

• Split Purchases – Split purchasing means dividing or failing to consolidate a known quantity of goods or services for the purpose of evading the P-Card single transaction limit of $5,000 and/or a quotation/bidding requirement. Examples include, but are not limited to; splitting an amount over $5,000 between two (2) or more swipes of the card, splitting the purchase between two (2) or more cardholders, splitting the purchase between two (2) or more vendors, or splitting the purchase between two (2) or more accounts or projects. Split purchasing is a serious violation of both OSU Policies and Procedures and State statutes.

• Regular Monthly Payments > $5,000 per Fiscal Year – Maintenance, lease/rental, and service agreements for office or scientific equipment should be processed on a requisition and a PO issued if the total for the fiscal year is greater than $5,000.

• Sales Tax – The cardholder is responsible for ensuring Oklahoma sales tax is not charged at the time of purchase.

• Cash, Cash Advances, Automated Teller Machine (ATM) Transactions

• Donations / Sponsorships

• Gifts, Gift Cards, Gift Certificates – The purchase of gifts is a violation of State statutes.

• Expenses for food and mileage while in travel status -– Per diem for food expenses and mileage may be claimed on a travel voucher.

• Items that do not Serve a Business Purpose – Includes, but is not limited to, flowers, candy, meals, greeting cards, health care items, etc.

• Personal Purchases – The P-Card may not be used under any circumstances to purchase items for personal use.

• Apple Products (For the Stillwater Campus Only) – Apple products available through the Student Union Bookstore must be purchased through the Bookstore. Please refer to “Guidelines for Purchases of Apple Products” at http://it.okstate.edu/facstaff/computers.php for more information. (For associated data plans, see Restricted Purchases.)

• Trade-in’s – Includes any purchase involving the trade-in of a University asset.

• Conflict of Interest Transactions – A cardholder may not purchase goods or services from themselves or a member of their immediate family or realize personal gain on a purchase transaction. Potential conflicts of interest must be disclosed by the cardholder.

• Any Purchase from a Company Owned by a University Employee – Any purchase from any company owned by any University employee must be bid.

• University Departments and/or Auxiliaries – The campus vendor invoice (CVI) system is to be used for the purchase of goods or services from University sources.

• Gasoline – Gasoline should be purchased from Transportation Services or with the gasoline credit card provided with each University vehicle.

• Mail, Postage – All U.S. mail, including parcel post, certified, and registered mail, should be sent through University Mailing.

• Weapons and/or Ammunition – A requisition must be completed for the purchase of weapons and/or ammunition.

• Moving Expenses – A requisition must be completed for moving expenses for University employees.

• Service and/or Incentive Awards (or Any Items Purchased for an Employee) – Service and/or incentive awards (or any items purchased for an employee) must be processed on a requisition.

• Late Fees

• Insurance – Insurance must be processed on a requisition through Risk and Property Management.

• Purchases for Student Organizations – A tax exempt University P-Card may not be used to make purchases for student organizations. A fiduciary fund P-Card assigned to a student organization must be used.

• Alcohol

• Decorations

• Personal/Individual Memberships – Payment of personal memberships and dues are a violation of State statutes.

• Prepayments or Deposits – Prepayments and deposits are a violation of State statutes. For determination of when subscriptions or registration fees (approved exceptions) may be paid in advance, see the Restricted Purchases section below.

• Salary / Wages and/or Benefits

• Other Purchases not Permitted under OSU Policies and Procedures, Purchasing Policies, and State Statutes

P-Card Violations

Misuse of the P-Card in any manner by a cardholder may result in revocation of the privilege to use the P-Card, disciplinary action, termination of employment, and/or the pursuit of any legal action available to the University.

Analytics mindset

P-card

Part II:

Task 2

This task emphasizes the second bullet of developing an analytics mindset — Extract, transform and load relevant data (ETL). Before using data for any analysis, it is imperative to understand the data. For this case, you have the following six data files:

• Analytics_mindset_case_studies_PCard_FY2010.csv

• Analytics_mindset_case_studies_PCard_FY2011.csv

• Analytics_mindset_case_studies_PCard_FY2012.csv

• Analytics_mindset_case_studies_PCard_FY2013.csv

• Analytics_mindset_case_studies_PCard_FY2014.csv

• Analytics_mindset_case_studies_PCard_FY2015.csv

Each data file contains all P-card transactions for one fiscal year (the fiscal year and calendar year are different). Combined, the data files contain all P-card transactions for fiscal years 2010 through 2015. The data files generally contain the following information (review each file carefully):

• Agency Number: This is a unique identifier for each agency. The number has no meaning other than uniquely identifying each state agency.

• Agency Name: This is the name of the government agency to which the employee belongs. Names are not necessarily unique.

• Cardholder Last Name: This is the last name of the person who is responsible for the P-card. Last names are not unique.

• Cardholder First Initial: This is the initial of the first name of the person who is responsible for the P-card. Initials are not unique.

• Description: This is a general description of the nature of the purchase.

• Amount: This is the amount charged to the P-card, denominated in dollars and cents. Negative amounts indicate a return and refund for the amount spent.

• Vendor: This is the name of the company that processed the charge.

• Transaction Date: This is the date the purchase was made. The date is accurate to the day, but not at a more refined level (e.g., hour or minute).

• Posted Date: This is the date the transaction was finalized and displayed for the customer.

• MCC: This abbreviation standards for merchant category code. It is a categorization made by the credit card company to group transactions from a vendor into primary categories.

One framework for understanding data is to consider the four Vs of data: variety, velocity, veracity and volume. Here are a few examples of important questions to consider when evaluating the four Vs of data.

• Variety – different forms and formats of the data

  • Are all of the data set formats the same? Do they need to be the same for your analysis?

  • Do all fields contain the same labels? Does the data with similarly titled labels contain the same type of data?

  • Are all of the data formats the same? Do they need to be the same for your analysis?

  • How are the files delimited? Are there any extra delimiters that may cause problems when importing? What strategies can you take to deal with any of these challenges?

  • Are formats consistent for all entries in a field?

  • Is the data structured or unstructured? What transformation would need to happen to any unstructured data to make it possible to analyze?

• Velocity – frequency of incoming data that needs processing.

Is your analysis performed on “live” data or only on historical data?

• • How often will you be updating this analysis? How automated should the analysis be?

• Veracity – trustworthiness of the data

  • Is the data you have complete? Do the data files you received contain all transactions? Are all of the data fields complete for each year and do the files contain all of the same data for each year?

  • Does the data contained in the data files accurately represent the economic transactions?

  • What human judgment went into establishing the data?

• Volume – the amount or scale of data

  • Should you include data for all years?

  • Should you include data from all entities in Oklahoma or only for OSU?

  • Are all fields relevant to your analysis?

  • How many rows will you need to import? What tools can handle this quantity of data?

Required

• Review the data and prepare responses to the questions above related to the four Vs.

Analytics mindset

P-card

Part III:

Task 3

This task continues to focus on the second bullet point of having an analytical mindset — Extract, transform and load relevant data (ETL process).

With an understanding of the business situation and the data, the next step is to prepare the data for analysis. In practice, preparing data follows the ETL process.

• Extracting data means acquiring data from wherever the data resides (e.g., databases, servers online).

• Transforming the data means preparing the data for analysis. This may entail such things as changing formats, combining data sources, aggregating or disaggregating data to the appropriate level, etc.

• Loading the data means importing the data into whatever tool you will use to analyze it (e.g., database, data visualization software or a statistics program).

For this case, the data already was extracted from the state government database for you. You can assume that the extracted data is complete and accurate. That is, all transactions that occurred are actually in the files and each transaction represents what happened (e.g., the computer did not alter transactions). That does not mean every transaction is real (i.e., an employee may have created a fake transaction), there are no mistakes, there is no fraudulent behavior, etc. It only means that the extraction of information from the government database was complete and accurate.

Before transforming the data, it is important to understand what the data should look like when you are finished. The American Institute of Certified Public Accountants (AICPA) has produced voluntary, recommended data standards for the extraction of information. While not required, these data standards, combined with other standard industry practices, form leading practices about how to format data. Since the data files you received do not necessarily conform to these leading practices, you should transform the data to comply with these practices, except as noted below.

The full AICPA Audit Data Standards Library can be found at https://www.aicpa.org/InterestAreas/FRC/AssuranceAdvisoryServices/pages/auditdatastandardworkinggroup.aspx. Relevant information for this case from the Base Standard is produced below.

• When dealing with flat files, the standards recommend using pipe-delimited, UTF-8 text files.

• Each file should include a header record that lists the field names for each data field. Field names should conform to the following specifications:

Industry leading practice also suggests the following:

• Field names should not contain spaces. Instead of Customer Name as the field name, it should be CustomerName or Customer_Name.

• Special characters (e.g., $, &, %, commas or semicolons) should not be used in field names.

• Field names should be descriptive and provide some information about the information in the field. Abbreviations are acceptable to avoid long field names.

• Capitalization in field names can enhance readability. Instead of acctnumber, the title AcctNumber is easier to understand.

Required

• Transform the data in accordance with the AICPA Base Standard, except as noted:

  • For this transformation, you should import all of the data from each of the six files into a single repository. That means, regardless of the agency to which the data pertains, you should combine it into a single file.

  • Because of this stipulation, the quantity of data is more than Excel can handle in a single sheet (i.e., there will be more than 1,048,576 lines of data). Thus, you should use Microsoft Access to create a database that lists all data in a single table.

• As you prepare your Microsoft Access database, here are some important tips to keep in mind:

  • Although the AICPA data standards suggest pipe-delimited files (that is, files that use a vertical line delimiter instead of a comma or other delimiter), use a comma-delimited or .csv file for all parts of this case.

  • You should only use a single table for the data import.

  • For the TransactionDate and PostedDate, use the format MM/DD/YYYY to make importing into Access easier. For example, May 4, 2002, would be formatted as 05/04/2002 (it is OK if the zeros preceding the month and date do not show up in Access).

  • Not all fields are labeled as discussed in Task 2. Relabel data in all files using the labels that described the information in Task 2, but remove the spaces between the words (e.g., Agency Number should be AgencyNumber). Perform this step in Excel before importing the information into Access.

  • Data might look different from year to year. You need to make it consistent across time.

  • If data is missing for a particular field, leave it blank in your database.

• When you finish the transformation, answer the following questions:

  • How many rows of data are contained in your final file?

  • What is the total of the amount column?

• Upload a copy of your Access file for your professor for grading.

Analytics mindset

P-card

Part IV:

With the data transformed and loaded into your software tool, you are now prepared to focus on the last two bullet points of an analytical mindset to complete the remaining tasks — Apply appropriate data analytic techniques and Interpret and share the results with stakeholders.

The data analysis is broken down into various tasks. Your professor will inform you which tools you should use in your analysis (e.g., Excel, Access, Tableau). For all questions, please note the following instructions and hints:

• You are only interested in transactions from OSU. Make sure to screen transactions so you only analyze those that pertain to OSU.

• Remove all transactions that have a null TransactionDate.

• For all questions related to dates, use the TransactionDate, unless otherwise specified.

• Pay attention to the dates listed in the following tasks. The dates pertain to calendar years for OSU. Even though OSU has a non-calendar year-end, to simplify this assignment, we only will focus on calendar year results.

• For all analyses, assume that the combination of first initial and last name for cardholders each represent a unique person. Similarly, assume that each unique vendor name represents a unique vendor.

• It may be easier to create a master query. That is, create a query that restricts the analysis only to the transactions you are interested in examining (e.g., transactions from OSU).

  • In this query, you may also want to compute common fields you will use later, such as computing the year and month from the TransactionDate field and a combined name from the CustomerFirstInitial and CustomerLastName fields. Then, in all subsequent queries, you can reference this single master query without performing the same thing over again.

Task 4

This task is meant as a review of your database querying skills. That is, most of these questions are not critical from a decision-making standpoint, but they allow you to practice and hone your database querying skills. Once you feel comfortable with your skills in producing these queries, the next tasks are focused more on the internal auditing context.

Required

Complete the following database queries. Label each of your solutions using the following convention: qry_T4_Question#, where # should be the question number. For example, the answer to a question should be labeled qry_T4_Question3. If you use multiple queries, then label the final query (i.e., the query with the solution) as qry_T4_Question3Final, making sure to replace the number three in the example with the question number. Some queries will require multiple queries to get the final answer.

1. Show all transactions sorted by amount (smallest to largest), with the most expensive transactions listed first. Display the year, month, cardholder name, the amount of the transaction and the vendor.

2. Show all transactions that occurred in the 2014 calendar year sorted by month, and then, within each month, list the most expensive transactions first and then alphabetize them by the name of the employee. Display the year, month, cardholder name, the amount of the transaction and the vendor.

3. Show all transactions that occurred in the 2014 calendar year for more than $3,000. Sort by month and then within each month list the most expensive transactions first and then alphabetize by the name of the employee. Display the year, month, cardholder name, the amount of the transaction and the vendor.

4. Show all transactions that came from a vendor with Amazon somewhere in the name that occurred in the 2014 calendar year that are more than $3,000. Display the vendor name, employee name, amount, year and month. Sort by amount so the smallest amount is listed first.

5. What is the total number of P-card transactions in the database? Label the column TotalTrans.

6. What is the total number of P-card transactions in the database with amounts more than or equal to $5,000? Label the column TotalTransGT5k.

7. What is the total dollar amount of transactions returned from January to March, inclusive, in 2014? Label the column TotalReturns2014Q1.

8. What is the total number of P-card transactions in the database for each year? Sort the data by year so the most recent date is listed first. Label the column with the counts as TotalTrans.

9. List the names of all cardholders sorted based on who had the most transactions in the 2014 calendar year. Display the name and number of transactions (labeled as NumTrans).

10. List the names of all people and sort based on who returned the greatest dollar amount of goods in the 2014 calendar year. Display the name, total amount of returned goods (labeled as ReturnedGoodsValue) and total number of returned transactions (labeled as ReturnedGoodsCount).

11. Compute the amount of sales tax for each transaction that would have been paid if OSU were required to pay sales tax in December 2014. The state sales tax rate is 4.5%. Display the month, year, cardholder name, description, vendor name, amount and computed sales tax amount (labeled SalesTax). Sort by the sales tax amount so the largest items appear on the top.

12. The university would like to know how much money it saved by not paying state sales tax. The state sales tax rate is 4.5%. If you assume all sales are made net of the state sales tax, how much money did OSU avoid paying in sales tax to the government on P-card transactions for each calendar year? Show the year, total amount spent and total amount saved in sales tax (label as SalesTaxSaved). Make sure to round dollar figures to the nearest cent using the Property Sheet, Format field (use the currency format) rather than using a formula. Sort the data by year with the most recent year listed first.

13. Related to problem 12., the university estimates it had to pay sales tax in other states 30% of the time. Reperform problem 12., but only compute the savings as 70% of the transaction totals instead of 100%. Make sure to round dollar figures to the nearest cent using the Property Sheet, Format field (use the currency format) rather than using a formula.

14. Compute the number of days between the transaction date and the posted date for all transactions in the month for November 2014. Display the month, year, cardholder name, description, vendor name, amount and amount of time (labeled as TimeDif). Numbers should be positive if the posted date took place after the transaction date. Sort by TimeDif and then the name of the employee so the items that took the most days appear on the top.

15. Create a risk ranking of returned items in 2014. Create a new column of data that shows whether something is high risk, medium risk or low risk based on the following criteria. If the return was equal to or more than $1,000, it is high risk; if the return is equal to or more than $500 and less than $1,000, it is medium risk; and if the return is less than $500, it is low risk. Display the year, name of the cardholder, description, vendor, amount of return and the new column (labeled RiskRanking). Sort the data so that the largest returns are listed first, and do not list any data that is not a return. As a hint, remember that returns are listed as negative numbers — make sure to think about this carefully.

16. OSU potentially can earn money from Amazon.com if employees make their purchases at smile.amazon.com and direct the earnings to OSU. Assume that for all transactions at the vendor Amazon.com, OSU would earn 0.75% for transactions $500 and more and 0.50% for transactions less than $500. What is the total amount that OSU would earn for 2014 based on these amounts? Label the column AmazonEarnings in your output.

17. Use a cross-tab query to display the total amount spent per MCC code each year. List the year across the top of the query. Sort in alphabetical order by MCC.

18. Use a cross-tab query to display the total number of transactions for each month of each year. Display each year across the top of the query and the months in the rows. Sort so that January is first, then February, etc.

19. Show all transactions that occurred in the year specified by the user. Sort the output by month and then within each month list the most expensive transactions first and then alphabetize them by the name of the employee. Display the year, month, cardholder name, the amount of the transaction and the vendor.

20. Show all transactions that occurred that match whatever part of the name the user entered for the vendor. For example, if the user enters the text Amz, then all transactions that have those letters, anywhere in the vendor name, should be returned. Display the names of the vendors and the total of all transactions (labeled TotalPurchases) made from that vendor in the database. Sort the results alphabetically by the name of the vendor.

These next queries require you to create multiple queries to solve them. That is, you will perform one query and then reference that query in a second (third, fourth, etc.) query to solve these problems.

1. Show all 2014 transactions sorted by the amount (with the most expensive transactions listed first) that are greater than the average transaction amount for 2014. Display the year, month, cardholder name, the amount of the transaction and the vendor.

2. List how much more or less was spent at each vendor in 2014 than in 2013. Only include companies for which there was a purchase in both 2013 and 2014. Display the vendor name and the difference in the amount spent in 2014 and 2013 (label as DiffSpending), with positive numbers indicating more was spent in 2014 than in 2013 (and sort the results so the largest increases in spending are listed at the top).

3. Show all 2014 vendors for which employees purchased more during the year than the total amount spent at the top vendor in 2013. Display the total amount of the transactions (labeled TotalSpent) and the vendor. Sort the results by the total amount of the transactions with the greater totals listed first.

4. Create a query that calculates the percentage increase and decrease in the number of transactions for 2012, 2013 and 2014 relative to 2011. That is, the query output should provide two columns, with the first being the year (using 2012, 2013 and 2014 as rows) and the second column labeled PercDiffFrom2010, and calculate the percentage increase and decrease in transactions since 2011 as follows: (# of transactions in 201X – # of transactions in 2011) / (# of transactions in 2011)*100 and round it to two decimal spaces using the Round formula. As a hint, make sure you do not count a field that has some null values, like the Description field, because this will give you the wrong answer.

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 only data for the 2014 calendar year, 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 with your results so your supervisor can review your work (if you use multiple queries, append FinalQuery to the end of the query name listed below for whichever query contains your final results).

  • The fourth column is where you should record a brief statement about the findings. Examples are, “There are no noted internal control violations,” or “There are X number of violations that will require additional follow-up.”

• 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.

Analytics mindset

P-card

Part VI:

Task 6

OSU’s internal controls prohibit transactions to purchase any of the following things:

• Alcohol

• Cash, cash advances, automated teller machine transactions

• Decorations

• Donations and sponsorships

• Gasoline

• Gifts, gift cards, gift certificates

• Insurance

• Late fees

• Mail and postage

• Moving expenses

• Personal purchases of any type

• Personal and individual memberships (cannot pay dues)

• Salary, wages and benefits

• Service and incentive awards (or any items purchased for an employee)

Required

• Build a form that will allow an auditor easily to perform tests to search for these transactions. The form should meet these requirements:

  • Have instructions on the form explaining how to use it

  • Allow the auditor to select the year to search

• Perform one of two different types of searches listed below. To do this, there should be a different button to perform each type of search. When you design the form, consider a way of structuring the form to make it intuitive, including how you label buttons.

  • Allow the auditor to search descriptions for possible key words that indicate internal control deviations (e.g., search descriptions for purchases of alcohol). That is, the form should have a text entry box that allows the auditor to enter a key word and click a button to run a query that contains that key word in searching the descriptions field. The query should return the relevant information about the transaction for the auditor to follow up in their investigation.

• • Allow the auditor to search vendor names for possible key words that indicate internal control deviations (e.g., search vendor names for purchases from the post office). That is, the form should have a text entry box that allows the auditor to enter a keyword and click a button to run a query that contains that key word in searching the vendor field. The query should return the relevant information about the transaction for the auditor to follow up in their investigation.

• Once the form is built, search for and report several possible transactions, individuals or vendors that should be investigated further for violation of these internal control policies. Document your results in the attached worksheet, by specifying the year, the search terms you used and the output you discovered. Write a brief statement about what additional tests you recommend performing on these transactions. Two examples are provided in the worksheet.

  • To receive full credit, you should document at least three additional different possible control violations (i.e., the findings shouldn’t all relate to a single category listed above but include at least three different categories).

    • Make sure you use professional judgment to eliminate false-positives. For example, purchasing rubbing alcohol for a laboratory would not be an example of a potential internal control violation even though alcohol was purchased.

    • Identifying larger-dollar-value control failures or high-volume control failures is superior to identifying less material internal control failures.

Analytics mindset

P-card

Part VII solutions:

Task 7

Internal auditors can help perform forensic audits to determine if fraud is taking place in the organization or by the credit card company.

Required

• Using the OSU 2014 calendar year data, perform several tests for the fraudulent transactions following and report your results. Some of these tests are more global and function like a risk assessment to provide a higher level of evidence suggesting fraud, and other tests are more granular, trying to find specific fraudulent transactions.

  • Label each of your solutions using the following convention: qry_T7_Question#, where # should be the question number. For example, the answer to question 3. should be labeled qry_T7_Question3. If you use multiple queries, then label the final query (i.e., the query with the solution) as qry_T7_Question3Final, making sure to replace the three in the example with the question number. Some queries will require multiple queries to get the final answer.

  • Note that just because these tests might suggest fraud or a higher likelihood of fraud, they are not definitive in nature. More work would need to be done to determine that fraud had in fact taken place.

1. Write queries to conduct a Benford’s analysis of the first digit of all transactions from OSU in 2014.2

   • The final output should be a single query that shows the percentage of transactions that begin with each of the digits one through nine.

   • From this analysis, suggest whether there is a low or high likelihood of fraud occurring.

   • Exclude all numbers less than 1.00 for this analysis and round your percentages to three decimal places.

2. Transactions should only be processed once. However, it is possible that the credit card company makes a mistake or a vendor submits a payment twice. Check for duplicate payments.

   • Assume that charges on the same day, to the same vendor, for the cardholder with the same first initial and last name, and for the same amount, are duplicates (if there are more than two charges, consider them all possible duplicate payments).

   • Make sure to exclude all returns from your results and sort them in ascending order by the transaction date and then by the vendor name.

   • Return the date of the transaction, the vendor, the cardholder name and the amount for additional testing.

3. The number of transactions to examine from the previous requirement is unwieldy. List the names of the individuals and how many times they have a possible duplicate transaction (not the number of duplicate transactions). This way, you can investigate those who appear most often on the list with infractions.

4. Round number transactions may indicate fraud. Return all transactions in 2014 that have four digits in the amounts field and end in 000 (ignore transactions with values in the cents).

   • Include the amount, vendor, description and name of the individual in the output.

   • Sort them by ascending, by vendor name and then employee name to look for any patterns.

1 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: http://opendatacommons.org/licenses/odbl/1.0/. Any rights to individual contents of the database are licensed under the Database Contents License: http://opendatacommons.org/licenses/dbcl/1.0/. The data for this case is made publicly available at 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.

2 To learn more about Benford’s Law, see Mark J. Nigrini, “I’ve Got Your Number,” Journal of Accountancy, http://www.journalofaccountancy.com/issues/1999/may/nigrini.html, accessed December 1, 2016.

Analytics mindset case studies – P-card 0

© 2016 Ernst & Young Foundation (US). All Rights Reserved.

SCORE No. 04134-161US