1 / 17

Data Analytics

Data Analytics. Updated: 3/6/2013. Agenda. About Us Define Data Analytics Data Diagram Tools to Analyze Data ACL Demo AP Testing Case Study SQL Demo SAS Demo JE Analysis Questions. Updated: 11/10/2010. Introductions. Aaron Wilton Maria Hao. Education Colorado State University

madge
Download Presentation

Data Analytics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Analytics Updated: 3/6/2013

  2. Agenda About Us Define Data Analytics Data Diagram Tools to Analyze Data ACL Demo AP Testing Case Study SQL Demo SAS Demo JE Analysis Questions Updated: 11/10/2010

  3. Introductions • Aaron Wilton • Maria Hao

  4. Education Colorado State University Bachelor Degree in: Accounting & Computer Information Systems Colorado State University Master of Accountancy Work Experience Deloitte (AERS Advisory) Certification CPA CISA Aaron Wilton

  5. Education Jiangxi University of Finance and Economics, China Bachelor Degree in: Accounting University of Denver Master of Accountancy Work Experience Prior to Deloitte Deloitte (AERS Advisory) Certification CPA CISA Yuhua (Maria) Hao

  6. Data Analytics • What is Data Analytics?

  7. Data Diagram Database #2 Database #1 Process Application (front-end): PeopleSoft, SAP, and some reporting tools (e.g., Essbase, Business Object), etc. Database (back-end): SQL, Oracle, etc. Interface: BizTalk, webMethods, etc.

  8. Tools to Analyze Data MS Excel MS Access SQL Server Management Studio Audit Command Language (ACL) SAS Others

  9. ACL Demo How to import data How to use the basic functions to run queries Advantage of ACL: • Cannot change the raw data • Log all the procedures performed

  10. Accounts Payable Testing Case Study Background: XYZ Corporation is a manufacturing client with offices at multiple locations. We have been engaged to provide services to the internal audit function of XYZ Corp. The client recently acquired a new business unit and is facing issues integrating the financial systems from the newly acquired business. The client is particularly concerned that these issues could be more significant around accounts payable processing and supplier master maintenance and could result in missing or invalid information or duplicate disbursements that might have been made due to lack of controls. The client is also unsure whether the user rights are properly set up to access/update various files. The internal audit function does not currently have the skill set to develop the data analytics that might help them better gauge the extent of the problem and the client has reached out to us for help. They would like us to test vendor master data and transactional information for the period starting 01/01/2009 to 12/31/2009.

  11. AP Risk Brainstorm Disbursements Vendor Master File Maintenance Open AP Other Fraud Risks

  12. AP Risk Brainstorm Disbursements • Duplicate payments • Inappropriate access • Active vendors only • Reconcile to authorized invoices • Timely (i.e. after invoice receipt) Vendor Master File Maintenance • SOD relative to disbursements Open AP • The Company’s Credit rating Other Fraud Risks • Sequential checks • Fictitious vendors

  13. SQL Demo Join several tables in the same time Deal with complicated calculations

  14. SAS Demo Benefits • Leaves Original data set untouched • Documented steps/procedures Disadvantages • Complicated, Steep Learning Curve

  15. Journal Entry Testing Entries made to unusual accounts • Purpose: To identify entries made to unusual accounts. • Logic: Performs word search within the account description field for the keywords specified in the AIF Users with few postings • Purpose: To identify users who posted less than X nonstandard entries or X standard entries during the period under review. • Logic: Total number of entries posted by each user for each type of population are calculated. • Users with less than the specified number of entries in the AIF are flagged. • Output: User analysis tab

  16. Journal Entry Analysis • Entries posted on holidays or weekends • Purpose: To identify entries posted on Saturdays, Sundays, or holidays. • Logic: Finds entries with a posted date that occurs on Saturdays, Sundays, or holidays. • The option to search for Saturdays, Sundays, or holidays is specified in the AIF • Output: Output contains entries flagged for each Saturday, Sunday, or holiday specified Large debits to revenue at the beginning of a quarter • Purpose: To identify journal lines that debit revenue for more than $X that were input and effective within the first Y days of a quarter. • Logic: • Finds all the journal lines that: • Were posted to accounts that are mapped to account subtype “Revenues”

  17. Questions ???

More Related