Sometimes even the simplest analytics are extremely useful.  They often tell you what is happening in the detailed transactions without you having to make assumptions about the data.  STATISTICS is one of those commands: with a couple clicks of the mouse you can get:

  • Min and Max
  • Highest and Lowest ‘X’ values
  • Number of Positive, Negative and Zero valued transactions
  • Average
  • Median, Mode and Q25 and Q75

But like any analysis, you need to know why you are running the analysis and you need to be able to interpret and understand the results.

Why run STATISTICS?  I run Statistics to get an overview of my data.  It helps me to understand the nature of the transactions, to verify my scope, to highlight anomalies, and much much more.  Here are a couple of examples:

  • Count
    • I use this to verify the control totals. In one case I found that my Total for a critical field was out by a significant amount.  It turned out that the numeric fields had 3 decimal points (implied) and not two as I was told.  Without verify my control total before doing additional analysis, I would have wasted a lot of time.
  • Min/Max values:
    • All transactions should be in the previous fiscal year. The Min value should be less than or equal to the start of the fiscal year and the max should be greater than or equal to the end of the fiscal year.  I found billing transactions that were in the wrong fiscal year.
    • All credit card (p-card) transactions should be less than $500.00. Not only did I find amount greater than $500, but I also found numerous credits being processed – which led to a fraud discovery.
  • Highest/Lowest values:
    • The Maximum value was significantly higher than the second highest value. It ended up being an error where the invoice number was entered into the amount field – making the amount much larger than it should have been.
  • Number of Positives/Negatives and Zero values
    • Found hundreds of thousands of transactions with zero values which led to a training issue. A/P clerks could use bulk entry forms for simple invoices.  The form automatically generated a blank invoice entry form with 100 lines.  If the clerk did not type “Cntl –“ before saving the entered invoice details, the blank (unused) lines remained and were processed as invoices with zero value.  This was causing the system to run slow and generating exception reports.
    • Found several hundred Negative “quantity received” amounts. The Receipting clerk at a warehouse was entering negative receipts after entering the correct receipt amount.  Invoice was paid based on the correct receipt amount; but the negative receipt entry (without an invoice number) reduced the quantity in inventory and allowed her to steal the items.
  • Mode value
    • This identified a fraud where an employee was entering meal charges when not on travel status. Amounts under $10.00 did not require approval.  The employee entered hundreds of meal claims at $9.99 which was pickup by the mode option.
    • The most often occurring date was just before bonuses were determined. Salespersons were entering a large number of fictitious sales on the day before performance bonuses were calculated.  The sales were reversed at later dates.
    • The most occurring amount for contracts was just under the “must go competitive” value. Managers were establishing contracts at $49,900 – which was below the $50,000 limit for sole-sourced contracts.

In general, STATISTICS lets me see what the data looks like from a high-level perspective.  However, it also can quickly identify anomalies (e.g. negative when all transactions should be positive; values outside the expected range; unusual maximum values, etc.).  .  This helps with determining risk, sampling strategies, identification of control weaknesses, materiality amounts, etc.  When combined with STRATIFY, I can get an overview of the ranges (or strata) of the data which provides even more useful information.

Missing items

I have been away on vacation and now I have exams to mark and Christmas preparations to finish.  So, I must confess that these examples are fillers as I have been too busy to write much else these days.  However, I do still feel that they have value.

Sometimes fraud is detected through the identification of missing items or transactions; in other cases unexpected transaction are found, highlighting the fraud.  However, in some fraud investigations identifying what is not there can be as important as finding out what is there.  Missing accounts receivable payments, branch offices not reporting revenue or unrecorded receipts of goods are just a few of the many symptoms of fraud.  In a similar fashion, duplicate invoice payments or payroll check numbers may be a result of fraudulent activity.  The following two examples illustrate the utility of identifying missing items in a sequence or unexpected consecutive items.

Case Study: Free Calls

Employees were permitted to use their office phone to make long distance calls as long as they paid for the charges.  From the employees’ perspective, it was a good deal since the company had a special discounted rate with the telephone carrier.  From the company perspective, it was a good deal too.  Employees liked the arrangement and it increased the number of calls that allowed the company to negotiate a better long distance discount rate.  Every week Caroline in accounts payable reviewed the long distance bill and identified non-company numbers called.  The person, from whose phone the call was made, was asked to complete a ‘long distance call’ slip detailing the date and cost of the call.  Each quarter, the employees were required to submit the long distance call slips and reimburse the company for the ‘personal calls’ portion.

One month, William accidentally lost a long distance call slip and, as a result, only reimbursed the company for the slips submitted.  Latter, he found the missing slip, but did not disclose the error.  When nothing happened, he deliberately failed to include several long distance call slips from time to time.

At the end of the year, the auditors tested the controls over the reimbursement of long distance charges and noted that there was no control to ensure that the employees were paying for all the personal calls they actually made.

The auditors reviewed the reimbursement records using the Gaps command.  Since the long distance call slips were pre-numbered, the test easily identified 26 missing slips.  The results were presented to Caroline who matched the numbers from the missing slips to the carbon copies of the slips.  William was identified as the culprit for 25 of the missing slips.  When approached by the auditors, he admitted to neglecting to include all of the weekly slips, and was ordered to reimburse the company.  In accordance with the strict policy on fraud, he was fired.

Cash Study: Frequent Traveler

The assistant to the President was required to accompany the President on many of her frequent business trips across the country.  As a result, the auditors did not normally question the high travel expenditures of the President, or of her assistant.  However, they had received an anonymous tip that the assistant was committing fraud.

During the initial fraud investigation of the assistant’s hotel bills, they calculated her total travel costs by type, and noticed that she had significantly higher accommodation expenditures than the President.  The team leader was curious as to why this was the case, and instructed his team to review all transactions related to her hotel expenditures.  In particular, they sorted them by invoice number and used the Duplicates command to look for duplicate invoices.  They also used the Gaps command to examine the sequence of these expenses.  As expected, there were no duplicates and the Gaps command run on the invoice number revealed many gaps in the sequence of the hotel invoice numbers. This was not surprising since each hotel used its own invoice numbering system and had many clients, each with their own invoice.  What surprised the auditors was that the analysis showed 10 bills from one hotel that had no gaps between – they were in sequence 20311 to 20320.  This was the case even though the dates of the invoice spanned several months.

The auditors checked with the hotel and discovered that the assistant had stayed at the hotel on the dates in question.  However, the billing manager told them that the invoice numbers were not part of their invoice sequence and had not been issued by the hotel.  The auditors brought the results of their analysis to the attention of the President and received permission to question the assistant.  The assistant admitted to using her computer to scan in a real invoice from the hotel.  She then used this scanned invoice to make copies – thereby falsifying her travel claims.  She would inflate her hotel bill every time they stayed at that hotel, which was often since the President had a lot of business in that city and always stayed at that hotel.  She unwittingly had simply incremented the invoice number each time she generated a new invoice.  The Gaps command allowed the auditors to find the altered invoices and discover her scheme.

Lessons-learned: The ability to see indicators of fraud in the data includes not only seeing what is there (and is not expected) but also what is not there (and is expected to be).  Missing items can range from inventory tags, purchase orders, health claim forms and even transaction ids in ERP system (why are we missing 6 transactions?).  In other cases, you should have gaps – in fact you shouldn’t have consecutive items (e.g. invoice numbers, credit card numbers (anything with a check digit), and various types of receipts.

The ability of auditors and investigators to analyze data is enhanced when they can manipulate and work with the data.  Sometimes it is necessary to create new fields, not existing in the original file – such as Total value of inventory (quantity on hand * unit price).  ACL is ideal for this type of analysis – allowing new field to be created and then compared with existing fields or data in other files.  It can perform simple calculations (quantity * unit price) or conditional calculations (markup is 57% if product code is ‘01’; 68% if product code is ‘02’ and so on.).  Whether there are millions of records or only thousands – the analysis is fast and easy; and the results are often revealing.

New System – control weaknesses

It is always important to test controls when systems and/or processes change.  Sometimes a current process may have adequate controls, but the new process may not be as secure.

Equipment Serial Numbers

A large company with several plants purchased expensive, highly specialized, equipment for use in its manufacturing plants.  A central purchasing organization made all the purchases and the inventory held until required by a plant.  The inventory manager was understandable proud of the inventory system; having recently implemented just-in-time inventory practices while maintaining a quick response time to orders from plant managers. This meant that expensive equipment was only purchased if, and when, required.  The new inventory practices were saving the company millions of dollars every year.  However, he had heard a few rumblings about inventory theft, and although he was not personally aware of any problems, he asked audit to take at look at the issue.

The audit teams conducted a thorough review of the controls and only found one area of concern – when items were shipped to the plants, they were automatically removed from the electronic inventory system.  The receiving plant manager did not have to send any proof of receipt, so there was no sure way of knowing if the item had reached its proper destination.  The inventory manager countered that if someone had ordered an item and did not receive it, he would certainly hear about it.  He even produced a few emails where the recipient had questioned the status of deliveries that were only a day late.  The audit team leader smiled as said, ‘what if they were not expecting a delivery?’.

The audit team requested a copy of all high dollar equipment that had been purchased in the last year; this included all equipment that had been shipped to the plants.  The data was sorted and a check for duplicate serial numbers performed.  The results revealed that 53 expensive items, used in the manufacturing process, had duplicate serial numbers.  The company had purchased hundreds of thousands of dollars worth of equipment – twice; and in each case, the shipping agent was the same person.

The auditors, with the help of the inventory manager, set out to catch the thief.  They noted that while all equipment had been shipped to various manufacturing plants, none of the managers at the plants had placed an order for, or received the equipment.  The next time the clerk in question prepared a shipment for delivery for which the receiving plant manager had not placed an order, audit arranged for a private security company to follow the truck.  Instead of delivering the equipment to the plant specified on the shipping receipt, the equipment was delivered to a warehouse in the city.  Two days later, the inventory manager asked the clerk to place an order for the same model equipment. The security personnel followed the truck as it delivered the same equipment back to the company warehouse.

In the weeks that followed, audit was able to prove that the clerk was placing false orders for equipment, charging the inventory to phony projects.  The equipment was delivered to a warehouse and held until a purchase order was placed for the same item.  The clerk would then arrange for the equipment to be shipped to the company – selling the company back its own inventory.  The serial number had not been changed, so it would have been identified as a duplicate if the equipment had not been removed from the inventory system when it was shipped the first time.

As a result of the investigation, the clerk was fired and the serial numbers of all new equipment were compared to those of equipment that had previously been in the inventory system.  Controls were put into effect to ensure that equipment was shipped to, and received by, project managers.


Year 29 – 2016 – Fraud

 Hidden Costs

The true cost of fraud is more than the total of the financial losses.  Stockholder confidence, employee morale and other intangible factors must be added to the monetary losses.  Most managers agree with this assessment; however management often encourages fraud by placing unrealistic goals on employees, or by disregarding the rules themselves.  Auditors must be aware of the pressures placed upon employees that may lead them to commit fraudulent activities.  In addition, the controls, to be effective, must apply to all employees and must be uniformly enforced.  Performance goals and objective should be attainable and adequately reviewed and monitored.

ACL, and other software, are powerful and flexible and can be used to run analyses to detect and even deter fraud.  However, no tool is more powerful than it users.  Auditors and fraud investigators, trained in the use of auditor software, innovative in their approach to the combating of fraud can make a difference.  The cost of fraud demands that we devote time, energy and resources to the battle.

Money Laundering Scheme

Regulations around anti-money laundering have increase, but it still happens.  Some of the larger schemes include the following:

Back in 2012, HSBC forfeited £1.2 billion for having inadequate money laundering controls. This followed a report published by the US Senate which alleged, amongst other things, that HSBC had:

  • supplied banking services and American dollars to some banks in Saudi Arabia in spite of their connections to terrorist financing
  • dodged restrictions created to prevent transactions involving Iran, North Korea and other countries subject to international sanctions
  • HSBC US didn’t treat its Mexican counterpart as high risk even though it has a problem with drug trafficking and money laundering.

In 2010 Wachovia paid federal authorities a total of £123.7 million for willingly failing to establish an adequate AML programme and subsequently allowing, from 2004 to 2007, the transfer of an estimated £292.5 billion into dollar accounts from money exchangers in Mexico that the bank did business with. This included nearly £10 million that went through correspondent banking accounts at Wachovia to buy aeroplanes to be used in the drugs trade – more than 20,000 kg of cocaine was seized from these planes.

Audit example:

Bill was suddenly a top salesman for the XYZ Insurance Company, selling more policies in a single month than had ever been achieved by a salesman in the company’s 20 year history.  In addition, few claims were being made against the policies he generated.  He easily met his targets and received the maximum bonuses, plus incentives such as trips and merchandise.  Until the story broke in the national newspaper, management had no idea that Bill’s policyholders were using the insurance company to launder ‘dirty’ money.

The requirement for companies conducting illegal activities to launder their illegal gains has generated many different schemes.  An increasingly used method to launder money is one where the money earned from criminal activity is used to purchase insurance policies with a ‘cash out’ clause.  Such a policy may pay the holder up to 80% of the policy value if cashed out within one year.  The purchase of millions of dollars in insurance policies and the subsequent cashing out of these policies can covert 80% of the dirty money into ‘clean’ money – a good return for the criminal element, and a good deal for the insurance agent, and even profitable for the insurance company.  However, the negative publicity that may be incurred might outweigh the cash benefits to the insurance company.

The audit director of another insurance company read the newspaper headlines with concern – was this happening at his company?  The next day he initiated an audit of all insurance policies to look specifically for this problem.  He met with the team leader, and explained what had happened at XYZ Insurance.  The team leader had also read the newspaper article and thought that this would be a perfect application for the new CAATTs software he had purchased recently.  He explained that the AGE command could be used to examine the length of time between the opening and closing of a policy.

The team obtained access to the policy file, and used the Age() function to calculated the time between the policy start and closing dates.  Further, by combining the results of the AGE() function with the CLASSIFY command, they were able to determine the number of policies, total length in years and the average policy life (in years) by salesman.

This quickly highlighted possible anomalies in sales practices, and assisted the team leader in checking for potential money laundering activities – whether knowingly abetted by the salesperson, or unknowingly.

The auditors reviewed the insurance claims raised by Paul, and found that many of them were made in the name of only a few individuals.  One person had opened 32 different policies, all of which had been canceled within two weeks.  Paul admitted that he was helping to launder money, and was fired.  Since the company had been proactive in finding the fraud, it was able to correct the problem and keep the incident out of the media, and avoided the adverse publicity.  The audit director smiled as he remembered what his first audit director had told him ‘Your job is to keep the company off the front page of the newspaper’.  This time, that was exactly what he had done.

Year 28 – 2015 – Fraud Risk Management Guidance

COSO had released an update to COSO-ERM which included Principle #8 (“The organization considers the potential for fraud in assessing risks to the achievement of objectives.”) related to fraud risk.  David Cotton (Cotton and Company LLP) put together a team of experts to develop guidance on how the audit profession and management could address the requirements of principle #8 and I was fortunate enough to be invited to be part of the team.   In particular, I was co-chair, along with Vincent Walden (EY), of the sub-group on data analytics which was responsible for developing guidance of the use of analytics to assess risk of fraud and to prevent and detect fraud.  I was an interesting and informative task that gave me the opportunity to work with many talented people.  The final guidance “Fraud Risk Management Guide” was published by COSO in 2016.

The executive summary can be viewed at

The following represents some of my thoughts on the area and served as input to the final guidance document.

Fraud Guidance – Data Analytics input

Data analysis is a powerful tool for assessing fraud risk and for fraud prevention and detection.  But according to an EY 2014 Global Fraud survey: 42% of companies with revenues from $100M – $1B are working with data sets under 10K records; and 71% of companies with more than $1B in sales are working with data sets of 1M records or fewer.  These companies may be missing important fraud prevention and detection opportunities by not mining larger data sets to more robustly monitor business activities.

Data analysis addresses all aspects of the fraud triangle:

  • if people know you are looking, they are less likely to commit fraud
  • Prevent fraud – verify that the key controls are in place and working properly
  • Detect instances of fraud earlier – could catch the first transaction (ACFE 2014 – reported a 50% reduction in duration and a 60% reduction in losses when proactive data analytics were used)
  • Focus the investigation – you know where to look and what to look at
  • Determine losses – reactive; proactive: identify all similar transactions – perhaps at other locations (e.g. payroll fraud)
  • Support the prosecution of people committing fraud – identify the evidence, fully cost the fraud, tell the story

The use analytics supplements the identification and assessment of fraud risk; allows for the monitoring and assessment of controls in areas of highest fraud risk; and supports the detection and investigation of possible fraud.

Fraud Risk Assessment

The ACFE Report to the Nations 2016 stated that proactive fraud analytics can reduce the duration and the loss due to fraud by more than 50%.  In areas of highest fraud risk – analytics can be used to search for control weaknesses and anomalies that could be indicators of fraud.  The Statement on Auditing Standards (SAS) #99 defines various risk factors for assessing the risk of fraudulent financial reporting and other fraudulent acts.   It also encourages you to devise appropriate data analysis strategies for each risk factor.

For example if you are in a competitive Industry, rapidly changing technology can lead to inventory becoming obsolete.  This creates a risk that the inventory may be not be appropriately re-evaluated which would lead to an overstatement on the financial report.  The data analysis to identify and assess this risk factor could include checking the date and results of last inventory evaluation and assessing inventory turnover figures.  If your company has attractive/easily transportable items in inventory, then you are at risk of theft.  Analytical tests could include verifying the effectiveness of the inventory controls by looking at trends in reorder quantity versus use in production or sales and identifying write-off and the use of management overrides to adjust inventory levels.

Fraud Monitoring

In areas of highest fraud risk you should develop a fraud monitoring plan.  The monitoring plan identifies the Why, What, Where and What’s Next of the analysis that will be performed.  For example, if there was a fraud risk that attractive items in inventory could be declared not repairable and written-off as scrap and taken home by employee, we would expect that there would be a separation of duties such that the same person could not be able to declare and item as not repairable and also write-off the item.  Data analysis would be to identify all employees who declared items as not repairable and those who declared items as a write-off.  We would not expect to find the same person on both lists – if we did, we would follow-up to see if their actions were applied to the same item.

Fraud Investigation

When fraud is suspected you need to enhance the fraud monitoring plan and develop a more detailed fraud investigation plan.  The following elements should be documented:

  • Define objectives of investigation. Detail why are you performing the analysis
  • Define the indicators of fraud. Describe what the symptoms of fraud would look like in the data.
  • Identify the required data sources. Working with IT and the business process owner – determine the appropriate source of the required data.
  • Obtain and safeguard the required data. Determine which fields are required – single year or several; one business unit or more; the best methods for obtaining the data; file formats; transfer mechanisms; and how you will safeguard the data.
  • Test the integrity and completeness of the data. Determine the extent to which you can rely on ten data and how you will assess the integrity and completeness of the data.
  • Analysis techniques. Describe the tests to be performed, the expected results and the follow up analyses.

In cases of suspected fraud, the auditor must verify to source or compare with other sources.  When performing the analysis, it is important to drill down into the data – challenging the assumptions and results.

In addition to providing input in each of the chapters – from risk assessment to investigation – Vince and I provided a series of analytical tools and techniques that were presented in an index and are available online.

Year 27 – 2014 – Car Maintenance – Part 2

Part2 – the audit had entered an investigative type phase looking into probable fraud.  As a result, the team leader developed a fraud analysis plan.  The plan outlined on the Who, What, How and Why and focused on analytics to look at the symptoms of fraud in the data.  Who could take advantage of the control weaknesses; what could they manipulate or control and what would it look like in the data; how could the fraud be accomplished; and why (not the rationale, but the benefit tot he fraudster).

While the team leader was thinking about what to do next, he instructed one of the team members to review the controls over the sale of used vehicles.  Twenty cars had been sold in the last year for a total of more than $68,100.  During a manual review of the copies of the purchasing forms the auditor noticed that one person showed up as the purchasers several times.   Using the Classify command on the purchasing data, the auditor totaled the number and dollar value of sales by purchaser.  The results showed that a Mr. Ford was listed as the purchaser 18 times.  What made this a little more disturbing was the fact that the average cost of purchase was $4,156 for the other 6 purchases, but only $2,399 for the 18 purchases made by Mr. Ford.

The team leader thought that maybe the cars purchased by Mr. Ford were older models or had had more mechanical problems and therefore were not worth as much.  He asked the auditor to extract data on all repairs performed in the last year on the 20 vehicles sold in the past year and to organize the information by vehicles and by date of the repair work.  The report indicated that the cars purchased by Mr. Ford were not any older than the other cars sold.  However, it did show that they had undergone a significant amount of repair work.

The team leader thought this might explain the difference in the purchase price until the auditor pointed out that in every case the cars had undergone repair work within a week or two of being sold to Mr. Ford.  Often the cars purchased by Mr. Ford were equipped with new tires, a muffler, and a battery less than 15 days before he purchased them.  Only one of the cars sold to another purchaser had had any repair work done on it in the month prior to being sold.  Finally, a car purchased by Mr. Ford for $800 and listed as being a 1992 model, was in fact in a 1996 model worth much more.

The final analysis performed on the sale of new vehicles was a comparison of the purchase price with the book value listed by the Automobile Association.  Cars of the same make, model, year and mileage were being sold for significantly more than the purchase price paid by Mr. Ford.  However, the 6 cars sold to other purchasers had been sold at prices that were comparable to the book value.

The analyses were presented to the team leader, giving him even more to think about.  During the same time period, one of the new auditors was given responsibility for conducting a review of the controls over gasoline purchases.  She was enjoying the sunny weather one afternoon and happened to walk passed the gas pumps at lunchtime.  She watched as an employee drove up, filled the car with gas and handed over some money to the assistant manager.  This was highly usual as all gas purchases were supposed to use company credit cards.  The auditor obtained an electronic copy of the gas purchase data.  This file contained a record of the number of gallons recorded against each company credit card at the company garage.  She also obtained a copy of the credit card purchases for each company vehicle.  This file provided details on the gas purchased from non-company gas stations.  After joining the two files together, she totaled the gasoline consumption for each vehicle purchased in the last year. The total distance on the odometer was divided by the gas consumption.  This analysis showed that the cars purchased in the last year were only obtaining an average of 7 mpg.  This was not enough to prove any wrongdoing, but encouraged the team leader to permit the auditor to perform some additional analysis.

The next thing the auditor did was to search the data for duplicate transactions – more than one gas purchase on the same day for the same vehicle.  She discovered that several times in the past year company cars had filled up at the company garage and at a retail gas station on the same day.  She obtained the actual credit card receipts and found that in four cases the retail station purchases were made in cities that were hundreds of miles away from the company garage.  In one case the audit team leader had signed the credit card receipt.  When she told him about the receipt and the date, he remembered the trip.  The purchase occurred during a three-week audit of regional offices and he had been on the road the entire time, so it was not possible for the car to have been filled with gas at the company gas station.

The audit team leader reported his suspicions to the president of the company.  A concealed camera was installed to monitor the gas pumps.  Further, the maintenance data was reviewed daily and a quick check was performed to determine if company cars were being repaired as stated.  Within a week the camera capture evidence of the manager and his assistant filling up non-company cars.  Further, the verification of the maintenance data found several instances where the repairs had not been performed as stated.  The repair records showed that 4 new tires had been installed on a company car, but when the auditors check the car they found old tires.  At first the manager claimed that he might have record the wrong license number – but he confessed to the entire scheme when shown the videotape of the gas purchases.


Lessons-Learned – The use of ACL to analyze electronic data, to identify anomalies, trends and duplicates can be invaluable when performing audits.  Such uses of audit software have been reported numerous times in audit magazines.  However, it is important to note that the use of audit software can also be extremely useful in detecting fraud.  Matching data, joining files, recalculating amounts and totals are performed easily and can identify serious exposures.  When fraud is detected, the use of tools like ACL can also help the auditor to quantify the amount or extent of the losses.

Year 27 – 2014 – Car Maintenance – Part 1

The company I worked had a fleet of cars that we maintained, and when beyond a certain age, were sold.  The analysis below describes an audit that looked at the controls around both of these processes.

The new manager of the company garage had only been in charge for a year and was already well respected and well liked.  He and his assistant provided quick and efficient maintenance service for all the company cars.  The garage also contained a gas pump and was considered a ‘Full Service’ station.  Unfortunately, the manager definition of full service went far beyond what the company’s management would have liked.

The garage manager was permitted to perform work on employee’s vehicles, as long as the employees were charged the full cost of the parts used for the work performed.  The company allowed employees to purchase automobile parts for their personal cars at the company rates that were reduced considerably when compared to the retail value of the parts.  The employee would purchase the parts and the company was invoiced at the discounted rate.  The employees would then submit their payment and invoice to the parts manager who would remit the money to the company; and the company would pay the vendor.  However, the manager was ‘correcting’ invoices and making it look like the parts had been used for company cars.  He would keep the money and the company would pay the bill.

The manager was also responsible for the disposal of used vehicles no longer considered economical to maintain.  The manager managed to sell many of the used vehicles to a friend at 65% of the book value.  The friend then sold the vehicle for the book value and split the profits with the manager.  The process called for sealed bids to be submitted by persons wishing to buy the vehicles.  However, the manager would show prospective bidders a car in much worse shape than the one actually being sold; or would invent stories of accidents or mechanical troubles the car had been through.  As a result, the bids from other buyers were usually even lower than the friend’s bid.

Vehicles, which were to be sold, were equipped with new tires, mufflers and other parts just prior to being sold to the manger’s friend.  This significantly increased the value of the vehicle being sold to the point that sometimes the new parts were worth more than the car.

Finally, the manager would fill up employees’ cars at the gas pump and charge the gas to a company car.  The company maintained a fleet of cars for use by employees.  A credit card was kept in the glove compartment of each card to be used when employees ‘purchased’ gas or had repair work done on the vehicle at the company garage.  However, several employees, who were friends of the service manager, were bringing their personal cars into the company service garage for maintenance and even filling up their tanks with gasoline.  The cost of the gas was charged against the car’s credit card.  The service manager then charged the employees half the actual cost of the gas ‘purchased’.  The employees benefited from only having to pay half of the cost and the manager kept all the cash he received.

The auditors were performing the yearly review of the garage operations.  They were totally unaware of the frauds being committed by the manager and his assistant.  This did not, however, stop them from finding out what was happening.

The first analysis performed by the auditors was to total the repair work by vehicle.  They were quite surprised by the total dollar value of the repairs performed on the company cars.  A refinement to the analysis separated the vehicles by year of purchase.  The manager had been so busy with his scheme that even newly purchased cars were showing repair work.  The auditor was particularly suspicious when invoices were paid for parts on cars that were still under the original warranty.  The analysis revealed that some cars less than one year old had undergone as much repair work in the last year as cars much older.  The auditors calculated the total repairs by type of repair to determine the 5 most costly repairs performed.  Next the auditors then totaled, by vehicle, the number and amount of repairs, by type of repair – tire, muffler, alternator, tune up and battery, for all cars less than one year old.  They found that three cars that were less than one year old had a more than usual amount of repair work.  One had four new mufflers installed and another had 12 new tires.

Because of this startlingly finding, the team lead decided to expand the scope of the audit and perform more testing on the repair work and examine the controls over the sale of used cars.  Next week we will see the results of their expanded analysis.

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 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.