Auditing the Expenditure Cycle – Weaknesses in the Payroll System

A 2012 study by the Association of Fraud Examiners revealed that 11 percent of workplace frauds involve payroll. The average cost of this type of fraud was $48,000. On average these types of schemes avoid detection for 36 months.

The creation of false, or “ghost”, employees is one of the most common forms of payroll fraud. Fictitious hours are submitted in the name of the “ghost” employee for work not performed. (Chris Bradford n.d.)

Responsibility for entering payroll data and processing paychecks should be divided between human resources and the accounting department to make sure that multiple people are needed to complete the payroll cycle. This segregation of duties helps prevent possible abuses, such as the creation of “ghost” employees, diverting money to personal accounts, or modifying vacation hours. (Nestor-Harper n.d.)

In most companies, the personnel department uses personnel action forms to designate employees receiving pay checks, what their salary is, job classification and any payroll deductions. Supervisors are not responsible for managing this information because of the potential for abuse. (Hall 2011)

Many organizations use time cards to track the hours that an employee is at work. Or they may ask each employee to enter a unique code into the system to clock in or clock out. Both of these methods are susceptible to potential fraud. One employee could clock in using another employee’s Time Card or code. Reasons for doing this may include covering for a late or absent employee. One method of discouraging this behavior is for supervisors to observe the practice of clocking in.

A payroll preview report should be generated and reviewed before paychecks are printed, or pay transferred into direct deposit accounts. This review should be done by someone other than the person or department responsible for payment processing. This review would look for terminated employees who shouldn’t receive paychecks, inordinate hours worked, or invalid vacation dates posted. (Nestor-Harper n.d.)

 

Bibliography

Chris Bradford. How Can Internal Control Overcome Payroll Fraud? n.d. http://smallbusiness.chron.com/can-internal-control-overcome-payroll-fraud-56266.html (accessed March 29th, 2014).

Hall, James A. “Chapter 9: Auditing the Revenue Cycle.” In Information Technology Auditing and Assurance, Third Edition, by James A. Hall, 647. Mason, Ohio: South-Western Cengage Learning, 2011.

Nestor-Harper, Mary. The Internal Control Weaknesses of a Payroll System. n.d. http://smallbusiness.chron.com/internal-control-weaknesses-payroll-system-15312.html (accessed March 29th, 2014).

Thoughts on Access Control

There are several layers of Access Control that need to be audited. These are some of the questions that need to be asked when auditing access.

Physical: 

  • Which groups or individuals have access to sensitive locations or equipment in the organization being audited?

Operating System/Network Access to network resources: 

  • Who has access to file shares or network resources on the network?  I.E. does the Marketing Department really need access to the Sales Team’s reports folder?
  • What kinds of privileges do individuals or groups have in these shares – Read, Write, Execute?
  • Are these privileges appropriate?

Access to Enterprise Resource Planning systems:

  • Which groups or individuals have access to each ERP module?
  • Is this access appropriate?

Getting Started with ACL 9

One of the objectives of this week’s class was to begin familiarizing students with ACL (Audit Command Language) software. The textbook for this class, Information Technology Auditing, 3rdEdition, includes a CD containing the ACL 9 Desktop Education Edition software. After the ACL 9 software has been installed, you can view the ACL Getting Started guide, located in C:\ACL Data\Sample Data Files\ACLStart.pdf.

You can also download ACL 9 tutorials from the publisher’s website, http://www.cengage.com. Click on the “Free Resources” button, setup an account, provide the Cengage website with the 13 digit ISBN number of the text book, and you’ll be able to download three ACL 9 software tutorials, in the form of zipped MS Word documents. (Hall 2011)

Loading the demonstration project for the tutorials is easy:

1. Click on the “ACL Desktop Education Edition” icon.

1 - ACL Icon

1 – ACL Icon

@2014 Image created by Harold Kay

2. ACL 9 opens. Click the “Open an existing Project” link.

2. Open ACL

2. Open ACL

@2014 Image created by Harold Kay

3. The Project dialog opens. Select ACL_Demo.acl

4. Click the “Open Button.

3. Select ACL Demo

3. Select ACL Demo

@2014 Image created by Harold Kay

5. The ACL_Demo.acl project opens. This post will now demonstrate some very simple functionality in ACL 9. Open the ACL_Demo.ACL project, using the instructions above.

6. In the left hand “Project Navigator” pane, select Tables|Metaphor_Trans_2002.

4. Select Table

4. Select Table

@2014 Image created by Harold Kay

7. Click the “Analyze” menu.

8. Select “Look for Gaps”

5. Select Gaps

5. Select Gaps

@2014 Image created by Harold Kay

9. The “Gaps” dialog box opens. Click on Invoice to search for gaps in Invoice Numbers.

10. Select “List Gap Ranges” radio button to view the gap ranges.

11. Click the “Ok” button.

6. Gaps Option

6. Gaps Option

@2014 Image created by Harold Kay

12. The screen now displays the “Gaps Found Between” report

7. Gap Report

7. Gap Report

@2014 Image created by Harold Kay This is just one simple function available to users of ACL. There are many others, and documentation on them can be found in the ACL Getting Started guide.  (ACL Services Ltd 2006)

ACL is a very powerful Computer-Assisted Audit Tools and Techniques (CAATT) application which allows auditors to take data from almost any platform, in any format, and derive meaning and analysis from that data. This post has provided a brief overview about opening the demo ACL project and running a simple Gap report.  (ACL Services LTD 2006)

Bibliography

ACL Services Ltd. “Testing for gaps and duplicates in sequential data.” In ACL Getting Started, 74. Vancouver, BC, Canada: ACL Services Ltd, 2006.

Hall, James A. “Chapter 9: Auditing the Revenue Cycle.” In Information Technology Auditing and Assurance, Third Edition, by James A. Hall, 647. Mason, Ohio: South-Western Cengage Learning, 2011.

 

SQL SELECT Basics. Plus – Run SQL Commands in your Browser!

Chapter 8 of the text book gave a high level overview of relational database data structures and Entity Relationship Diagrams (ERD). The book only briefly discussed several Structured Query Language (SQL) commands, and what they did. When auditing relational databases, a basic understanding of SQL could make it much easier to pull appropriate data from a system in a format that makes it easier to import into a CAATTs Generalized Audit Software. This knowledge could save the auditor time and effort.

In a relational database, data is stored in tables. The rows of these tables are discrete collections of data, or records. The columns of these tables are attributes, or fields that describe the records.

Table Structure

Table Structure

SQL is the language that gives the user access to the data contained in a relational database’s tables. With the proper use of SQL commands, a user can build queries that either SELECT data from database tables, UPDATEs that data, INSERTs new data, or DELETEs it. (Systems 2005) This post will focus on the SELECT command, and will direct readers to a website where SQL commands can be practiced right from the user’s browser, without installing any software. (w3schools.com n.d.)

A basic knowledge of SQL can be very useful if you have to generate a report or export data from a relational database. The SQL SELECT statement has a number of elements, but we are going to focus on the most common:

  • SELECT
  • FROM
  • WHERE
  • ORDER BY

The most basic SELECT query is the statement “SELECT * FROM [Employees];” where Employees is the name of a data table in the database, and the * character is shorthand for “select all fields in this table”. This statement will grab all the columns (field names) of a table, and all the rows of the table (records). (Systems 2005).

The user can be more selective. An example of this would be an Employees table, where the user wants to retrieve data from the EmployeeID, LastName, FirstName and BirthdDate columns, and return only employees who were born after January 1st, 1960, sorted by employee birthdate. We would use the following query:

SELECT EmployeeID, LastName, FirstName, BirthDate
FROM [Employees]
WHERE BirthDate >= ’1960-01-01′
ORDER BY BirthDate;

SELECT Statement - WHERE clause results

SELECT Statement – WHERE clause results

The WHERE command limits the rows returned by the query, based on the criteria supplied in the WHERE command. In this case, only return records where the employee’s birthday is greater than or equal to January 1st, 1960.

The ORDER BY command sorts the rows returned based on the field name used with the ORDER BY command. In this case, we are sorting by Birthdate.

Readers of this post can try the bolded queries included in this post by going directly tohttp://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all, pasting the query into the “SQL Statement” text box, and clicking the “Run SQL” button.

Test out SQL Statements for yourself!

Test out SQL Statements for yourself!

The reader can try a number of other SQL commands, such as INSERT, UPDATE, or DELETE for herself by going to the website http://www.w3schools.com/sql/default.asp, selecting a command from the list on the left hand side, and clicking the “Try it yourself” button. (w3schools.com n.d.)

More and more companies are moving toward relational databases and enterprise resource planning (ERP) systems. Having a basic understanding of SQL can give auditors much finer control over retrieving and extracting the data to import into their CAATTs.

 

Bibliography

SQL SELECT Statement. n.d. http://www.w3schools.com/sql/sql_select.asp (accessed March 17, 2014).

Systems, Jackie Goldstein of Renaissance Computer. http://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx.November 01, 2005. http://technet.microsoft.com/en-us/library/bb264565(v=sql.90).aspx (accessed March 17, 2014).

 

 

Using SSIS ETL Methodology for CAATs and Data Mining

I’d like to mention that Microsoft SQL Server has extract-transform-load (ETL) capabilities built into it called SQL Server Integration Services (SSIS).  SSIS uses a Graphical User Interface very similar to what you might see when building applications with Visual Basic or VB .NET.

With SSIS, you can extract data from almost every kind of data source: text files, CSV files, MS Excel spreadsheets, database tables using ODBC or OLEDB connectors, etc.
SSIS has a number of tools for transforming that data into a usable format. Do you need to filter certain data out, or concatenate two fields together, or create a calculated field based on several other fields? It can do that.
Finally, it can load data into almost every kind of format: text files, CSV files, MS Excel spreadsheets, database table using ODBC or OLEDB connectors, etc.
Since SSIS is a part of SQL Server, you can use SQL Server’s Scheduling service to schedule SSIS jobs. I use SSIS to initiate SFTP transfers of data files to and from several of our vendor’s SFTP sites on a nightly basis. It’s a great tool that’s available for the price of a SQL Server installation. I really like working with SSIS.
Just for clarity, this service used to be called Data Transformation Services (DTS) from SQL Server 7.0 through SQL Server 2000.
Anyway, my point about SSIS is that it is a powerfull, moderately inexpensive solution and is fairly easy to use. It may allow you to take your company’s data and transform it into a format that could be used by a CAATT.

Formats for Importing Data into a CAAT

One of the challenges involving the use of Computer Aided Audit Tools and Techniques (CAATs) when testing application controls is the transfer of information from the production system being audited into the auditor’s CAAT of choice.

There are a large number of data formats that could be used, depending on the DBMS (database management system), application software, or platform of the system being audited. The extracted data file could be a delimited text file, an XML file, an MS Excel spreadsheet, a PDF file, a dBase .dbf file, or even a connection to the database itself via ODBC (Open Database Connectivity).

In the article, the author ranks Data File formats by how efficient they are for importing data into a CAAT. His ranking, from most efficient to least efficient is:

  • dBase dbf file.
  • Adobe PDF File exported from system (not a scanned image).
  • Microsoft Excel XLS or XLSX file.
  • Delimited text file, such as CSV or TXT file.
  • XML file.
  • Miscellaneous others. (Tommie W. Singleton 2010)

I don’t agree with his ranking – I think that trying to extract data from a PDF file is more difficult and challenging than using almost any of the other methods.

There are limits to how many rows of data an MS Excel file can utilize. In the older XLS format, there is a 65,536 row limit (Microsoft Corporation 2012). In the newer XLSX file format, the limit is 1,048,576 rows. (Microsoft Corporation n.d.)

XML has become more common as a data extaction and communication tool. However, XML data use data dictionaries, and the auditor may not have access to the audited system’s data dictionary.

In the article, the author states that the “ideal format” is typically a flat file composed of rows of data. The first row contains column heading names, while subsequent rows contain the data.

CAAT Flat File

CAAT Flat File

After the data has been imported into the CAAT, it’s important to verify that it is exactly the same as the data in the operational system. One method of verification is similar to the batch transmittal sheet method. This involves using metrics about the data itself, such as the number of data records, summing total dollar amount or quantity columns or other similar kinds of data.

The author concludes that CAATs provide an effective and efficient method for meeting the goals of the audit. However, the most difficult step of the process may be putting the data into a format that can be used by the CAAT. (Tommie W. Singleton 2010)

Bibliography

Microsoft Corporation. Excel specifications and limits. n.d. http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx (accessed 03 09, 2014).

—. Text files that are larger than 65,536 rows cannot be imported to Excel 97, Excel 2000, Excel 2002 and Excel 2003. December 19, 2012. http://support.microsoft.com/kb/120596 (accessed March 09, 2014).

Tommie W. Singleton, Ph.D., CISA, CGEIT, CITP, CMA, CPA. Data Extraction, A Hindrance to Using CAATs . June 01, 2010. http://www.isaca.org/Journal/Past-Issues/2010/Volume-6/Pages/Data-Extraction-A-Hindrance-to-Using-CAATs.aspx (accessed 03 09, 2014).

 

Cloud Service Definitions

The beauty of cloud based systems is that things like security, software updates, system upgrades and data backups can now be handled offsite by organizations that have core competencies in these areas.
The amount of support the cloud provider gives your organization depends on on the type of cloud service provider they are, Software as a Service(SaaS), Platform as a Service (PaaS), Infrastructure as a Service (IaaS).
Software as a Service (SaaS): The customer accesses applications controlled by the Cloud Provider. These applications run within a cloud infrastructure. The customer does not control application capabilities, servers, operating systems, storage, network or underlying cloud infrastructure. In this model, the customer has the least amount of flexibility and control of the cloud environment. On the other hand, security is integrated at a high level because the cloud service provider is responsible for everything. Salesforce.com would be a good example of this SPI Model.
Platform as a Service (PaaS): The Cloud Provider grants customers the ability to deploy customer created applications or purchased applications onto the cloud. The customer does not control servers, operating systems, storage, network or underlying cloud infrastructure. However, the customer can deploy their own applications. This model gives the customer more control over their cloud platform than the SaaS model. There are fewer pre-built features for the customer. The customer can add additional layers of security.
Infrastructure as a Service (IaaS): The customer can deploy software, including applications and operating systems. The customer has control over operating systems, storage, and applications. The customer does not have control of the underlying cloud infrastructure. The customer has a great deal of latitude about how the operating system and applications are configured. Because of this, the customer bears a much greater share of responsibility for managing the security of their platform. Amazon’s AWS EC2 offering is an example of this model.
Whatever cloud based model your organization uses, the IT team can focus on supporting core aspects of your business.
The downside to cloud based ERP systems is that you are relying on another organization to guard your data. If an organization is planning on moving to a cloud based system, it’s very important that Service Level Agreements and assurances are worked out ahead of time.

ERP Implementation: Big Bang versus Phased Implementation

We discuss Enterprise Resource Planning Systems (ERP), and strategies for implementing them. Implementation can be broken down into two major strategies.

The Big Bang strategy focuses on switching from the legacy system to the new ERP system in one shot, or “Big Bang”. This means switching from the old to the new system across all departments, and at all levels, all at once.

There are a number of advantages to the Big Bang strategy. It tends to have a lower implementation cost because implementation occurs over a shorter time frame. The number of issues that inevitably crop up tend to occur during this shorter period of time. If everything goes according to plan, systems implemented under the Big Bang strategy are up and running on the first day, and legacy systems do not need to run simultaneously with the new system.

There are also a number of potential disadvantages to the Big Bang strategy. Because a Big Bang implementation occurs at all levels of the organization and across departments, there is a higher chance of technical problems, which makes implementation riskier. There is also a near term impact on productivity because there is a shorter time to o train employees on the new system. (International Business Systems 2013)

 

Big Bang Theory

Big Bang Theory

The Phased Implementation strategy focuses on phasing out portions of the legacy system and phasing in the new system in stages, or phases, instead of replacing the entire system all at once.

There are a number of advantages to implementing the Phased Implementation strategy. There is a lower risk to implement this strategy because only a portion of the system is being replaced at any given time, instead of the entire system. If there is a problem, there is more time to resolve the issues and test. There is also more time to train employees.

There are also a number of potential disadvantages to the Phased Implementation strategy.

It takes longer to implement because portions of the system are being replaced. This means that the company has to temporarily connect the old system to the new system as the new system is being phased in. It is also possible that there will be disruptions to inter-departmental business processes during this process. (International Business Systems 2013)

 

Phased Implementation

Phased Implementation

The article notes that neither strategy is better than the other. Whether an organization uses the Big Bang strategy or the Phased Implementation strategy depends on the business requirements of the organization itself, the scale of the organization, IT infrastructure and preparedness.

Bibliography

International Business Systems. How to Implement ERP for Your Business: All at Once or In Phases? August 02, 2013. http://globial.com/globialtalksbusiness/how-to-implement-erp-for-your-business-all-at-once-or-in-phases/ (accessed March 02, 2014).

 

Scrooge McDuck vs. the Consultants

Several years ago I was involved in the process of migrating the U.S. branch of my company from an ancient Pick D3 system to the much more modern SAP Business One, which is an ERP system for smaller businesses.

The Australian branch of our company brought in a team of consultants and let them drive their conversion. Because the Australians let the consultants move the process forward, that side of the company spent almost a year haunted by issues related to their “Big Bang” implementation of SAP Business One.

My boss, who I thought could, at times, be incredibly abrasive and nit picky, ended up doing an amazing job of driving the process in the U.S. He had us map out every user’s data needs, worked with me on our data conversion process, and laid out a plan for implementing a hard cutover date to the new system, while allowing us to continue to refer back to the legacy system to validate our business processes.
I believe that the key difference between the two implementations was that the Australian branch relied to much on the consultants to drive the process, whereas my boss rolled up his sleeves and drove the process himself. At the end of the day, consultants are concerned about billable hours and being paid for project completion. They are not nearly as passionate about the data and internal processes as the users will be.
Based on my experience, consultants may initially know the system they are selling better than the customer, but they don’t know your company’s processes, and won’t be as passionate and precise about getting it right the first time. Your companies IT office should be heavily involved in this process from the start and be the ones pushing it forward.

Millions of Account Credentials found in the Wild

In this recent PC World article,
http://www.pcworld.com/article/2102120/360-million-account-credentials-found-in-the-wild-says-security-firm.html, cyber security researchers studying underground black market forums were able to uncover a list of 360 MILLION account credentials, which were probably collected through multiple retail and corporate data breaches.
The company that uncovered this trove of user credentials is Hold Security, LLC. They estimate that that roughly 30% of these credentials, 105 million credentials, contained email addresses and passwords, and may have come from job-search and dating web sites.
The article surmises that while it’s possible that some of these credentials were gathered using malware installed on individual computers, the sheer scale of the data suggests that attackers have changed tactics, are now focusing on acquiring large stores of data from companies instead of individuals.
This underscores how critical security is for companies involved in any kind of electronic commerce, whether they sell physical items, run dating sites, or help people find jobs. It also highlights that the nature of the threat is continuously evolving, and that companies need to use SDLC to continuously monitor and update their security.