Year 26 – 2013 – Payroll

 I haven’t looked at payroll very often; at least not as often as I think I should or would have liked.  Payroll can be a significant cost to an organization – easily representing 50% of a company’s total expenditures in some industries – but senior management seems to think that the controls over payroll are good and therefore it is low risk.   This belief is often transferred to audit even though studies, and the analysis I have performed over the years, have indicated that this may not be the case.  The ACFE Report to the Nations (2016) stated that payroll fraud occurred in 8.5% of the fraudulent disbursement fraud and had a median loss of $90,000.   It also stated that payroll schemes were twice as common in small organizations as in larger organizations.  This may add some credence to the belief that the controls are better in larger organizations but it may be simply that auditors in larger organizations are not looking at payroll; however, larger organization can sometimes have larger frauds.  When I did perform analysis on payroll I typically found errors and occasionally fraud.

As part of an audit at a large US city, I was asked to examine payroll.  The audit objective sought to ensure that the controls contributed to a payroll function that was efficient and effective and that pay was accurate.  I performed a number of common tests to support the audit objective.

In my post for Year 21 – 2008, I described an analysis which looked at the pay rates for different categories of employees.  This same analysis identified two employees who were being paid more than 25% over the pay rate for other employees in the same job category/position.   A second, simple, analysis identified eight missing check numbers.  The manager asked for more information and I replied, “I can’t tell you much more than you have eight checks that were not issued”.  I provided the missing check number and encouraged the manager, and the auditors, to look into the matter.  Missing checks could be checks that were accidentally destroyed when the check were being printed or (my concern) stolen blank checks.  The controls over the blank check stock needed to be reviewed as well as determining the procedures when checks were being printed (what do you do to damaged, misprinted, or otherwise unusable checks?).

Note: to perform the analysis by job category to identify employees being paid more than the usual rate for the category, I ran a Min/Max ratio analysis.  For each job category (rows in the output file), it calculates the Total amount and gives the minimum, maximum and average amount for each job category.  Starting in version 11, ACL provides a checkbox which will includes this information when you Classify or Summarize on a field.  In version 12, the option to include the standard deviation for each row was also added.

Another analysis looked at the length of time it took to get new employees on the payroll.  Using data from the HR system which gave the employee start date, I ran an analysis to determine how long it took before they received their first paycheck.  Management expectations that it would be the next pay period or certainly the second pay period, however the analysis showed that in 31% of the cases, employees did not receive their first pay for more than 28 days (almost four pay periods after their start date).  Drilling down by pay office revealed problem with the HR on-boarding process in two regions which contributed to the late paychecks.

I also did an analysis to determine if employees were being paid before their “start date” or after their “termination date”.  There was no evidence of control weaknesses in these areas.


Lessons-Learned – Similar problems occur all the time.  It is worth looking at what types of controls weakness have occurred elsewhere when planning an audit.  Look at the ACFE and other reports produced by the big accounting firms, perform a simple Internet search, and check the ACL forum to see what others have found. I find the same types of problem are happening in different industries around the world.

Secondly, there is a reason why the standard set of commands were developed by ACL: they are useful.  I have used the basic commands thousands of times to perform useful analysis.  In this case GAPS, a standard ACL command, identified missing checks.  The results of the standard commands can be extremely useful – you need to understand when to use them and, importantly, how to interpret the analysis.

Lastly, even large payroll system can have errors; and when they do they can be even more significant.  I recently learned about a hospital payroll system which was being run on SAP that was overpaying employees (more than $1M in overpayments in a year).  It was a systemic problem tied to interfaces, pay tables, and complex hourly schedules, work days, and numerous employee classifications.  In another case, employees agreed to be on-call during the Australia Day public holiday, and were subsequently recalled for duty.  However, the payroll system did not identify this as a holiday and incorrectly calculated entitlements, resulting in significant underpayments.  These examples highlight the fact that auditors cannot rely on the controls – in fact the Statement on Auditing Standards (SAS) #94 states that substantive testing alone is not sufficient when the data is gathered, processed, and reported via IT systems.  It requires auditors to test the IT controls and recommends the use of analytics to do so.  This includes any IT system, not just payroll.

I have only discussed errors in employee pay, but there are also errors that can impact on income tax.  In Accounting Today Brian Cumberland, a managing director with Alvarez & Marsal Taxand, LLC in Dallas, offer his list of the top ten payroll errors: 1. Classification of Employees as Independent Contractors; 2. Failure to Subject Vendor Payments to Backup Withholding; 3. Failure to Issue Appropriate Tax Forms; 4. Not Including the Fair Market Value of Gift Cards, Prizes and Awards in Employees’ Income; 5. Failing to Timely Deposit Withheld Taxes; 6. Failure to Timely Deposit Withholding Taxes on Vested Restricted Stock and Exercise of Stock Options; 7. Incorrectly Excluding Expense Reimbursements from Reportable Wages; 8. Failure to Include Nonqualified Deferred Compensation in Executives’ Incomes; 9. Not Including the Appropriate Value of Taxable Fringe Benefits in Employees’ Income; and 10. Excluding Travel and Commuting Expense Reimbursements from Employees’ Income. (Source:

Year 25 – 2012 – Vacation Leave and Sick days

 I always jump at the chance to perform analysis in non-financial areas.  Not only does this expand my knowledge of audit risks and different business processes, but it also further demonstrates the flexibility and power of analytics.  Some of the analytics I have perform include areas such as environmental control, HR – staffing, succession planning, transportation, maintenance, IT security, system conversion, control testing and risk.  Normally, there is an element of finance association to the area.  This could be: fine for non-compliance, cost to address inefficient process or outcomes, reputation or public confidence (affecting share price), etc.  However the analysis involves non-financial systems, data and processes – so I enjoy the challenge.

I was asked to take a look at a couple of focused questions that management wanted assistance with – more of a consulting engagement that an audit.  The first was the liability associated with employees carrying forward unused vacation and the second centered on both sick leave taken and the liability from accumulated sick leave.

Every employee was entitles to a certain amount of vacation credits – based on their collective agreement and years of service.  Since there were over 30 different collective agreements, managing the vacation entitlement was complex, particularly if employees changed job categories (and collective agreements).  Most agreements allowed employees to carry over unused vacations credits, but there was a maximum.  For most agreements, the maximum was one year’s worth of vacation.  This could be anywhere from 15 to 45 days depending on years of service.

As a result of some accounting changes management wanted to report the liability associated with the value of the unused vacation (don’t ask me accounting questions.  I took account 101 and “learned” than 1 + 1 could equal 2, but depending on the accounting rules applied, it could also equal 0, or -1.  This did not make any sense and still doesn’t, so I stayed away from accounting.)

The analysis was reasonably straight forward – calculate the unused vacation balance and multiply it by the pay rate.  The HR system already had the vacation balance for each employee, and the pay system had the pay rate.  However, management want to reconfirm the unused balance which made it a bit more complicated.  This required information about the employees, start date, union (for the collective agreement), union start date, and detailed transactions showing vacation days taken.

I calculated the number of years each current employee was in each union and their year of service.  Then for each collective agreement I created an expression that determined the vacation days – based on years of service.  This was multiplied by the pay rate and the liability was totalled.  Three things became evident: first the vacation system had the correct vacation balance in over 98% of the cases – a testament to the manual reviews performed by employees, managers and HR staff; second, there was a huge liability.  With close to 50,000 employees and the average unused vacation balance at 14 days – things add up; third, some employee had balances that were greater than their allowed maximum.  I reported the liability figure to management and made a recommendation concerning the “over the maximum” carry forward.

While the results were interesting and useful to management I also provided a recommendation concerning mandatory vacation for all employees.  This was supported from two perspectives: from an employee health and a fraud perspective, mandatory vacations are a good thing.  Employees, whether they realize it or not, need time of to recharge their batteries.  And many frauds are prevented and detected by forcing employees to take vacations and having another employee perform their duties while they are away.  Additional, it would help to reduce the unused vacation liability.

I think the third point was what sold it to senior management, and after numerous discussions with the unions, management put in place a mandatory vacation requirement.

The second analysis looked at accumulated sick leave.  Employees earned sick leave (similar to earned vacation, it was based on agreements and years of service).  They could use earned sick leave or bank it (no maximum).  The analysis determined that some employee had zero unused balance in their sick leave while other had hundreds of days of unused sick leave.  The liability was presented to senior management and I also highlight some interested facts including a list of employees who regularly took “sick” days on Fridays or Mondays during the summer.  The intent of banking unused sick leave was to provide employees with a cushion in case of a serious illness that kept them off work for weeks.  These employees did not have this cushion.  Other employees never took a sick day – probably came into work and infected everyone else.

I made recommendations regarding management supervision and validation of sick leave, communication strategy around the purpose taking sick days and for allowing carry forward of vacation days.


Lessons-learned: performing analysis in a business area always provides insight.  The obvious insight is related directly to the question you were trying to answer (what is the total liability of the unused vacation?), but often additional insights can also be made.  In this case, there was an increased concern about employee health and welfare and a fraud risk that became evident when we determined the amount of unused vacation that was being carried forward.  It is important to stop and think about was else the analysis is telling you about the business process.  Don’t just look at the numbers (14 days * 50,000 employees = 700,000 Days – QED), think about what it means (employees are not taking vacation) and the associated risk.

Finally, even when asked a focused and relatively simple question by management, the analysis could become tricky.  Also, the request to provide advice to management in one area (what is the liability?) can present an opportunity to provide advice and recommendations in related areas (mandatory vacation leave).  Don’t miss out on this opportunity because you did not try to obtain as much insight as possible from your analysis.

Year 24 – 2011 – Fraud Detection – part 2

Continuing on from last week …..

Figure 1 from the book “Computer –Aided Fraud Prevention and Detection: A Step-by-Step Guide” describes two approaches used to identify fraud risks and control exposures.  The first looks at control weaknesses and assesses how these exposures could be exploited.  The second starts with the key information or data fields and examines who could modify or manipulate these critical pieces of information; and then assesses the controls that should be in place to prevent this from happening.  The essential element of both approaches is examining the business process from the perspective of the fraudster – basically who can do what and why.

Figure 1 – Approaches to identifying fraud risks


The first approach encourages you to think about the risks and possible control weaknesses; and to answer three questions:

  1. Who could benefit from the control weaknesses?
  2. What can they influence, control or affect to permit the fraud to occur?
  3. What would it look like in the data?

By looking at the adequacy and effectiveness of critical controls you can identify the critical opportunities for fraud.

The second approach starts with the key fields and identifies the key controls that should be in place.  You are encouraged to consider the key pieces of information required by the business process; and ask four questions:

  • Who can create, modify or delete this information?
  • Why might they do this?
  • What are the key controls to prevent this from happening?
  • What tests can be performed to see if someone is committing a fraud?

Once you have identified a control weakness or key fields that could be altered in order to commit a fraud, the next step is to examine the actual data.

There are two types of symptoms of fraud that may occur in the data known and unknown.  The ideal situation is one where the risks are measurable and the symptoms known.  In these cases, it is possible to develop specific tests to look for symptoms.  However, sometimes the symptoms are not well-known or understood.  Another approach looks for anomalies or patterns in the data to detect symptoms of fraud – unknown symptoms.  Fraud in particular, often looks different than a normal transaction – but is hidden by the volume of transactions.  The fraudulent transactions often follow an unusual pattern or trend, such as an excessive use of management override to bypass key controls.  By filtering, sorting, summing, and performing other manipulations on the data, the fraud transactions often stand out.  A filter can easily identify instances where contracting authority was exceeded (e.g. contracts over the contracting limit for the individual) or avoided (e.g. split contracts).  A simple sort on credit card number, insurance policy number, invoice number, vendor name, employee number, etc will quickly reveal transactions that are not within the normal pattern (e.g. insurance policies that start with ‘9’ where all others start with the year “2014”).  Examining key dates can find fraud – for example reviewing the date the contract bid was submitted to find bids submitted after bid close date; or identifying patterns in the contracts such as the ‘last bid wins’.  A review of the completeness and integrity of the data can highlight fraudulent transactions – for example, examining mandatory fields to identify instances where there is no employee number, or an invalid employee number, but the employee is still being paid; or negative receipt quantities where the receiving clerk is entering negative “receipts” to lower the inventory levels in the inventory system and then stealing the “excess” items.  Comparisons of data in different systems can also identify frauds such as persons on the payroll who are not in the employee database or can highlight unusual rates of pay.

Data analysis can provide you with an indication of where to look and what to look for.  It can focus your review; and help you to rule out transactions that are correct.  In addition, with known frauds, you can use it to size the extent of the loss.  You can also use it to see if the same symptoms are occurring elsewhere.  Finally, in many cases, data analysis will be a direct pointer to the critical evidence – the forged check, the serial number of the stolen item, or the evidence of collusion.

Lessons-Learned – using analytics to detect possible frauds is only the start.  I have successfully identified possible fraudsters and then failed to follow through sufficiently to “prove” the fraud.  As a result, they got off the hook.  At the same time, I have run analytics that looked pretty solid, but in the end exceptions, misinterpretation (or even worse – incorrect analysis) falsely identified the person as a fraudster.  You have to pursue the guilty and protect the innocent.  In either case, it is important to validate and verify; and then trust your analysis so that you don’t fall for the misdirection and excuses you are being fed by the guilty parties.

Year 24 – 2011 – Fraud Detection – part 1

By 2011, I was becoming more and more involved in data analysis to detect fraud.  I had been doing this for years but had never really thought about the approaches I was taking to assess fraud risk and determine the analytics to perform.  The following is the result of my deliberations (which continue to this day).

Fraud Detection

The unrelenting advancement of technology is affecting virtually every aspect of our lives.  And as technology becomes more pervasive, so do schemes to commit fraud. Fraudsters are taking advantage of users’ inexperience with newer technology and weaknesses in the controls to perpetuate these schemes.  This is proving to be a challenge for evaluators, auditors and investigators in their efforts to identify and detect fraud.  However, technology is also a tool that can help prevent and detect fraud. Data analysis techniques can search for the symptoms on fraud that are buried in the millions of transactions flowing through the business process.

Whether you are investing to see if a fraud occurred or following up on an allegation of fraud, a good first step is to understand the ‘why’ of fraud.  The “Fraud Triangle”, created by famed criminologist Donald Cressey, outlines three basic things that must be present in order for fraud to occur: opportunity, pressure or motivation, and rationalization.

Opportunity.  An opportunity is likely to occur when there are weaknesses in the internal control framework or when a person abuses a position of trust.  For example:

  • organizational expediency e.g. it was a high profile rush project and we had to cut corners;
  • downsizing means that separation of duties no longer exists;
  • business re-engineering removed checks and balances in the control framework

Pressure.  The pressures are usually financial in nature, but this is not always true.  For example, unrealistic corporate targets can encourage a salesperson or production manager to commit fraud.  The desire for revenge – to get back at the organization for some perceived wrong; or poor self-esteem – the need to be seen as the top salesman, at any cost; are also examples of non-financial pressures that can lead to fraud.   In addition, living a lavish lifestyle, a drug addiction, and many other aspects can influence someone to commit fraud.

Rationalization.  In the criminal’s mind rationalization usually includes the belief that the activity is not criminal.  They often feel that everyone else is doing it; or that no one will get hurt; or it’s just a temporary loan, I’ll pay it back, and so on.

Interviews with persons who committed fraud have shown that most people do not originally set out to commit fraud.  Often they simply took advantage of an opportunity; many times the first fraudulent act was an accident – perhaps they mistakenly processed the same invoice twice.  But when they realized that it wasn’t noticed, the fraudulent acts became deliberate and more frequent.

Interestingly, studies have shown that the removal of the pressure is not sufficient to stop an ongoing fraud.  Also, the first act of fraud requires more rationalization than the second act, and so on.  As it becomes easier to justify the acts occur more frequently and the amounts increase in value.  This means that, left alone, fraud will continue and the losses will increase.

While I have been unable to find conclusive evidence to support the 10-80-10 rule, but it is well known in the ACFE-world.  Basically, it states that 10% of the people would never commit fraud; 80% might; and 10% are actively searching for opportunities to commit fraud.  I think as auditors and fraud investigators we must be concerned not only with the 10% who are actively attempting to commit but, but also the 80% who might.  By ensuring that the fraud triangle is not adversely affecting these people we can prevent fraud and save people careers and lives.

Pressure – audit can examine corporate performance targets and inform management of times when targets are likely to contribute to cutting corners, bypassing controls and possibly committing fraud.

Rationalization – an audit of corporate value and ethics program and the top-at-the top can help to make sure that the tone-at-the-top is aligned to organizational goals and objectives.

Opportunity – by performing fraud risk assessments and addressing control weakness in the areas most prone to fraud audit can protect the 80% from making a mistake.

Next week I will describe two approaches that can assist you in determining where you have fraud risks and the data you require to perform analytics to determine if fraud is happening.

Year 23 – 2010 – HR Data

Following on the success achieved by the development of the standard extract of financial data from SAP, I decided to design and develop a standard extract from PeopleSoft – our company used SAP for finances and PeopleSoft for HR and another package for Pay (don’t ask why).  For HR data, we really only needed three files to address most of the audit requirements:

  • Person – providing information on the employee such as gender, date of birth, start date, job classification, position number, etc.)
  • Position – providing information on the position such as position number, title, security clearance, level, etc.
  • Dept – providing information of the department such as title.

Since it was a snapshot of the data base and not transactions, I extract the information of a quarterly basis.  Also, if current data was required, I could take a snapshot on any day.

The data was meant to support various audits that required information on personnel as well as payroll audits (e.g. compare actual pay to the HR salary levels).  I also produced summaries to provide trend analysis and identify risks.  This include: employment equity percentages; percentage that could retire within two years; percentage of employees in acting positions; percentage of vacant position; average turnover, etc.

Once again I ran afoul with lawyers and my authority to access personal information.  This despite the fact our audit charter clearly stated that we had “unfettered access to any and all information required”.  This issue was exacerbated by the fact that my HR extracts from PeopleSoft were not linked to a specific audit.  The issue was compounded by the government requirement that personal information only be used for the purposes for which it was collected.  Apparently, the PeopleSoft data – when collected – did not indicate that it would be used for “audit purposes”.  When confronted with this fact, I realized that for pay, travel and entertainment, health claims – basically all personal data being collected by numerous systems – “audit” was never identified as a “purpose”.  It seemed ridiculous, but the law meant that audit couldn’t have personal information, such as the employee name and number, in order to perform a payroll audit because the pay system stated that personal information was collected “to pay employees and produce income tax reports”.  The system did not state that the personal information could be used for “audit purposes”.

I was confused as I had never heard of this “consistent use” requirement before – turns out it was a new law to protect personal information.  At first I thought that I would have to go through each system and request a change – a lengthy process involving the system owner, lawyers and who knows how many other people – to each and every system we used that contained personal information.  But, while my manager thought about the effort involved in even trying to do this, I had an epiphany – we could write an overarching statement that would say that all personal information collected by the company could be used for audit purposes.  (I was careful not to say “used for audits” since I wanted to identify and assess risks by looking at data on an ongoing basis (i.e. not tied to a specific audit).   The main system owners, such as PeopleSoft, had no problem – it was less work for them – and even the lawyers presented very little argument.  As a result, internal audit now have a clear statement in place that supported their authority to access and use personal information.

Audits: in addition to obtaining access to PeopleSoft and developing a mechanism to support audit’s access to personal information, I also supported several audits with analytics.

For an audit of fleet cars, we accessed the credit card data.  Each car had its own credit card to be used for gas and automotive repairs.  The transactional data included the number of gallons of gas, the fuel type and octane level, the price per gallon, date, etc.  It was easy to identify credit cards that had been used several times in a day (some were being used 3-4 times within 5 minutes).  This lead to a fraud investigation which included a stake-out and resulted in several employees being charged with fraud when they used the company credit card to fill not only the company car, but also their spouse’s and children’s cars.  We also identified cars with n abnormal number of repairs and replacement parts (e.g. three mufflers within 6 months; two sets of tires within 3 months).  A fraud investigation determined that the parts had been used to fix employee cars.

For an IT audit, we took a snapshot of the main tables of the current system and compared these to the new system’s tables.  Both were relational data bases.  In the new system, one of the table had one less record that the old system.   Turns out the first record was treated as the field names which meant that the index keys were off by 1 so every record in the parent was matched to the wrong record in the child table.

On a personal note, I won the IIA for “Contribution to the Profession” for my many years of encouraging auditors to embrace analytics.


Lesson-learned – you have to constantly be looking at things that can affect your ability to access data.  This could be a system conversion; a merger/acquisition; changes to federal laws; etc.   I have been impacted by each of these on more than one occasion.  The result can mean many months of limited or no access to data if you do not know far enough in advance of the change to plan for it.

Analytics is only limited by your imagination.  It can be used for more than financial data.  It can be used it to compare the source code in production with the ‘approved’ source code; to looking at staffing and succession planning for HR; to perform employee health and welfare audits; and even to determine if a military unit was ready to go to war.