{"id":900,"date":"2020-02-01T13:54:14","date_gmt":"2020-02-01T13:54:14","guid":{"rendered":"https:\/\/alminawi.com\/?p=900"},"modified":"2025-12-07T18:21:09","modified_gmt":"2025-12-07T18:21:09","slug":"tts-series-e-internal-audit-fieldwork-and-evidence-collection-episode-09-data-analytics-caats-excel","status":"publish","type":"post","link":"https:\/\/e.americanpro.org\/?p=900","title":{"rendered":"T&#038;Ts Series E, Internal Audit, Fieldwork and Evidence Collection, Episode 09 &#8212; Data Analytics, CAATs, Excel"},"content":{"rendered":"<p data-start=\"113\" data-end=\"698\"><strong data-start=\"113\" data-end=\"151\">Introduction to the Tool\/Technique<\/strong><br data-start=\"151\" data-end=\"154\" \/>Computer-Assisted Audit Techniques (CAATs) and Microsoft Excel are powerful tools in the internal auditor\u2019s toolbox, particularly during fieldwork and evidence collection. These tools help auditors analyze large datasets, detect anomalies, and validate business processes efficiently. Excel remains widely used due to its accessibility, flexibility, and compatibility with most systems. When combined with CAATs techniques such as filters, pivot tables, conditional formatting, and formulas, Excel becomes a low-cost yet high-impact audit tool.<\/p>\n<p data-start=\"700\" data-end=\"894\"><strong data-start=\"700\" data-end=\"767\">Practical Template: Transaction Testing Worksheet (Excel-based)<\/strong><br data-start=\"767\" data-end=\"770\" \/>A useful template for applying CAATs in Excel is a <em data-start=\"821\" data-end=\"852\">Transaction Testing Worksheet<\/em>. This structured Excel workbook includes:<\/p>\n<ul data-start=\"895\" data-end=\"1359\">\n<li data-start=\"895\" data-end=\"1027\">\n<p data-start=\"897\" data-end=\"1027\"><strong data-start=\"897\" data-end=\"925\">Sheet 1: Raw Data Import<\/strong> \u2013 Unaltered transactional data exported from the system (e.g., general ledger, payroll, procurement).<\/p>\n<\/li>\n<li data-start=\"1028\" data-end=\"1156\">\n<p data-start=\"1030\" data-end=\"1156\"><strong data-start=\"1030\" data-end=\"1061\">Sheet 2: Criteria &amp; Filters<\/strong> \u2013 Key audit criteria (e.g., transactions above $10,000, weekend postings, duplicate payments).<\/p>\n<\/li>\n<li data-start=\"1157\" data-end=\"1259\">\n<p data-start=\"1159\" data-end=\"1259\"><strong data-start=\"1159\" data-end=\"1180\">Sheet 3: Analysis<\/strong> \u2013 Pivot tables, formulas (e.g., IF, VLOOKUP, SUMIFS), and flags for anomalies.<\/p>\n<\/li>\n<li data-start=\"1260\" data-end=\"1359\">\n<p data-start=\"1262\" data-end=\"1359\"><strong data-start=\"1262\" data-end=\"1287\">Sheet 4: Findings Log<\/strong> \u2013 Documented exceptions, comments, supporting evidence, and references.<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"1361\" data-end=\"1693\"><strong data-start=\"1361\" data-end=\"1420\">Practical Application: Real-Life Scenario at ACME Corp.<\/strong><br data-start=\"1420\" data-end=\"1423\" \/>At ACME Corp., the internal auditor Auren was assigned to review vendor payments as part of the Procurement to Payment (P2P) process audit. The CEO, Liora, emphasized the need for transparency, and Project Manager Aven wanted a fast turnaround to inform budget planning.<\/p>\n<p data-start=\"1695\" data-end=\"1823\">Auren exported 12 months of payment data into Excel from ACME\u2019s ERP system. He then populated the Transaction Testing Worksheet:<\/p>\n<ul data-start=\"1824\" data-end=\"2589\">\n<li data-start=\"1824\" data-end=\"2013\">\n<p data-start=\"1826\" data-end=\"1868\">In <em data-start=\"1829\" data-end=\"1838\">Sheet 2<\/em>, he applied criteria such as:<\/p>\n<ul data-start=\"1871\" data-end=\"2013\">\n<li data-start=\"1871\" data-end=\"1898\">\n<p data-start=\"1873\" data-end=\"1898\">Duplicate invoice numbers<\/p>\n<\/li>\n<li data-start=\"1901\" data-end=\"1931\">\n<p data-start=\"1903\" data-end=\"1931\">Payments on non-working days<\/p>\n<\/li>\n<li data-start=\"1934\" data-end=\"1966\">\n<p data-start=\"1936\" data-end=\"1966\">Payments to unapproved vendors<\/p>\n<\/li>\n<li data-start=\"1969\" data-end=\"2013\">\n<p data-start=\"1971\" data-end=\"2013\">Transactions exceeding approval thresholds<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<li data-start=\"2014\" data-end=\"2252\">\n<p data-start=\"2016\" data-end=\"2252\">In <em data-start=\"2019\" data-end=\"2028\">Sheet 3<\/em>, Auren used Excel\u2019s <strong data-start=\"2049\" data-end=\"2065\">pivot tables<\/strong> to summarize payments by vendor and date. He created <strong data-start=\"2119\" data-end=\"2145\">conditional formatting<\/strong> rules to highlight weekend payments and used the <strong data-start=\"2195\" data-end=\"2207\">COUNTIFS<\/strong> formula to spot potential duplicate entries.<\/p>\n<\/li>\n<li data-start=\"2253\" data-end=\"2445\">\n<p data-start=\"2255\" data-end=\"2445\">The analysis revealed that three vendors were paid twice for the same invoice within a five-day span. It also showed that a junior staff member approved payments above their authority limit.<\/p>\n<\/li>\n<li data-start=\"2446\" data-end=\"2589\">\n<p data-start=\"2448\" data-end=\"2589\">In <em data-start=\"2451\" data-end=\"2460\">Sheet 4<\/em>, Auren documented the findings, noted responsible parties, and added screenshots of flagged entries and approval trail extracts.<\/p>\n<\/li>\n<\/ul>\n<p data-start=\"2591\" data-end=\"2802\">In the wrap-up meeting, Auren presented the template to Aven and Liora, who appreciated the clarity and audit trail. They used the findings to tighten controls and train procurement staff on the approval matrix.<\/p>\n<p data-start=\"1175\" data-end=\"1260\">____<\/p>\n<p data-start=\"1175\" data-end=\"1260\"><span style=\"color: #339966;\">Upon reading the <span style=\"color: #008080;\"><strong><span style=\"color: #800000;\"><a style=\"color: #800000;\" href=\"https:\/\/americanpro.org\/?page_id=105\" target=\"_blank\" rel=\"noreferrer noopener\">Disclaimer<\/a><\/span><\/strong><\/span>, a reader may click <a href=\"https:\/\/americanpro.org\/wp-content\/uploads\/2020\/02\/TTs-Series-E-Internal-Audit-Fieldwork-and-Evidence-Collection-Episode-09-Data-Analytics-CAATs-Excel.xlsx\" target=\"_blank\" rel=\"noopener\"><span style=\"color: #3366ff;\"><strong>HERE<\/strong><\/span> <\/a>to download an example of a relevant tool<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction to the Tool\/TechniqueComputer-Assisted Audit Techniques (CAATs) and Microsoft Excel are powerful tools in the internal auditor\u2019s toolbox, particularly during fieldwork and evidence collection. These tools help auditors analyze large datasets, detect anomalies, and validate business processes efficiently. Excel remains widely used due to its accessibility, flexibility, and compatibility with most systems. When combined with [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":783,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[22],"tags":[],"class_list":["post-900","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tt-internal-audit"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/e.americanpro.org\/wp-content\/uploads\/2025\/06\/ED-21.gif","_links":{"self":[{"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/posts\/900","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=900"}],"version-history":[{"count":1,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/posts\/900\/revisions"}],"predecessor-version":[{"id":1299,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/posts\/900\/revisions\/1299"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=\/wp\/v2\/media\/783"}],"wp:attachment":[{"href":"https:\/\/e.americanpro.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=900"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=900"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/e.americanpro.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=900"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}