Table of Contents for

Forensic Analytics: Methods and Techniques for Forensic Accounting Investigations

Chapter 18

Using Analytics on Purchasing Card Transactions

This chapter shows how the tests in the prior chapters could be used in a forensic analytics project related to purchasing card transactions. The first three chapters reviewed the use of Access, Excel, and PowerPoint in forensic analytics. These are the software programs used in this chapter. The next few chapters reviewed some high-level tests designed to analyze the internal diagnostics of transactional data. This was followed by more focused tests that identified small clusters of highly suspect items. The later chapters dealt with risk-scoring techniques to identify high-risk forensic units. These forensic units could be franchised locations, bank accounts, travel agents, or the controllers of distant divisions. This chapter now applies some selected tests to the high-risk environment of corporate purchasing cards. The methods and techniques in the book can be adapted to various environments by selecting a set of relevant tests, and perhaps even including innovative adaptations or revisions to the Nigrini Cycle of tests or the other tests discussed in the book.

The chapter starts by describing corporate purchasing cards. Examples of fraud, waste, and abuse are then listed. The examples are selected examples from forensic audits of the transactions. The work of the National Association of Purchasing Card Professionals is reviewed together with some results from a poll conducted by the association. An example of a purchasing card dashboard is then shown together with a discussion of using Excel for these applications. The chapter then reviews the results of selected tests run on a real-world data table of purchasing card transactions. The chapter ends with some concluding thoughts on various matters related to forensic analytics.

Purchasing Cards

A purchasing card (hereinafter “card”) is a government or company charge card that allows an employee to purchases goods and services without going through the “rigors” of the traditional purchasing process. Cards are issued to employees who are expected to follow the policies and procedures with respect to card use. These procedures state which items may be purchased using the cards, the purchase approval process, and the reconciliation procedure required so that personal expenses can be reimbursed to the company. The use of cards is an efficient way of simplifying and speeding up the purchases of low-value transactions. The advantages are that employees can buy goods and services quickly, there is a reduction in transaction costs, there is the capability to track expenses, and the use of cards frees up the time of purchasing and accounts payable staff. The card issuer (a bank) typically invoices the agency or company on a monthly basis with an electronic invoice showing the total dollars per user and the grand total for the month. From a legal liability perspective it is important to understand that the organization assumes full liability for payment. The employee cardholder does not interact with the card issuer, but is expected to follow the company's policies and procedures relating to card usage. A typical statement in a set of policies and procedures is as follows:

Under no circumstances is a cardholder permitted to use the P-Card for personal purchases. Using the P-Card for personal purchases may result in disciplinary action, up to and including termination from State employment and criminal prosecution. The Official code of Georgia, Annotated (O.C.G.A.), paragraph 50-5-80 states that any cardholder who knowingly uses the card for personal purchases under $500 is guilty of a misdemeanor. A cardholder who knowingly uses the card for personal purchases of $500 or more is guilty of a felony punishable by one to 20 years in prison. Supervisors or other approving officials who knowingly, or through willful neglect, approve personal or fraudulent purchases are subject to the same disciplinary actions as cardholders.

It would seem that with clear policies and effective audit procedures that a card program would make it easier for employees to do their jobs. Unfortunately, it seems that in some cases the card simply gives the employee the opportunity (one of the three parts of the fraud triangle) to commit fraud. Serious violations were documented in the U.S. General Accounting Office (GAO) audit of two Navy units and the Department of Education in 2002. The GAO is now known as the Government Accountability Office (www.gao.gov). In the report (GAO-02-676T dated May 1, 2002) the director notes that she supported the purchase card program because it resulted in lower costs and less red tape for the government and the vendor community. However, several GAO card audits in the early 2000s turned up instances of fraud, waste, and abuse. Selected examples are:

• A cardholder made over $17,000 in fraudulent transactions to acquire personal items from Walmart, Home Depot, shoe stores, pet stores, boutiques, an eye-care center, and restaurants over an eight-month period (Navy, GAO-01-995T, 2001).
• A military officer conspired with cardholders under his supervision to make nearly $400,000 in fraudulent purchases from five companies. He owned two of the companies and the other three companies were owned by family and friends. The purchased items included DVD players, Palm Pilots, and desktop and laptop computers (Navy, GAO-01-995T, 2001).
• A maintenance/construction supervisor made $52,000 in fraudulent payments to a contractor for work that was actually done by the Navy's Public Works Center (Navy, GAO-01-995T, 2001).
• A purchasing agent made about $12,000 in fraudulent purchases and planned to submit a further fraudulent $103,000 for expenses such as hotels, airline tickets, computers, phone cards, and personal items from the Home Depot (Navy, GAO-01-995T, 2001).
• A cardholder had transactions for $80,000 that was not supported by documentation. He admitted to making thousands of dollars of personal purchases including EZ-Pass toll tags, expensive remote-controlled helicopters, and a dog (Navy, GAO-03-154, 2002).
• A cardholder used his card to purchase $150,000 in automobile, building, and home improvement supplies. The cardholder then sold some of these items to generate cash (Navy, GAO-03-154, 2002).
• Two cardholders conspired with seven vendors to submit about $89,000 in fictitious and inflated invoices. The cardholders sold, used, and bartered the illegally obtained items (Navy, GAO-03-154, 2002).
• The Navy inappropriately issued five cards to individuals who did not work for the government (Navy, GAO-03-154, 2002).
• The audit report also identified other issues such as (a) purchases that did not serve an authorized government purpose, (b) split purchases, and (c) purchases for vendors other than the specifically approved vendors for certain categories of expenses (Navy, GAO-03-154, 2002).
• Cardholders and approving officials bought items for $100,000 that were for personal use, including a computer game station, a computer, a digital camera, and a surround sound system (Army, GAO-02-732, 2002).
• One cardholder bought fraudulent items for $30,000 including a computer, rings, purses, and clothing from vendors such as Victoria's Secret, Calvin Klein, and others (Army, GAO-02-732, 2002).
• A cardholder bought fraudulent items for $30,000 including various items for personal use and cash advances (Army, GAO-02-732, 2002).
• A cardholder bought fraudulent items including cruises, cell phones, hotels, Payless Car Rental, and Extended Stay America. The cardholder claimed that the card was stolen and that the card thief had made the purchases (Army, GAO-02-732, 2002).
• The Army audit showed many examples of fraud including a card that had $630 charged to it for escort services (Army, GAO-02-732, 2002).
• The audit of the Veterans Affairs and the Veterans Health Administration identified more than $300,000 in purchases that were considered wasteful including movie gift certificates of $30,000, and an expensive digital camera for $999 when many cheaper models were available. The vendors used included Sharper Image, Baltimore Orioles, Daddy's Junky Music, Eddie Bauer, Gap Kids, Hollywood Beach Country Club, Harbor Cruises, and Christmas Palace (VHA, GAO-04-737, 2004).
• Control issues raised in the VHA audit included (a) untimely recording (where the cardholder does not notify the agency that a purchase has been made), (b) late reconciliations (where the goal is to detect invalid transactions, billing errors, and unauthorized purchases) or signing off that a reconciliation was done when it was not actually done, and (c) lack of a review by an approving official (so as to identify fraudulent, improper, or wasteful transactions) (VHA, GAO-04-737, 2004).
• In a sample of 1,000 transactions the GAO identified 17 purchases for $14,000 for clothing, food, and other items for personal use. One transaction was for winter jackets for warehouse employees, another purchase was for 18 pairs of jeans that the cardholders claimed were employee uniforms, and there were several purchases of food that should have been the personal responsibility of the employees. About 250 transactions lacked documentation and the GAO could not determine what was actually purchased and the cost of each of the items purchased, and whether there was a legitimate need for the items (VHA, GAO-04-737, 2004).
• An Air Force audit showed (a) a down payment of a $10,000 sapphire ring for $2,400 at E-Z Pawn, (b) suitcases, garment bags, flight bags, and briefcases for $23,760 from 1-800-Luggage, Patagonia, and Franklin Covey, (c) clothes for parachutists and pilots for $23,600 from REI, L.L.Bean, Old Navy, and Nordstrom, (d) two reclining rocking chairs with vibrator-massage features from La-Z-Boy Furniture, (e) tractor rentals for $52,500 from Crown Ford, and (f) a dinner party and show for a visiting general including $800 for alcohol from Treasure Island Hotel and Casino for $2,141 (Air Force, GAO-03-292, 2004).
• Other Air Force findings included a cardholder who purchased $100,000 in helmets by splitting the purchase into four parts to stay within their $25,000 transaction limit. The goods were not needed, but the cardholder wanted to spend the funds before the end of the budget period. The cardholder then returned the items and used the credits to purchase other items. This effectively converted fiscal year 2001 appropriations to a fiscal year 2002 budget authority, which was a violation of appropriation law (Air Force, GAO-03-292, 2004).
• Department of Homeland Security findings included (a) more than 100 laptops missing and presumed stolen for $300,000, (b) unauthorized use of a card by a vendor to purchase boats for $200,000, (c) more than 20 missing and presumed stolen printers for $84,000, and (d) three Coast Guard laptops missing and presumed stolen for $8,000 (DHS, GAO-06-1117, 2006).
• Other Department of Homeland Security cases of abuse included the purchase of a beer brewing kit, a 63-inch plasma television set for $8,000, which was found unused in its box six months later, and tens of thousands of dollars for training at golf and tennis resorts (DHS, GAO-06-1117, 2006).
• The Forest Service also had its share of wasteful purchases that included (a) extravagant digital cameras, (b) premium satellite and cable TV packages including HBO, Cinemax, NFL, and NBA games for their recreation facilities, (c) employee awards, which included hats, mugs, backpacks, and blankets from Warner Brothers, Eddie Bauer, and Mori, Luggage and Gifts, (d) two fish costumes, Frank and Fanny fish, from the Carol Flemming Design Studio at $2,500 each, and 14 high-end PDAs from vendors such as Palm when there were many economical alternatives available (Forest Service, GAO-03-786, 2003).
• Housing and Urban Development came in with (a) $27,000 spent at Dillard's, JCPenney, Lord & Taylor, Macy's and Sears, (b) $74,500 spent at Ritz Camera, Sharper Image, Comp USA, and PC Mall, (c) $9,700 spent at Legal Sea Food, Levis Restaurant, Cheesecake Factory, and TGI Fridays, and (d) $8,900 spent at music and audio stores such as Sound Craft Systems, J&Rs Music Store, and Guitar Source (GAO-03-489, 2003).
• The FAA audit showed (a) purchases of personal digital assistants, keyboards, and leather cases for $66,700, (b) individual subscriptions to Internet providers for $16,894, (c) store gift cards for $2,300, and (d) retirement and farewell gifts including Waterford crystal, a glass clock, and an engraved statue for $1,200 (GAO-03-405, 2003).

In March 2008 the GAO reported on the use of cards across all government agencies. This report was based on an audit of a sample of card transactions for fiscal 2006. The results were that about 40 percent of all transactions sampled and audited did not meet the basic internal control standards of the purchase being authorized, the goods or service being received, and that a third party vouched for such receipt. Also, for large transactions (more than $2,500) the agencies could not show that these large purchases met the standards of proper authorization, and independent receipt and acceptance.

Table 18.1 shows a select list of fraudulent cases and other acts similar in nature to fraud. One way for a cardholder to avoid a fraud “issue” is to claim that the card was stolen or compromised. The examples indicate that effective monitoring and internal controls are absolutely vital to detect and deter fraud. Table 18.2 lists several improper and abusive charges. Here the goal should be to detect these charges early so as to warn the cardholder against their recurrence. Abusive charges are more difficult to detect because a charge is only abusive because of the circumstances. It might be allowed by company policy for the marketing vice-president of a high-end luxury yacht company to treat a potential customer, or a celebrity endorser, to a lavish meal with alcohol. The same meal enjoyed by a federal government employee, while at a conference, would be abusive. If Live Nation Entertainment hired a chauffeured limousine to drive a professional comedian from the airport to her hotel then this would be an acceptable business charge, whereas it would be extravagant for a federal government employee going to a conference. Some abusive charges are described in Table 18.2.

Table 18.1 Fraudulent Acts Discovered during a GAO Purchasing Card Audit (GAO, Report GAO-08-333, 2008)

Table 18.2 Examples of Improper and Abusive Purchases (GAO, Report GAO-03-333, 2008)

The improper and abusive purchases listed in Table 18.2 are blatant and therefore not too difficult to detect. There are many other purchasing card fraud cases and an Internet search of “sentenced for purchasing card fraud” will list thousands of hits showing that purchasing card fraud is serious and pervasive. Abuses such as buying goods from vendors that are quite normal for the cardholder (e.g., Home Depot for someone working in maintenance) and then reselling the goods through an auction site or classified adverts will be very difficult to detect. Also, an issue with the approval of charges is that neither the approver, nor the cardholder gets to see the big picture. Given all the complexities of a card program it is not surprising that there is an association for those employees charged with administering the card programs. This is discussed next.

The National Association of Purchasing Card Professionals

The National Association of Purchasing Card Professionals (NAPCP) is an association that provides services and guidance to purchasing card managers. NAPCP provides continuing education and networking through conferences and seminars. The association undertakes and sponsors research in the form of white papers and survey results related to purchasing card matters. They administer the Certified Purchasing Card Professional (CPCP) designation, which requires applicants to pass an exam and to maintain the designation through a process similar to continuing professional education for public accountants and internal auditors. The association's website is www.napcp.org.

The NAPCP regularly polls its members with interesting questions related to purchasing cards. One poll asked members to indicate the percentage of transactions that were audited. The survey defined an audit as an independent review by someone other than the cardholder or the cardholder's manager with or without of the supporting documentation. The results cannot be generalized because the survey was not based on a random sample. Approximately 40 percent of managers said that they audited 100 percent of transactions, about 20 percent of managers audited 25 percent, and about 20 percent of managers audited 10 percent of transactions. The remaining 20 percent of managers were evenly spread across the remaining percentages. The graphical results show a large spike at 100 percent and two medium spikes at 10 percent and 25 percent. The audit rate seems to depend on the organization. Selected written responses are shown here:

• We start with 10 percent and if there are a lot of issues, we increase that percentage.
• We audit 50 different cardholders in detail each month.
• We don't have the personnel available.
• I would like to audit more by using electronic methods. I haven't developed these tests yet.
• Random selection as well as an ad hoc review of out of the ordinary transactions.
• We randomly select the cardholders. We choose new cardholders, cardholders with large monthly purchases, or cardholders that have strange activity of some sort.
• We have about 500,000 transactions annually. We select random departments and random cardholders and all statements are reviewed monthly by managers.
• Because of a lack of resources we do not have a regular audit procedure.
• We have 2,000 cardholders and 130,000 transactions per year. We audit 25 percent and run many high-level reports to look for suspicious activity.
• We look at 2 percent of transactions because of a lack of resources.
• We try to identify risky transactions first and focus on those using this selective approach.
• We go through the cardholder report on a monthly basis looking for split purchases or other issues.

Industry practice varies widely suggesting that the field might be open to the development of Standards of Professional Practice, along the lines of the standards that are applicable to external or internal auditors. The next section shows an example of a purchasing card dashboard.

A Forensic Analytics Dashboard

The internal auditors of a global technology company recently developed an operational dashboard to continuously monitor their purchasing card data. The project was a joint effort between internal audit and the IT support staff. The goal was to monitor several aspects of the program on a continuous basis. The first page of the dashboard was a high-level overview that shows that the monthly amount spent using the cards was about $8 million. The dashboard was created in Excel and a screen shot is shown in Figure 18.1.

Figure 18.1 The Summary Page of an Excel Dashboard

Figure 18.1 shows the summary page of an Excel dashboard. The summary shows the dollar totals and the transaction totals for the preceding six months. Additional statistics regarding plastic cards, strategic cards, and ghost cards are provided. In the lower half of the screen the results are shown graphically. The dashboard shows that plastic cards account for a little more than one-half of the spending. The lower half of the dashboard is shown in Figure 18.2.

Figure 18.2 A Second-Level Analysis in the Excel Dashboard

The second-level analysis of the purchasing card transactions is shown in Figure 18.2. This analysis deals with the plastic card purchases. The table shows the number of employees with a total monthly spend in each of the six ranges. Not surprisingly, most of the dollars are spent by employees with monthly totals above $5,000. Other worksheets in the dashboard system contain more detailed information including reports of a forensic nature. The dashboard was developed by an internal auditor who had an excellent knowledge of the policies and procedures and also the controls related to the use of the purchasing cards. The data procurement and the analysis tasks were done by IT staff, but the process was under the control of the auditor. The company has had its fair share of past instances of fraud and waste and abuse. The dashboard will be updated monthly within three weeks of the end of the month.

An Example of Purchasing Card Data

Card data seldom requires extensive data cleansing. The data tables provided by the card issuers include an extensive amount of descriptive data. In Figure 18.3 the transactions were extracted from the company's Oracle accounting system. There were extra fields that were relevant to accounting and other fields that would have been useful that were omitted.

Figure 18.3 A Typical Table Layout of Purchasing Card Data Extracted from an Oracle System

Figure 18.3 shows the Access table of card transactions imported from an Oracle system. Several useful data fields are missing (transaction time and vendor codes) and also several irrelevant rows are included courtesy of double-entry bookkeeping and the Oracle system. The first step was to delete all records where Account equals 30135 and also where Debit equals $0.00. The result was a table with 276,000 card transactions totaling $75,000,000. This data table has no credits and it would seem that the accounting system only enters the net amount of each purchase. Although this table would work adequately for a forensic analysis, the best data source is the full set of transactions in electronic form as prepared by the card issuer.

High-Level Data Overview

The data used for the case study is a table of card transactions for a government entity. The entity was the victim of fraud in the prior year and management wanted an analysis of the current transactions to give some assurance that the current year's data was free of further fraud. The focus was on fraud as opposed to waste and abuse. The first test was the data profile and this is shown in Figure 18.4.

Figure 18.4 The Data Profile for the Card Purchases

The data profile in Figure 18.4 shows that there were approximately 95,000 transactions totaling $39 million. The total should be compared to the payments made to the card issuer. It is puzzling that there are no credits. This might be because there is a field in the data table indicating whether the amount is a debit or credit that was deleted before the analysis. It might also signal that cardholders are not too interested in getting credits where credits are due. The data profile also shows that about one-third of the charges are for amounts of $50.00 and under. Card programs are there to make it easy for employees to pay for small business expenses. The data profile shows one large invoice for $3,102,000. The review showed that this amount was actually in Mexican pesos making the transaction worth about $250,000. This transaction was investigated and was a special circumstance where the Mexican vendor needed to be paid with a credit card. This finding showed that the Amount field was in the source currency and not in U.S. dollars. Another query showed that very few other transactions were in other currencies and so the Amount field was still used “as is.” There were some Canadian transactions in Canadian dollars but this was not expected to influence the results in any meaningful way. The second high-level overview was a periodic graph. This graph is shown in Figure 18.5.

Figure 18.5 The Monthly Totals for Card Purchases

Because the “$3,102,000” purchase was an abnormal event, this number was excluded from this graph. The graph shows that August and September had the largest transaction totals. The entity's fiscal year ends on September 30th. The August/September spike might be the result of employees making sure that they are spending money that is “in the budget.” The average monthly total is $3 million. The two spikes averaged $4.2 million, which is a significant amount of money. An earlier example of abuse was a cardholder buying unnecessary helmets in one fiscal year, only to return them the next fiscal year and then to use the funds for other purchases. The transactions for 2011 should be reviewed for this type of scheme. In another card analysis a utility company found that it had excessive purchases in December, right around the festive season. This suggested that cardholders might be buying personal items with their corporate cards.

The data profile and the periodic graph are high-level tests that are well-suited to purchasing cards. The high-level overview could also include a comparative analysis of the descriptive statistics which would use the data for two consecutive years.

The First-Order Test

The Benford's Law tests work well on card transactions. It would seem that the upper limit of $2,500 on card purchases would make the test invalid, but this is not the case because most of the purchases are below $1,000 and the $1,000-plus strata is dwarfed by the under $1,000 purchases. Also, the $2,500 limit can be breached if the purchase is authorized. The purchase might also be in another currency and the analysis can be run on the “transaction currency” as opposed to the amounts after converting to USD. The first-order test results are shown in Figure 18.6.

Figure 18.6 The First-Order Results of the Card Purchases, and the Card Purchases that Are $10 and Higher

The first-order results in the first panel of Figure 18.6 show a large spike at 36. A review of the number duplication results (by peeking ahead) shows a count of 5,903 amounts in the $3.60 to $3.69 range. These transactions were almost all for FedEx charges and it seems that FedEx was used as the default mail carrier for all documents larger than a standard first class envelope. Although this was presumably not fraud it might be wasteful because USPS first class mail is cheaper for small documents. It is also noteworthy that a government agency would prefer a private carrier over the USPS. The test was run on all purchases of $10 and higher and the results are shown in the second panel of Figure 18.6.

The first-order test in the second panel in Figure 18.6 shows a reasonably good fit to Benford's Law. The MAD is 0.0015, which gives an acceptable conformity conclusion. There is, however, a large spike at 24, which is the largest spike on the graph. Also, there is a relatively large spike at 99 in that the actual proportion is about double the expected proportion. The spike at 24 exists because card users are buying with great gusto for amounts that are just less than the maximum allowed for the card. The first-order test allows us to conclude that there are excessive purchases in this range because we can compare the actual to an expected proportion. The number duplication test will look at the “24” purchases in some more detail. The “99” purchases showed many payments for seminars delivered over the Internet (webinars) and it seemed reasonable that the seminars would be priced just below a psychological boundary. There were also purchases of computer and electronic goods priced at just under $100. This pricing pattern is normal for the computer and electronics industry. The purchases also included a payment to a camera store for $999.95. This might be an abusive purchase. The procurement rules state that a lower priced good should be purchased when it will perform essentially the same task as an expensive item. The camera purchase was made in August, which was in the two-month window preceding the end of the fiscal year.

The Summation Test

The summation sums all the amounts with first-two digits 10, 11, 12, . . ., 99. The test identifies amounts with the same first-two digits that are large relative to the rest of the population. The results so far have highlighted the large 3.102 million transaction, and the fact that there is an excess of transactions just below the $2,500 threshold. The summation graph is shown in Figure 18.7.

Figure 18.7 The Results of the Summation Test Applied to the Card Data

The summation test in Figure 18.7 shows that there is a single record, or a group of records with the same first-two digits, that are large when compared to the other numbers. The spike is at 31. An Access query was used to select all the 31 records and to sort the results by Amount descending. The query identified the transaction for 3,102,000 pesos.

The summation test was run on the Amounts greater than or equal to $10. The summation test could be run on all the positive amounts in a data set. The expected sum for each digit combination was $433,077 ($38,976,906/90). The 24 sum is $2.456 million. The difference is about $2 million. The drill-down query showed that there were eight transactions for about $24,500 and about 850 transactions for about $2,450 each summing to about $2,250,000. There is a large group of numbers that are relatively large and that have first-two digits of 25 in common. So, not only is the spike on the first-order graph significant, but the transactions are for large dollar amounts.

The Last-Two Digits Test

The last-two digits test is usually only run as a test for number invention. The number invention tests are usually not run on accounts payable data or other types of payments data because any odd last-two digits results will be noticeable from the number duplication test. For purchase amounts this test will usually simply show that many numbers end with “00.” This should also be evident from the number duplication test. The results are shown in Figure 18.8.

Figure 18.8 The Results of the Last-Two Digits Test Applied to the Card Data

The result of the last-two digits test is shown in Figure 18.8. There is a large spike at 00, which is as expected. The 00 occurs in amounts such as $10.00 or $25.00. An interesting finding is the spike at 95. This was the result of 2,600 transactions with the cents amounts equal to 95 cents, as in $99.95.

The last-two digits test was run on the numbers equal to or larger than $10. If the test was run on all the amounts there would have been large spikes at 62 and 67 from the FedEx charges for $3.62 and $3.67. The large spike in the left graph of Figure 18.6 was for amounts of $3.62 and $3.67, which have last-two digits of 62 and 67 respectively. The 62 and 67 spikes are there not because of fraud but rather because of the abnormal duplications of one specific type of transaction.

The Second-Order Test

The second-order test looks at the relationships and patterns found in data and is based on the digits of the differences between amounts that have been sorted from smallest to largest (ordered). These digit patterns are expected to closely approximate the expected frequencies of Benford's Law. The second-order test gives few, if any, false positives in that if the results are not as expected (close to Benford's Law), then the data do have some characteristic that is rare and unusual, abnormal, or irregular. The second-order results are shown in Figure 18.9.

Figure 18.9 The Second-Order Results of the Card Purchases Amounts

The graph has a series of prime spikes (10, 20, . . ., 90) that have a Benford-like pattern and a second serious of minor spikes (11–19, 21–29, . . . .) that follow another Benford-like pattern. The prime spikes are large. These results are as expected for a large data set with numbers that are tightly clustered in a small ($1 to $2,500) range. The second-order test does not indicate any anomaly here and this test usually does not indicate any anomaly except in rare highly anomalous situations.

The Number Duplication Test

The number duplication test analyzes the frequencies of the numbers in a data set. This test shows which numbers were causing the spikes in the first-order test. This test has had good results when run against bank account numbers and the test has also been used with varying levels of success on inventory counts, temperature readings, health-care claims, airline ticket refunds, airline flight liquor sales, electricity meter readings, and election counts. The results are shown in Figure 18.10.

Figure 18.10 The Results of the Number Duplication Test

The number duplication results in Figure 18.10 show four amounts below $4.00 in the first four positions. A review showed that 99.9 percent of these amounts were for FedEx charges. The charges might be wasteful, but they were presumably not fraudulent. A second number duplication test was run on the numbers below $2,500. This would give some indication as to how “creative” the cardholders were when trying to keep at or below the $2,500 maximum allowed. Purchases could exceed $2,500 if authorized. The “just below $2,500” table is shown in Figure 18.11.

Figure 18.11 The Purchase Amounts in the $2,495 to $2,500 Range

The $2,495 to $2,500 transactions in Figure 18.11 show some interesting patterns. The large count of “at the money” purchases of $2,500 shows that this number has some real financial implications. Either suppliers are marginally reducing their prices so that the bill can be paid easily and quickly, or some other factors are at play. Another possible reason is that cardholders are splitting their purchases and the excessive count of $2,500 transactions includes partial payments for other larger purchases. Card transaction audits should select the $2,500 transactions for scrutiny. Also of interest in Figure 18.11 is the set of five transactions for exactly $2,499.99 and the 42 transactions for exactly $2,499.00. There are also 21 other transactions in the $2,499.04 to $2,499.97 range. It is surprising that people think that they are the only ones that might be gaming the system. The review of the eight transactions of $2,497.04 showed that these were all items purchased from GSA Global Supply, a purchasing program administered by the General Services Administration. It seems that even the federal government itself takes the card limit into account when setting prices.

The Largest Subsets Test

The largest subsets test uses two fields in the data table and tabulates the largest subsets (or groups). The subsets could be vendors, employees, bank account holders, customer refunds, or shipping charges per vendor. This test has produced some valuable findings, despite the fact that it is neither complex nor difficult to program. The test can be run in Excel using pivot tables or it can be run in Access using a Group By query. With purchasing cards the subset variable could be cardholders, vendors, dates (a monthly or daily graph of purchases), vendor codes, vendor zip codes, or cardholder by type of purchase (convenience checks or gift cards).

The merchants with purchases of $200,000 or more for 2010 are listed in Figure 18.12. The name of the Mexican vendor for 3,102,000 MXN has been deleted. The largest merchants are all suppliers of technology, scientific, or other business-related products. Some vendors, such as Buy.com also sell items that could be for home use. Internet purchases of home items are easier to detect because the electronic records are reasonably easily accessible. In another analysis of purchasing card transactions the purchasing vice president looked at their equivalent of Figure 18.12 and remarked that there was a vendor on the largest subsets list for $31,000 that was a “hole in the wall restaurant next to the manufacturing plant.” Company employees would have no reason to charge any meals in that restaurant as valid business expenses. Fraud and abuse is therefore not confined to merchants at the top of this list but that a careful look at all the vendors above $10,000 should be done by someone with institutional knowledge.

Figure 18.12 The Largest Merchants for Card Purchases in 2010

The total annual dollars for each card is shown in Figure 18.13. The table shows cards with dollar totals above $200,000. This report should be more detailed by adding the cardholder's names and perhaps some other details (department or job description) to assess the amounts for reasonableness. The second largest amount of $1.433 million should be carefully reviewed. The program had 1,634 active cards and if the total dollars per card (of which the largest amounts are shown above) are tested against Benford's Law then the card totals have a MAD of 0.00219. This result implies marginally acceptable conformity. For this test the auditors would need to know the cardholders and their jobs and responsibilities to assess these numbers for reasonableness. In another analysis the vice president of purchasing saw an amount of $650,000 for a cardholder and immediately recognized that the cardholder was the person that paid the company's cell phone bills by credit card.

Figure 18.13 The Total Dollars for the Individual Cards for 2010

The Same-Same-Same Test

The same-same-same duplications test does not usually have any interesting findings because most payment systems have ways to detect and prevent accidental duplicate payments. This uncomplicated test has shown many interesting results when applied to card transactions. The test was set up to identify (a) same cards, (b) same dates, (c) same merchants, and (d) same amounts and the results are shown in Figure 18.14.

Figure 18.14 Cases of Identical Purchases on the Same Dates by the Same Cardholder

The rightmost field is the count and most cases were for two identical purchases. The exceptions were one case of three identical purchases, two cases of four identical purchases, and one case of six identical purchases. The two largest purchases (for $23,130 and $24,845) would merit special attention because they not only exceed the card limit, but they are close to the limit for convenience checks. Also of special interest would be the purchase labeled “Retail Debit Adjustment.” The initial review showed that the four hotel payments for $2,500 were a $10,000 deposit (to secure a conference venue) split into four payments of $2,500. There were 786 duplicates on the report after limiting the output to dollar amounts greater than $100.

The Same-Same-Different Test

It would seem that this test would give few, if any, results. The test was run to identify (a) different cards, (b) same dates, (c) same merchants, and (d) same amounts. The test provided some remarkable results in another application when it showed many cases where purchases were split between two different employees (usually a manager and their subordinate) to avoid detection with a simple same-same-same test.

The same-same-different results are shown in Figure 18.15. Each match is shown on two lines because there are two different card numbers. Each match has the same amount, date, and merchant, but different card numbers. The near-duplicates could be coincidences and they could also be cleverly split purchases. Split purchases are a willful circumvention of internal controls, and a split purchase might just be a red flag for other fraudulent or wasteful or abusive acts. The transactions above are all interesting and the most interesting near duplicate is the last match because it occurred one day before the end of the federal fiscal year-end, and it is the type of purchase (paper products) that cardholders might use to spend “what's in the budget.”

Figure 18.15 The Largest Cases of Identical Purchases Made on Different Cards

The Relative Size Factor Test

The relative size factor (RSF) test uses the ratio of the largest amount divided by the second largest amount. This test has had valuable findings in many areas including accounts payable amounts and health care payments. Experience has shown that the findings in a purchasing card environment are limited. Even with limited findings, the test is still recommended for inclusion in the set of forensic analytic tests for purchasing card data.

The relative size factor results are shown in Figure 18.16. The results are not very interesting, but the full table should be looked at with a skeptical eye. For this application there were 6,642 merchants. The list could be pruned to 3,300 records by only listing the merchants where the maximum exceeded (say) $500. The most interesting records in Figure 18.16 are the Rampy Chevrolet purchase because auto expenses were prohibited expenses, and the FedEx payment for $1,801.14 because this was a very large amount to pay to FedEx. The payment might be employee relocation costs, which were prohibited under the card rules.

Figure 18.16 The Results of the Relative Size Factor Test

Conclusions with Respect to Card Purchases

The forensic analytic tests run on the card data would fit in well with a continuous monitoring program. The data format would stay the same from period to period and these tests would show valuable results. The test interval could differ among the various tests. The data profile, the periodic graph, the digit-based tests, and the relative size factor test could be run quarterly. These high-level tests work best with a longer time interval. The number duplication test, the largest merchants, and the largest card spenders could be run monthly. Finally, the same-same-same and same-same-different tests could be run every week to quickly discover anomalies such as purchase splitting.

The analytic tests are efficient and effective at detecting large errors and anomalies and large changes in behavior (where a small spender suddenly becomes a large spender). The tests are not very effective at detecting waste and abuse. The tests cannot identify that a $125 limo ride from the airport to a hotel was wasteful. This would require a close scrutiny of the largest vendors report and the ability to see possible waste when XYZ Limo Service appears on the report. Other tests to detect waste and abuse would be to search for specific vendors or vendor codes that might indicate issues. For example, Best Buy sells a range of products that are aimed at people rather than government agencies. A review of the Best Buy purchases might therefore yield some results. The test could extract all purchases where the vendor's name is like “Best Buy.” The results sorted by dollars descending are shown in Figure 18.17.

Figure 18.17 A Series of Best Buy Card Purchases

The Best Buy card purchases are shown in Figure 18.17. The dollar amounts suggest that sales tax was not paid on many of the purchases. Even if a cardholder later reimburses the agency for the purchases, they would have evaded sales tax by using a government purchase card. The $1,749.99 purchases would be of interest to a forensic investigator. These purchases were made just before the end of the fiscal year and the three purchases were made using only two cards.

Other more sophisticated tests could also be used. For example, we could look for split purchases where we have the same merchant, same date, different cards, and different amounts. This differs from the earlier tests where we assumed that the split purchase would be split equally between the two different cardholders. This query would be more complex than the usual same-same-different query. Another test would be to use the largest growth test to identify cardholders with large increases from 2010 to 2011.

The fraud triangle is made up of (1) pressure, (2) opportunity, and (3) rationalization. Without an opportunity the other two components (pressure and rationalization) may exist in vast quantities and there would still be no fraud because the individual would not be able to commit a fraud without opportunity. Purchasing cards give individuals with pressure and rationalization the means (the opportunity) with which to commit fraud. Management that are aware of the relationship among the three components will have an effective and efficient fraud, waste, and abuse program in place to detect and deter employees from making illicit use of their opportunities.

A Note on Microsoft Office

The Office suite allows forensic analytics to be carried out on data tables large enough for most uses. Excel is limited by the row count of 1,048,576 rows and the memory limitations of personal computers while Access is limited to databases up to 2GB in size. These two products are adequate for most forensic applications. It is problematic to continually add new data to Excel worksheets and to add new reports and new functionalities down the road, but it is easy enough for most people to use and is a firm favorite among forensic investigators. In Excel it is difficult to distinguish between data, formulas, and the results of formulas, whereas Access has all of these components neatly compartmentalized. Excel has some advanced reporting features and references to Excel dashboards have become more and more commonplace.

A longtime issue with the digit-based tests is because of the computer's use of floating point arithmetic. In general, 9 is close enough to 8.9999999999999999 for most purposes except for taking the first digit, which is 9 in the first case and 8 in the second case. Data analysts should be aware of this issue and should program accordingly when using any data analysis program mentioned in the book and also any others such as SAS or SPSS.

The size restrictions of Excel and Access can be overcome with the use of more specialized auditing software such as IDEA (www.caseware.com/products/idea). IDEA tables and databases are only limited in size by the user's memory and processing speed. IDEA also has a number of the tests referred to in the book (e.g., correlation, time-series, the summation test, and the second-order test) built in as preprogrammed routines.

The combination of PowerPoint and Word contain many powerful features that make them highly effective for the preparation and presentation of forensic reports. Many aspects of a forensic presentation are important, including the actual content, the color schemes used, the ability to copy from Word and Excel, the ability to copy images to the presentation, and the use or misuse of animations. PowerPoint also acts as a distraction and presenters should work to make themselves, as opposed to their slides, the center of attraction.

Several contenders are coming forward to challenge the dominance of the Office suite. These include Google Docs (http://docs.google.com) and OpenOffice (www.openoffice.org). At the time of writing these two suites lack the processing power and the broad range of functions of the Office suite. The Office suite has many tools that lend themselves to forensic analytics and the ability to send data, document, and presentation files to almost anyone else is a very important consideration.

A Note on the Forensic Analytic Tests

The book reviewed a series of forensic analytic tests in Chapters 4 through 16. Benford's Law forms the theoretical basis of the tests. Benford's Law dates back to 1938 and sets out the expected patterns of the digits in tabulated data. The four main Benford-related tests are the first-two digits test, the summation test, the second-order test, and the last-two digits test. These tests, together with the high-level data overview and the number duplication tests, make up the Nigrini Cycle and it seems that these tests should be applicable to almost every data analysis project.

Several tests are geared toward identifying abnormal duplications. These tests are the largest subsets test and the tests that identify the same-same-same and same-same-different conditions. The tests that use some advanced statistical methods include the relative size factor test, correlation, and time-series analysis. Chapters 15 and 16 showed examples of risk scoring where forensic units are scored for fraud risk.

The tests described in this book range from being quite straightforward to being reasonably complex. The suite of tests is both efficient and effective at detecting many types of fraud, errors, biases, and other anomalies. New tests are currently being developed. One new test that is close to completion is a test to assess by how much a set of numbers has changed from one period to the next. This test will allow an analyst to conclude that the financial statements for fiscal 2011 differ from the 2010 statements by a magnitude of x. The belief is that large changes signal large changes in conditions and could be red flags for errors or fraud. Another test under development assesses by how much a transaction differs from the normal transactions in the data table. The belief again is that it is the high-risk transactions that stand out from the crowd. The tests in the domain of forensic analytics are continually evolving and with the passage of time new tests will be added to the arsenal and perhaps even replace some of the older tests that might be losing some steam in the detection and deterrence of fraud.

The increase in computing power over the past 20 years has made it possible to run complex queries on large data tables. These tests are now not only possible on a personal computer but can be done reasonably inexpensively. This has allowed auditors and investigators to become much more efficient and effective at detecting data issues. These tests can play an active and useful role as detective controls that detect errors or incidents that bypassed the preventative controls.

Conclusion

Fraud is here to stay. The only really surprising fact is that people are still surprised by the discovery of fraud. The financial press and the popular press regularly report on the largest cases. It seems that when people are given the opportunity to commit fraud, many do indeed commit fraud. A few general comments are listed below:

• Forensic analytics is only one part of the forensic investigations process. An entire investigation cannot be completed with the computer alone. The investigation would usually include a review of paper documents, interviews, reports and presentations, and concluding actions.
• It is best to collect and analyze the data at the start of the investigation, and long before the suspect suspects that an investigation is underway. In a proactive fraud detection project the data is automatically analyzed before the suspect has any wind of an investigation.
• Incomplete and inaccurate data might give rise to incorrect and incomplete insights. Data should be checked for completeness and accuracy before being analyzed.
• The data should preferably be analyzed together with a subject-matter expert. Such a person would know company policies and procedures and would prevent valuable hours being wasted by investigating apparent anomalies. For example, the rules, policies, and procedures related to airline baggage claims, and frequent-flyer mileage programs are numerous. What may seem to be an anomaly can often be explained by the subject matter expert. Without a subject matter expert the analysis of something as complex as trader's mark-to-market activities is nearly impossible.
• The legal environment should be in the forefront of the investigator's mind in any forensic investigation. The evidence obtained should meet the standard for admissible evidence. The legal environment is especially complex when dealing with data that spans national boundaries.

Forensic analytics is the analysis of a large number of records to identify signs of fraud, errors, and biases. This is far more efficient than combing through documents. The analysis usually starts with an investigation question or with a mission to proactively look for signs of fraud or errors. The GAO studies reviewed at the start of this chapter were proactive investigations where the auditors went looking for waste and abuse without having a specific suspect in mind.

The first analytics-related task is to identify the data that is easily available. The next task is to identify the additional skills available to the forensic investigator (besides their own abilities). The next consideration is how the forensic analytics phase fits into the investigation as a whole. In a proactive fraud investigation the investigator might want access to the employee master file, payroll data, the vendor master file, the customer master file, cash disbursements, check registers, customer invoices, vendor invoices, and general ledger detail. The investigator should not be surprised if managers along the way simply refuse to provide the data requested. It is quite normal for a marketing manager to refuse to supply customer data, a human resources manager to refuse to supply employee data, and a coupon payments manager to refuse to supply systems data related to access to their payments system. Another important point is that investigators should limit their requests to only relevant data and should take into account the demands usually placed on IT staff. It is usually best to request data in a format that is easy for IT personnel to supply and then for the investigator to do some data-cleansing work before the analysis can proceed, than to ask IT to take time getting the format perfect.

Certain technical skills are expected from forensic investigators. These technical skills include accounting skills, information technology skills, reporting skills, and a healthy dose of patience. A forensic analytics project is an iterative process. If an analysis of card payments shows significant gift card purchases then this might signal a significant type of fraud and abuse. Drilling deeper and deeper into those purchases would be warranted. The investigator might then discover that these cards can easily be sold on Internet auction sites and that might lead to the next step of trying to try to find employees listed as sellers on Internet auction sites.

Forensic analytics is an evolving subdiscipline of forensic accounting. Changes in fraudulent behavior, software upgrades and enhancements, changes in the way that data is accumulated and stored, and changes in priorities will all call for changes in the forensic analytics landscape by way of new and improved detection techniques. There is also a changing corporate environment with managers and directors becoming more aware of the risks of fraud and their ever-increasing obligations to deter, to detect, and to combat corporate fraud. Managers are becoming aware that the costs are not just financial, but also reputational, too. As the world becomes more global, so the ability to conduct fraud across national boundaries increases. These changes make the science of forensic analytics both interesting and in-demand. These changes not only require forensic practitioners to keep up with trends and techniques through publications, training, and conferences, but they also require practitioners to be willing and able to share their successes and best practices with others in the field. This concluding sentence is not the end of the road, but rather the beginning of the trip with exciting findings, an ever-improving technology, and more new techniques in the years ahead.