The verification of Information Technology (IT) controls is a core responsibility of IT auditors. This case places the student in the role of an IT auditor assigned to test the operating effectiveness of a specific IT general control: user access management. In addition to learning about IT controls, the case introduces several Excel functions such as VLOOKUP, MATCH, INDEX, and various text functions. The student documents the results of the IT controls tests by completing a testing matrix and writing a memo. General controls, user access management, and Excel applications are all topics taught in Accounting Information Systems (AIS) and Audit courses. As such, instructors can use this case at the undergraduate or graduate level in an IT Audit, Accounting Information Systems, or Audit class.
The Sarbanes-Oxley Act of 2002 (SOX) requires that the management of public companies implement, maintain, and test a system of internal controls to reduce the probability of material financial misstatements and requires evaluation of these internal controls by auditors. SOX requires a company to adopt a recognized framework of internal controls such as the framework developed by the Committee of Sponsoring Organizations of the Treadway Commission (COSO). SOX created the Public Company Accounting Oversight Board (PCAOB) to oversee the audits of public companies and to establish auditing and related professional practice standards. Specifically, as related to internal controls, the PCAOB established AS 2201, a standard for the audit of internal control over financial reporting.
The AS 2201 standard specifies that the auditor use a top-down approach to the audit of internal control over financial reporting. In a top-down approach, the audit starts at the top at the financial statement level, with the auditor obtaining an understanding of the overall risks to internal control over financial reporting. The auditor then focuses on entity-level controls and works downward towards significant accounts and disclosures (PCAOB, 2007). AS 2201 identifies entity-level controls and application-specific controls as internal controls.
Entity-level controls are those controls related to the overall control environment. Examples of entity-level controls include controls related to risk assessment, centralized processing, the monitoring of operations, and the monitoring of other controls (PCAOB, 2007). In contrast, application-level controls relate to controls in specific applications designed to prevent, detect, or correct errors and fraud within the application (Romney & Steinbart, 2018).
Information technology (IT) general controls are a subset of entity-level controls. According to the audit standard AU-C Section 315 (AICPA, 2018, p. 302), IT general controls are “policies and procedures that relate to many applications and support the effective functioning of application controls.” IT general controls include the IT control environment, the change management process, system software acquisition and development, user access management (both logical and physical access controls), and backup/recovery procedures. Without effective IT general controls, reliance on the systems related to the financial reports may not be possible.
The purpose of this paper is to describe an instructional case that focuses on the testing of a specific IT general control (user access management) and to review the use of specific Excel functions in testing the control. User access controls prevent unauthorized users from accessing, modifying, or deleting the organization's information. A recent study by PwC determined that former employees are responsible for 26% of information security incidents in 2017 and 28% in 2016 (PwC, 2018). In addition to learning about IT controls, the case introduces several Excel functions such as VLOOKUP, MATCH, INDEX, and various text functions. The student documents the results of the IT controls tests by completing a testing matrix and writing a memo.
Internal controls including general controls, spreadsheets, systems auditing, and user security are all topics covered in Accounting Information Systems (AIS) textbooks and curriculums (Badua, Sharifi, & Watkins, 2011). IT Auditing and Auditing classes cover general controls including user access management. Proficiency in Excel is a necessary skill in all three classes as well as in the profession. The case presented in this paper is an interdisciplinary case that could be used is an AIS, Audit, or IT Audit class.
This paper is organized as follows. First, we perform a brief review of the accounting educational literature on cases involving internal controls and IT controls. Second, we identify the learning objectives associated with the case. Third, we provide background information on the two primary concepts associated with the case: 1) user access management and 2) various intermediate Excel functions. Fourth, we describe the case and provide guidance associated with implementing the case. Finally, we provide evidence of efficacy in the classroom.
Internal controls and internal controls testing are a key component of accounting information systems, audit, and IT audit and have been the subject of educational cases in the accounting literature. Table 1 provides examples of educational cases related to both internal controls and IT controls.
In Table 1, several cases directly relate to COSO and internal controls. For example, Premuroso and Houmes (2012) use the COSO framework to perform a financial statement risk assessment, while Fleak, Harrison, and Turner (2010) use COSO to evaluate internal controls in a small organization. Savage, Norman, and Lancaster (2008) use a movie to introduce COSO concepts and to identify internal control failures.
Table 1 also highlights educational cases involving the identification and testing of application-level controls. For example, Ragan, Perrotto, and Rizman (2011) use SAP screens to identify internal controls within the SAP software, while Loraas and Mueller (2008) identify specific application controls in spreadsheets.
Finally, several cases in Table 1 relate to specific IT general controls. For example, Norman, Payne, and Vendrzyk (2009) provide a comprehensive discussion of IT general controls and provide an opportunity for students to perform a risk assessment related to the IT general controls. Lehmann (2010) presents a compendium of several short internal control cases, some of which relate to IT general controls (e.g. disaster recovery / business continuity and technology assessments). More recently, Davis, Ramamoorti, and Krull (2017) develop a case scenario to evaluate the internal control structure and conduct a control risk assessment.
Our case adds to the literature related to IT general controls by providing a hands-on application of testing one specific IT general control using Excel: user access management. Prior literature has not specifically addressed user access management. Additionally, as auditors recognize Excel as a widely-used tool, this case allows students to increase their understanding of Excel functions while performing an audit-related test. Students can bridge the gap between theory and application by learning about IT general controls concepts and then performing the detailed testing of IT controls through the use of Excel functions.
The purpose of this case is to educate students about IT general controls and to provide an exercise where students can apply that knowledge and test the operating effectiveness of one particular type of IT general control: user access management. This case provides the opportunity to integrate theoretical concepts related to IT general controls and user access management with specific Excel technical functionality. With this case, students use Excel to assess IT general controls. Specifically, we have four learning objectives:
Educate the student on the concept of IT general controls as related to user access management
Introduce intermediate and advanced Excel functions
Demonstrate how Excel can be used in the assessment of IT general controls
Test and document the operating effectiveness of user access management controls
User Access Management
User access management refers to controls related to the logical and physical access of systems and data. The processes and controls associated with user access management are of primary concern in audits (Schroeder and Singleton, 2010), with the most prevalent IT control weaknesses uncovered during SOX section 404 reviews related to user access management (Worthen, 2005). In fact, CIO magazine reports that 57% of companies still assign local administration rights to ordinary users even in large corporations (Tynan, 2019).
User access controls are the first line of defense against unauthorized access to different parts of the accounting system. User access controls provide the foundation for implementing segregation of duties in a digital environment. A user logs in with a user ID and password, gaining access to subsets of the accounting information system (AIS). The assignment of the user ID to the different parts of the AIS should be based on segregating roles related to authorization, transaction, and recording. For example, different user IDs would have the right to set up a customer (authorizing), create a customer order (transacting), and enter an invoice (recording). In addition, user access controls can prevent a single employee from both entering a bogus purchase order or invoice and then authorizing a payment to the employee for the bogus transaction.
User access management continues to be a concern to information security, especially with the advent of cloud computing. With cloud computing, users no longer have to be physically on site to access the accounting information system. As such, the logical controls associated with user access management ensure that only the authorized users can access the protected resources.
The objectives of user access controls are to reduce the risk of unauthorized or inappropriate access to systems. A key part of testing user access management controls is performing periodic reviews of active users. A periodic review of users can uncover employees who have left the organization or who have transferred to another group but may still have access to the systems. Although unauthorized access is a risk, another issue is the lack of procedures or the ineffectiveness of the existing procedures in addressing employee change of status.
Auditing standard AU-C Section 315 (AICPA 2018) addresses the auditor's responsibility to identify and assess the risks of material misstatement in the financial statements through understanding the entity and its environment. AU-C Section 315.A64 (AICPA 2018, p. 292) specifically identifies several IT risks related to internal controls and user access management such as the “unauthorized access to data that may result in the destruction of data or improper changes to data, including the recording of unauthorized or nonexistent transactions or inaccurate recording of transactions.”
In order to provide guidance in this area, the AICPA developed the 2017 Trust Services Criteria for evaluating and reporting on controls as related to security, availability, processing integrity, confidentiality, and privacy (AICPA, 2017). With respect to user access management, Common Criteria (CC) 5.2 from the Trust Services Criteria (AICPA, 2017, p. 202) states:
CC5.2 New internal and external system users are registered and authorized prior to being issued system credentials and granted the ability to access the system. User system credentials are removed when user access is no longer authorized.
Additionally, the Trust Services Criteria reiterates the importance of separation of duties with respect to user access management. For example, a user's manager approves a user's request for a specific role, and the manager submits the approved request to the security group via a formal change management system. In this example, separation of duties exists among individuals who request access, authorize access, grant access, and review access (AICPA, 2017).
From a software application perspective, user access management generally encompasses the processes associated with creating, changing, and deleting user accounts for the associated applications. When user accounts have access to the systems associated with financial reporting, the IT controls should be formal and documented. The IT controls associated with user access management include the following:
Document account creation and change requests.
Require formal approval from different areas of management for account creation and change requests. For example, HR should initiate account creation for new employees, and the IT department should implement the request.
Create a process to ensure that account administrators are notified in a timely manner when an employee is terminated.
Remove access by terminated employees in a timely manner.
Review access privileges for existing users and verify that those privileges are appropriate for each user's role.
Various studies have frequently identified Excel as an important tool for accountants. For example, Ragland and Ramachandran (2014) confirm that public accounting firms are seeking graduates proficient in Excel and identify specific topics and functions of Excel particularly applicable to new graduates. From the perspective of accounting faculty, Rackliffe and Ragland (2016) explore Excel in the accounting curriculum and find that faculty understand the importance of Excel in public accounting and the need to improve students' overall proficiency in Excel. Finally, in an exploratory study, Lee, Kerler, and Ivancevich (2018) identified Excel as the most frequently utilized software or tool used by accounting practitioners, as well as the most important software tool for new hires.
Excel skills are clearly valued by the accounting profession, but they are sometimes underemphasized in accounting curriculums. For example, instructors may teach Excel skills in a general business course and then perhaps review Excel again in an introductory AIS class. A potential problem is that students only learn basic competency in Excel without an opportunity to focus on more advanced, in-depth Excel skills in the accounting context.
CASE DESCRIPTION AND IMPLEMENTATION GUIDANCE
Description of Case
In this case scenario, the student takes the role of an IT auditor assigned the task of testing the IT controls related to user access management. The student tests the following two control assertions: 1) new employees receive timely access to the system; and 2) after an employee leaves the organization, the employee's account is closed in a timely manner. To test the effectiveness of these control assertions, the IT auditor at the end of each quarter requests a list of new and terminated personnel from Human Resources and a list of active system users from the IT department. The IT auditor validates that the new employees are on the list of active users and that the terminated employees are no longer on the list of active users. In this case scenario, the IT auditor is verifying that the account is opened/closed within the same quarter of the hire/termination.1 Appendix A provides the full case scenario.
In order to complete this case, students will have the opportunity to use several intermediate Excel features (Table 2) to accomplish the testing, most notably text functions and lookup functions. Ragland and Ramachandran (2014) identified text and lookup functions as particularly important to accountants. With respect to text functions, new hires in accounting ranked formatting as 4th in overall importance from a list of 15 Excel functions, while supervisors ranked formatting as 3rd. New hires ranked concatenation, another text-oriented function, 8th, and supervisors ranked it 11th. For lookup functions, new hires in accounting ranked lookup functions 3rd in overall importance, and supervisors ranked lookup functions 5th (Ragland & Ramachandran 2014).
In this case, the student has two data files. The first file is from the Human Resources department, which contains the employee's first name and last name as two separate fields, along with the employee's hire date and termination date (if applicable). From the IT department, the student receives the employee's system user name and the employee name, but with the employee name as one field.2 This inconsistency in format requires the student to perform a process known as “data cleansing” or “data scrubbing.”
Data cleansing or scrubbing is the process of maintaining consistent and accurate data through the removal of any inaccurate or dirty data. In this case, the data from the client is assumed to be accurate, but the format of the employee name between the two files must match before the student can properly test the controls. Excel text functions can address the data preparation step to resolve the formatting differences.
The trim function in Excel removes spaces from a text string. Spaces in a text string may prevent the lookup function from correctly identifying a match. It is a good practice to use the trim function on both sets of data (source and destination) to ensure correct matching of what may be perceived as identical data. The concatenate function joins text together so that a new string can be created from various input strings, such as creating a “last name, first name” string or a “first name space last name” string.
By reviewing the Excel features in Table 2, the instructor provides general guidance on potential Excel features that could be useful in accomplishing the task. However, this task is fairly unstructured in that there are various ways to accomplish the goal. The student independently determines the required Excel functions to use and the specific steps to accomplish the controls testing.
A graduate-level IT Audit class has implemented this case three times, in Fall 2016 (44 students), Fall 2017 (55 students), and Fall 2018 (58 students). This class is a required class in a Master of Science in Accounting program at a southeastern U.S. university. The class is a 7-week, two credit hour class and meets face-to-face twice a week for 100 minutes per class session. The course typically uses the case midway into the term, with students already exposed to initial concepts of IT controls and basic Excel skills.
This case is intended as an individual case for undergraduate or graduate accounting students. The student should be able to complete the case outside of class in 1–2 hours. Courses that could incorporate the case include Audit, IT Audit, and Accounting Information Systems.
The instructor should spend about 45 minutes to 1 hour of class time preparing the students for the case. First, the instructor can assess students' existing knowledge of IT general controls, application controls, and various Excel features used in the case by administering a pre-test, which is included in the Instructor Resources. Second, the instructor can review the concepts associated with IT general controls, including excerpts from the AS 2201 and AU-C Section 315 standards.3 Third, the instructor can discuss the Excel features of VLOOKUP and INDEX/MATCH in more detail and provide examples of the applicability of those features. After reviewing these three steps, the instructor can introduce the actual case scenario, the assignment, and the files (spreadsheets) required to complete the case. The students should complete the actual work on the case individually and outside of class.
After completing the case, the student submits the following files: 1) a memo documenting the results; 2) an Excel worksheet representing a work paper with the completed testing matrix; and 3) a merged Excel workbook that demonstrates how the student combined the two input spreadsheet files and performed the matching task. In a subsequent class, the instructor can review the correct solution and the sample memo, as well as generate classroom discussions on the variety of approaches used by students to perform the testing. It might be especially interesting to note the number of students using VLOOKUP versus “INDEX/MATCH” and again discuss the differences in the two approaches. Additionally, the instructor could assess retention of the knowledge from this case by having the students re-take the same pre-test or by creating a new post-test.
The following teaching materials are available to instructors: 1) a set of teaching slides to guide the instructor through the steps described above; 2) two versions of a pre-test that include questions on IT controls and the applicable Excel features in the case; 3) a suggested solution spreadsheet; 4) a spreadsheet with the intermediate steps to derive the solution; 5) a sample memo documenting the results of the controls testing; and 6) a suggested grading rubric.
The student materials include: 1) a case scenario in Appendix A; 2) a testing matrix for students to report test results (“Case Testing Matrix.xlsx”); 3) a list of new and terminated employees (“New and Terminated Employees.xlsx”); and 4) a list of authorized computer users (“System Usernames.xlsx”).
A possible extension of this case is to work it with a database such as Microsoft Access. By using Access, students would need to be familiar with database concepts related to primary keys, table organization, and database querying. Instructors teaching AIS classes using both Access and Excel can work this case first with Excel and then later with Access. Another more unstructured approach would be for the student to have the option to use either software to complete the IT controls testing.
We assessed the efficacy of the case in two ways: 1) knowledge related to Excel skills and IT controls, and 2) student perception of the case.
To assess the knowledge related to Excel skills and IT general controls, we administered a pre-test and a post-test (Table 3). Version A of the test instrument was used in 2016 and 2017, while version B was used in 2018. Table 3 describes the differences between the two versions. Although Table 3 provides the general questions, the full tests are available in the instructor resources.
In assessing improvements in knowledge relevant to the case, we first gave the pre-test to students to develop a baseline number. Students then completed the case to develop their proficiency with the functions and features of the assignment. After completion, we gave the students a post-test with the same questions as the pre-test. We analyzed the results using a paired-sample t-test. This analysis revealed a significant difference between the mean number of correct responses between the pre-test and the post-test for all three years. Table 4 presents the results for the pre-test and post-test, showing an overall improvement in the scores of 60.07% (Fall 2016), 35.04% (Fall 2017), and 6.12% (Fall 2018).
To assess student perceptions of the case, we surveyed students at the end of the semester for the three years (Table 5). From Table 5, students from each of the three years responded positively to the case, agreeing that the case improved their understanding of IT controls (Q1) and improved their knowledge of Excel functions (Q2–Q4). Notably, the respondents agreed that the case will be useful to future accounting graduate students (Q8) and recommended continual usage of the case (Q9). Additionally, we compared the mean differences among the years using an independent-samples t-test. We compared the mean values for Q1–Q9 between 2017 and 2018 and found no significant differences (p < .05) in the means between the two years. Similarly, there were no significant differences (p < .05) in the mean values for Q1–Q9 for 2016 versus 2017. However, in comparing the means for 2016 versus 2018, we found that Q1 (p = .0289) and Q8 (p = .0036) were significantly different between 2016 and 2018, which provides limited evidence on improvements in the case as it was implemented during the three-year period.
A potential limitation of this case is that it has only been formally implemented with graduate students in the Master of Accounting program as part of an IT Audit class. However, with the core focus of the case related to IT general controls, we believe that the case is also appropriate at the undergraduate level in an AIS or Audit class.
Overall, this case provides students the opportunity to perform IT general controls testing related to user access management and to use specific Excel features and functions in this testing. As this case is based on the experiences of actual interns through their internship work experience in public accounting, it provides a real-world task that future audit / advisory interns may encounter. In addition, this case provides an accounting-based scenario for students to use and improve their Excel skills, as well as an opportunity for instructors to emphasize the accounting standards related to internal controls and IT controls.
We wish to thank Andrew Archibald for his assistance.
Editor's Note: This article contains hyperlinks to World Wide Web pages. Readers who have the ability to access the Web directly from their devices and applications may be able to gain direct access to these linked pages. Readers are warned of the following caveats regarding these links.
The links existed as of the date of publication but are not guaranteed to be working thereafter.
The contents of web pages may change over time. Where version information is provided in the AISEJ published article, different versions may not contain the information or the conclusions referenced.
The author(s) of the web pages, not AIS Educator Journal nor AIS Educator Association, is (are) responsible for the accuracy of their content.
The author(s) of this article, not AIS Educator Journal nor AIS Educator Association, is (are) responsible for the accuracy of the URL and version information.
You are an IT auditor and have been assigned by your senior (Max Rogers) to test IT controls at a large data center. One of the controls you are testing is management's review over authorized user accounts for one of their database systems. Due to the confidential nature of this database, management is required to review and update the authorized users list periodically and to issue quarterly reports on the authorized users.
As the IT auditor, you must test the quarterly reports of authorized users maintained by management against both the new employee lists and the terminated employee lists received from Human Resources. Specifically, you must verify the following assertions:
All new hires have an account created with access to the database within the same quarter they are hired.
All terminated employees are removed from the authorized users list within the same quarter they are terminated.
Emily Hanover is the regional manager over the data center and your main point of contact. She has sent you management's quarterly reports regarding authorized user accounts (“System Usernames.xlsx”).
Sam November is head of the HR department and has sent the lists of new employees and terminated employees for each quarter of 2014 (“New and Terminated Employees.xlsx”).
Step 1: Examine the files received from Emily and Sam. Develop a way to test the names from the lists received from Sam against the list received from Emily.
Step 2: Test the lists for Q1–Q4 to determine if there are any exceptions. An exception would be a new hire without an account or a terminated employee with an account.
In this assignment, you are testing two assertions for each quarter:
Assertion A: Newly hired employees have authorized user accounts created within the quarter of hire
Assertion B: Terminated employees have their authorized user accounts deleted within the quarter of termination
Please be sure to consider the following:
If a new hire does not have an authorized account in the quarter of hire, you must check if the new hire was granted access in a subsequent quarter.
If an employee was terminated in a particular quarter and still had access in that same quarter, you must continue to check if he or she has access in subsequent quarters. If the employee still has access in subsequent quarters, it would continue to be considered a test failure for that quarter until the employee's account was properly deleted. For example, if John Doe was fired in Quarter 2 and still had a user account in Q2, Q3, and Q4, Assertion B would be considered a “Fail” for those three quarters (Q2, Q3, Q4).
If an employee is hired and terminated in the same quarter, the employee is expected to be on both the hired list and the terminated list but not on the “authorized users” list. For example, if John Doe was hired in Q2 and fired in Q2, he would be listed in the Q2 report (6/30/2014) as a new hire and a terminated employee. Additionally, it would be expected for him to NOT be listed as an authorized user since the authorized user list represents the authorized users at a particular point in time.
Step 3: Fill in the testing matrix (“Case Testing Matrix.xlsx”) with the test results. In the testing matrix spreadsheet, you will complete the shaded cells in the following table:
For example, for Q1 (3/31/2014), if Assertion A is verified as correct in that all newly hired employees during Q1 have been added as authorized users, then you would put a check mark in the appropriate cell.
To use a check mark in the table, go to Insert>Symbol>Font: Wingdings and select the checkmark symbol.
If exceptions are noted, put an alphabetic footnote and explain the exception in the text box at the bottom of the matrix. For example, if John Doe was hired on 3/1/2014 and was not on the authorized user's list as of 3/31/2014, an exception would be noted in the testing matrix and indicated by Footnote A and documented in the Exceptions box.
Complete the testing matrix based on your test results for the four quarters.
Step 4: Prepare a one-page memo to your senior documenting the testing objectives, detailed procedures, results, and recommendations.
Include the WHO, WHAT, WHY, WHEN, WHERE, and HOW
One-page (single-spaced; single space between paragraphs; no indention on first paragraph; 11-point font)
1 The quarterly timeframe is used in this case as part of the audit verification process. In practice, the client is likely to have more stringent requirements on the timing of account provisioning and closures, e.g. the client would likely have a terminated employee immediately removed as an authorized user on the employee's last day of work. This timing on actual account provisioning and closure versus the timing of audit verification can be included as a part of the overall classroom discussion.
2 In practice, employers would likely have an Employee ID as a primary key that would be used as part of the matching process. For this case, an Employee ID is not provided in order to provide additional practice with Excel text functions.
3 Information on AS 2201 can be found at: https://pcaobus.org/Standards/Auditing/Pages/AS2201.aspx. Accessed 21 April 2019.
Information on AU-C Section 315 can be found at: https://www.aicpa.org/content/dam/aicpa/research/standards/auditattest/downloadabledocuments/au-c-00315.pdf. Accessed 21 April 2019.
A teaching note and electronic files are available to faculty members for use with this case. If you are a member of the AIS Educator Association, please go to www.aiseducators.org, sign in to your account, select the Journal menu option and the last item listed provides a secure link to Instructor-only materials.