1 http://www.caats.ca/acl.html Functions in ACL Function UPPER(), LOWER(), PROPER() MATCH( comparison_value , test1 , test2 <,test3 ...

> ) ABS(value) SUBSTR(string, start, length) CTOD(field, date format) Date(date) DOW(Date) CDOW(date) STRING(value, length) Value(string, decimals) MOD(number, divisor) INCLUDE(string, chars_to_include) ECLUDE(string, chars_to_exclude) AGE(date1, date2) LEN(string) LTRIM(string) TRIM(string) ALLTRIM(string) Verify(field) BETWEEN(string, min, max) Logical (T or F) Description Changes the case of a character string Compare value with a list of values; T if at least one match Absolute value of number Returns substring of a string Convert text to date Convert date to text Determine Day of Week Converts Numeric Value to a string Converts string to a numeric value Remainder of Number when divided by divisor Removes all characters except include string Returns all characters except exclude string Date1 - date2 Calculate length of a string Removes leading blanks Removes trailing blanks Removes leading and trailing blanks Checks integrity of field with field type Selects only records where the value is >= min and <= max Example UPPER(dave) = "DAVE" LOWER('DAVE') = "dave" PROPER("DAVE code")= "Dave Code" MACTH("DAVE", NAME, INIT, LNAME) - T if NAME="DAVE" or INIT="DAVE" or LNAME="DAVE" ABS(-12.3) = 12.3 SUBSTR("GARY",2,2) = "AR" SUBSTR("DAVE",4,1) = "E" CTOD("20010301") = 2001/03/01 DATE(2001/03/01) = "2001/03/01" DOW(`20010301`) = 5 CDOW(`20010301`) = "THU" STR(123.5,5) = " 123.5" STR(645,1) = "5" VALUE("123",2) = 123.00 VLUE("12.3",2) = 12.30 MOD(25,5) = 0 MOD(14,3) = 2 INCLUDE("9A6","0987654321")="96" INCLUDE("A55A2","A") = "AA" EXCLUDE("N/A","/") = "NA" EXCLUDE("99-OCT","-") = "99OCT" Age(`20010331`,`20010415`) = 15 LEN("David") = 5 LEN("Gary") = 4 LTRIM(" ABC") = "ABC" TRIM("DAVE ") = "DAVE" TRIM("G IVINGS ") = "G IVINGS" ALLTRIM(" Johnny ") = "Johnny" VERIFY(DATE) - T if record contain valid date BETWEEN(Name,"D","F") = all names starting with D, E or F) BETWEEN(Amount,2,6) = amounts >=2 and <=6

FIND(string, field) MAX(num1,num2) MIN(num1, num2) AT(pos,#,,search_for,string) OCCURS(string, search_for) SPLIT(string, separator, segment) RECNO() LAST(string, length) INSERT(string, insert_text, location) REPLACE(string, old_text, new_text) MAP(string, format) Logical (T or F)

Find a string

FIND("Dave", NAME) - T is NAME contains "Dave" (not case sensitive) Returns Maximum of two MAX(2,5) = 5 values MAX(8,2) = 8 Returns Minimum of two MIN(2.3,7) = 2.3 values MIN(8,3) = 3 Returns position of AT(2,"B","ABBA") = 3 search_for_string in field AT(1,"0","1234") = 0 Counts the number of OCCURS('ALLAN','A') = 2 times the search_for string OCCURS("YNNYYN","Y") = 3 occurs in the field Creates a field based on SPLIT("Johnny,Shilo,1233 Main separator and specified St,Ottawa,",",3) = "1233 Main St" segment Record Number The last 'x' characters of the field Inserts new text into field at specified location Replaces specified character with new characters Test string to see if it is in format specified: x-alph; 9-numeric; ? - any char; \ - a literal Repeats given string 'x' times Creates a character string of 'x' blanks Identify records where field is blank T if specified position contains string Reverse the character string Right justifies field RECNO() LAST("Johnny Shilo",5) = "Shilo" INSERT(Phone,'-',4) 9960784 = 996-0784 REPLACE("2000-0-01","-","/") = "2001/03/01" MAP(Date,'99/99/99") checks date field to see if data is in format 99/99/99. MAP('99-03-26','99/99/99') = False MAP('99/03/26','99/99/99') = True MAP('K1A0K2','x9x9x9') = True REPEAT("A",3) = "AAA" REPEAT("AB",2) = "ABAB" BLANKS(3)=" " ISBLANK(Phone) = records with no phone number TEST(72,".") - T is position 72 contains a decimal point (dot) REVERSE("Music") = "cisuM" RJUSTIFY("ABC ") = " ABC"

REPEAT(string, count) BLANKS(count) ISBLANK(string) Logical (T or F) TEST(position, string) REVERSE(string) RJUSTIFY(string) Copyright Dave Coderre 2007

4 ACL Command - Explanation FILTER - Show me only records where .. a condition is true COUNT - How many ? - counts number of records TOTAL - How much ? - totals one or more numeric fields STATISTICS - Minimum, Maximum, Average, Standard Deviation, etc. SEQUENCE - Is file sorted (sequenced) on DUPLICATE - Duplicate Records - one or more criteria (key fields) GAPS - Are there any missing records? CLASSIFY - Total of one or more numeric fields by any Character field STRATIFY - Ranges of a Numeric field (0-100, 101-200, 201-300 ) AGE - Number of Days past a Cutoff Date CROSS TAB - Creates rows and columns for specified fields, totaling a numeric field SUMMARIZE - Totals by one or more Character fields - can list other fields SORT - Re-order the data - creates a new data file (faster processing) INDEX - Re-orders the data - using an index (less space required) EXTRACT - Create a new file containing only some of the records and/or fields EXTRACT - APPEND - Add records to the bottom of another file - must have same table layout MERGE - Combine two sorted files into one sorted file same table layout EXPORT - Send the data to another software package (Excel, Word, etc.) JOIN - Combine two files to create a third file - Matched, Unmatched, Primary, Secondary, Primary Secondary. The Secondary file must be sorted RELATE - Logically combine up to 18 files - Children must be indexed BENFORD - compare actual frequency of first x digits distribution to Benford frequency VERIFY - Checks the integrity of the data - identifies invalid numeric, date or character fields SIZE - Determines sample size - based on confidence level etc. SAMPLE - Select sample records - creates new file EVALUATE - Projects the sample results to the entire population SCRIPTS - To save commands in a file so you can re-execute them later; to ensure consistency; to speed up analysis

LOG - Automatically records all ACL Commands and the results Generic Approach to the Application of Data Analysis to Auditing CAATS 2007 The first step is to ensure that you understand the goals and objectives of the audit. Then: Meet with the client and the programmer for the client applications. Identify all available databases both: Internal to the client organization main application systems; and External to the client organization including benchmarking and standards List fields in all available databases and the standard reports that are available. Based upon the audit objectives, identify the data sources, the key fields or data elements required by the audit team. Request the required data trying to ensure that unnecessary fields are excluded for the request. Prepare a formal request for the required data, specifying: the data source(s) and key fields, the timing of the data (for example: as of Sept 31 2002), the data transfer format (floppy, LAN, Internet, CD ROM, tape, etc.), the data format (DBF, Delimited, flat file, ODBC, ASCII print file, etc.), control totals (number of records, key numeric field totals), record layout (field name, start position, length, type, description), a print of the first 100 records Create or Build the ACL Input File Definition - automatically created by ACL for DBF, ODBC, and Delimited files. Verify the data integrity: - Use Verify Command - to check data integrity, - Check ACL totals against control totals, - check the timing of the data to ensure proper file has been sent, - compare ACL view with Print Out of first 100 records - authorization obtain client agreement on data (source, timing, integrity, etc.). Understand the Data - use ACL commands COUNT, STATISTICSC, STRATIFY, CLASSIFY, etc to develop an overview of the data For each objective - formulate hypotheses about field and record relationships - Use ACL to perform analytical tests for each hypothesis - Run tests - the output is your hit list - possible problem records - Evaluate initial results and refine the tests - Re-run and refine test to produce shorter, more meaningful results (repeat steps 5-7 as needed) - Evaluate the results - using record analysis, interview, or other techniques - to examine every item on the refined results. - Form an audit opinion on every item in your results. For each you should be say that

6 the record is OK - there is a valid explanation; or that it is a probable improper transaction and more review is needed Quality Assurance and Documentation - exceptions to source; confirm analysis and nature of exceptions; and identify reasons for the exceptions. TRAINING FOR CATTS CAATS offer four courses: Introduction to ACL (3 days) Advanced ACL (3 days) Fraud Detection and Data Analysis Continuous Auditing Workshop (1 day) CAATS also offers a unique opportunity to combine training and consulting activities to address the objectives of an ongoing audit, while learning ACL.

Audit specific training (minimum 4 days)

All courses can be customized to meet your specific requirements using your data files and tailored exercises. Introductory ACL CAATS offers a 3-day Introductory ACL course. The purpose of the hands-on course is to expose you to the basics of ACL and give you an opportunity to use ACL in a classroom setting. The first part of the course stresses the ACL user interface and the ACL Project concepts. It also covers the definition of different file types: flat file, Excel (ODBC), Delimited, dBASE and Printed report files. The second part of the course will concentrate on the ACL commands: Verify, Count, Total, Statistics, Sequence, Duplicates, Gaps, Stratify, Classify, Age, Cross tabulation, Sort, Index, Quick Sort, Summarize, Extract, Export, Benford Analysis, Join and Relate. The course uses a combination of instructor-led presentation and hands-on exercise. The materials are provided only as a supplement to the ACL materials (electronic reference documentation and help). By the end of the course you should understand the:

ACL project concept, including table layouts (creating and using); data file types, expressions and filters ACL commands allowing you to
o

assess data integrity and completeness

7
o o o o

get an overview of the data drill-down into the details export/extract data join and relate data files

How to use the ACL Log file

Advanced ACL The Advanced ACL course is 3 days of intensive instruction aimed at experienced ACL users. The first part of the course should be a refresher - starting with a review of importing the five types of data dBASE, Delimited, flat file, ODBC, and Report files. The second part of the course covers: reusing table layouts; maximizing efficiency through the use of the LOG; expressions logical, unconditional and conditional; workspaces; and a review of Join and Relate. Next the advanced course covers some of the most commonly used Functions and then moves on to discuss methods of creating script. It then discusses Interactive Scripts, including IF Command, Accept Command and Dialog boxes. The course concludes on Day 3 with instruction on Interactive Dialog, and Groups: Simple, Conditional, and Nested. By the end of the course you should fully understand the:

ACL project concept, including table layouts (creating and using); data file types, expressions and filters ACL functions Developing ACL applications (scripts).

The prerequisite is a good working knowledge of ACL usually obtaining through frequent use of ACL over a period of 6 months or more. Fraud Detection and Data Analysis: Over the 3 days, the course covers the following topics:

Define what constitutes fraud and look at the cost of fraud

How to use the fraud toolkit examining the uses of each of the scripts in the toolkit; and look at how they can be used to create your own fraud templates. Fraud schemes in several areas - payroll, A/P, A/R, etc Exercises to obtain practice using an approach to detect fraud and abuse The building a series of ACL scripts in the payroll and purchase card areas

There are only a few additional handouts for this course. Most of the materials are contained in the two books that you received with your registration Fraud detection a revealing look at fraud; and the Fraud Toolkit. During the exercises you should refer to these books often. During the course, you will be working in groups to address a series of cases. The goal of this course is to leave with working series of ACL scripts. The prerequisite is a good knowledge of ACL - usually obtaining through frequent use of ACL over a period of 6 months or more. Continuous Auditing One day presentation/workshop David Coderre will discuss the main concepts presented in the IIAs third Global Technology Audit Guide (GTAG #3) Continuous Auditing: Implications for Assurance, Monitoring and Risk Assessment. GTAG #3 discusses continuous auditing tools and techniques for assessing controls and risks. The morning session will introduce participants to the theory and tools supporting continuous control assessment- used to identify control deficiencies - and continuous risk assessment - used to develop enterprise-wide audit planning. The afternoon session will allow participants to identify indicators of risk in specific areas (finance, operations, informatics), and to develop a continuous risk assessment tool Continuous auditing approaches will be employed by the participants, who will work in groups to identify/assess indicators of risk supporting three audit activities: the development of an annual enterprise risk-based audit plan; the audit/monitoring of corporate credit cards; the follow-up for an audit of accounts payable. Examples of an automated continuous auditing application for these three areas will be also demonstrated. At the end of the workshop you will:

Understand the concept of continuous auditing Have practice in applying continuous auditing techniques

Benefit from the contribution of others (group work) See the utility of continuous auditing Have the beginning of a continuous auditing application

Audit Specific ACL training Maximize your benefits by combining training with an actual audit that requires data analysis. Using your own data, and the objectives of an ongoing audit, CAATS will tailor the ACL training to help you work through your audit program.

Perform a p-card audit while learning how to build ACL scripts to detect p-card fraud. Perform a payroll audit while learning how to develop specific payroll test.

Prerequisites: must have an on going audit with defined objectives and pertinent data files. Note: only available for in-house courses. Consulting Services A wide range of consulting services are available. These include both on-site and remote (via the Internet) consulting services. Please contact Mr. Coderre for more information [email protected]

The Case for Continuous Auditing

By David Coderre, Author of CAATTs and Other BEASTs for Auditors; Fraud Detection: A Revealing Look at Fraud; and The Fraud Toolkit The notion of continuous monitoring was first introduced to auditors in the 1980s. Its basic premise was using ongoing automated data analysis to draw conclusions concerning risk in a subject area. The results would help to determine where an audit was required and to focus the audit on the areas of greatest risk. Unfortunately, auditors were not ready; they lacked the tools and necessary data access or were unwilling to embrace this idea at the time.

10 Now, however, there is a proliferation of information systems in the business environment, giving auditors and managers easier access to more relevant information. Further, the rapid pace of business requires prompt identification of, and response to issues. Sarbanes-Oxley Section 409 requires timely disclosure to the public of material changes to financial conditions. This regulation, plus changes in auditing standards and the evolution of audit software, has combined to persuade auditors to adopt new approaches to assessing information. Increasingly, the marketplace demands independent assurance that control procedures are effective and that the information produced for decision-making is both relevant and reliable. Often the need for high quality information for decision-making in the highly volatile business environment is greater than the need for reliable historical cost-based financial statements. If a company cant adjust to changing market, technological and financial conditions, it wont be in business for long. The environment, technology and audit standards are driving auditors to make more effective use of information and data analysis and encouraging auditors to adopt continuous monitoring. This has produced a shift in the focus of internal audit activities. However, many auditors are still resistant to or confused about continuous monitoring, so its acceptance and implementation is far from widespread. One of the main reasons for the reluctance is the term monitoring, which is seen as a management function. Another point of confusion is the application of the term continuous monitoring to both instantaneous auditing (a review of transactions in real time) and to the notion of ongoing or frequent, but not real time, audits. Real-time analysis is still beyond the capabilities of many audit organizations. Therefore, proponents of continuous monitoring now define it as the identification of systems or processes that are experiencing higher-than-normal levels of risk, such as where the values of the performance attributes fall outside the acceptable range. In this context, continuous monitoring measures specific attributes that, if certain parameters are met, will trigger auditor-initiated actions. The nature of these actions will vary depending on the risk identified. They may range from sending an email to the manager to a rapid response audit of the area. For example, the financial system may notify the auditors of any journal vouchers over $250,000. The auditors response will depend on whether or not this is seen as a single item of concern or as a systemic problem. Continuous auditing versus continuous monitoring To help overcome some of the problems and confusion associated with the term continuous monitoring, auditors ought to consider the notion of continuous auditing, -- a similar, but more powerful approach to identifying and assessing risk. I define continuous auditing as: The identification and assessment of risk through the application of analysis and other audit techniques including, but not limited to,

11

The identification of anomalies; The analysis of patterns within the digits of numeric fields (digital analysis); Comparisons against cut-off or threshold values; Comparisons across years; and Comparisons of one audit entity to another.

Both continuous monitoring and continuous auditing have their genesis in data analysis. But continuous auditing goes beyond simple data analysis and includes techniques from statistical analysis, trend analysis, digital analysis and neural networks. Typically, data analysis and, to a large extent continuous monitoring, is only used to identify transactions that fail a specified cut-off or threshold value, whereas continuous auditing helps auditors to identify and assess risk, as well as establish dynamic thresholds that respond to changes in the organization. Further, while data analysis contributes to an individual audit by identifying or supporting specific audit objectives, continuous auditing also supports risk identification and assessment for the entire audit universe supporting the development of the annual audit plan in addition to contributing to the objectives of a specific audit. Continuous auditing is a unifying structure or framework that brings risk assessment, audit planning, digital analysis and the other audit tools and techniques together. It supports the macro-audit issues, such as using risk to prepare the annual audit plan; and micro-audit issues, such as developing the objectives and criteria for an individual audit. The main difference between the macro- and micro-audit levels is the amount of detail that is considered. The annual audit plan requires high-level information to establish the risk factors, prioritize risks and set the initial timing and objectives for the planned set of audits. Individual audits start with the risks identified in the annual audit plan but use digital analysis and other techniques (interviews, control self-assessment, walk-throughs, questionnaires, etc.), to further define the main areas of risk and focus the risk assessment and subsequent audit activities. There also are a number of differences between continuous auditing and continuous monitoring. The main differences are:

Continuous auditing recognizes and acknowledges that monitoring is a management function not an internal audit function. The frequency of continuous auditing is based on the assessed level of risk and is not continuous unless the level of risk justifies a real-time analysis of transactions.

12

Continuous auditing uses not only the comparison of both individual and summarized transactions against cut-off or threshold values but also the comparison of an entity against other entities (e.g. one operational unit to all other operational units) and a time-wise comparison of the entity against itself (e.g. the entitys performance over the last five years compared to its current performance). Continuous auditing also allows auditors to follow up on the implementation of audit recommendations.

Continuous auditing can be used by audit to determine if risk is at a level where audit intervention is required. However, it is not a form of monitoring that would determine if operations are functioning properly (which is a management issue). Continuous auditing allows auditors to quickly identify instances that are outside the allowable range (known thresholds), and those that can only be seen as anomalies when compared to other similar entities or when viewed across time (unknown thresholds). Simply knowing that an audit entity processed a journal voucher that is greater than a cut-off amount will not help auditors to gauge whether the entity has improved in its use of journal vouchers. Measuring variability Continuous auditing seeks to measure not only transactions against a cut-off but the totality of the transactions. This allows one to test the consistency of a process by measuring the variability of each dimension. For example, measuring the variability in the number of defects is a method for testing the consistency of a production line. The more variability in the number of defects, the more concerns about the proper functioning of the production line. This premise can just as easily be applied to the measurement of the integrity of a financial system by measuring the variability (e.g. number and dollar value) of the adjusting entries over time and in comparison to other similar entities. The concept of variability, over time and against other audit entities, is the key differentiating factor in continuous auditing versus continuous monitoring or embedded audit modules. Auditors need to be considering questions like: How many journal vouchers were processed this year? What percentage was above the threshold amount? How does this compare to last year and to other audit entities? Can we tighten the criteria and lower the cut-off value? Answering these questions will allow auditors to develop a dynamic set of thresholds that provide a better idea of the direction the organization is headed, rather than simply identifying a transaction that failed to meet a static cut-off value. Supporting audit follow-up Finally, continuous auditing supports automation of audit recommendation follow-up. With continuous auditing, auditors can track specific data-driven measures of performance to determine whether management has implemented the agreed-upon recommendations and whether they are having the desired effect. Tracking performance over time is critical to ensuring the organization is being successful in meeting established goals and in identifying additional actions to be taken. It is an integral

13 element of performance measurement and continued improvement in operations. Audit, through continuous auditing, can assess the quality of performance over time and ensure the prompt resolution of identified problems. Further, once the risks related to an activity are identified and activities to reduce such risks are undertaken, the review of subsequent performance (continuous auditing) can gauge how well the mitigation efforts are working. As the actions of an organization become more observable, continuous auditing facilitates the implementation of ongoing quality improvement and assurance. The data-driven predictors of performance must be responsive to changes in performance, provide an early warning when performance is deteriorating, be easy to use and not be resource intensive. They should help an organization answer three basic questions if the indicator goes Red: 1. What happened? 2. What is the impact? 3. What are we going to do about it? Continuous auditing applied to accounts payable an example While continuous auditing can be used in any area of the organization, a simple example involving accounts payable illustrates the differences and strength of this approach. The example assumes that there are numerous separate accounts payable (A/P) processing centers, of different sizes, performing similar functions. The example will be used to discuss four main objectives: 1. Identification and assessment of risk related to the accounts payable processes. 2. Identification of trends related to performance and efficiency. 3. The identification of specific anomalies and potential frauds. 4. The tracking of the implementation of audit recommendations and their affect on accounts payable operations. In each case, the analysis would consider trends over time and compare the accounts payable section under review to other accounts payable sections within the organization. Benchmarking against external A/P operations adds another dimension to the examination. Risk Identification and Assessment. A wide variety of data-driven and non-data-driven risk factors should be included in the initial risk assessment. A comprehensive evaluation of business performance looks at cost, quality and time-based performance measures. Cost-based measures cover the financial side of performance, such as the labor cost for

14 accounts payable. Quality-based measures assess how well an organizations products or services meet customer needs, such as the average number of errors per invoice. Timebased measures focus on efficiency of the process, such as the average number of days to pay an invoice. It is also possible to determine, for each A/P section, the types of transactions and dollar amounts for each. For example, look at number of correcting journal entries and manually produced checks. These are indicators of additional workload. The analysis also will tell you how many different types of transactions are being processed. Generally speaking, there is greater complexity in operations when more transaction types are processed. You can also examine organization structure: reporting relationships, number and classification/level of staff, length of time in job, retention rates and training received this data should be available from the HR system. The combination of this type of information with the transaction types and volumes can help to identify areas of risk, such as understaffing or lack of trained staff to handle complex transaction types Trends in performance and efficiency. When considering A/P, trending data will easily identify performance and efficiency concerns. For example, for each A/P operation, continuous auditing can determine:

Number and classification/level of accounts payable staff. Number of invoices processed by each user at either end of the spectrum. (Too many or too few can increase risk.) Average dollar cost to process an invoice. Average number of days to process a payment. Percentage of invoices paid late; percentage paid early. (Particularly telling if early payment discounts are not taken.) Percentage of adjusting entries. Percentage of recurring payments or Electronic Funds Transfer (EFT) payments. Percentage of manual checks. Percentage of invoices that do not reference a purchase order. Percentage of invoices that are less than $500. (Purchase card could be used for more efficiency and less cost).

Efficiency measures allow you to compare one audit area to another: Analyzing trends can help to identify not only problems but also areas where improvements have been made. The graph below shows that Division D still has the

15 highest percentage of invoices without a purchase order reference, but they have made considerable improvements over the previous year, whereas Division Gs percentage has gone up. Identification of anomalies or potential fraud. Within A/P, possible anomalies and measures of potential fraud include:

The identification of duplicate payments (should include a comparison to previous years to see if operations are improving). Invoices processed against purchase orders that were created after the invoice date (back-dated purchase orders). Number of invoices going to suspense accounts. Identification of all functions performed by each user to identify incompatibility or lack of segregation of duties. Identification of vendors that were created by, and only used by, a single accounts payable clerk. Identification of instances where the entry user is the same as the user who approves payment. Identification of instances where the payee is the entry or approving user. Identification of duplicates in the vendor table or of vendors with names such as C.A.S.H., Mr., Mrs. or vendor with no contact information, phone numbers or other key information.

Tracking of recommendations. The final objective of continuous auditing is the tracking of recommendations. The aim is to determine whether management has implemented the recommendations and whether the recommendations are having the desired effect. Possible measures include:

Evidence of increased used of purchase cards for low dollar transactions (reduction in percentage of invoices less than $500 and increase in percentage of purchase card payments less than $500). Reduction of duplicates in the supplier master table. Decrease in the number and dollar value of duplicate invoices. Improvements in the days-to-pay figures (reduction in late payment charges, and more opportunities to take early payment discounts). Improved operations lower cost per invoice, more use of EFT payments.

16

The graph below shows how continuous auditing can be used to determine whether A/P operations in each division have successfully implemented the recommendation calling for purchase cards to be used for low dollar transactions. Preparing for continuous auditing Continuous auditing starts with the selection of audit projects, continues into the conduct and reporting phase and culminates with the ongoing monitoring and follow-up activities. All stages of the process should be risk-based and, to the maximum extent possible, datadriven. The basic implementation strategy must include a consideration of the risk, an assessment of the baseline assurance, the design of the predictive indicators, monitoring for changing conditions and follow-up as required. More detailed steps include: Audit plan preparation and planning phase

Identification of categories/areas of risk. Identification of sources of the data to support risk assessment. Understanding of the data and an assessment of its reliability. Assessment of the levels of risk. Prioritization of risk. Selection of audit projects.

Audit conduct phase

Integration of audit procedures and technology. Definition of relevant variables (predictors) to be measured. Definition of the criteria for these variables to be used to predict outcomes. Definition of the desired traits for the variables (normal range, anomalies). Measurement of the variables (predictors). Assessment of the predicted level of risk. Follow-up audit activity as required. Revision to variables that will be measured, criteria and the traits.

Conclusion

17

The implementation of continuous auditing will place certain demands on internal auditors. In particular, the audit organization will have to develop and maintain the technical competencies necessary to access and manipulate the data in multiple information systems. If the auditors are not already using data analysis techniques to support audit projects, the audit group will have to purchase analysis tools and develop and maintain analysis techniques. The implementation of continuous auditing will also require the adoption of the concept by all persons within the audit organization. Monitoring and review is the final component of an effective control framework (COSOs five elements of a control). It is a key ingredient in an organizations continuous improvement process. An effective monitoring and review environment uses both periodic reviews and those undertaken by internal and external audit, as well as built-in review mechanisms and internal review measures. Continuous auditing will support and strengthen the monitoring and review environment in an organization. Finally, it will help focus the audit effort but will not obviate managements responsibilities to perform a monitoring function

18

Chapter 1
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete tutor chapter 1 is 15-20 minutes) Read Chapter 1: Introducing Metaphor Corporation of the ACL Tutor (Note: ACL software is bundled with each copy of the 4th edition of the textbook. Insert the ACL disk and follow the instructions to install the software.) To access the ACL Tutor: 1. After installing ACL Version 8, open the Start Menu on your computer 2. Click All Programs 3. Find the ACL Version 8 folder icon 4. Single click on the ACL Version 8 folder icon to display the folder contents (i.e., ACL Help, ACL Tutor, ACL Utility, ACL Version 8, Readme). 5. Click on the PDF file, ACL Tutor to open the tutor 6. Alternatively, you can navigate directly to the file, Tutor.pdf, in the folder where ACL data and the tutor are saved on your hard drive (e.g., C:\ACL 8 Data\Sample Data Files) (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document.) Problem 1 Briefly describe Metaphors credit card policy. Problem 2 Looking at Figure 1-3 in Chapter 1 of your auditing textbook, during which stages of the financial statement audit process might ACL be the most useful? Problem 3 Go to the ACL internet website by opening ACL Version 8 and clicking on ACL Homepage under ACL Weblinks. While on the website, click on Audit under the Solutions tab. Read that page and explain how ACL can help in an audit.

19 Chapter 2
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete is tutor chapter 2 is 20-30 minutes.) Read and complete the tasks in Chapter 2: Examine Employee Data of the ACL Tutor Hint: Staying actively involved while completing the tutorial assignment will help you to complete the ACL problems more efficiently. You may find it helpful to review the assigned problems before completing the Tutorial Assignment. To access the ACL Tutor: 1. Open the Start Menu on your computer 2. Click All Programs 3. Find the ACL Version 8 folder icon 4. Single click on the ACL Version 8 folder icon to display the folder contents (i.e., ACL Help, ACL Tutor, ACL Utility, ACL Version 8, Readme). 5. Click on the PDF file, ACL Tutor to open the tutor 6. Alternatively, you can navigate directly to the file, Tutor.pdf, in the folder where ACL data and the tutor are saved on your hard drive (e.g., C:\ACL 8 Data\Sample Data Files) 7. Complete the tasks in the ACL Tutor, Chapter 2 (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document.) Chapter 2 Tutorial Exercises: 1-5 There are five questions at the end of the Chapter 2 tutorial. Your instructor will inform you which, if any, of the exercises to complete and submit. Problem 1 Create a filter to display the employees at Metaphor Company that were hired after January 1, 2000 and who make more than $60,000 per year in salary. How many records result from the filter described? Include the expression you used to create the filter in your solution. Problem 2 Use ACL to compute how much was paid in commissions to Metaphor Agents (Comm 2002 column, Agents_Metaphor table). Problem 3 How does computing the amount paid in commissions to Metaphor agents in Problem 2 help an auditor verify the management assertion of completeness?

20 Chapter 3
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete the chapter 3 tutor is 20-30 minutes.) Read and complete the tasks in Chapter 3: Set up Your Project of the ACL Tutor Hint: Staying actively involved while completing the tutorial assignment will help you to complete the ACL problems more efficiently. You may find it helpful to review the assigned problems before completing the Tutorial Assignment. For instructions on accessing the ACL Tutor please see earlier assignments. (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document.) Chapter 3 Tutorial Exercises: 1-2 There are two questions at the end of the Chapter 3 tutorial. Your instructor will inform you which, if any, of the exercises to complete and submit. Problem 1 Edit the layout of the Employee_List table to form a new column for total compensation (salary plus bonus). Now determine how many employees earned more than $85,000 in total compensation in 2002. Problem 2 Roger Company is a mid-size company located in the Midwest that handles the distribution of various home and garden products. You are part of the engagement team assigned to audit the financial statements of Roger Company. Roger Company has been a client of your firm for many years, and your firm has rarely encountered any problems with them. However, the engagement partner has made it very clear to you that there is no room for mistakes. Your tasks as one of the auditors on the engagement are outlined below and in other problems of the remaining chapters. Please download the Roger Company ACL files, found under Course-Wide Content on the Student Edition of your texts Online Learning Center. Net income before taxes at Roger Company is stable, predictable, and representative of its size. Thus, the auditors at Roger Company calculate materiality to be 5 percent of net income before taxes. Net income before taxes at Roger Company for fiscal 2004 is $1,388,500. Determine materiality for the audit of Roger Companys 2004 financial statements. Use ACL to determine if the reported AR account balance, $487,000, is materially different from the detailed files in Rogers_Company_AR table. Define tolerable misstatement as 60 percent of materiality. What might cause differences between the number reported in the financial statements and the details in the file?

21

Problem 3 As a quality control procedure, management at Roger Company reviews each approved vendor at least once a year. In the reviews, management compares pricing across vendors, retests products being purchased from vendors to ensure they meet quality control standards, and performs testing to ensure purchasing personnel are not inappropriately favoring a vendor or potentially colluding with vendors (e.g., receiving kickbacks from the vendors). Use ACL to check the Roger_Company_Vendors table to make sure each vendor has been reviewed sometime since January 1, 2004. 1. Open the Roger_Company_Vendors table 2. Click on the Edit View Filter button to open the Edit view filter dialogue box 3. In the Available Fields list, double-click on the Last_Review field 4. Click on the < sign 5. Click on the Date button to display the date selector 6. Click on the down arrow, find January 1, 2004, click on it, and click OK 7. Click OK 8. Which vendors have not been reviewed since January 1, 2004? When was the last review for those vendors? Chapter 4
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete the chapter 4 tutorial is 20-30 minutes.) Read and complete the tasks in Chapter 4: Begin Your Analysis of the ACL Tutor Hint: Staying actively involved while completing the tutorial assignment will help you to complete the ACL problems more efficiently. You may find it helpful to review the assigned problems before completing the Tutorial Assignment. For instructions on accessing the ACL Tutor please see earlier assignments. (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Chapter 4 Tutorial Exercises: 1-8 There are eight questions at the end of the Chapter 4 tutorial. Your instructor will inform you which, if any, of the exercises to complete and submit. Problem 1 Roger Company has a policy that their allowance for uncollectible accounts should be 50% of the amount in the 61-90 day past due category plus 75% of the amount in the >90

22 day past due category as of the reporting date (in this case 12/31/04). Use the Roger Company AR table in ACL and the Analyze >> Age command to re-compute the allowance for uncollectible accounts. In addition to re-computing the allowance for uncollectible accounts, report the results of the aging table that you are asked to complete. 1. 2. 3. 4. 5. Once in the Roger Company AR Table, click the Analyze tab. Click Age. In the Age dialog box, make sure Due_Date is the selected field for Age On. Change the cutoff date to December 31, 2004. In the Aging Periods box, delete the numbers 10000 and 120 so that your table will compute a >90 day past due total. 6. Highlight the Amount field under the Subtotal Fields column. 7. Click Okay. Problem 2 Assuming no cash is collected on past due accounts, how much will be more than 60 days past due as of January 31, 2005? Chapter 5
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete is 30-40 minutes.) Read and complete the tasks in Chapter 5: Examine Expense Patterns of the ACL Tutor Hint: Staying actively involved while completing the tutorial assignment will help you to complete the ACL problems more efficiently. You may find it helpful to review the assigned problems before completing the Tutorial Assignment. For instructions on accessing the ACL Tutor please see earlier assignments. (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Chapter 5 Tutorial Exercises: 1-5 There are five questions at the end of the Chapter 5 tutorial. Your instructor will inform you which, if any, of the exercises to complete and submit. Problem 1 How many inventory items at Roger Company are valued at more than $10,000? What is the total value of those items? How many inventory items at Roger Company cost more than $10,000? What is the total cost of those items?

23 Problem 2 Use information from Roger Company to determine how many inventory items are valued lower than original cost. What is the total market value of those items? What is the total cost of those items? Chapter 6
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete is 40-60 minutes) Read and complete the tasks in Chapter 6: Analyze Transactions of the ACL Tutor Hint: Staying actively involved while completing the tutorial assignment will help you to complete the ACL problems more efficiently. You may find it helpful to review the assigned problems before completing the Tutorial Assignment. For instructions on accessing the ACL Tutor please see earlier assignments. (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Chapter 6 Tutorial Exercises: 1-5 There are five questions at the end of the Chapter 6 tutorial. Your instructor will inform you which, if any, of the exercises to complete and submit. Problem 1 After reviewing a list of parties related to Roger Company, you notice that the customers with customer numbers 803882 and 512198 are related to the owners of the company. Your assignment now is to use the Roger_Company_AR table to determine how much in sales were made to these related customers. What percent of total sales were made to the two related customers? Problem 2 As part of the audit of Accounts Payable, you want to identify all invoices (Invoice_Amount) are greater than $50,000 so that you can vouch the transaction to original documentation (i.e., approved purchase order, receiving records). Use ACL to identify all Accounts Payable invoices greater than $50,000 and compute the total value of those transactions. Why is it important for auditors to determine if large purchases are properly authorized? Chapter 7
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

Tutorial Assignment (Estimated time to complete is 20-30 minutes)

24 Read and complete the tasks in Chapter 7: Validate, Correct, and Extract Data of the ACL Tutor Hint: Staying actively involved while completing the tutorial assignment will help you to complete the ACL problems more efficiently. You may find it helpful to review the assigned problems before completing the Tutorial Assignment. For instructions on accessing the ACL Tutor please see earlier assignments. (Note: Unless otherwise instructed, please submit your answers to the following exercises and problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Chapter 7 Tutorial Exercises: 1-7 There are seven questions at the end of the Chapter 7 tutorial. Your instructor will inform you which, if any, of the exercises to complete and submit. Problem 1 Roger Companys policy is to not ship goods unless a valid purchase order has been received. However, based on information obtained during your walk through to confirm your understanding of processes and controls, you learned that occasionally a rush order is received via telephone and the goods are shipped before receiving the purchase order. Rush orders are only processed for existing customers. When rush orders are received the sales person taking the order completes a Rush Order form which is then approved by the sales department supervisor. The Rush Order form is then attached to the purchase order when it is received and the details of the two forms (i.e., product and quantity) are compared. To test the effectiveness of the controls around rush orders, you want to identify all situation where product is shipped before a purchase order is received. Using the Roger Company shipping file, determine how many invoices were shipped before they were ordered. Problem 2 In discussions with order the fulfillment and shipping departments, you learn that it is common for a partial or split shipment to go out because a sufficient quantity of items a customer orders is not in stock. However, controls over shipping should prohibit shipping a higher quantity than was ordered. Using information from Roger Companys shipping file, determine how many records contain fields where the quantity shipped exceeds the quantity ordered.

25 Chapter 8
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document.) Problem 1 Use ACL to determine the sample size an auditor should use for attributes sampling given the criteria listed below: 1. With any ACL project (e.g., Roger Company) open, choose Sampling on the menu toolbar 2. Click on Calculate Sample Size 3. Choose the Record option Confidence is 95 Population is 1000 Upper Error Limit (%) is 8 (this is tolerable error) Expected Error Rate (%) is 3 4. Click Calculate 5. What is the recommended sample size? Problem 2 How would the sample size change if all sample-size inputs listed in Problem 1 stayed the same with the exceptions listed below? Please evaluate each item independently by resetting the inputs to those listed in Problem 1 and changing only the one factor listed in each item below. (Hint: If you use the Calculate button rather than the OK button the sample size window will stay open). a. Confidence dropped to 90 percent? b. Population increased to 500,000? c. Expected Error Rate (%) increased to 4? d. Upper Error Limit (%) decreases to 7? e. Upper Error Limit (%) increases to 15? Problem 3 Using your results from Problem 2 above: a. Which of the following four input factorsconfidence, population, upper error limit, or expected error ratehas the smallest effect on the sample size? b. Which two factors appear to have the greatest effect on sample size? c. Go into ACLs sampling size tool and input the factors listed in Problem 1 and then experiment with increasingly larger expected error rates. What happens as the expected error rate is nearly as large as the upper error limit or tolerable error? Why does this happen?

26 Problem 4 For the following three control attributes, you want to be 90 percent confident that the population deviation rate does not exceed 7.5 percent. 1-The purchase order was approved (purchasing department stamp provides evidence) 2-The purchase order, receiving report, and vendor invoice are included in each voucher packet 3-The accounts payable department compared product and quantities across the three documents (initials by an accounts payable clerk and auditor reperformance provide evidence) You tested a sample of 52 voucher packages and discovered the following deviations: 2 deviations for attribute 1 1 deviation for attribute 2 0 deviations for attribute 3. With any ACL project open (e.g., Roger Company) evaluate the results of your testing by: 1. Select Sampling >> Evaluate Error 2. Make sure Record is the selected sample type 3. Enter the applicable parameters (e.g., Confidence is 90 and Sample Size is 52, Number of Errors or deviation listed above) 4. Click OK What is the upper error limit frequency for each attribute? Based on the results of your controls testing, which controls are considered effective? Please explain why or why not? Problem 5 Use ACL to complete problems 8-27 and 8-28 in your book. For problem 8-27, does the population amount you enter change the results? For 8-28, use the sample sizes computed by ACL in 8-27. ACLs sample sizes and upper error limit frequency will differ from those computed using the tables in the textbook. Did the differences lead to different conclusions or auditor decisions?

27 Chapter 9
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 In addition to determining sample size, ACL can also select a random sample for you. Draw a sample of Accounts Receivable (AR) transactions from the Roger Company AR table assuming the confidence is 95, the upper error limit is 9 percent, and the expected error rate is 5 percent. 1. Open the Roger_Company_AR table 2. Select Sampling >> Sample Records 3. In the Sample Dialogue box make sure Record is the chosen sample type 4. Under Sample Parameters, click on the Random option 5. Click on the Size button so the Size Dialogue box opens 6. Enter the parameters as specified above (Note: The Population field should automatically have a value in it.) 7. Click on Calculate, click on OK 8. In the To field, type Roger AR Sample 9. Click OK 10. How many records are in the new Roger Company AR Sample table? Problem 2 Assuming that the electronic data were difficult to obtain and that the client compiled the electronic data only for the sample you selected in Problem 1, evaluate the effectiveness of the control that the invoice date should always precede the due date. 1. Create a filter in the Roger AR Sample table for the control described above 2. How many exceptions are there to the control above? 3. Select Sampling from the menu toolbar and click on Evaluate Error 4. Make sure Record is the selected sample type 5. Enter the appropriate parameters (i.e., Confidence 95, Sample Size 175, and the number of exceptions you observed) 6. Click OK 7. What is the upper error limit frequency? Based on the results from the operations you completed above, can the control be considered effective? Why or why not? Problem 3 Determine an appropriate sample size to test the Roger_Company_AR table using monetary unit sampling by following the directions below. 1. Open the Roger_Company_AR table 2. Sum the Amount field 3. Select Sampling >> Calculate Sample Size

28 4. In the Size dialogue box make sure Monetary is the chosen sample type 5. Input the following: Confidence is 92 percent Population is the sum of the Amount field Materiality is 10,000 Expected Total Errors is 1500 6. Click on Calculate 7. What is the appropriate sample size? Problem 4 Create a Roger Company MUS Sample table (or file) by selecting Sample >> Sample Records. Make sure MUS is the chosen sample type and Fixed Interval is the chosen option under Sample Parameters. Enter the appropriate Interval value from the results in Problem 3, and chose 350 as the Start. Ignore the Cutoff. Save the table as Roger Company MUS Sample. How many records are in the sample table? Why is the sample size different from what was calculated in Problem 3? Problem 5 Use ACL to complete questions b and c of problem 9-21 in your textbook.

29 Chapter 11
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 A risk in the purchasing process is that a purchase is made from an unauthorized vendor. Use the Relate Tables command in ACL to relate, by vendor number, the Roger_Company_Vendors table to the Roger_Company_AP_Transactions table (see Tutorial Chapter 6), and determine how many purchases were made from unauthorized vendors. This can be accomplished by creating a new column of vendor numbers from the Roger_Company_Vendors table and adding it to Roger_Company_AP_Transactions table. Problem 2 In prior years audits, the auditor has discovered cutoff errors in the purchasing area. In some cases, Rogers included a liability in the subsequent year when it should have been included in the current year. In other cases, Rogers included a liability in the current year, even though the purchase transaction related to the next year. The fiscal year for Roger Company is from March 1, 2004 to February 28, 2005. What is the total invoice value of the purchases that were inappropriately included in the fiscal 2005 AP balance that belong in the fiscal year 2006 balance?

30 Chapter 12
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 A relatively common fraud involves a fraudster writing checks to a ghost employee. Although Roger Company hasnt had any problems of this nature in the past, there has been significant turnover in the HR department and you want to test that payroll checks are going only to current, valid employees. Use the Data >> Relate Tables command in ACL (see Tutorial Chapter 6) to make sure that all employees who are receiving checks are actual employees of the company. Document your results. Problem 2 Using ACL, test the Roger_Company_Employee_Master_List table for duplicate records. How many duplicate records exist? Also, test the Roger_Company_Payroll table for duplicate records. How many duplicate records exist? Comment on the results. Chapter 13
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 Inventory is typically sold at a price higher than cost. However, sometimes certain inventory items become obsolete and must be priced lower than the original cost. Using the Roger Company file, determine which items Roger Company is selling at a price below the original cost. Problem 2 Create a histogram of the market value of the inventory items at Roger Company. This histogram will help you to visualize the market value of inventory items at Roger Company. Choose 100 as a minimum and 10,000 as a maximum. Leave the interval at 10. Comment on the results of your histogram. Copy the graph to your clipboard and paste it to the word processing document you are using to submit your answers. Alternatively, you can print the graph and submit it to your instructor with your solutions.

31 Chapter 14
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 An audit procedure that is useful for identifying potential risks is to scan transactions for unusual items. ACL can help with such a procedure by expediting the scanning process, especially when the database of transactions is large. Use the Analyze >> Stratify command in ACL to stratify the Invoice_Amount field in the Roger_Company_AP_Transactions table. Comment on the results. Are there any transactions that seem unusual? Include a copy of the stratification table in your answer. Problem 2 Another way auditors can quickly scan a large database of transactions is to use the Analyze >> Classify command in ACL. Use the Analyze >> Classify command in ACL to classify the Vendor_Number field in the Roger_Company_AP_Transactions table. Choose Invoice_Amount as the subtotal field. Comment on the results. Are there any transactions that seem unusual? Include a copy of the classification table in your answer. Chapter 15
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 Common procedures that auditors perform are footing and cross-footing. Footing is the process of adding a column of numbers, and cross-footing is the process of adding a row of numbers. As was demonstrated in earlier ACL problems, footing can easily be done by simply selecting a column and pressing the button. Cross-footing, on the other hand, is not as easy. In the Roger_Company_Shipping table use the expression filter to determine if the Subtotal field and the Tax field add up to equal the Invoice_total field. Include the expression you used in your answer. What seems to be the reason why there are a few cases where the Subtotal field added to the Tax field doesnt equal the Invoice_total field?

32

Chapter 16
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 Use ACL to test the integrity of the data in the Roger_Company_AR table. Specifically, use the Analyze >> Look for Gaps and Analyze >> Look for Duplicates commands to determine the consistency of the data in the Invoice_Number field. Are there any gaps and/or duplicates? Imagine what it would be like to manually look for duplicates or gaps in a large database and compare that to how ACL was able to accomplish the same task. Problem 2 Roger Company has a policy of making routine cash disbursements on a bi-monthly basis and saving the cash disbursements information in a database that is available to you as the Roger_Company_Cash_Disbursements table. Data for non-routine cash disbursements is saved in a different database. Roger Company considers cash disbursements under $1,500 as routine, and everything else should be in the other database. Use ACL and the Roger_Company_Cash_Disbursements table to determine if all cash disbursements are under $1,500. Comment on the results. Chapter 17
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 Use ACL and the Roger_Company_Cash_Disbursements table to determine how much has been paid for legal services over the last year. From experience on past audits, you know that the law office that handles all of Roger Companys legal issues is Happy Homes Law Offices and that Roger Company records legal payments as Legal Services. However, you want to make sure no other legal fees have been paid to other law offices. Using ACL conduct a search for payments to other lawyers. How much has been paid for legal services? Were payments for legal services made to law offices other than Happy Homes? Problem 2

33 Roger Company has a policy that routine payments should be made frequent enough so that a vendors accounts payable balance never exceeds $500. The Roger_Company_Cash_Disbursements table is organized to display the running accounts payable balance over time. Use ACL and the Roger_Company_Cash_Disbursements table to determine if all balances are under $500. Comment on the results. Chapter 20
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 In Problem 2 from Chapter 16 it was discovered that there were some routine cash disbursements that exceeded the $1,500 limit to Hartford Brothers for cleanings services. Use ACL and the Roger_Company_Cash_Disbursements table to ascertain what the typical running balance is for the Hartford Brothers vendor. Comment on the results considering both the typical running balance and payments that exceed the routine transaction limit (see description in Problem 2, Chapter 16). Chapter 21
For technical assistance with ACL, please contact technical support: 604-669-4225; [email protected]; http://www.acl.com/supportcenter/

(Note: Unless otherwise instructed, please submit your answers to the following problems to your instructor in a word processing document. Roger Company files can be downloaded from the Course-Wide Content on the Student Edition of your texts Online Learning Center.) Problem 1 Write a brief paragraph explaining how you believe ACL is most useful to auditors. If necessary, go back to the ACL problems you have completed throughout the term to refresh your memory regarding the capabilities within ACL.

34

Questionnaire for Problem 6-34 Risk Assessment Questionnaire Client: EarthWear Clothiers
Completed by: ___ Date: ______ Balance Sheet Date: 12/31/2005 Reviewed by: ___ Date: ______

A risk assessment process should consider external and internal events and circumstances that may occur and adversely affect its ability to initiate, record, process, and report financial data consistent with managements assertions. Management should initiate plans, programs, or actions to address significant, identified risks or accept the risk because of cost considerations. Yes, No, N/A Does the entity set entity-wide objectives that state of what the entity desires to achieve, and are they supported by strategic plans? Does the entity have a risk analysis process that includes estimating the significance of the risks, assessing the likelihood of their occurring, and determining the actions needed to respond to the risks? Does the entity have mechanisms to identify and react to changes that may dramatically and pervasively affect the entity? Comments

35

Control Procedures Questionnaire Client: EarthWear Clothiers

Completed by: ___ Date: ______ Balance Sheet Date: 12/31/2005 Reviewed by: ___ Date: ______

Control procedures include the policies and procedures that insure that managements directives are effective in processing and preparing financial statements. Control activities ensure that the entitys financial reporting objective is carried out. Yes, No, N/A Does management have clear objectives in terms of budget, profit, and other financial and operating goals? Are such objectives: Clearly written? Actively communicated throughout the entity? Actively monitored? Does the appropriate level of management: Adequately investigate variances? Take appropriate and timely corrective actions? Has management established procedures to prevent unauthorized access to, or destruction of, documents, records, assets, programs and data files? Comments

36

Information and Communication Questionnaire Client: EarthWear Clothiers

Completed by: ___ INFORMATION SYSTEMS Information systems initiate, record, process, and report information. Relevant information includes industry, economic, and regulatory information obtained from external sources, as well as internally generated information. Yes, No, N/A Does the information system give management the necessary reports on the entitys performance relative to established objectives, including relevant external and internal information? Is the information provided to the right people in sufficient detail and in time to enable them to carry out their responsibilities effectively? Is the development or revision of information systems over financial reporting based on a strategic plan? COMMUNICATION Communication includes the extent to which personnel understand how their activities in the financial reporting information system relate to the work of others and the means of reporting exceptions to an appropriate level within the entity. Yes, No, N/A Does management communicate employees duties and control responsibilities in an effective manner? Are communication channels established for people to report suspected improprieties? Does communication flow across the organization adequately to enable people to discharge their responsibilities effectively? Comments Comments Date: ______ Balance Sheet Date: 12/31/2005 Reviewed by: ___ Date: ______

37

Monitoring Questionnaire Client: EarthWear Clothiers

Completed by: ___ Date: ______ Balance Sheet Date: 12/31/2005 Reviewed by: ___ Date: ______

Management should monitored internal control in the ordinary course of operations. This monitoring includes regular management and supervisory activities and other actions personnel take in performing their duties that assess the quality of internal control. Yes, No, N/A How many customer complaints are received about billings? Are complaints investigated for underlying causes and any internal control deficiencies corrected? Does the entity have an internal audit function? Are internal control recommendations made by internal and external auditors implemented? Does the entity conduct separate evaluations of internal control? Comments

38

Control Environment Questionnaire

Client: EarthWear Clothiers Completed by: _____ Date: ______
BOARD OF DIRECTORS AND AUDIT COMMITTEE An entitys control consciousness is influenced significantly by the entitys board of directors or audit committee. Attributes include the board or audit committees independence from management, the experience and stature of its members, the extent of its involvement and scrutiny of activities, the appropriateness of its actions, the degree to which difficult questions are raised and pursued with management, and its interaction with internal and external auditors. Yes, No, N/A Comments Are there regular meetings of the board of directors (or comparable bodies) to set policies and objectives, review the entitys performance, and take appropriate action, and are minutes of such meetings prepared and signed on a timely basis? Does an audit committee exist? Does the audit committee adequately assist the board in maintaining a direct line of communication with the entitys external and internal auditors? Does the audit committee have adequate resources and authority to discharge its responsibilities? Is this evidenced by Regular meetings? The appointment of qualified members? Minutes of meetings?

Balance Sheet Date: 12/31/2005 Reviewed by: _____ Date: _______

39

MANAGEMENT PHILOSOPHY AND OPERATING STYLE

Managements philosophy and operating style significantly influence the control environment particularly when management is dominated by one or a few individuals. Managements philosophy and operating style should create a positive atmosphere that reduces the risk of misstatement and that is conducive to the effective operation of internal control. Yes, No, N/A Comments Are management and operating decisions dominated by one or a few individuals? Are business risks adequately monitored? Is management willing to adjust the financial statements for misstatements that approach a material amount? Does management adequately consult with its auditors on accounting issues? Has management been responsive to prior recommendations from its auditors? Is a high priority given to internal control?

ORGANIZATIONAL STRUCTURE An entitys organizational structure provides the framework within which its activities for achieving entity-wide objectives are planned, executed, controlled, and monitored. Establishing a relevant organizational structure includes considering key areas of authority and responsibility and appropriate lines of reporting. Yes, No, N/A Comments Is the organization of the entity clearly defined in terms of lines of authority and responsibility? Are controls for authorization of transactions established at an adequately high level? Are such controls adequately adhered to? Is the organizational structure appropriate for the size and complexity of the entity? Has management established policies for developing and modifying accounting systems and control activities? Are accounting and data processing centralized or decentralized?

40

ASSIGNMENT OF AUTHORITY AND RESPONSIBILITY The methods of assigning authority and responsibility should result in clear understanding of reporting relationships and responsibilities established within the entity. Yes, No, N/A Comments Is there a clear assignment of responsibility and delegation of authority to deal with such matters as organizational goals and objectives, operating functions, and regulatory requirements? Are employee job responsibilities, including specific duties, reporting relationships, and constraints, clearly established and communicated to employees? Has management clearly communicated the scope of authority and responsibility to data processing management? Does adequate computer systems documentation indicate the controls for authorizing transactions and approving systems changes? Is there adequate documentation of data processing controls?

HUMAN RESOURCE POLICIES AND PROCEDURES Human resource policies and practices relate to hiring, orientation, training, evaluating, counseling, promoting, compensating, and remedial actions. The entitys human resource policies and practices should positively influence the entitys ability to employ sufficiently competent personnel to accomplish its goals and objectives. Yes, No, N/A Comments Do client accounting personnel appear to have the background and experience for their duties? Do client accounting personnel understand the duties and procedures applicable to their jobs? Is the turnover of accounting personnel relatively low? Does the entity adequately train of new accounting personnel? Does the workload of accounting personnel appear to permit them to control the quality of their work? Does previous experience with the client indicate sufficient integrity on the part of personnel?

41 EarthWear Clothiers Memo on Internal Control Components 12/31/05

Prepared by: MKT

Date: 10/15/05 The following information was gathered through inquiry with EarthWears Risk Management Officer, Chief Financial Officer, Chief Information Officer, and Chief Internal Auditor. The Entitys Risk Assessment Process: EarthWear has established broad objectives. Management has prepared a five-year business plan that includes goals about that companys products, responsibilities, and growth plans. The company uses a business plan and budgeting process to monitor decisions. Monthly meetings are held by senior management to discuss recent events and how they may affect the company. Management relies heavily on its Risk Management Department to identify risks that may affect the company, and to recommend appropriate actions. Control Procedures: EarthWear has a very sophisticated budgeting process that includes monitoring activities. All significant budget variances are summarized and explained in a monthly controllers report. See A20 for extracts of significant items from the controllers report. See B10 for a description of the IT Departments controls over access to (1) the computer operations area and (2) data programs and files. Information System and Communication: The strategic plan and the budgeting process identify information that is needed to analyze and monitor the entitys objectives. All department groups within EarthWear are required to provide timely and adequate financial reporting. Actual performance is reported on a weekly and monthly basis. Performance is monitored by the controllers office. There is a strategic plan for updating the information systems over financial reporting that is revising on an annual basis. Employees are provided with information regarding their duties during their initial training. EarthWears employee manual states that suspected violations of company policies should be reported to a vice president. There are procedures that allow such suspected violations to be reported anonymously. There are good communications channels across departments. Monitoring:

42

Customer complaints are generally very low (1 out of every 5,000 invoices). Most complaints relate to delays in receiving goods that are on order by the company. Recommendations that management and the board feel are cost beneficial are implemented. The board of directors focuses on the control environment and monitoring activities. The audit committee meets regularly with the internal and external auditors about control related activities. E20 SAA 1/10/06 EarthWear Clothiers Reserve for Returns Account 12/31/05 Monthly Sales (in 000s) $ 73300 82800 93500 110200 158200 202500 Historical Return Rate 0.004 0.006 0.01 0.015 0.025 0.032 Estimated Returns 293.200 496.800 935.000 1653.000 3955.000 6480.000 13813.000 x 0.425 5,870.525

Months July August September October November December

Gross Margin % Auditor expectation Legend: = Traced to monthly sales journal. = Historical rate tested for reasonableness Conclusion:

The expectation of $5,870,525 is approximately $20,000 less the book value of $5,890,000. Since this amount is less than the tolerable difference of $885,000, the analytical procedure supports the fair presentation of the reserve for returns account.

43

44

45

46

47 E20 SAA 1/7/06 EarthWear Clothiers Reasonableness Test of Interest Expense 12/31/03
Month January February March April May June July August September October November December Total Average $19,240,000 x .0525 = $1,010,000 Legend: = Traced to monthly general ledger balance. = Recaluated the average interest rate for the clients debt. Conclusion: EarthWares income statement shows $983,000 of interest expense. The difference of $27,000 from the auditors calculation is not material based on tolerable difference of $49,150. No further investigation. Balance (in thousands) $ 21,500 18,600 18,100 17,900 16,100 15,500 14,200 20,200 34,500 28,100 15,200 11,000 $ 230,900 19,240

48

49

50