Can you please help me with this case problem?

AICPA Case Development Program. Case No. 2000-02: Recreation, Inc

Case No. 2000-02: Recreation, Inc. 1 AICPA Case Development Program RECREATION, INC. AN INFORMATION TECHNOLOGY RISK ASSESSMENT CASE STUDY OF ENTERPRISE RESOURCE PLANNING (ERP) SYSTEMS James E. Hunton, Associate Professor University of South Florida, Tampa, Florida Tammy L. Jones, Senior Auditor and Consultant Arthur Andersen, LLP, Tampa, Florida The Information Technology (IT) function1 of a business organization is responsible for many aspects of the firm's computer, communication, and information processing systems. With respect to the latter, the IT department plays a key role in (1) ensuring complete and accurate processing of accounting transactions, (2) protecting and maintaining security over one of the organization's most valuable resources (information), and (3) assuring that relevant decision-making information is available to appropriate individuals when needed. Because most financial information is processed and maintained within a company's IT environment, the IT function is a critical focus area whenever a financial audit is performed. Many accounting firms have developed specialized practice areas that focus on assessing and managing various risks associated with the IT function. The purpose of this case is to familiarize you with a number of risk and control considerations related to an organization's IT environment. Part I of this case is designed to acquaint you with the case company (Recreation, Inc.) and to provide an overview of the company's IT environment. The case questions at the end of Part I are not necessarily specific to Recreation, Inc., as the risks and controls you are asked to consider are applicable to most business organizations. The next three parts of the case decompose the IT infrastructure of Recreation, Inc. into the network and operating system (Part II), database system (Part III), and application system (Part IV). While the case questions presented at the end of each of these sections are somewhat specific to the circumstances of Recreation, Inc., the issues and considerations involved are nevertheless applicable to IT functions in a wide array of profit, not-for-profit, and governmental entities. 1 The IT function, often referred to as the 'IT Department,' may also be labeled the 'Information Systems (IS) Department', 'Management Information Systems (MIS) Department', and 'Computer Information Systems (CIS) Department', among other similar terms. Copyright 2000 by the American Institute of Certified Public Accountants (AICPA). Cases developed and distributed under the AICPA Case Development Program are intended for use in higher education for instructional purposes only, and are not for application in practice. Permission is granted to photocopy any case(s) for classroom teaching purposes only. All other rights are reserved. The AICPA neither approves nor endorses this case or any solution provided herein or subsequently developed. Case No. 2000-02: Recreation, Inc. 2 AICPA Case Development Program Part I: Overview of Recreation, Inc. Recreation, Inc., located in Tampa, Florida, is the parent company for three subsidiary organizations that sell recreation vehicles (RVs). The subsidiaries are located in St. Petersburg, Florida, Miami, Florida, and Atlanta, Georgia. Recreation, Inc. manufactures RVs at two plants: one located in Columbia, South Carolina and the other in Birmingham, Alabama. Recreation, Inc. is preparing to issue an initial public offering (IPO) later this year; therefore, its financial statements for the past three years are being audited. In accordance with the AICPA's Generally Accepted Auditing Standards, the financial audit team is properly complying with the ten Standards of Fieldwork as they perform their audit of Recreation's financial statements. As they prepare for the audit, the team considers the second Standard of Fieldwork, which states that "a sufficient understanding of internal control is to be obtained to plan the audit and to determine the nature, timing, and extent of tests to be performed." The audit manager realizes that Recreation, Inc. depends heavily on its IT function to capture and process the accounting information that is ultimately reported on the financial statements. Therefore, in order to obtain a sufficient understanding of internal control, an assessment of the company's risks surrounding the IT function must be performed. Assume that you are a first-year staff member on the audit team and that you have the responsibility for conducting an IT risk assessment of Recreation's computing environment. A risk assessment is a process whereby risks are identified and their potential effects are evaluated.2 You have been charged with reviewing facts associated with the IT environment, identifying the general and specific risks within the IT environment, determining the significance level that each risk poses to Recreation's account balances, and recommending appropriate internal controls designed to mitigate the identified risks. IT MANAGEMENT Recreation's IT department is managed by Dan Drake, Chief Financial Officer (CFO) and IT Director. Dan has been with the company for 14 years. His education and experience are concentrated primarily in the areas of accounting and finance. Dan was promoted to CFO two years ago. At that time, he also assumed the role of IT Director. He spends the vast majority of his time in dealing with CFO related issues, as his financial management skills are excellent. Dan has developed his IT knowledge 'on the job.' Dan has a staff of ten employees. The corporate office is supported by five IT personnel, including Network Administration and Support, Computer Operations, Programming and Testing, & Support functions. Additionally, there are five IT personnel (Field Technicians) who support the regional IT systems located at the three sales subsidiaries and two manufacturing sites. The organization chart (see Figure 1 on the next page) illustrates the personnel structure of the IT department. 2 Information Systems Audit and Control Association, Use of Risk Assessment in Audit Planning Guideline, Version I-1.0, 2000.Chart of the Information Technology Department Case No. 2000-02: Recreation, Inc. 3 AICPA Case Development Program Figure 1: Organizational Chart of the Information Technology Department Daniel Drake CFO/IT Director Ned Anderson Network Administration Nick Smith Network Support Carol Olson Computer Operations Field Technician St. Petersburg Patty Porter Programmer Tom Tyler Testing & Support Field Technician Miami Field Technician Atlanta Field Technician Columbia Field Technician Birmingham IT STRATEGY AND OTHER CRITICAL IT POLICIES Recreation, Inc. does not have a formal IT strategy, but it intends to incorporate IT upgrades and enhancements into its overall corporate growth strategy as time and resources permit. Formal policies and procedures exist for general user interaction with each software application, such as logging into an application or querying a database, but policies and procedures directly addressing IT functions, such as application development and maintenance, do not exist. However, procedures for these functions are informally followed. Additionally, a formalized business contingency plan does not exist, although some informal procedures have been established. BACKUP & RECOVERY Recreation performs weekly incremental backups of its servers and monthly full system backups. Incremental backups save all files that have been updated since the last incremental backup. A full backup saves all data. Two copies of the backup tapes are made. One copy is maintained in a fireproof vault in the corporate offices and the second copy is sent via courier to the St. Petersburg sales dealership. Furthermore, the St. Petersburg server can be used as a backup server in the event one of the corporate servers becomes unavailable. Case No. 2000-02: Recreation, Inc. 4 AICPA Case Development Program APPLICATION DEVELOPMENT AND MAINTENANCE Development of new applications and changes to existing applications are submitted to Dan Drake. Dan reviews the requests for appropriateness and compliance with corporate IT strategies, then forwards the requests to Patty Porter (the programmer). When making modifications to existing programs, Patty first copies the applicable source code from the production environment, modifies the code via her desktop computer, and then sends the modified source code to the test server. Once in the test server, Patty and Tom Tyler (Testing and Support) compile the modified code and test the code for functionality and processing integrity. Once the code has been tested and approved, Patty copies the modified source code into the production environment and compiles the source code into object code. As a result, the new changes take effect immediately. On a monthly basis, Dan reviews code changes made the previous month for appropriateness. With respect to new application development, the same basic processes take place. That is, Patty writes the code, Patty and Tom test the code, Patty places the new application into production, and Dan reviews new applications each month. COMPUTER ROOM SECURITY Recreation maintains its critical computing equipment, namely its servers and routers, in a separate computer room. The computer room, which is located along a main hallway on the second floor of Recreation's headquarters, is not locked, but the door is usually closed by the data entry clerk whose desk is also located in the computer room. The floors in the computer room are not raised and there are no water detection devices. Most servers are stored above the floor on racks, but some are sitting on the floor. Recreation's management does not feel that water detection devices or raised floors are necessary since the computer room is on the second floor of the building. An automatic fire suppression system has not been installed because hand-held fire extinguishers are located in the computer room. Also, all of the computer equipment is plugged into outlets along the computer room walls, with no visible signs of power surge protection. There is an uninterruptible power supply (UPS) system that provides 20 minutes of alternative power to the servers in the event the main power supply is unavailable. However, Carol Olsen (Computer Room Operations) is not sure whether the UPS provides surge protection, and if it does, she is not sure that it is adequate for the servers and routers. Part I: Case Questions Based on the facts presented, your first objective is to assess and evaluate general IT risks associated with Recreation's IT environment. Focus your responses on the previously described areas of (1) IT management, (2) IT strategy and policies, (3) backup and recovery, (4) application development and maintenance, and (5) computer room security. 1. What are the inherent risks associated with Recreation's IT environment? Inherent risks are defined as the susceptibility of account balances to unintentional material misstatements before considering the effectiveness of the related internal control structure (SAS 47). Inherent risks are present regardless of the industry in which the company conducts business, the size or nature of the organization, or the type of processing performed; however, their precise nature and magnitude can vary from across companies. 2. Ideally, what controls would you recommend to mitigate each inherent risk just identified? Inherent risks are mitigated when one or more controls reduce the risks to acceptable levels. Controls may be manual, computerized, or a combination of both. It is important to recognize that, in many cases, inherent risks can not be totally eliminated; however, they can be lowered to tolerable levels via internal controls. Please categorize your responses into preventive and detective controls. Preventive controls are designed to ensure compliance with prescribed or desired events and behaviors, such as passwords and edit checks (e.g., ensuring that the customer number entered on a sales order matches a valid customer). Detective controls are designed to identify and expose undesirable events and behaviors that slip through the preventive controls, such as audit logs and comparison of inputs (e.g., cash receipts) to bank deposits. Thus, preventive and detective controls are applied before and after the fact, respectively. AICPA Case Development Program Case No. 2000-02: Recreation, Inc. 5 3. What are the control risks associated with Recreation's IT environment? Control risk is defined as the risk that the client's controls will fail to prevent or detect material misstatements (SAS 55 & 78). When answering this question, match Recreation's existing controls with the corresponding inherent risks identified above, then assess the remaining control risk, also known as residual or net risk (gross inherent risk minus risk mitigated by existing controls). 4. How would you test the effectiveness of existing controls surrounding Recreation's IT environment? Internal controls can be tested in various ways, such as direct observation, interviews, and audit trails. Please list each existing control identified above and describe the ways in which you might test each control. 5. What specific control changes and improvements would you recommend to manage the residual risk associated with Recreation's IT environment? When answering this question, first identify the 'high residual risk' areas, then determine how you would augment existing controls and/or implement new controls such that residual risk is lowered to acceptable levels. Part II: Network & Operating System Applications IT INFRASTRUCTURE A company's IT infrastructure is multi-dimensional, consisting of various layers, with the bottom layers supporting those above them. Figure 2 illustrates the layers of a typical IT infrastructure. Note that this is a high-level example; additional layers may be included and each layer can be divided into many sub-layers. For purposes of clarity and understanding, the IT infrastructure of Recreation, Inc. is depicted in terms of the three layers shown in Figure 2. This part of the Recreation, Inc. case focuses on the first layernetwork & operating system. Database Network & Operating System Figure 2: IT Infrastructure NETWORK & OPERATING SYSTEM The operating system is the foundation of the IT environment and is considered the real "brain" of a computer. Computers cannot function without an operating system. An operating system is software that, after being initially loaded into the computer, manages all of the computer's internal resources, such random access memory, video cards, disk drives, pointing devices, and so on. In addition, users can interact directly with the operating system through an interface known as a command language. Familiar operating systems are Windows and UNIX. A network is a series of nodes interconnected by communication paths. Networks can interconnect with other networks and contain sub-networks. In a network, a node is a connection point, either a redistribution point (e.g., a router, hub, or switch) or an end point, such as a computer or some sort of peripheral equipment (e.g., printers, fax machines, scanners, and routers). The network serves as the backbone of an IT environment. Without a network, the organization's IT environment would consist of a number of stand-alone computers which could not communicate with each other. All applications and data would be restricted to only the computer on which they are stored, and peripheral devices could only used by the computer to which they are physically connected. Networks enable a nearly unlimited number of computers to share resources, including applications (software), directories, files, data, printers, and Internet connections. Networks can be characterized in terms of spatial distance, such as local area networks or wide area networks: Local Area Networks (LANs) connect computers and other hardware located in a limited geographic area, such as an office or a building. Typically, nodes in a LAN are physically connected to each other via copper wire or optical fiber; however, some LANs use infrared light and radio frequencies as the connection media. Wide Area Networks (WANs) provide connectivity over large geographic areas, indeed worldwide, using a combination of media, such as microwaves, telephone lines, satellites, and the Internet itself. Network software is installed on all linked computers in the network. Basically, network software is classi- AICPA Case Development Program Case No. 2000-02: Recreation, Inc. 6 fied as either server or client. Network server software provides 'services' (e.g., data retrieval, data storage, data access, and node connections) to other computers throughout the network. The computer on which network server software is loaded is often called the server. There may be multiple servers on a network, as a given server can communicate with other servers. Network client software is loaded on all non-server computers throughout the network, which are called clients. Client software is designed to request 'services' from servers throughout the network. Some network software is separate from but linked to the computer operating system (e.g., Novell), while other network software contains its own operating system (e.g., Windows NT). Recreation, Inc. uses Windows NT software on all network servers. The client computers use Windows 95, wherein Microsoft has incorporated network client software designed to communicate with Windows NT. Recreation, Inc. manages its network from the corporate headquarters, where seven servers are located. These servers are named as follows: 1. Peoplesoft Server: 2. Oracle Server: 3. Test Server: 4. E-mail Server: 5. Backup Server: 6. Firewall Server: 7. Network Server: Maintains Peoplesoft applications, Holds the Oracle database and related software tools, Tests new applications and software changes/upgrades, Processes e-mail requests and stores e-mail messages, Stores backup data, Houses firewall software for remote access purposes, Accommodates Windows NT local area network (LAN) software and provides user authentication. Five additional servers are utilized, one located at each of the three sales dealerships and one at each of the two manufacturing sites. Due to the close proximity to the corporate offices, the server located at the St. Petersburg sales dealership serves as a backup should any of the corporate servers fail. Each corporate server is physically connected to the LAN. The remote servers at the sales dealerships and the manufacturing sites are connected to the LAN via a router, thus creating a WAN. Remote users can access the network using the Internet; however, a firewall server is used to ensure only authorized personnel gain access to the network. There are approximately 100 personal computers (PCs) within the Recreation, Inc. organization, where each is assigned specifically to a Recreation employee. See Figure 3 (next page) for an illustrated diagram of Recreation's network configuration. Case No. 2000-02: Recreation, Inc. 7 AICPA Case Development Program Figure 3: Recreation's Network Configuration St. Pete Users Miami Users Atlanta Users Columbia Users Birmingham Users St. Pete Server Miami Server Atlanta Server Columbia Server Birmingham Server Router Firewall Tampa Users Network Server Internet Network backbone Remote Users PeopleSoft Server Oracle Server Test Server E-Mail Server Backup Server WINDOWS NT NETWORK Windows NT controls access to and functionality of Recreation's network. Ned Anderson manages the network. Ned grants user access privileges after the users' submit written requests describing their position, responsibilities, and access requirements. Network tracking tools have been installed to monitor network activity. Unauthorized access attempts and network violations are written to an audit log. Dan Drake reviews the log whenever abnormal activity is suspected. All unauthorized attempts are researched and appropriate action is taken as necessary. Case No. 2000-02: Recreation, Inc. 8 AICPA Case Development Program Recreation, Inc. personnel may access the network in various ways. Local users, who are those accessing the network from their respective offices in Tampa, St. Pete, Miami, Atlanta, Columbia, and Birmingham, are simply presented with a network logon screen whenever their computer is turned on. A user must provide a valid User ID and password in order to gain network access. Employees are assigned unique User IDs consisting of their last name and first initial. For example, Dan Drake's ID is "draked." The network is configured to enforce certain password parameters, as follows: Parameter Recreation Network Setting Minimum password length 4 characters Character representation Alpha-numeric (passwords must contain both letters and numbers) Expiration period 365 days (passwords must be changed every 365 days) Once a user provides a correct User ID and password combination, the network authenticates, or verifies, the identity of the user. After authentication, the network grants users access to network functions to which they have been authorized, including applications, files, data, printers, etc. While most Recreation users access the network from their offices, there are times when they may need to gain access to Recreation's network from a remote location. In these circumstances, users must go through the Internet to establish a connection to the network. Remote users first dial a designated phone number to the network server from their remote computer. Once a connection is made, the users are not yet presented with the network login screen. Rather, they are required to authenticate to a firewall3, which, like the network itself, requires a valid User ID and password. The User ID and password are the same combination as the users' network authentication combination. Once the firewall authenticates the users, they are presented with the network login screen as previously described. All Recreation employees have access to the Internet. Internet usage is deemed a necessary part of conducting business. Marketing personnel research market trends and monitor competitors' websites for promotions, new products and features. Finance executives monitor financial markets for interest rate fluctuations, market performance, and trend indicators. Human Resources personnel use the Internet to post job openings, to review on-line resume repositories, and research salaries to ensure they offer competitive employment opportunities. Since Recreation's policy is to enable all employees to "surf the web," they do not feel that a special policy should be implemented regarding Internet usage that might restrict certain users from accessing web-based information. 3 A firewall represents a type of computer software designed to protect computer systems from unauthorized intrusions when connected via telephone lines or the Internet. Firewall software is a necessary precaution when connecting a business computer network to the telephone system or the Internet via a dedicated router, or any other system designed to control traffic between corporate networks and the public. Firewalls can restrict certain types of traffic and can log all network accesses. Part II: Case Questions 1. What are the inherent risks associated with Recreation's network configuration? Inherent risks are defined as the susceptibility of account balances to unintentional material misstatements before considering the effectiveness of the related internal control structure (SAS 47). Inherent risks are present regardless of the industry in which the company conducts business, the size or nature of the organization, or the type of processing performed; however, their precise nature and magnitude can vary from across companies. 2. Ideally, what controls would you recommend to mitigate each inherent risk just identified? Inherent risks are mitigated when one or more controls reduce the risks to acceptable levels. Controls may be manual, computerized, or a combination of both. It is important to recognize that, in many cases, inherent risks can not be totally eliminated; however, they can be lowered to tolerable levels via internal controls. Please categorize your responses into preventive and detective controls. Preventive controls are designed to ensure compliance with prescribed or desired events and behaviors, such as passwords and edit checks (e.g., ensuring that the customer number entered on a sales order matches a valid customer). Detective controls are designed to identify and expose undesirable events and behaviors that slip through the preventive controls, such as audit logs and comparison of inputs (e.g., cash receipts) to bank deposits. Thus, preventive and detective controls are applied before and after the fact, respectively. Case No. 2000-02: Recreation, Inc. 9 AICPA Case Development Program 3. What are the control risks associated with Recreation's network configuration? Control risk is defined as the risk that the client's controls will fail to prevent or detect material misstatements (SAS 55 & 78). When answering this question, match Recreation's existing controls with the corresponding inherent risks identified above, then assess the remaining control risk, also known as residual or net risk (gross inherent risk minus risk mitigated by existing controls). 4. How would you test the effectiveness of existing controls surrounding Recreation's network configuration? Internal controls can be tested in various ways, such as direct observation, interviews, and audit trails. Please list each existing control identified above and describe the ways in which you might test each control. 5. What specific control changes and improvements would you recommend to manage the residual risk associated with Recreation's network configuration? When answering this question, first identify the 'high residual risk' areas, then determine how you would augment existing controls and/or implement new controls such that residual risk is lowered to acceptable levels. Part III: Database In this section of the case, the second layer in the IT infrastructure is examined. Specifically, you will learn about database software and how such software fits into an organization's overall IT environment. Applications Database DATABASE A database is a repository wherein all of the organization's data is stored in an orderly fashion. The data itself is stored in numerous tables, which are organNetwork/ Operating System ized according to a predefined structure that describes the relationship among tables. An example of the relationship among tables is shown in Figure 4 (below). As shown, the 'customer' table is related to the 'sales order' table, which is related to the 'product ' table. Notice that one table is related to another via a common attribute. For instance, the customer table contains an attribute called 'customer number'. When, say, customer '0098' submits a sales order, this number is also recorded in the sales order table. If the customer orders product number '23454' then this product number is recorded in both the sales order and product tables. In this manner, tables are 'related' to each other via common attributes, which are typically expressed in numeric form. Figure 4: Example of Database Relationships Customer Table Customer Number Customer Last Name Customer First Name Customer Phone Number Customer Address Sales Order Table Sales Order Number Customer Number Product Number Quantity Ordered Product Table Product Number Product Name Product Unit Price AICPA Case Development Program Case No. 2000-02: Recreation, Inc. 10 It is the 'relationship' aspect of databases that has given rise the most common type of database structure called a relational database. There are many benefits ascribed to a relational database. For instance, a relational database improves the reliability of organizational information because each data item, say a customer name, is stored only once in the database. This way, data items are not duplicated across departments, say marketing and accounting, which eliminates any chance that the data item is incorrect in one or more of the duplicate locations. Another benefit of relational databases is that the data is independent from the applications. As such, changes can be made to applications and new applications can be brought on-line without impacting existing data structure. Conversely, changes to the data structure can be made without affecting existing applications. While a database can reside on a single computer, it is most effective when placed in a network environment, where authorized users throughout the entire organization can access the database. There is a suite of database tools (software) associated with a relational database. For example, one database tool is designed to optimize the performance of the database; that is, the software is designed to 'tune' the database to run at an optimal speed. A database security tool is used to restrict access to specific data items and database tables to authorized users. A 'query' tool is available whereby authorized users can directly access database tables and perform ad hoc queries. For instance, a user can query the database to find out how many widgets have been sold in region A from January through May, or the dollar amount of outstanding accounts receivable from customers over the age of 50 in city B, and so on. While the database tools are very useful, they also pose a security problem because if unauthorized individuals gain access to the tools, they can bypass the applications and directly view confidential information, change data values in the database, alter the database structure, and so on. In most organizations, a database administrator (DBA) manages the company's database. This does not mean that the DBA is responsible for the data values contained in the database; rather, the DBA manages the data dictionary (which describes the configuration of each data item and table), database structure (which defines the relationships among tables), database performance, and database security. As such, the DBA has access to the entire suite of database tools. Responsibility for data itself is assigned to data owners, who are specific individuals within each functional unit of the organization to which the data applies. For example, the Accounting Director is the 'owner' of accounting data and the Sales Manager 'owns' the sales data. In larger organizations, data ownership may be further delegated, e.g., the Payroll Manager might own the payroll data and the Accounts Payable manager might own the accounts payable data. Data owners are responsible for maintaining the integrity of the data that falls within their span of control. Dan Drake has been designated as Recreation's DBA. Nick Smith (Network Support) is the data owner for all data retained in the database. If users have inquiries regarding data values, they must submit requests describing the purpose of the inquiry and the applicable data. Otherwise, Nick performs independent data integrity checks on a periodic basis to ensure that data is processed properly. Recreation, Inc. stores all organizational data in a database application developed by Oracle, Inc. Oracle is just one of a number of database products; some other products are MS Sequel Server, Sybase, and IBM DB2. Recreation's Oracle database contains a repository of financial, customer, inventory, manufacturing, and service agreement information. The information contained in the Oracle database is accessed primarily by Peoplesoft applications, which are described in Part IV of the case. However, other software vendors have developed database tools that can link to the Oracle database, such as Microsoft Access and SQL Plus. As such, these tools can be used to gain access to data contained in Oracle tables. In many cases, these tools are handy, as they are used to access data that is not referenced by the Peoplesoft applications. Each Oracle table, and the data contained therein, can be restricted from users. For instance, the Payroll Administrator might be assigned 'read-write' access to the payroll table while payroll clerks may be restricted to 'read-only' access. This security feature is especially important when considering proper segregation of incompatible functions. For example, a payroll clerk who has been granted 'update' and 'add' privileges to the payroll register table should not be able to generate paychecks. If this were to happen, the payroll clerk could then produce erroneous paychecks, e.g., by modifying his/her hours worked, adding a new timecard for a friend, adding a fictitious employee with a post office box address to which the payroll clerk possesses the key, and so on. This type of security represents a logical separation of duties. Manual separation of duties is also important, such as restricting the payroll clerk from possessing blank payroll checks. Case No. 2000-02: Recreation, Inc. 11 AICPA Case Development Program Recreation's IT department has determined that data access has been appropriately restricted via security tools built into the Peoplesoft applications. For example, the Peoplesoft general ledger application requires an authorized password and ID before allowing access to general ledger data. Accordingly, additional data restrictions within the Oracle database are not imposed, as they would be redundant, inefficient, and unnecessary. Part III: Case Questions 1. What are the inherent risks associated with Recreation's Oracle database? Inherent risks are defined as the susceptibility of account balances to unintentional material misstatements before considering the effectiveness of the related internal control structure (SAS 47). Inherent risks are present regardless of the industry in which the company conducts business, the size or nature of the organization, or the type of processing performed; however, their precise nature and magnitude can vary from across companies. 2. Ideally, what controls would you recommend to mitigate each inherent risk just identified? Inherent risks are mitigated when one or more controls reduce the risks to acceptable levels. Controls may be manual, computerized, or a combination of both. It is important to recognize that, in many cases, inherent risks can not be totally eliminated; however, they can be lowered to tolerable levels via internal controls. Please categorize your responses into preventive and detective controls. Preventive controls are designed to ensure compliance with prescribed or desired events and behaviors, such as passwords and edit checks (e.g., ensuring that the customer number entered on a sales order matches a valid customer). Detective controls are designed to identify and expose undesirable events and behaviors that slip through the preventive controls, such as audit logs and comparison of inputs (e.g., cash receipts) to bank deposits. Thus, preventive and detective controls are applied before and after the fact, respectively. 3. What are the control risks associated with Recreation's Oracle Database? Control risk is defined as the risk that the client's controls will fail to prevent or detect material misstatements (SAS 55 & 78). When answering this question, match Recreation's existing controls with the corresponding inherent risks identified above, then assess the remaining control risk, also known as residual or net risk (gross inherent risk minus risk mitigated by existing controls). 4. How would you test the effectiveness of existing controls surrounding Recreation's Oracle database? Internal controls can be tested in various ways, such as direct observation, interviews, and audit trails. Please list each existing control identified above and describe the ways in which you might test each control. 5. What specific control changes and improvements would you recommend to manage the residual risk associated with Recreation's Oracle database? When answering this question, first identify the 'high residual risk' areas, then determine how you would augment existing controls and/or implement new controls such that residual risk is lowered to acceptable levels. Case No. 2000-02: Recreation, Inc. 12 AICPA Case Development Program Part IV: Applications APPLICATIONS Software applications are the most common 'portal' through which users enter, store, and retrieve organizational information. As such, applications represent the 'front-end' tools4 that enable users to manage enterprise data. Software applications can range from simple to complex. Applications may be used by a single Applications user in stand-alone computer or shared by multiple users in a network environment. Software applications can be purchased and installed 'out of the box', purchased and then modified to meet the specific needs of the company, or writDatabase ten entirely in-house by developers and programmers. Commonly used generic software applications are word processors (e.g., Word and WordPerfect) and spreadsheets (e.g., Excel and Lotus). Network/ Operating In the business world, there are many vendors of financial and accounting System software, such as Great Plains, Macola, MAS 90, Peachtree, and QuickBooks. Most financial and accounting software is packaged with a suite of typical applications, e.g., general ledger, accounts payable, accounts receivable, inventory, and so on. Some financial and accounting software packages are designed to store data in their own pre-defined file structures, while other packages are developed such that they integrate with a relational database software, such as Oracle, MS Sequel Server, Sybase, and IBM DB2. During the 1990's, expanded suites of business software applications became popular, known as enterprise resource planning (ERP) systems. ERP systems are designed to integrate the entire enterprise's data into a single database. In this manner, authorized users throughout the organization can access relevant decision-making information across all functional areas. The applications built into ERP systems cover the entire spectrum of business functions, e.g., accounting, customer relationship management, distribution, marketing, sales, warehousing, etc. Some of the more prominent ERP vendors are SAP, Oracle, Peoplesoft, Baan, and J. D. Edwards. All ERP systems are designed to integrate with relational database software. For instance, the ERP system offered by SAP can interface with several databases, such as Oracle and MS Sequel Server. Oracle's ERP suite is not to be confused with Oracle's database application. That is, Oracle's ERP and Database are two different, yet related product lines. Recreation, Inc. has implemented the ERP system offered by Peoplesoft. The extensive suite of Peoplesoft applications used by Recreation is reflected in Figure 5 (next page). Recreation, Inc. is comprised of seven (7) functional areas, which are further subdivided into 29 applications. As shown, Recreation is extensively integrated within and across functional areas. 4 The database software tools described in Part III are often referred to as 'back-end' tools. Case No. 2000-02: Recreation, Inc. 13 AICPA Case Development Program Figure 5: Peoplesoft Applications Used by Recreation, Inc. Functional Areas Peoplesoft Applications Customer Relationship Management Prospecting Selling Servicing Retaining Technical Service R&D Quality Management Operational Analysis Product Launch Plant Engineering Facilities Management Maintenance Planning Maintenance Execution Regulatory Compliance Sales & Services Execution Sales Force Automation Order Management Technical Service Customer Service Operations Production Planning Manufacturing Quality Management Process Controll Distributions Logistics Planning Execution Compliance Storage & Warehouse Management Accounting Procurement Human Resource Management Finance Business Support Case No. 2000-02: Recreation, Inc. 14 AICPA Case Development Program PEOPLESOFT APPLICATION MAINTENANCE Carol Olson (Computer Operations) is responsible for the functional maintenance of Peoplesoft, including periodic upgrades and troubleshooting. Upgrades for the Peoplesoft applications are released twice each year and the upgrades must be installed as part of Peoplesoft's customer service agreement. Because Carol has been so busy lately, she is behind by two upgrades. Meaning, she has not installed the Peoplesoft upgrades for a year. However, the current applications are running just fine, so Carol feels no urgency in implementing the upgrades. It takes Carol a considerable amount of time to install upgrades because Recreation, Inc. has made many functional modifications to the Peoplesoft applications over the years to accommodate its specific information processing and reporting needs. Modifying ERP applications to fit a given company's circumstance is not an uncommon practice; however, it does mean that upgrades are more difficult and time consuming. Thus, each time an upgrade is performed, Carol must involve the programmer (Patty Porter) to ensure that the functional modifications are properly inserted into the applicable applications. Then, Carol, Patty, and Tom Tyler (Testing & Support) must check each application before implementing it into production. INFORMATION SECURITY Peoplesoft accesses data stored in the Oracle database tables. As stated previously in the database discussion, Recreation, Inc. relies on the security application built into Peoplesoft to protect access to company data. This is accomplished by assigning users to pre-defined classes. Within a given class, user privileges are restricted to certain data, tables, and database tools. When users log on to Peoplesoft, they must enter valid passwords and IDs, which in turn assigns them to their designated class. Tom Tyler (Testing and Support) manages the user list and class definitions. FINANCE AND ACCOUNTING APPLICATIONS Peoplesoft's financial and accounting applications are primarily used in Recreation's corporate office. In particular, Dan Drake is the primary user. Although Peoplesoft applications are designed such that multiple users can access data in a single database, each Recreation, Inc. location maintains its own separate Peoplesoft database for financial information. All non-financial information, such as customer relationship management, technical service, plant engineering, etc., is maintained in the corporate database and is shared by all functional areas and locations. When corporate accounting requires consolidated financial information, an ad hoc query retrieves financial information from the individual locations and consolidates the information into the corporate financials. Other than corporate accounting personnel, only accounting and sales personnel at each location have access to their respective Peoplesoft financial databases. Part IV: Case Questions 1. What are the inherent risks associated with Recreation's Peoplesoft applications? Inherent risks are defined as the susceptibility of account balances to unintentional material misstatements before considering the effectiveness of the related internal control structure (SAS 47). Inherent risks are present regardless of the industry in which the company conducts business, the size or nature of the organization, or the type of processing performed; however, their precise nature and magnitude can vary from across companies. 2. Ideally, what controls would you recommend to mitigate each inherent risk just identified? Inherent risks are mitigated when one or more controls reduce the risks to acceptable levels. Controls may be manual, computerized, or a combination of both. It is important to recognize that, in many cases, inherent risks can not be totally eliminated; however, they can be lowered to tolerable levels via internal controls. Please categorize your responses into preventive and detective controls. Preventive controls are designed to ensure compliance with prescribed or desired events and behaviors, such as passwords and edit checks (e.g., ensuring that the customer number entered on a sales order matches a valid customer). Detective controls are designed to identify and expose undesirable events and behaviors that slip through the preventive controls, such as audit logs and comparison of inputs (e.g., cash receipts) to bank deposits. Thus, preventive and detective controls are applied before and after the fact, respectively. Case No. 2000-02: Recreation, Inc. 15 AICPA Case Development Program 3. What are the control risks associated with Recreation's Peoplesoft applications? Control risk is defined as the risk that the client's controls will fail to prevent or detect material misstatements (SAS 55 & 78). When answering this question, match Recreation's existing controls with the corresponding inherent risks identified above, then assess the remaining control risk, also known as residual or net risk (gross inherent risk minus risk mitigated by existing controls). 4. How would you test the effectiveness of existing controls surrounding Recreation's Peoplesoft applications? Internal controls can be tested in various ways, such as direct observation, interviews, and audit trails. Please list each existing control identified above and describe the ways in which you might test each control. 5. What specific control changes and improvements would you recommend to manage the residual risk associated with Recreation's Peoplesoft applications? When answering this question, first identify the 'high residual risk' areas, then determine how you would augment existing controls and/or implement new controls such that residual risk is lowered to acceptable levels. Appendix A Glossary of Computer and Network Terms Used Throughout the Case AccessThe privilege or ability granted to a user to retrieve computer information, gain entry into specific software applications, and/or utilize computer hardware and communication devices. Various access levels may be granted based on user need. Common access levels for data include read-only (a.k.a. inquiry), read-write (a.k.a. update, modify), delete, execute, and save. Audit LogA list of recorded, historical activity. Audit logs may be generated for nearly any type of computer activity, including application, database, network, and operating system activity. Audit logs are used to analyze historical computer activity and to determine which users performed which functions at a given time. AuthenticationThe process of identifying a user, typically based on a user-name and password combination. Authentication merely ensures that the individual is who he or she claims to be, but says nothing about the access rights of the individual. Authorizationthe process of granting access rights to system objects (e.g., files, computers, networks, etc.) based on the user's identity. AvailabilityEnsuring that important information resources, such as computers, applications, networks, and data files are accessible when needed. Business Continuity PlanA plan designed to ensure that key business processes are not interrupted for a relatively lengthy period of time due to a computeretwork system failure. Such a plan typically defines hardware and software needs, alternative processing sites (hot-sites/cold sites), required resources for short-term processing, and telecommunications re-routing plans for on-line processing. Related to a business continuity plan, a disaster recovery plan defines the specific actions to be taken in the event of a computeretwork system interruption due to natural disaster or malicious destruction. ClientThe client part of a client-server architecture. Typically, a client is an application that runs on a personal computer or workstation and relies on a server to perform some of the more CPU intensive operations. DataDistinct pieces of information, usually formatted in a specific manner. Data that has been transformed into something meaningful is referred to as information. AICPA Case Development Program Case No. 2000-02: Recreation, Inc. 16 FirewallHardware and/or software systems designed to prevent unauthorized access into or out of a private network. Firewalls are frequently used to prevent unauthorized Internet users from accessing private networks connected to the Internet, especially intranets. All messages entering or leaving the intranet must pass through the firewall, which examines each message and blocks those that do not meet the specified security criteria. InfrastructureThe hardware and communication components that make up a computer systemetwork. IntegrityThe quality and accuracy of data. Loss of integrity in the management of the information system infrastructure may result in unauthorized access to data, irrelevant data, and/or the untimely delivery of data. Additionally, loss of integrity in the application systems that support the organization's business processes may result in unauthorized, incomplete, or inaccurate processing of transactions. IT StrategyA long-range Information technology plan that defines future enhancements and developments in the IT environment, such as human resources, software, hardware and communication systems. NodeIn a network, a node is a specific processing location. A node can be a computer or some other peripheral hardware device, such as a printer, fax, or scanner. Every node has a unique network address, sometimes called a Data Link Control address or Media Access Control address. Object CodeObject code arises from a computer process known as 'compiling', where human readable code (see Source Code below) is transformed into machine readable code. The purpose of compiling source code into object code is to gain processing speed, as computers can process object code much faster than source code. Unfortunately, humans can not read object code, so they must program in source code. PrivilegesThe ability to access and use computers, networks, and information in some pre-specified manner. Production EnvironmentA special location within a computer that houses all applications that are used for daily business operations. In other words, all programs that are actually used to process company information are stored in the production environment, a.k.a. the production library. RelevanceThe extent to which information created or summarized by an application system is useful in making business, investment, and strategic decisions. Router - A device that connects any number of LANs. Routers use headers and a forwarding table to determine where packets of electronic information go. Server - A computer or device on a network that manages network resources. For example, a file server is a computer and storage device dedicated to storing files. Any authorized user on the network can store files on the server. A print server is a computer that manages one or more printers, and a network server manages network traffic. A database server is a computer system that processes database queries. Source CodeHuman readable programming code, e.g., COBOL, C++, HTML and Java, used by programmers to develop computer applications. Case No. 2000-02: Recreation, Inc. 17 AICPA Case Development Program Appendix B Suggested Background Readings Hayes, D. C. and Hunton, J. E., Working With Databases, Journal of Accountancy, Volume 189 (5), 2000: 70-79. Hayes, D. C. and Hunton, J. E., Building a Database From Scratch, Journal of Accountancy, Volume 188 (5), 1999: 63-73. Hayes, D. C. and Hunton, J. E., What You Better Know About Databases, Journal of Accountancy, Volume 187 (1), 1999: 61-65. Kimbrough, R. B., Jr., High-Tech Audits of LANS, IS Audit & Control Journal, Volume III, 1998: 50-53. Lilly, R. S., Client/Server Database Security, IS Audit & Control Journal, Volume VI, 1997: 50-51. Manello, C. and Rocholl, W., Security Evaluation: A Methodology for Risk Assessment, IS Audit & Control Journal, Volume VI, 1997: 42-46. Pfenning, R. S., An Introduction to Auditing SAP, IS Audit & Control Journal, Volume II, 1999: 43-45. Santos, R. A., Internet Security, IS Audit & Control Journal, Volume I, 1999: 33-37. Turner, T., Implementing and Enhancing Data Security in a PeopleSoft Environment, IS Audit & Control Journal, Volume III, 1999: 51-52. Wilson, C., Using Oracle Tools to Audit Oracle Logical Security, IS Audit & Control Journal, Volume IV, 1998: 15-22