Transcription
1/14/2013Effective Use of Data Mining Toolsin AuditsTom Poon, Senior Assoc. Director, San FranciscoMike Lee, Staff Auditor, San FranciscoLaura Bishin, Principal Auditor, RiversideWhat are CAATs?Computer Assisted Auditing Techniques (CAATs)and Data Analytics Tools include: ACL (Audit Control Language) Brio IDEA (Interactive Data Extraction & Analysis) Microsoft Excel Microsoft Access SQL & Other Reporting/Query ToolsWhat can CAATs do? CAATs can enhance audit projects by:– Allowing for increased scope coverage– Broader samples– Identifying exceptions– Trending analysis– Producing scripts for continuousmonitoring/process automation for clients– Direct access to data1
1/14/2013Case Examples1. Effort Reporting (UCSF)2. Mission Bay Hospital Construction Project ‐Invoice Validation Process Automation (UCSF)3. Contracts & Grants (UCR)4. Travel & Entertainment (UCR)5. Conflict of Interest (UCR)Case #1: UC San FranciscoSOM EFFORT REPORTINGSUMMARYRequest & Challenges Request from School of Medicine Dean to provide asummary of FY11 effort reported by the SOMdepartment faculty and students/postdocs tosponsored and non‐sponsored awards. ERS Data is housed in OP’sOP s Hosted ApplicationsGroup – required significant effort and coordinationto define the criteria for data request. Data does not contain hierarchical departmentalinformation, title codes or definitions of Core/Non‐Core Faculty.2
1/14/2013Solution & Results Combined data from various sources to produce areport:– ERS extract for all SOM departments from OP HostedApplications Group– Summarize Distribution of Payroll Expense reports todetermine employees’ primary title code for the scopeperiod.– SQL Query provided by SOM to determine ‘Core Faculty’,‘Non‐Core faculty’, student and postdoc title codes.– Payroll and Personnel Systems to determine departmentalroll‐up information.Data Elements Used / MapCase 2: UC San FranciscoMB HOSPITAL INVOICE PROCESSINGAUTOMATION USING VBA3
1/14/2013Request Create an automated process that will reduce the needfor manual invoice verification. Monthly verification of 15 of invoices from 9 sub‐contractor by 2 Project Accountants that includes :– Validate the labor invoices for missing information,informationcalculation errors, etc.– Identify employees with charged rates that do notmatch agreed‐upon contract rates based on jobclassification or over‐scale pay agreements.– General summary of overtime hours worked and thepremium pay totals by work week & subcontractorChallenges Soft‐copy invoices are provided by subcontractorsthat do not conform to standard format. Manyformatting inconsistencies & errors (e.g. spelling,missing fields, etc.) Large number of over‐scale employees andapprentices receiving part‐time pay. Rates change at least every 6 months (requires aneasier way to update the key tables)Proposal: Excel VBA Template Created an Excel template with built‐invisual basic scripts.– ACL, IDEA and Access are far less forgiving of non‐standardized data.data– Easier learning curve for the clients – more peopleare already familiar with Excel– Password‐protected key‐tables (rate tables andoverscale employees) are relatively secure andeasier to maintain4
1/14/2013Step 1: Data ValidationData validation macro cleans up the soft‐copy invoices andchecks for instances of: Missing fields Amountcalculationl l i errors Negative rates Adjustments(e.g. negative or 40 hours)Allows users tomake changesdirectly.Step 2: Rate ChecksRate check macro then tests the cleaned invoices toidentify: Mismatchesbetween chargedrates and agreedrate by title. Mismatchesbetween chargedrates and over‐scale rates byemployeeStep 3: Overtime TrackingOnce the rates are verified, the macro producesan overtime report that includes: Summary of total overtime and double‐timehours worked by work week The total ‘premium’ pay for the work week– (OT/DT rate – REG rate)*OT/DT hours worked5
1/14/2013Case 3: UC RiversideCONTRACTS & GRANTSEVOLUTION TOWARDS CONTINUOUS MONITORINGContract & Grants ReportsWe produced this information for our audits, but theunits didn’t have such information readily available: Deficit Trend and Aging Reports Transactions after Fund End Date Cost Transfers ReportsUnits became very interestedin our reports!!!C&G – Deficit Trend ReportMocked Up Data ‐ 4 Depts and Unit Total ‐ C&GDept DescrDepartment 1Department 2Department 3Department 4Total Unit2013‐Per 5 2013‐Per 4 2013‐Per 3 2013‐Per 2 2013‐Per 1 2012‐Per 2012‐Per 2012‐Per 2012‐Per 9 2012‐Per 8 2012‐Per 7 2012‐Per 6 2012‐Per 5 2012‐Per 4 2012‐Per 3 2012‐Per 2 2012‐Per 24,630)(32,500) ,262)mockup ‐ sample 4 depts & unit C&G deficit trend‐2013‐Per 2013‐Per 2013‐Per 2013‐Per 2013‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 2012‐Per 000)(80,000)(100,000)(120,000)Department 1Department 2Department 3Department 4Total Unit6
1/14/2013C&G – Deficit AgingDeficit Aging from Fund End Date AnalysisAs of Date: 9/30/2010Mock Sample DepartmentFundFund rrentClosedPeriod 3 9)(26,669)(53,088)1‐ 5)7 ‐ 11Months‐‐‐‐‐‐‐‐‐‐‐1 Year(518)‐‐‐‐‐‐‐‐‐(518)2 Years3 Years4 Years5 �‐‐‐‐‐‐‐‐‐‐‐‐Dept. 20,321)(1,645)(889)(26,669)(59,160)Case 4: UC RiversideTRAVEL & ENTERTAINMENTTravel & Entertainment– Travel 21 days– Number of days travel voucher is in system beforefinal approval– Stats on rejects (Departmental vs AP)– Transient Occupancy Tax– Premium Parking– Meals pushing max daily cap– Final approver doesn’t report to traveler7
1/14/2013Case 5: UC RiversideCONFLICT OF INTERESTConflict of Interest– Schedule of Classes to determine who wasn’tteaching either all year or most of the year– Address information – identify non local address– Faculty on payroll (sabbatical info)– Cross ref data above & select faculty to test.– Google search, ratemyprofessor.com, other socialmedia sites (i.e. LinkedIn, Facebook)– Review Travel & Shipping Expenses– Review Phone activity (or lack of)8
Effective Use of Data Mining Tools in Audits Tom Poon, Senior Assoc. Director, San Francisco Mike Lee, Staff Auditor, San Francisco Laura Bishin, Principal Auditor, Riverside What are CAATs? Computer Assisted Auditing Techniques (CAATs) and Data
