Year 30 + P-card fraud

I didn’t realize how quickly it would take to get to 30 years when posting one blog per week for each year (30 weeks).  Even drawing some of the posts out to two weeks didn’t add much.  So now I am posting additional analysis performed over the years.  Another thing I didn’t take into account was that I would continue to perform analysis – even after I retired.  So I will likely have enough to continue to post – maybe not every week since I am trying to slow done a bit.

I have often said that I never performed the same audit twice.  This is not entirely true.  I certainly have perform Accounts payable and Payroll more than once, but for different organizations.  I have also done a variety of audits around contracting and construction or major capital projects.  But I have never implemented the same audit program twice.  There was always new risks, additional concerns, and different analysis to be done.  This has made every audit a unique challenge.

The audit that comes closest to be repetitive is p-cards.  I first mentioned this in my Year 2000 post which described a standard set of analysis I performed to find misuse, abuse and fraud in p-card charges.  It started because I was tasked to assist the USA IG with some complicated analysis looking at totals by cardholder within any 5 day period.  The audit of p-cards continued with my own company and the standard analysis scripts I have developed have been used over and over again in various organizations.

Perhaps not coincidentally, I was asked to develop and analysis program for p-cards again a couple of weeks ago.  Many of the tests were the same as I had performed numerous times.  The usual risks such as split transactions to avoid financial limits, duplicates to detect merchant fraud; personal expenditures charged to corporate p-cards, etc.  But this time the organization actually had a list of prohibited Merchant Category Codes (MCC) that could verify by individual cardholder.  This particular test was made more difficult because the list of prohibited MCCs was formatted like “4511, 3351-3499, 7512, 3500-3999, 7011, 4814-4815, 5541-5542, 7523-7524, 4112, 4817-4821”. While I did develop a script that expanded “3351-3499” into “3351, 3352, 3353, …. 3499” using nested loops, I thought there might be an easier way so I posted the question on the ACL Peer Community (aka User Forum).  One of the regulars, Thomas Larson, posted a much easier script that used BETWEEN() when there was a range such as “3351-3499”, and a FIND() when it was a single MCC. However, this is not the point of my story.  My point is – once again I found misuse, abuse and possibly fraud (still needs to be reviewed and verified) in p-card transactions.

P-card were introduced because they are cheaper than invoices, and have additional controls both at the bank and at the organization level.  Why then do I consistently find issues with p-card transactions?  The single most contributory cause is management review of p-card transactions.  Employees who have been assigned a p-card are often asked to sign off an official looking form that says that they understand the rules around p-card use (basically, only to be used for business purposes that comply with policy).  As a second level of control, the employee’s manager (or a p-card manager) is tasked with reviewing their employee’s use of the p-card.  For some managers this can mean review 50-100 employees p-card usage which amounts to thousands of transactions.  Since many are small dollar, managers can be less than diligent, providing employees with the opportunity they need to commit fraud.

However, sometimes it is a higher level manager who commits the fraud.  For example, we had one such fraud in the city where I work. Finance director at charity organization charged personal expenses to her corporate credit card including: $78K in home furnishing and new appliances; $69K in groceries; $50K for gas and car repairs.  She covered the expenses for 8 years with transfers ranging from $663.03 to $40,500.00 from various accounts.  A simple review of transactions by MCC would have identified this fraud in the first few months.

Back in 2000, the office of the Inspector General in the US did a government-wide audit and identified the following control weaknesses – which based on audits I have been involved in – are also applicable in non-government companies.  These include:

  • Inadequate review of purchases by approving officials
  • Unmanageable span of control
  • Excessive number of cardholders
  • Exceeding authorized purchase limits
  • Lack of/inadequate documentation
  • Inappropriate purchase methods
  • Unrecorded accountable property
  • Lack of security over purchase card
  • Inadequate training for cardholders and approvers
  • Inappropriate financial coding
  • Inadequate reconciliation

I have seen numerous cases where:

  • The approving official’s review is the most essential element of the p-card control system. The approver should ensure purchases are appropriate and charges are accurate.  At the same time, the span of control can be quite large (1000’s of cardholders) making it difficult to perform adequate review
  • Cardholders have developed unique ways to get around purchase limits, including one I know of that had a consultant who was working for them – write a letter to the credit card company to get the limit to be raised – it was.
  • People confuse having a credit card with “authority to purchase” and are able to bypass purchasing controls
  • Items that are purchased are often not recorded in any corporate system – this includes computers and other expensive and attractive items
  • Cards are lost, stolen, misplaced and often not reported
  • Financial coding is often “general office supplies” even though many different items can be purchased and it is difficult to reconcile transactions.


Lessons Learned: the implementation of an improved system of controls (p-card versus accounts payable invoices) can still have serious weaknesses and must be assessed.  Also, when you are relying on managers to perform (manual) reviews of thousands of transactions, the likelihood of this being a good control is small.

In addition, control weaknesses in one company or one portion of a company, likely exist elsewhere.  When performing a fraud risk assessment be sure to look at what is happening in your own company and others.  Fraud schemes are often repeated whenever and wherever similar control weaknesses exist.

Lastly, despite close to 30 years of using ACL, I can and do ask for help.  Some of the users on the Peer Community have analytical skills that put mine to shame; and they offer them freely to those of us who ask for help.

Year 26 – 2013 – Payroll

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

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

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

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

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

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


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

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

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

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

Year 25 – 2012 – Vacation Leave and Sick days

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

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

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

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

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

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

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

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

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

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


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

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

ACL Connections 2016

Sorry, I missed posting last week as I was at ACL Connections 2016.  It was another great conference – lots of experienced users as well as new users.  The user forum folks got to together for a dinner and had a chance to meet face-to-face.  I asked each of them to describe a “cool” analysis they had done and almost everyone talked about use REGEXFIND() or REGEXREPLACE() which made me realize that I have to buckle down and learn these powerful functions.

Lesson-Learned – you can’t stop learning about new features and capabilities.

I was ask to give a short presentation on the future of analytics and decided to post my speaking notes here.  I looks back in order to look forwards – discussing what was happening in audit and analytics; and how ACL responded.

1980’s – Analytics wasteland

  • I started my audit career in the late 1980’s, but I had already been using computers for 8-10 years.  These were primarily mainframe or mini-computers.   I used punched cards to submit my programs to the computer and had to understand and use JCL, TSO and ISPF as well as the application software or program I was running.
  • My first PC cost over $10K and did not have a hard drive; but things were changing rapidly, and 6 months later we acquired an IBM XT which had a 10Mb hard drive.
  • Audit was primarily concerned about being an early warning to management that things had gone wrong.   Kind of an oxymoron – but management wanted to find out about what went wrong before anyone else did – so we were an early warning of past errors.
  • For my first audit I performed a manual review of hundreds of thousands of telephone calls – by flipping through several feet of computer reports.  In another audit, I manually entered thousands of amounts into an adding machine to verify report subtotals.  There had to be a better way.

1990’s – growth of analytics

  • Audit took on a “control” perspective.  Audits sought to determine if control were adequate and effective.
  • We were starting to see the use of computers in audit.  The concept of 100% testing rather than sampling was being explored.  However, it was usually only one or two individuals; and analysis was performed for a single purpose.
  • Data was still very much mainframe based – so you had to know how to extract and download the data.  In the early 1990’s download speeds were approaching 1Mb an hour.  But there were other roadblocks: IT felt they owned the data and getting it was difficult.  You had to address questions about: audit’s authority to access the data; security; use of personal information; and the ability to understand and analyze the data – so not much has changed in 25 years on this front.
  • ACL for DOS and MVS (Mainframe) was introduced – followed in the mid-90’s by ACL for Windows.
  • ACL also started offering training and consulting services – so auditors had analytics help for the first time.

 2000’s – analysis takes hold

  • Audit is now looking at risk rather than focusing on controls.
  • Data is stored in large integrated ERP systems with centralized data bases
  • Analysis is used in all aspects of the audit process
  • More people performing analysis and scripts are being built to re-run analysis
  • ACL responded to the increase in ERP systems by developing Direct Link for SAP
  • ACL also entered a new phase of advocating on behalf of auditors; working with the IIA and other professional organizations; producing whitepapers and technology guides.

2010-2015 – analysis established

  • Audit is adapting a more continuous approach; looking at efficiency and effectiveness of business operations; using analytics to provide not only hindsight, but also insight and foresight
  • Analysis is being used not only to examining controls, but also to perform risk assessment and monitoring
  • ACL introduces AX and GRC – expanding into non-audit areas and supporting a more structured analytics environment; improving workflow and documentation; and providing everyone with access to results.
  • ACL continues to support its users with ACL Forum, User Group, Inspirations and the ACL Academy

2016+ – analysis flourishes

  • Audit will be focused more on emerging risk and the identification and assessment of business opportunities – adding even more value to senior management.
  • We will continue to see the integration of audit, compliance and monitoring functions
  • Analysis will be more instantaneous – fully automated risk assessment and control testing
  • Data analysis will have an even larger impact and more people will be involved
  • Big Data will increasingly be used to identify emerging risk and opportunities
  • ACL will take more of an enterprise view of analytics and provide increased analysis functionality – in order to be able to handle Big Data – such as the integration with R and Python

 Analytics will be used for:

  • Continuous risk and control assessment
  • Artificial intelligence/machine learning to identify  fraud risk and anomalies
  • On demand data-mining to support management
  • Assessment of business opportunities (e.g. M&A activities, new product lines, etc.)
  • Assessment of KRIs and KPIs related to strategic objectives

There will be three tiers of analytics users:

  1. Data scientists – who build the data repository as well as the real hardcore analytics
  2. Local data analytics champions – who will be able to build impactful analytics for the teams in their regions against the central data repository
  3. Analytic consumers – who will act upon the analytics provided – includes auditors, managers, compliance and monitoring functions

Auditors will need to understand:

  1. Integrated business processes
  2. The data side of business risks and opportunities
  3. How KRIs affect KPIs and impact strategic objectives\
  4. How to interpret the results of analytics
  5. How to draw conclusions and make recommendations in real time – based on the results of analytics.  No more taking 6 months to produce a final report.
  6. How to present the results of analytics to management – who often don’t know the difference between mean, median and mode.

It will be challenging and exciting – I hope you enjoy the ride.

Year 21 – 2008 – part 2 – Payroll and Vacation

No having to rely on samples allowed us to perform audits more efficiently and effectively.  The audit results were more easily defendable (no arguments about the representativeness of the sample and the validity of the extrapolation).  It also supported better coverage and more comprehensive audits.

Vacation audit – the company allowed employees to carry forward vacation days – but different unions had negotiated different amounts.  Depending on the union to which they belonged, some employees were only allowed to carry forward one year’s worth of vacation credits while others were allowed to carry forward up to five year’s worth.  As part of an audit of vacation leave, the VP of Finance was interested in determining the liability (vacation days * daily pay rate) and if employees were carrying more vacation days than permitted.

Using HR data, we were able to determine the union and years of service – which gave us the vacation entitlement.  The calculation was a little complex because the vacation entitlement depended on years of service and the union to which the employee belonged.  Next, we calculated the balance (entitlement – usage) for each employee.  Finally, we calculated the liability (Balance * daily salary rate).

The analysis identified a large number of employees who were carrying more than the maximum vacations days they were allowed and determined that the financial liability had been significantly understated.

Continue reading Year 21 – 2008 – part 2 – Payroll and Vacation

A little about me…

Before I begin, I must recognize the support from my wife (aka “Kitten” – which she does not like to be called). Not only did she allow me to take a year’s leave without pay so I could write my books and kick start my consulting business, but she was also my go-to person when I need help with the logic and programming behind a difficult analysis. Many the times I have explained my problem and the functionality of ACL so that she could write the pseudo-code for me which I then converted to ACL commands. Typically it took her less than 30 minutes to program something that I had been working on for days (and she doesn’t know ACL).

My current plan is to write a blog that includes snippets from each of my 28 years of audit and my consulting both during and after my ‘retirement’. We will see if I manage to accomplish my goals – it is already several months since I retired and I only recently figured out how to develop and maintain a blog (with help from my daughter).

In this blog I have included estimates of the saving identified through the analytics, but perhaps more important are the lessons-learned at each stage of my development. I have learned many things from both the effort involved in performing the task and my mistakes in doing so. Some of the lesson-learned were evident immediately, other were only recognized many years after the initial event.

Lastly, please publicize this blog far and wide to allow everyone to learn from the contents. Thanks, and I hope you enjoy the read.