A recent article in this Journal by John White1 discussed the ability of Microsoft (MS) Access database applications to support certain controls. The article connected various control categories with certain features available in MS Access. Certainly, MS Access does not directly support all types of controls and it is up to developers to provide mechanisms for many controls that cannot be explicitly included in applications.
In more advanced development environments, control features are simply included by turning switches on (or off) depending on the actions that are pertinent to the project. While it is important to understand the nature of these switches and their interactions, knowledge of specific approaches to the actual coding of the features is also valuable for a variety of reasons. First, many applications are best suited to the MS Access development environment and, therefore, it is important to understand how certain capabilities, particularly those that enhance the controls, can be included. An alternative issue involves gaining an understanding of possible approaches to enforcing controls.
Understanding exactly how specific controls are enforced, even if this is done automatically, can give a developer or an auditor a better view of exactly what is being controlled and what is not. For professionals who will be taking a position in either development or audit, this knowledge is invaluable and can be best learned by actually developing the controls features.
This article discusses a project undertaken by three undergraduate business students to include continuous monitoring capabilities in an MS Access database (for Control Objectives for Information and related Technology [COBIT] ME1 and ME2 processes). The students completed specific components of the project based on a conceptual model developed in an earlier class. This article discusses the conceptual model used to create the database, the model that was used to support the design of the continuous monitoring features and data extracts, and the queries that were developed to review the extracted data.
REA Conceptual Model
The resources, events and agents (REA)2 conceptual model provides a framework to organize the creation of tables, fields and relationships in a relational database. By providing this structure, the process of translating the data requirements for a particular business process into a data model and then finally into a database model becomes more exact and reproducible (figure 1). For a specific business process (transaction cycle), such as the revenue cycle, the generic names are replaced with more specific references (figure 2).3 When these business processes are tied together in a value chain, the various value-added activities are easy to visualize and their shared resources and information become more apparent (figure 3 and figure 4).4 The focus of the project was the revenue cycle, as the issue of revenue recognition and its audit implications were more central to some of the interests of the students. The project also focused on the specific transactions that had direct impact on the financial statements. This meant that, while orders were taken, the actual monitoring activities were restricted to sales and cash receipt transactions, the resources exchanged, and the agents involved in the exchange.
Conceptual Model of Continuous Monitoring
There are two questions that must be answered when designing controls into an information system:
- What are the risks associated with the particular business process?
- What control(s) should be used to mitigate the risk?
A control can either prevent certain actions or suggest what appear to be correct actions. For instance, creating a primary key field in MS Access prevents someone from assigning the same customer number to different customers. On the other hand, a date field can suggest that today’s date be used as the date of the sale. A person entering a sale can use the suggested date, but for various reasons may want to enter a different date. Another example involves the sale of inventory items. If the records indicate that the quantity on hand is less than the quantity to be sold, allowing the sale to be recorded would result in a negative quantity on hand.
In these two cases—changing the date and selling more than what appears to be on hand—the system can prevent the action or note it. If the system prevents these actions, and in general has too many preventive controls, users may find the system too cumbersome and, in many cases, may find ways to do what they want to do anyway. From an auditor’s perspective, this is a problem because now the information system does not have a record of what actually happened. However, from a monitoring perspective, both of these activities represent a problem with company procedures and indicate that controls are either missing or inadequate. The date example could be the result of individuals who are overworked and cannot enter sales when they occur, or they could be back-dating a sale so it is reported in a specific accounting period.
The monitoring project undertaken by the authors categorized events in terms of those that were part of normal transaction processing and those that were part of maintenance processing. Thus, there was a distinction between a date that was changed during a sale and one that was changed during maintenance. When evaluating control procedures, the changes to quantity on hand during maintenance activities were also separated between changes that brought items from negative to positive and changes that simply increased the quantity. This provides an auditor with the ability to judge effectiveness of controls in terms of the length of time it takes to find and correct errors. A breakdown of the types of controls and monitoring is depicted in figure 5.
The conceptual model of the activities to be monitored and the conceptual model of the database led to the inclusion of three tables—one each for continuous monitoring messages related to resources, events and agents. The table fields are shown in figure 6. Each table kept track of events that affected the different entities of the particular type. This means that an event could cause an entry in more than one table. For instance, if a sale was made that caused the customer balance to exceed the credit limit, an entry was made in the events table to note the particular sale and the attributes of that event, and an entry was also made in the agents table to note which salesperson was responsible for making a sale that exceeded credit limits.
These tables and entries allow auditors to form queries that provide them with the information they need.
The next section looks at some of the queries that were created in the project as part of the audit module and that made use of the information contained in the continuous monitoring tables.
Continuous Monitoring Queries
The traditional audit process requires the collection of evidence and then a review of that evidence to come to a conclusion about the acceptability of the financial statements. With the passage of the US Sarbanes-Oxley Act, auditors are faced with an additional evaluation of internal controls. Some of the evidence for this evaluation comes from outside of the formal information system, but there is also a great deal of information available from the continuous monitoring system described herein. This section will give a few examples of queries that were developed during the project both from the continuous monitoring tables and from a general review of other information.
One of the major objectives of internal controls systems is that transactions are executed in accordance with management’s general or specific authorization. These types of policy statements can take many forms, but one in particular has to do with the customers to whom salespeople are allowed to sell. These policies can be quite in depth, especially if there are government regulations that come into play.
For this project, the requirement was simply that the amount of a sale must not cause the customer’s credit limit to be exceeded. If this occurred, an entry would be made in the events table indicating which event caused the condition and in the agents table indicating the salesperson responsible for the violation. The project also included queries that allowed an auditor to look at the agents who were responsible for this control violation or at the specific events or business processes that had the violations. Another set of queries was put in place with regard to resource errors, specifically when the recorded quantity fell below zero. In addition, for each of these situations, the auditor was able to look at the correcting event—the event that brought the quantity above zero. This could either be the result of a maintenance activity, in which case the query would show the person who entered the corrected quantity along with the date and time of the entry, or a purchase for that particular inventory item. The first case indicates a control problem in recording specific sale items, while the second indicates a problem with timing of related business processes. Another set of queries was in place that had to do with evidence of fraudulent practices.
If management is trying to enhance the income reported to investors, there are many different ways to do this.6 One particular approach is to make sales near the end of a fiscal year and then process a return after year end. The query the authors used to gather this type of evidence combined two different results. First, it looked at sales that were unusual and occurred in the last month of the fiscal year. In this case, “unusual” was defined as sales that were above one standard deviation from the mean. This meant that there was a rolling set of values that would be affected by the number of sales and their distribution. These two parameters, the period before closing and the definition of unusual, could be set by the person reviewing the data. The next part of the query took the unusual sales and then looked for returns on these sales within the first month of the subsequent fiscal year. If the monetary value of these sales was material, an auditor would be inclined to adjust revenue amounts for the previous year.
The development of an information system is a task that has become more structured as specific tools have become available. The passage of the Sarbanes-Oxley Act makes adding structure to this process a requirement rather than simply a good idea. To the extent that models can be used to guide the development process, the resulting systems will be more consistent and structured. The project discussed in this paper began with a model—the REA model—that has become more accepted as a design framework.7, 8 The project discussed in this paper required understanding the particular model and then developing a system based on this model. Then, consideration was given to what should be monitored on a continuous basis, what problems could occur and, finally, how to collect information on these problems.
While the system itself was not as large and complicated as would be found outside of a classroom, the knowledge gained by using a particular design as a guide to an actual implementation is something that can be applied in all system development settings. The problem solving required while implementing the model as a group project is something that development teams wrestle with daily, and is best learned from experience rather than as a chapter in a text.9
As Tommie Singleton10 pointed out in a recent article, there are very few schools that completely meet the requirements of ISACA’s Model Curriculum. With the constraints placed on regular curricula, there is a need to provide students with the necessary experience while still allowing them to complete their program requirements. At the University of Massachusetts, the project described herein was part of an independent study that also met the requirements of a capstone experience of the IT minor. The requirement to take information from previous classes and apply it as part of a realistic project is something that is difficult to cover in the structure of a large class.
1 White, J.H.; “Important, But Often Dismissed: Internal Controls in a Microsoft Access Database,” Information Systems Control Journal, vol. 6, 2006, p. 30-34
2 McCarthy, William E.; “The REA Accounting Model: A Generalized Framework for Accounting Systems in a Shared Data Environment,” The Accounting Review, July 1982, p. 554-578
3 While the specific identifiers may vary depending on the terms used within an enterprise, the concepts of resource exchange and agents that play roles in the exchange are universal across business entities.
4 These REA models include only the portion of the business process related to the actual accounting. This allowed the continuous monitoring to focus on events and information relevant for the financial auditor.
5 This connection is for a retail enterprise in which inventory is purchased wholesale and sold at retail. For an enterprise that buys raw materials, converts them and then sells finished goods, the inventory entity would be split and the conversion process REA model would fit in between the two types of inventory.
6 Lundelius, Charles R.; “Financial Reporting Fraud: A Practical Guide to Detection and Internal Control,” American Institute of Certified Public Accountants, USA, 2003
7 Hruby, Pavel; “Model-Driven Design Using Business Patterns,” Springer-Verlag Berlin Heidelberg, Germany, 2006
8 See www.workday.com for an example of a system that uses the REA model as the underlying structure.
9 It was interesting to note that presumably simple issues such as consistent naming of variables arose. Fortunately, the students discovered the problem early as opposed to later in the process.
10 Singleton, Tommie; “IT Audit Education and Professional Development,” Information Systems Control Journal, vol. 3, 2007, p. 13-14
graduated cum laude from the Isenberg School of Management at the University of Massachusetts (USA) with a major in operations management and a minor in IT. He is the manager of Incline Training, an athletic coaching and consulting company based in Greenfield, Massachusetts.
Graham Gal, Ph.D.
is an associate professor of accounting and information systems at the Isenberg School of Management at the University of Massachusetts in Amherst. He teaches accounting information systems and systems analysis and design at the undergraduate level and IT audit and security as part of the masters in accounting program. Gal is a member of the University of Massachusetts’ IT curriculum and policy committee and is the university’s academic advocate to ISACA.
graduated magna cum laude from the Isenberg School of Management at the University of Massachusetts in 2007 with a major in accounting and information systems and a minor in IT. He currently works for PricewaterhouseCoopers LLP in the systems and process assurance group in Boston, Massachusetts.
graduated cum laude from the Isenberg School of Management at the University of Massachusetts in 2007 with a major in accounting and information systems and a minor in IT. He currently works for PricewaterhouseCoopers LLP in Charlotte, North Carolina, USA.
Information Systems Control Journal, formerly the IS Audit & Control Journal, is published by the ISACA. Membership in the association, a voluntary organization of persons interested in information systems (IS) auditing, control and security, entitles one to receive an annual subscription to the Information Systems Control Journal.
Opinions expressed in the Information Systems Control Journal represent the views of the authors and advertisers. They may differ from policies and official statements of the Information Systems Audit and Control Association and/or the IT Governance Institute® and their committees, and from opinions endorsed by authors' employers, or the editors of this Journal. Information Systems Control Journal does not attest to the originality of authors' content.
Instructors are permitted to photocopy isolated articles for noncommercial classroom use without fee. For other copying, reprint or republication, permission must be obtained in writing from the association. Where necessary, permission is granted by the copyright owners for those registered with the Copyright Clearance Center (CCC), 27 Congress St., Salem, Mass. 01970, to photocopy articles owned by the Information Systems Audit and Control Association Inc., for a flat fee of US $2.50 per article plus 25¢ per page. Send payment to the CCC stating the ISSN (1526-7407), date, volume, and first and last page number of each article. Copying for other than personal use or internal reference, or of articles or columns not owned by the association without express permission of the association or the copyright owner is expressly prohibited.