Taking a close look at payroll risks can enable internal auditors to help their organizations save money and identify wrongdoing. ayroll can amount to 40 percent or more of an organiza- tion's total annual expenditures. Payroll taxes, Social Secu- P rity, Medicare, pensions, and health insurance can add several percentage points in variable costs on top of wages. So for every payroll dollar saved through audit identifica- tion, bonus savings arise automatically from the on-top costs calculated on base wages. Different industries will exhibit different payroll risk Christopher Kelly profiles. For example, firms whose culture involves salaried Frans Deklepper employees who work longer hours may have a lower risk of payroll fraud and may not warrant a full forensic approach. Organizations may present greater opportunity for payroll fraud if their workforce patterns entail night shift work, variable shifts or hours, 24/7 on-call coverage, and employees who are mobile, unsupervised, or work across multiple locations. Payroll-related risks include over-claimed allowances, overused extra pay for weekend or public holiday work, fictitious overtime, vacation and sick leave taken but not deducted from leave balances, continued payment of employees who have left the organiza- tion, ghost employees arising from poor segregation of duties, the vulnerability of data outputted to the bank for electronic payment, and roster dysfunction. Yet the personnel assigned to administer the complexities of payroll are often qualified by experience more so than by formal finance, legal, or systems training,thereby creating a competency bias One way to analyze payroll cost is over how payroll is managed. On top through a distribution analysis of aggre- of that, payroll is normally shrouded in gate salary data. This can be obtained secrecy because of the inherently private by stratifying 12 months of earnings by nature of employee and executive pay. individual employees in a distribution Underpayment errors are less probable chart, to show the composition of sala- than overpayment errors because they ries across the entire workforce, from are more likely to be corrected when the small number of highly paid execu- the affected employees complain; they tives to lower-paid, urskilled labor (see are less likely to be discovered when "Distribution of Total Payroll Costs" on employees are overpaid. These systemic page 47). Typically the distribution will biases further increase the risk of unno- skew to the left because not all employ- ticed payroll error and fraud. ees will have worked a full 12 months. All these factors make assuring Some employees may have joined or payroll controls entail a great deal of departed the organization during the year, and not all employees will be employed full-time. What this chart Internal auditors may add greater value shows is how the mean salary level compares to the industry and whether by launching the audit with a top-down or not the shape of the distribution is analysis of total payroll cost. what management would expect. An insightful audit rest can be to ask management how it expects salaries to be distributed above and audit work chat can easily leave audi- below the average. For instance, the tors disoriented in details. Payroll two peaks shown in the chart reveal risk's silver lining is that it can provide that many employees were paid close opportunities for auditors to uncover to the average (the left peak). while actual cost savings and labor produc- a significant number were paid well tivity gains. above average (the right peak). Further analysis will reveal how this is attrib- HELICOPTER ANALYSIS ured to additional carnings such as It is tempting to start a payroll review overtime, late night or weekend pay, by auditing payroll compliance, such as and allowances. checking that salary rates are in accor- Using the same source, depart- dance with appropriately authorized mental data concentrations can be contracts or checking that time sheets graphed in a bubble chart where each agree with clock in/out times. How- bubble represents a department or cost ever, internal auditors may add greater center (see "Average Total Payroll Cost value by launching the audit with a by Department" on page 48). These cop-down analysis of total payroll cost charts highlight areas for audit ques- and using that perspective to inform tioning, such as where weaknesses in the detailed tests needed to provide internal control may have permitted assurance about the effectiveness of some employees to be overpaid. controls around the most crucial risks. If auditors omit a helicopter overview Remuneration Payroll data analysis of payroll data and the payroll process, can reveal individuals or entire teams they risk performing detailed work who are unusually well-remunerated where it is less needed while missing because team supervisors turn a blind out on significant discoveries. eye to payroll malpractice, as well as 46 INTERNAL AUDITOR APRIL 2016The average payroll fraud incident lasts 241 months and cost US$50,OOO, notes the Association of Certified Fraud Examiners' 2014 Report to the Nations on Occupational Fraud and Abuse. DISTRIBUTION OF TOTAL PAYROLL COSTS 350 120 Finance, Information and Communications Technology, Human Resources, and admin support staff across organization. 300 100 Sales, manufacturing and engineering. 250 Distribution left skewed due to weighted average salary + benefits part year earnings of starters, approximately AU$42.253 (excludes leavers, and part-timers. starters, leavers, and part-timers). 200 Number of employees 4% cumulative 150 Small number of 40 100 senior executives. Employees on significantly higher SO pay than expected. $123 $126 Annual salary in AU$ thousands, including overtime, holidayweekend pay, benefits, and allowances Individual annual eamings Cumulative earnings low-remunerated personnel who rep- are kept updated with operational resent excellent value to the organiza- needs, daily time and attendance con- tion. For example, it can identify the trols, overtime approval, time sheet night shift worker who is paid extra data entry, employee sick leave, leave for weekend or holiday work plus approval, and how internal controls can overtime while actually working only potentially be overridden. The data on half the contracted hours, or workers which pay is calculated originates in who claim higher duty or tool allow- these often manual subprocesses, which ances to which they are not entitled. are reliant on employee honesty and are In addition to providing management vulnerable to error and fraud, translat- with new insights into payroll behav- ing into real payroll dollars. iors, which may in turn become part Rosters should be designed to of ongoing management reporting, the optimize the allocation of employees total payroll cost distribution analysis to operational needs. If done well, can point auditors toward urgent pay- rosters should eliminate, or at least roll control improvements. minimize, the need for overtime and weekend work. Therefore, if the Rosters Process analysis also can help analysis of earnings across the work- steer the detailed audit test program. A force shows departments where over- payroll process overview can encompass time, holiday, or weekend bonus pay how staff duty rosters, or schedules, is higher than expected, this might APRIL 2016 INTERNAL AUDITOR 47ON THE HUNT FOR PAYROLL FRAUD AVERAGE TOTAL PAYROLL COST BY DEPARTMENT $70,000 $65,000 $60,000 Finance IT $55,227 Manufacturing Sales $55.580 $58,673 $55,000 $56,441 Engineering $53,341 $50,000 Safety $46,280 Average Salary AUS $45,000 $40,000 Human Resources Customer 538,755 Support $35,000 $37.500 Trainees $32,686 $30,000 $25,000 $20,000 200 400 600 800 1000 1200 Number of Employees indicate roster dysfunction, neglect of cycle, which canceled 12 pay cycles internal controls, or under-staffing. per annum and eliminated risks The helicopter overview may identify around paying executive employees business units that require special half a month's wages in advance. The audit examination. changeover also increased the accuracy of the attendance and leave recording, Process Efficiency Similarly, the because all employees went onto the TO COMMENT efficiency of the payroll process can be same fortnightly pay cycle. Permanent on this article, considered. Organizations sometimes efficiency savings like this are a tangible EMAIL the run multiple payroll processes across way for internal audit to add value. author at christopher. different sites such as between white- kellyathelia.org collar and blue-collar workforces or DISCOVERING DIAMONDS arising from historic business mergers. IN THE DETAIL Efficiency savings may be achievable Using the helicopter overview to through collapsing multiple payrolls generate insights into the payroll into a single cycle. At one organiza- subprocesses most vulnerable to fraud tion, auditors found that the monthly and error can position internal audit executive mid-month payroll cycle to mine the rich payroll data to either easily collapsed into the assure the board that all is well orPayroll fraud comprises 16 %% of fraud in small businesses and 8 %% in other companies. It's most common in religious, charitable, and social service organizations, the 2014 Report to the Nations notes. DATA MINING TIPS D ownloading and analyzing data across multiple numeric strings as numbers, whereas auditors may pre- ources is not easy, but doing so can be a worth- fer to use telephone numbers as text strings for sorting while investment in enhancing audit effectiveness. and lookup. When importing, Excel can also misinter- Depending on internal audit's organizational status, pret the international telephone dialing symbol "+" as a access to data may need to be negotiated with the rel- mathematical operator. If telephone call logs are being evant custodians, subject to local privacy restrictions matched against an electronic telephone directory, the and audit right of access. auditor may need to convert all telephone log data into Once obtained, downloaded data usually arrives in text format to preserve leading zeros; otherwise they disparate formats, most commonly text (TXT, RTF) or will be truncated and mismatched if the imported tele- comma-separated values (CSV), which in turn may be phone data is converted to numeric format. To avoid variously imported into Microsoft Excel or other spread- Excel stripping the leading zeros, telephone numbers sheet software in ways that impede audit analysis. can be preceded by an apostrophe ("), using the CON- For example, data containing numbers with slashes or CATENATEO formula, or by using Excel's TEXT(cell_ref, hyphens may be converted into dates, and numbers and "#") formula where "#" can be substituted with a colons may be converted into time values. variety of syntaxes. Parsing is another technique if Data containing characters deemed as wildcards data fields contain consistent patterns of numeric and by Excel, such as "*" and "?" may need to be replaced alphabetic data. If all else fails, it may be easier to trim (using "-") to ensure Excel does not treat the character all leading zeros in both the telephone log data and the as a wildcard. Numbers with leading zeros such as tele- lookup table by treating both as numeric fields rather phone numbers may be imported as integers with the than text. Once telephone call data is obtained, it can leading zeros truncated, making them difficult to cross- be traced to available phone number lists. Even Google match with a telephone directory. yields a surprising amount of information if auditors type in a telephone number. Time Data mining envisaged here often involves the analysis of time, which can be complicated in Excel, For Email Data associated with email can provide both analyzing time sheets and clock in/out data, Excel's date and time of day transactional information as well DATEVALUE() and TIMEVALUE() functions can assist as the content of the written messages, themselves. with converting cells containing a mix of date and time Email software such as Microsoft Outlook often into date only or time only values either in AM/PM or enable users to export entire mailboxes as plain text, 24-hour clock format, which can then be sorted and Comma separated files, Excel-readable files, and other analyzed. Excel does this by dividing each second into formats for advanced searching. one 86,400th of a day-that is 60 seconds x 60 minutes x 24 hours. So 1/86,400 is one second after midnight, The above are just some of the ways to scrub data 86,399/86,400 is one second before midnight, and 0.5 before audit analysis, In the event the data needs to is midday. Complementary to that, dates in Excel are be recreated at a later date-for example, if a legal numbered in positive integer sequence from 1 (Jan. 1, situation arises- it is helpful to ensure the data- 1900). So logically, the date value is the positive inte- scrubbing methodology is documented in the internal ger and the time value is the decimal component, Both audit workpapers. Over time, this array of cleansed dates and times can then be sorted and used in calcula- data can become a valuable research lab kept up to tions and pattern-seeking, which can then be presented date to support future audits. back to management as a candlestick chart showing Compiling and analyzing data is worth the effort. actual hours worked compared to the day-by-day ros- Findings informed by the organization's own data tered shift over a period of several weeks or months. become harder to refute. Sometimes findings can be sufficiently startling that management will implement Telephone numbers Telephone numbers can present audit's recommendations quickly and decisively to show another challenge because Excel automatically imports they have corrected the problems.ON THE HUNT FOR PAYROLL FRAUD otherwise expose potential wrongdo- disciplinary records. Or, auditors could deducting leave taken and ing. Available data likely includes each invert those factors to find the unrecog overstating leave balances. employee's start time, finish time, nized exemplary performers. 23 Employees who moonlight in hours worked, location worked, vaca- Where audit findings suggest fraud businesses on the side during tion dates, sick time, standard pay concerns about identified employees, normal working hours, some- races, night-shift pay rates, overtime internal audit can add value by tri- times using the organization's pay rates, and allowances. To accom- angulating time sheet claims against equipment to do so. modate the volume of data, payroll external data sources such as site access The problems are magnified where systems typically contain a job posi- biometric data, company cell phone supervisors collude with their employ- tion master file, employee master file, logs, phone number caller identifica- ces by approving exaggerated time and time sheet transaction history tion, GPS data, company email, Inter- sheets and perpetuate the culture by holding all hours worked as well as net usage, company motor fleet vehicle inducing others to engage in what audi- leave, which in turn update balances tolls, and vehicle refueling data-most tors may see referred to as "custom and across all leave types. Additionally, of which contain useful date and time- practice." When analyzed systematically the organization's human resource of-day parameters (see "Data Mining and corroborated with other intel- systems may hold data on perfor- Tips" on page 49). Before taking this ligence such as whistleblower informa- mance appraisals, competencies, and approach, CAEs should consider the tion, these disparate data sources can disciplinary history that frequently is audit committee's risk appetite, internal reveal systemic fraud. MAKING A DIFFERENCE Management welcomes findings that Often management welcomes audit findings that reveal specific wrongdoing reveal specific wrongdoing because because they provide hard-to-dispute they provide hard-to-dispute evidence. evidence with which to remedy low- performing teams, discipline or ter- minate unproductive personnel, and sharpen finance and management focus linked to the employee number used audit's data access rights, and local pri- on cost control. These are audits that for payroll purposes. vacy laws. make an impact. The detail inside these databases The data buried within these Well-researched and documented can reveal hidden information, Who are databases can reveal employee behavior, audit fieldwork can support manage- the highest earners of overtime pay and including what they were doing, where ment action against those who may why? Which employees gained the most they were, and who they were interact- have defrauded the organization or from weekend and public holiday pay? ing with throughout the work day. work teams that may be taking inappro- Who consistently starts late? Finishes Common findings include: priare advantage of the payroll system. early? Who has the most sick leave? Employees who leave work Simultaneously, internal auditors can Although most employees may perform wrongfully during their shift. partner with managerment to recover a fair day's work, the audit analysis may 1 Employees who work fewer historic costs, quantify future savings, point to those who work less-some- hours and take sick time during reduce reputational and political risk, times considerably less-than the time the week to shift the workload improve the organization's policies, and for which they are paid. to weekends and public holi- boost the productivity and morale of Joined-up query combinations to days to maximize pay. employees who knew of the wrongdo- search payroll and human resources * Employees who use company ing but felt powerless to stop it. IF data can generate powerful insights into property excessively for personal the organization's worst and best ourli- purposes during working hours. CHRISTOPHER KELLY, DPROF, FCA. ers, which may be overlooked by the 10 Employees who visit vacation is partner with Kelly & Yang based in Mel- data custodians. An example of a query destinations while on sick leave. bourne, Australia combination would be: employees with > Employees who take leave but FRANS DEKLEPPER, is senior software high sick leave + high overtime + low whose managers do not log engineer at Callista Software Services performance appraisal scores + negative the paperwork, thereby not in Melbourne