Analytics mindset: Pcard
Adapted for MGT1181 Introduction to IntegrationProfessional Decision Making
Case Due Date: As stated on Quercus.
Deliverables:
1. Case Report including a detailed description of your findings. At most 10 pagesappendices. 2. All Python source code either in a Jupyter Notebook .ipynb or a Python file .py. One file!
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 Pcards that are used on campus. Pcards 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 Pcard 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 Pcard records provided by the credit card companies.
You have been assigned the task of auditing all of OSUs Pcard transactions for 2014. To perform this audit, you received a file of all Pcard 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
1 The data for this case comes from the Purchase Card Pcard Fiscal Year 2015 database and is made available under the Open Database License: http:opendatacommons.orglicensesodbl1.0. Any rights to individual contents of the database are licensed under the Database Contents License: http:opendatacommons.orglicensesdbcl1.0. The data for this case is made publicly available at https:data.ok.govdatasetpurchasecardpcardfiscalyear2015. 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.
Analytics mindset case studiesPcard 12016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
For this case, you will perform the following tasks. Each task focuses on a different part of developing an analytics mindset.
Task 1Gain an understanding of OSU Pcard 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 Pcards.
Task 2Gain an understanding of the data used for testing.
Task 3Transform the data and load it into the appropriate tool for analysis.
Omitted and partially included in Task 5: Task 4Review or learn basic querying techniques. Covered in class using a variety of different examples.
Task 5Perform a test of various Pcard internal controls. Task 1
This task focuses on the first bullet point of developing an analytics mindsetAsk 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 Pcard guidelines stipulate that It is the intent of Oklahoma State University OSU to use a commercial purchasing card PCard to facilitate the acquisition of lower dollar goods and services needed for conducting official University business.
Required
Gain a more indepth understanding of the OSU Pcard guidelines and objectives by reviewing the Pcard 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 Pcard 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.
Analytics mindset case studiesPcard 22016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Using the PCard
Appendix
OSU PCard Guidelines
The cardholder is the only person authorized to make purchases using hisher card. Loaning a PCard 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 preapproval procedure.
Many companies provide discounts through their Education Sales Department, so ask for that department.
There should be no upcharge by merchants to use the PCard except where there are specific contractual arrangements with OSU to do so.
The following procedures should be followed for all purchases made by PCard:
Decision to Use PCard for Purchase
When making the decision whether to use the PCard 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 cardholders single transaction andor cycle limit.
Give fair treatment to all merchants and determine if the price obtained is reasonable.
Making the Purchase Using a PCard
There are three main methods of making purchases:
Over the CounterWhen making an over the counter purchase, the cardholder should:
Verify the vendor accepts VISA. If the merchant accepts VISA, provide the PCard 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 Departments 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
cardholders Bank Statement.
Mail, Phone, or FaxWhen 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.
Analytics mindset case studiesPcard 12016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
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 receiptinvoice and packing slip, of the purchase. The documentation must be maintained with the cardholders Bank Statement.
InternetWhen 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 merchants website.
Complete the necessary order process and provide cardholder information to include the billing
address that appears on the cardholders 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 descriptions, itemized cost, and total cost including shipping and handling.
Retain appropriate documentation, including receiptinvoice and packing slip, of the purchase. The documentation must be maintained with the cardholders Bank Statement.
Delivery Address
Items purchased with the PCard 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 ExceptionGoodsItems to be Shipped to a NonUniversity Address Form must be completed and approved by the Associate Vice President and Controller. This form can be found at http:controller.okstate.eduexceptionsformsguidelines. The approved form must be maintained as a part of the purchase documentation and attached to the cardholders 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 PCard need to be returned, the cardholder should:
Work directly with the merchant.
Carefully read all instructions enclosed with the order. A phone number andor instructions for
returning the materials are usually included on the receipt andor 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
Analytics mindset case studiesPcard 22016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
the restocking fee. A PCard 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 items is credited properly.
Retain appropriate documentation of the return and associated credit. The documentation must be maintained with the cardholders 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 merchants dock.
Card Denied
If the PCard is denied for any reason, the cardholder should contact Bank of America at the number on the back of the PCard. 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 PCard application form, or the phone number listed on the PCard 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 PCard transaction consists of the following steps:
Cardholder follows hisher departments preapproval procedure.
Cardholder makes a purchase from a merchant using the PCard 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 hisher transaction in Works, enters a detailed description and the purpose of the purchase, and signs off electronically on hisher transaction.
The transaction is routed to the cardholders approver. The approver reviews and electronically approves the transaction.
The transaction is routed to the cardholders 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 hisher receipts with the Bank Statement and forwards the Bank Statement, receipts, and other required supporting documentation to the cardholders 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
Analytics mindset case studiesPcard 32016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
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 PCard, authorization controls have been added to the PCard accounts. These limits are imposed at the point of sale when the card is swiped or applied. The available limits on a PCard include, but are not limited to:
Credit Limit dollar amount per cycleshall not exceed 50,000
Single Transaction Limit dollar amount per transactionshall 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 PCard 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 PCards 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 PCard purchase has been denied due to an MCC, contact the PCard Administrator.
Cardholder Limit Recertification
Cardholder limits will be reviewed annually to determine whether actual usage is consistent with a cardholders 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 PCard Administrator will recommend an adjustment be made to the card limits, 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 PCard Administrator and should provide justification for why the card limits 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.
Analytics mindset case studiesPcard 42016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Cardholder, Approver, and Accountant Responsibilities
Cardholder Responsibilities
When accepting a PCard, the cardholder becomes an authorized purchasing agent for the University and has certain responsibilities. These include:
Protection of the CardThe cardholder must protect the security of the PCard and the card number. The cardholder is the only person authorized to make purchases using hisher 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 PCard Administrator.
Limitations on MerchantsPurchases 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 PCard. The cardholder must not accept any gift or gratuity from any merchant when it is offered, or appears to be offered, to influence the cardholders decision regarding a PCard purchase.
Card ChangesThe 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 LimitationsThe cardholder accepts the responsibility for ensuring unallowable, prohibited, or restricted items are not purchased.
Receipt MaintenanceThe cardholder must ensure appropriate documentation, including the original invoicereceipt and packing slip, if applicable, is received and maintained for each purchase. The documentation is to be matched with the cardholders Bank Statement and forwarded to the cardholders accountant for review.
Transaction ProcessingThe cardholder is required to complete PCard training. The cardholder is responsible for timely review of hisher 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 hisher Bank Statement to verify the purchases and returns are accurately listed, and forward the Bank Statement and all supporting documentation to hisher accountant.
Prohibited Purchases
Certain types of purchases are prohibited by the StateState statutes. Certain purchases may be allowable if processed on a requisition, but may not be made with a PCard.
A PCard may not be used for the following:
Split PurchasesSplit purchasing means dividing or failing to consolidate a known quantity of goods or services for the purpose of evading the PCard single transaction limit of 5,000 andor a quotationbidding 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
Analytics mindset case studiesPcard 52016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
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 Payments5,000 per Fiscal YearMaintenance, leaserental, 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 TaxThe cardholder is responsible for ensuring Oklahoma sales tax is not charged at the time of purchase.
Cash, Cash Advances, Automated Teller Machine ATM Transactions
DonationsSponsorships
Gifts, Gift Cards, Gift CertificatesThe purchase of gifts is a violation of State statutes.
Expenses for food and mileage while in travel statusPer diem for food expenses and mileage may be claimed on a travel voucher.
Items that do not Serve a Business PurposeIncludes, but is not limited to, flowers, candy, meals, greeting cards, health care items, etc.
Personal PurchasesThe PCard may not be used under any circumstances to purchase items for personal use.
Apple Products For the Stillwater Campus OnlyApple 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.edufacstaffcomputers.php for more information. For associated data plans, see Restricted Purchases.
TradeinsIncludes any purchase involving the tradein of a University asset.
Conflict of Interest TransactionsA 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 EmployeeAny purchase from any company owned by any University employee must be bid.
University Departments andor AuxiliariesThe campus vendor invoice CVI system is to be used for the purchase of goods or services from University sources.
GasolineGasoline should be purchased from Transportation Services or with the gasoline credit card provided with each University vehicle.
Mail, PostageAll U.S. mail, including parcel post, certified, and registered mail, should be sent through University Mailing.
Weapons andor AmmunitionA requisition must be completed for the purchase of weapons andor ammunition.
Moving ExpensesA requisition must be completed for moving expenses for University employees.
Service andor Incentive Awards or Any Items Purchased for an EmployeeService andor
incentive awards or any items purchased for an employee must be processed on a requisition.
Analytics mindset case studiesPcard 62016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Late Fees
InsuranceInsurance must be processed on a requisition through Risk and Property Management.
Purchases for Student OrganizationsA tax exempt University PCard may not be used to make purchases for student organizations. A fiduciary fund PCard assigned to a student organization must be used.
Alcohol
Decorations
PersonalIndividual MembershipsPayment of personal memberships and dues are a violation of State statutes.
Prepayments or DepositsPrepayments 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.
SalaryWages andor Benefits
Other Purchases not Permitted under OSU Policies and Procedures, Purchasing Policies, and
State Statutes PCard Violations
Misuse of the PCard in any manner by a cardholder may result in revocation of the privilege to use the P Card, disciplinary action, termination of employment, andor the pursuit of any legal action available to the University.
Analytics mindset case studiesPcard 72016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Part II: Task 2
Analytics mindset Pcard
This task emphasizes the second bullet of developing an analytics mindsetExtract, 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 two data files:
AnalyticsmindsetcasestudiesPCardFY2014.csvAnalyticsmindsetcasestudiesPCardFY2015.csv
Each data file contains all Pcard transactions for one fiscal year the fiscal year and calendar year are different. Combined, the data files contain all Pcard transactions for fiscal years 2014 and 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 Pcard. Last names are not unique.
Cardholder First Initial: This is the initial of the first name of the person who is responsible for the Pcard. Initials are not unique.
Description: This is a general description of the nature of the purchase.
Amount: This is the amount charged to the Pcard, 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.
Analytics mindset case studiesPcard 12016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
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.
Varietydifferent 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?
Velocityfrequency 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?
Veracitytrustworthiness 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?
Volumethe 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 case studiesPcard 22016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Part III: Task 3
Analytics mindset Pcard
This task continues to focus on the second bullet point of having an analytical mindsetExtract, 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.
We will use Python for this purpose.
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.orgInterestAreasFRCAssuranceAdvisoryServicespagesauditdatastandardworkinggr oup.aspx. Relevant information for this case from the Base Standard is produced below.
When dealing with flat files, the standards recommend using pipedelimited, UTF8 text files.
Analytics mindset case studiesPcard 12016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Each file should include a header record that lists the field names for each data field. Field names should conform to the following specifications:
Data type
Standard
Text
Text is left justified with no leading or trailing blank spaces.
Numeric
Currency symbols and thousands separators for example, commas should not be used.
Decimal symbols must be included and must be a period ..
Decimals must be included for nonwhole numbers.
Negative numbers should be indicated with a minus signpreceding the number.
Date
ISO 8601the date should be CCYYMMDD.
For example, April 3, 1982, should be listed as 19820403 or as 19820403. Note that if day is excluded, the format is YYYYMM and should not be YYYYMM. This is to avoid confusion with some date formats that are still used but do not follow this pattern.
Note that CC stands for century, and year can also be referenced as YYYY.
Time
ISO 8601time is represented in 24 hours HHMM, for example, 1:00 p.m. is 1300.
Boolean
This is true or false.
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 CustomerName.
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 two 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 close to what Excel can handle in a single sheet namely 1,048,576 lines of data. Considering that we would like to include more than just two years into our data set, another tool is required here to work with the data.
Analytics mindset case studiesPcard 22016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
As you write your Python code, here are some important tips to keep in mind:
Although the AICPA data standards suggest pipedelimited files that is, files that use a vertical line delimiter instead of a comma or other delimiter, use a commadelimited or .csv file for all parts of this case.
You should import all files into a single data set.
Hint: Using pandas, datasets can be easily concatenated if they have matching column names: datasetpd.concatdataset1, dataset2
For the TransactionDate and PostedDate, the format used is MMDDYYYY. For example, May 4, 2002, would be formatted as 05042002. You need to ensure that the date is recognized correctly and the data type is datetime.
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 before merging the files into one.
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?
Safe a copy of your final output into a file.
Analytics mindset case studiesPcard 32016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Part V:
Task 5
Analytics mindset Pcard
One of the key objectives of your internal audit is to determine if employees of OSU are following internal controls.
Required
You are only interested in transactions from OSU. Make sure to screen transactions so you only analyze those that pertain to OSU.
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.
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 higherrisk transactions, employees and vendors that should be targeted for additional testing. Carefully discuss your findings in the case report.
Analytics mindset case studiesPcard 12016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No.
Internal control
Test to perform and desired output
1. User shall not spend more than 5,000 per transaction.
Display all transaction details Amount, Name, Description, Vendor, TransactionDate, PostedDate and MCC for any transaction in 2014 that was for more than 5,000. Sort by the total transaction amount in decreasing order.
2. User shall not spend more than 50,000 per year.
Display the name and total amount spent during the year for all employees who spent more than 50,000 in 2014. Sort by the total amount spent with the larger amounts listed first.
3. User shall not spend more than 10,000 per month without approval.
Display the name, total amount spent during the month and the month for all employees who spent more than 10,000 per month in 2014. Sort by month January listed first and then total the amount spent with the larger amounts listed first.
4. An amount more than 5,000 should not be split between two or more swipes of the card by the same person.
Display all transaction details where the vendor and purchaser are the same on a specific day, there is more than one transaction for the day and the combined total of the transactions was more than 5,000. Sort them in ascending order by the TransactionDate.
5. Continued from 4.
Count how often each individual purchaser did not follow the previous restriction. Sort this in descending order by the count.
Analytics mindset case studiesPcard 22016 ErnstYoung Foundation US. All Rights Reserved.
SCORE No. 04134161US
Reviews
There are no reviews yet.