Test 5 - Inflated Overtime Wage

In a previous Payroll test, we looked for any issues where employees not eligible to receive overtime pay received overtime pay. But what if employees are receiving more overtime pay than they should? Or at a higher rate than they should? In today's test, we'll look for employees who received a heftier slice of overtime pay than they should have. We'll also look for any employees who were paid an inflated overtime rate. 

Oftentimes, overtime rates are governed by, or at least influenced by, labour laws. For our purposes, we'll use 1.25 x the regular hourly wage as our rate for overtime pay, but you can adjust the test as needed to fit your organization's policies. 

Risk

Inflated overtime wages result in excessive costs and indicate a weakness in the control process and could be indicative of fraud or waste.

Objective

Identify issues where employees were paid more overtime than they should have been.

Supporting documentation

Regular Hourly Wage 

Depending on your organization's structure and policies, the regular hourly wage could be calculated in several ways. For our purposes, the regular hourly wage is calculated from the annual salary, based on 250 working days per year and eight working hours per day: 
Regular hourly wage
= Annual salary / (250 * 8)

Overtime Pay

Keep in mind that the rate at which overtime is paid will vary depending on your organization. For our purposes, overtime is paid at 125% of the regular hourly wage. Employees must exceed 9 minutes (0.15 of an hour) overtime during the day in order for it to be paid. 

Analyzing Data

  1. In the Payroll table, create a computed field, c_RegHourlyWage, to calculate the regular hourly wage for each employee:
    • Empmast.salary / ( 250 * 8)
    Keep in mind that depending on your organization, this may be calculated differently.

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

  2. In the TimeCard table, create a computed field, c_TimeWorked, to calculate the hours worked between each time in and time out, using this syntax:
    • DEC((TimeOut - TimeIn) * 24,2)

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))

  3. Now that we've calculated each employee's hours worked per clock in/clock out period, we'll want to calculate the total time per day they each worked. In order to do this, we'll need to first parse the date worked from the TimeIn field by creating a computed field, c_DateWorked, using this syntax:
    • DATE(TimeIn,"MM/DD/YYYY" )

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

  4. Now that we've parsed the date worked for each clock in/clock out period, we can Summarize on Employee Number and c_DateWorked, and subtotal c_TimeWorked to calculate each employee's total time worked per day. We'll want to conduct further analysis on our results, so we'll send the result to a new table, t_TimeCardByEmpDate.

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

  5. Now, in the t_TimeCardByEmpDate table, we'll calculate each employee's overtime hours per day, by creating a computed field, c_OvertimeHours, that subtracts 8 from the c_TimeWorked field. 

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate
    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)

  6. In the t_TimeCardByEmpDate table, create a computed field, c_MonthPaid, to calculate the payment month. In our data, this is the month following the month in which the overtime was worked. The expression is comprised of three nested functions, which means that there are functions used ("nested"), within one another. The order of operations is from the parenthesis nested within, moving outward. The CTOD() function converts the character version of the date to a date version, the MONTH() function parses the month form the date, and the STRING() function converts the numeric month to a character, which is necessary for the summarize command in the next step.

    • STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)
    • CTOD( ) function converts the character field to a date field
    • MONTH( ) function parses the month from the date
    • STRING( ) function converts the numeric month to a character, which is necessary for the summarize command in the next step.

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate
    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)
    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

  7. Then, in the t_TimeCardByEmpDate table, we'll calculate each employee's total overtime per month by Summarizing on the Employee Number and c_MonthPaid fields, and subtotaling the c_OvertimeHours field. Since we'll want to conduct further analysis, let's send the result to a new table, t_OvertimeByEmpMonth.

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate
    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)
    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL" OPEN PRESORT

  1. Now, in the Payroll table, we'll want to create a character computed field, c_Month, that parses the month from the paydate field. This is so that we'll be able to compare the c_Month field to the c_MonthPaid field, so we'll use the syntax:
    • STRING( MONTH( paydate), 2 )

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate
    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)
    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL" OPEN PRESORT

    OPEN Payroll
    DEFINE FIELD c_Month COMPUTED STRING( MONTH( paydate), 2 )

  1. Now, we'll join the Payroll table (primary) with the t_OvertimeByEmpMonth table, so we can compare the overtime paid to what should have been paid. Since we'll want compare each employee's pay per pay period (month), we'll use these key fields:

    Payroll

    Employee Number

    c_Month

    OvertimeByEmpMonth

    Employee Number

    c_MonthPaid

    *Include all primary and secondary fields 
    *Name your new, joined table r_OvertimeComparison

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate
    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)
    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL" OPEN PRESORT

    OPEN Payroll
    DEFINE FIELD c_Month COMPUTED STRING( MONTH( paydate), 2 )

    OPEN Payroll
    OPEN t_OvertimeByEmpMonth SECONDARY
    JOIN PKEY c_Month empno FIELDS bonus c_Month c_Quarter c_RegHourlyWage c_TotalPay commission ddnum empno fedtax grosspay netpay overtime paydate statetax vacationpay vacationdays SKEY c_MonthPaid empno WITH c_MonthPaid c_OvertimeHours COUNT empno TO "r_OvertimeComparison.fil" OPEN PRESORT SECSORT

  2. In the r_OvertimeComparison table, create a computed field, c_OvertimeRecalc, that recalculates the overtime that should have been paid:

    • c_RegHourlyWage * c_OvertimeHours * 1.25
    Keep in mind that your organization's pay rate for overtime may vary.

    OPEN Payroll
    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard
    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))
    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT
    OPEN t_TimeCardByEmpDate
    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)
    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL"
    OPEN PRESORT

    OPEN Payroll
    DEFINE FIELD c_Month COMPUTED STRING( MONTH( paydate), 2 )

    OPEN Payroll
    OPEN t_OvertimeByEmpMonth SECONDARY
    JOIN PKEY c_Month empno FIELDS bonus c_Month c_Quarter c_RegHourlyWage c_TotalPay commission ddnum empno fedtax grosspay netpay overtime paydate statetax vacationpay vacationdays SKEY c_MonthPaid empno WITH c_MonthPaid c_OvertimeHours COUNT empno TO "r_OvertimeComparison.fil" OPEN PRESORT SECSORT

    OPEN r_OvertimeComparison
    DEFINE FIELD c_OvertimeRecalc COMPUTED (c_RegHourlyWage * c_OvertimeHours * 1.25)

  3. Now we can create a computed field, c_OvertimeVariance, that calculates the difference between the actual overtime paid and the recalculated overtime:

    • overtime - c_OvertimeRecalc

    OPEN Payroll

    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard

    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))

    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate

    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)

    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL"

    OPEN PRESORT

    OPEN Payroll

    DEFINE FIELD c_Month COMPUTED STRING( MONTH( paydate), 2 )

    OPEN Payroll
    OPEN t_OvertimeByEmpMonth SECONDARY
    JOIN PKEY c_Month empno FIELDS bonus c_Month c_Quarter c_RegHourlyWage c_TotalPay commission ddnum empno fedtax grosspay netpay overtime paydate statetax vacationpay vacationdays SKEY c_MonthPaid empno WITH c_MonthPaid c_OvertimeHours COUNT empno TO "r_OvertimeComparison.fil" OPEN PRESORT SECSORT

    OPEN r_OvertimeComparison
    DEFINE FIELD c_OvertimeRecalc COMPUTED (c_RegHourlyWage * c_OvertimeHours * 1.25)
    DEFINE FIELD c_OvertimeVariance COMPUTED (overtime - c_OvertimeRecalc)

  4. In the r_OvertimeComparison table, create a computed field, c_RecalcOvertimeWage, that calculates the wage that was actually used to pay each employee's overtime: 

    • (overtime / c_OvertimeHours) / c_RegHourlyWage

    Show me

    OPEN Payroll

    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard

    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))

    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate

    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)

    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL"

    OPEN PRESORT

    OPEN Payroll

    DEFINE FIELD c_Month COMPUTED STRING( MONTH( paydate), 2 )

    OPEN Payroll
    OPEN t_OvertimeByEmpMonth SECONDARY
    JOIN PKEY c_Month empno FIELDS bonus c_Month c_Quarter c_RegHourlyWage c_TotalPay commission ddnum empno fedtax grosspay netpay overtime paydate statetax vacationpay vacationdays SKEY c_MonthPaid empno WITH c_MonthPaid c_OvertimeHours COUNT empno TO "r_OvertimeComparison.fil" OPEN PRESORT SECSORT

    OPEN r_OvertimeComparison
    DEFINE FIELD c_OvertimeRecalc COMPUTED (c_RegHourlyWage * c_OvertimeHours * 1.25)
    DEFINE FIELD c_OvertimeVariance COMPUTED (overtime - c_OvertimeRecalc)
    DEFINE FIELD c_RecalcOvertimeWage COMPUTED ((overtime / c_OvertimeHours) / c_RegHourlyWage)

  5. Create a filter that identifies issues where employees where paid more than the overtime rate of 1.25 and extract to a new table, r_InflatedOvertime:

    • c_RecalcOvertimeWage > 1.25

    Show me

    OPEN Payroll

    DEFINE FIELD c_RegHourlyWage COMPUTED (Empmast.salary / ( 250 * 8))

    OPEN TimeCard

    DEFINE FIELD c_TimeWorked COMPUTED (DEC((TimeOut - TimeIn) * 24,2))

    DEFINE FIELD c_DateWorked COMPUTED (DATE(TimeIn,"MM/DD/YYYY" ))

    SUMMARIZE ON empno c_DateWorked SUBTOTAL c_TimeWorked TO "t_TimeCardByEmpDate.FIL" OPEN PRESORT

    OPEN t_TimeCardByEmpDate

    DEFINE FIELD c_OvertimeHours COMPUTED (c_TimeWorked - 8.00)

    DEFINE FIELD c_MonthPaid COMPUTED STRING(MONTH(CTOD( c_DateWorked,"MM/DD/YYYY" ))+1,2)

    SUMMARIZE ON empno c_MonthPaid SUBTOTAL c_OvertimeHours TO "t_OvertimeByEmpMonth.FIL"

    OPEN PRESORT

    OPEN Payroll

    DEFINE FIELD c_Month COMPUTED STRING( MONTH( paydate), 2 )

    OPEN Payroll
    OPEN t_OvertimeByEmpMonth SECONDARY
    JOIN PKEY c_Month empno FIELDS bonus c_Month c_Quarter c_RegHourlyWage c_TotalPay commission ddnum empno fedtax grosspay netpay overtime paydate statetax vacationpay vacationdays SKEY c_MonthPaid empno WITH c_MonthPaid c_OvertimeHours COUNT empno TO "r_OvertimeComparison.fil" OPEN PRESORT SECSORT

    OPEN r_OvertimeComparison
    DEFINE FIELD c_OvertimeRecalc COMPUTED (c_RegHourlyWage * c_OvertimeHours * 1.25)
    DEFINE FIELD c_OvertimeVariance COMPUTED (overtime - c_OvertimeRecalc)
    DEFINE FIELD c_RecalcOvertimeWage COMPUTED ((overtime / c_OvertimeHours) / c_RegHourlyWage)

    EXTRACT RECORD IF c_RecalcOvertimeWage > 1.25 TO "r_InflatedOvertime.fil"

Conclusion

We have seen that the process for recalculating the correct overtime wage was quite complex, but necessary. Although there may be controls in place to prevent overtime from being paid incorrectly, there is no guarantee that they are working without proving it in this way. If these types or error are being made deliberately, then it increases the concern that other areas of the payroll cycle might be vulnerable.