Building Simple Continuous Reviews in ACL

  • Published on

  • View

  • Download


Continuous auditing and monitoring (continuous reviews) has been discussed for decades but implemented in moderation based on recent surveys. It comes down to how much are data analytics integrated into our audit processes initially to then become continuous. If a high degree of integration exists, then there is probably a good amount of continuous reviews happening in the organization already. However, most companies fall into the other camp and have not integrated analytics well enough or considered how to take full advantage of continuous reviews. This course will explain culturally what audit departments must do to embrace continuous reviews and how that can be integrated with ACL Desktop software techniques. Sample files and scripts will be provided to get you started down the road to continuous reviews. As regulatory changes sweep the globe, auditors, risk management, and compliance professionals are using more sophisticated tools, and methods. Using a live/video training library approach, we help companies of all sizes use audit and assurance software to improve business intelligence, increase efficiencies, identify fraud, test controls, and bottom line savings. AuditNet and Cash Recovery Partners Webinar recording available at and (registration required) Recording free to view. Sample Data Files for All Courses are available for $49 To purchase access to all sample data files, Excel macros and ACL scripts associated with the free training visit AuditSoftwareVideos.


  • Building Simple Continuous Reviews in ACLTM July 2, 2014 AuditNet and Collaboration Brought to you by and AuditNet, working together to provide: Practical audit software training Resource links Independent analysis Tools to improve audit software usage Today focused on providing practical data analysis training Page 1
  • About Jim Kaplan, CIA, CFE President and Founder of AuditNet, the global resource for auditors (now available on Apple and Android devices) Auditor, Web Site Guru, Internet for Auditors Pioneer Recipient of the IIAs 2007 Bradford Cadmus Memorial Award. Author of The Auditors Guide to Internet Resources 2nd Edition Page 2 About AuditNet LLC AuditNet, the global resource for auditors, is available on the Web, iPad, iPhone and Android devices and features: Over 2,000 Reusable Templates, Audit Programs, Questionnaires, and Control Matrices Training without Travel Webinars focusing on fraud, audit software (ACL, IDEA, Excel), IT audit, and internal audit Audit guides, manuals, and books on audit basics and using audit technology LinkedIn Networking Groups Monthly Newsletters with Expert Guest Columnists Book Reviews Surveys on timely topics for internal auditors Introductions Page 3
  • Webinar Housekeeping Page 4 Thiswebinaranditsmaterialarethe propertyofCashRecoveryPartnersLLC. Unauthorizedusageorrecordingofthis webinaroranyofitsmaterialisstrictly forbidden.Wearerecordingthewebinar andyouwillbeprovidedwithalinktothat recordingasdetailedbelow.Downloadingor otherwiseduplicatingthewebinarrecording isexpresslyprohibited. Webinarrecordinglinkwillbesentviaemail within57businessdays. NASBArulesrequireustoaskpolling questionsduringtheWebinarandCPE certificateswillbesentviaemailtothose whoanswerALLthepollingquestions TheCPEcertificatesandlinktotherecording willbesenttotheemailaddressyou registeredwithinGTW.Wearenot responsiblefordeliveryproblemsdueto spamfilters,attachmentrestrictionsorother controlsinplaceforyouremailclient. Submitquestionsviathechatboxonyour screenandwewillanswerthemeither duringorattheconclusion. AftertheWebinarisoveryouwillhavean opportunitytoprovidefeedback.Please completethefeedbackquestionnairetohelp uscontinuouslyimproveourWebinars IfGTWstopsworkingyoumayneedtoclose andrestart.Youcanalwaysdialinandlisten andfollowalongwiththehandout. Disclaimers 5 Theviewsexpressedbythepresenters donotnecessarilyrepresentthe views,positions,oropinionsofAuditNetorthepresentersrespective organizations.Thesematerials,andtheoralpresentationaccompanyingthem, areforeducationalpurposesonlyanddonotconstituteaccountingorlegal adviceorcreateanaccountantclientrelationship. WhileAuditNetmakeseveryefforttoensureinformationisaccurateand complete,AuditNetmakesnorepresentations,guarantees,orwarrantiesas totheaccuracyorcompletenessoftheinformationprovidedviathis presentation.AuditNetspecificallydisclaimsallliabilityforanyclaimsor damagesthatmayresultfromtheinformationcontainedinthispresentation, includinganywebsitesmaintainedbythirdpartiesandlinkedtotheAuditNet website Anymentionofcommercialproductsisforinformationonly;itdoesnotimply recommendationorendorsementbyAuditNet
  • Richard B. Lanza, CPA, CFE, CGMA Over two decades of ACL and Excel software usage Has written and spoken on the use of audit data analytics for over 20 years. Received the Outstanding Achievement in Business Award by the Association of Certified Fraud Examiners for developing the publication Proactively Detecting Fraud Using Computer Audit Reports as a research project for the IIA Recently was a contributing author of: Global Technology Audit Guide (GTAG #13) Fraud in an Automated World Institute of Internal Auditors. Data Analytics A Practical Approach - research whitepaper for the Information System Accountability Control Association. Cost Recovery Turning Your Accounts Payable Department into a Profit Center Wiley and Sons. Please see full bio at Learning Objectives Why it is important to development continuous monitoring applications in key areas of your business to reduce audit time while increase audit quality. Integrating analytics in all areas of the audit process from audit planning to follow up. Outline an effective data request process to ensure complete and accurate extractions of data every time. Discuss auto-normalization techniques of data to speed the reporting process. See how analytics can maximize the annual audit plan and better ensure focus is placed on organizational risk. Understand how to develop Excel / CSV export routines that auto-export data from ACL at the end of processing. Pros and cons of specific software tools when implementing data analytics and continuous reviews. Understand some common approaches to overcoming obstacles to planning data analytics based on case studies from companies and survey attendees themselves. Page 7
  • Continuous Audit / Continuous Monitoring - Why Its Important and Audit Software Integration Page 8 Analytic Command Center Auditor in a Can Page 9
  • Top Audit Software Implementation Practices Page 10 Audit Analytic Best Practices Automate the data import and normalization process Develop Excel macros to improve client provided data quickly Automate the data extraction, file cleansing, and data validation Everyone is different with their data so normalize Reduce false positives in the final reports Prioritize the likelihood of findings Use mathematical scoring Document, save, and possibly videotape your work plan Audit logs in software, flowcharts, and documents take minutes to develop to the hours to later remember what was done Video editing (Camtasia) can be used to show how to run applications for future auditors Page 11
  • Think Prevention vs. Future Detection Identify issues earlier in their lifecycle Build a aura of deterrence within procure to pay Build a Continuous Review Process Technology Minimal staff time / external assistance Keep Improving the Model Model on identified frauds Remove false positives to isolate interesting events Best Practice Approach on Fraud - Find It Before It Finds You Data Marts and Server Benefits Centralized data and backups Audit knowledge is saved in one place Remove the Learn and Leave dilemma facing companies Security and user management Better data than the business units Faster processing for large data sets Page 13
  • Data Marts and Server Reality Most audit areas dont require it Data is only needed once a year or more for some audits Data extraction routines can be saved by IT and audit Server speed is not needed for majority of audits Sometimes difficult to explain the ROI to I.T. if smaller example audits are not completed first High initial cost of a sledgehammer for hanging a picture May turn off management to all data analytics Page 14 Value of ACL Scripting Useful for routine tasks Can be for periodic audit steps Also can assist in importing similar data For example, OPEN FILE 1, EXTRACT RECORD, OPEN FILE 2, EXTRACT RECORD, etc. Build the quality in once Set it and forget it Learn how ACL works behind the scenes Value of learning ACL for DOS Page 15
  • Making it Continuous Does not make sense for every audit area Makes sense for general ledger in all companies Find willing business units who want to self-review Start small, roll out audit applications, and have a management plan that does NOT include audit except for business requirement changes Audit can have access and should periodically kick the tires Page 16 Polling Question 1 Polling Question Displayed during the Webinar
  • Effective Data Request Process and Auto-Normalization Page 18
  • 20 Theres Gold In That Data Taken from the 2007 Buyers Guide to Audit, Anti-Fraud, and Assurance Software Data Request is Sent Prior..11/14/12 Webinar Page 2111/14/12
  • Top 10 Data Import Mistakes 1. Not knowing what is possible within the tool to import and normalize data 2. Asking for data before understanding reporting needs 3. Not including knowledgeable system professionals to assist in or review the extract 4. Forgetting to run statistics on amount/date fields 5. Not summarizing text code fields (including invoice numbers to find E+ issues) Page 22 Top 10 Data Import Mistakes 6. Lack of hardcopy information for review in relation to imported data 7. Not validating field totals to batch totals 8. Using report files vs. fixed length system files 9. Getting data in Excel vs. a more raw format 10.Lack of understanding of the various data types Page 23
  • Clear Data Request Accounts Payable Data Request.doc Data Import Module From Excel Import Script Code.xlsx
  • Polling Question 2 Polling Question Displayed during the Webinar Maximize the Audit Plan and Building an Organizational Viewpoint Page 27
  • Data Analytics in the Audit Plan AuditNet Nov. 14, 2012 Page 28 Process to Report Mapping Page 29
  • Mapping Data and Objectives 30 Query Viewpoints
  • Query T&E Reports Unmatched query of cardholders to an active employee masterfile Cards used in multiple states (more than 2) in the same day Cards processing in multiple currencies (more than 2) in the same day Identify cards that have not had activity in the last six months Cardholders that have more than one card Extract any cash back credits processed through the card Extract declined card transactions and determine if they are frequent for certain cards Data Mining T&E - Its The TrendsRight? Trend categories (meals, hotel, airfare, other) Trend by person and title Trend departments Trend vendors Trend in the type of receipts Trend under limits (company policy) 33
  • Transactional Score Benefits The best sample items (to meet your attributes) are selected based on the severity given to each attribute. In other words, errors, as you define them, can be mathematically calculated. Instead of selecting samples from reports, transactions that meet multiple report attributes are selected (kill more birds with one stone). Therefore a 50 unit sample can efficiently audit: 38 duplicate payments 22 round invoices 18 in sequence invoices .and they are the best given they are mathematically the most severe. 34 Pick Items Rare in Several Ways Dont choose just ANY weekend invoice Choose an UNUSUAL weekend invoice Large weekend invoices are the rarest kind (i.e., only 2 percent of large invoices) The odds of finding a recoverable error go up AND since the invoice is large, the value recovered goes up too! Page 35
  • Summaries on Various Perspectives 36 Summarizeby dimensions(andsub dimension)topinpoint withinthecubethe crossoverbetweenthetop scoredlocation,time,and placeoffraudbasedon thecombinedjudgmental andstatisticalscore ALLTIESBACKTOTHE ORIGINALANALYTIC APPROACH Combining the Scores ACL Code Page 37
  • Obtain data at least once a year for use in the planning exercise Basic data may all that is needed from key systems and locations Run a series of tests across each locations data Consider focusing on general ledger given its broad exposure to the business Consider using risk scoring in a consistent fashion across the company Prototype a few local audit toolkits each year Most cost effective way to develop and ACL is cheaper than almost any other development platform for finance/audit professionals Identify low hanging fruit for cost savings and improvements. Considerations for Overall Audit Planning Normalize and test general ledger data consistently on all locations Most internal audit shops obtain general ledger data quarterly for their external auditors anyway no excuse G/L data is the lifeblood of the organization Planning will improve as focus can be placed on key accounts, entries, timeframes, enterers, etc. and will guide future data extracts Most systems post in detail (even down to inventory movements) which can allow detailed review of subledgers.using G/L data Identify savings, better ideas,and fraud Fraud has been the focus and we should still test for it Savings in cost recoveries can now become more of a focus Better ideas leveraged through technology Efficiency (to help a faster close) Revenue and business enhancement Key Mantras of G/L Analytic Auditing
  • Journal Entry Surveillance System (J.E.S.S) Overall Review Objectives Model normal account behavior Identify unusual changes in activity Over time By account sequence In amount In value (I/S, B/S, Net effect) In enterers behavior or words entered Key concern accounts Detect unusual journal entries based on past risk factors and expected schemes Monitor unauthorized enterers Reconcile GL activity to trial balance figures (completeness) and financial statements Profit Opportunities Outweigh Analytic Costs Accounts Payable Audit Fee Benchmarking Advertising Agency Document Fleet Freight Health Benefits Lease Media Order to Cash Proactive Fraud Detection Project Fraud Real Estate Depreciation Sales & Use Tax / VAT / R&D tax Strategic Sourcing Telecom Travel and Entertainment Utilities
  • Cost Recovery Opportunity Tests A/P and G/L Review Factors Accounts that are sole sourced Accounts that have too many vendors Categories that map to the recovery list Assess to industry cost category benchmarks Top 100 vendors Trend analysis over time Trend analysis by vendor (scatter graph) Polling Question 3 Polling Question Displayed during the Webinar
  • Auto-Workpaper Export Routines Page 44 Polling Question 4 Polling Question Displayed during the Webinar
  • Pros and Cons of Tools When Implementing Continuous Analytics & Overcoming Obstacles Page 46 Most Popular Audit Software Products ACL / AX ActiveData for Excel Arbutus Software IDEA Microsoft Access Microsoft Excel TopCaats
  • Tool Selection Considerations Core processing features Advanced features Advanced data import Scripting Ease of use Training / Customer Support / User Groups Years in business / Company sustainability Workpaper system integration Proposal Decision Analysis Page 49 earch_reports
  • Use data analytics on almost every audit Brainstorm the use data analytics in the audit planning process Drop an audit and instead plan 10% for data fun across all audits / Understand the process and benchmark it for future reviews Make it part of annual objectives to use the product Find cost savings to pay for the usage & Track it & Promote it Risk assess the general ledger stratify by month by account J/E risk scores Search for cost recovery opportunities Sell the need for increased software and education Also sell the cost/benefit of ACL to other development platforms (especially for prototyping). Overcoming Obstacles Page 50 Plan on education and make it routine Meet every three weeks on analytics no matter what Build a team of champions to coach the team Get everyone else involved..even if it is with Excel to start Make it part of annual objectives to learn the product Establish data channels to all (material) parts of the organization Have more freedom to extract data as needed Obtain quarterly feeds of data Obtain data from all locations once a year for audit planning Overcoming Obstacles Page 51
  • Polling Question #5 Page 52 Polling Question Displayed during the Webinar Questions? Any Questions? Dont be Shy! Page 53
  • In the Queue Sampling in Excel and Other Simple Add-In Products Aug 13 ACL Basic/Intermediate Scripting Sept 10 Financial Statement Analysis to Journal Reviews With Excel Tools Oct 7 Videos accessible for FREE subscriptions Repeat video and text instruction as much as you need Sample files, scripts, and macros in ACL, Excel, etc. available for purchase Bite-size video format (3 to 10 minutes) Page 55 >> Professionally produced videos by instructors with over 20 years experience in ACL, Excel , and more
  • Thank You! Jim Kaplan AuditNet LLC 1-800-385-1625 Richard B. Lanza, CPA, CFE, CGMA Cash Recovery Partners, LLC Phone: 973-729-3944 Cell: 201-650-4150 Fax: 973-270-2428 Email: Page 56


View more >