Test 7 - Minimal Vacation Used

There are numerous obvious fraud "red flags," such as unexplained wealth, but there are also less obvious indicators, indicators that may even be viewed and treated as exemplary work habits. Consider the employee who works long hours and doesn't take vacation time; they're exceptionally dedicated to their work and company, aren't they? Not necessarily. Apart from the socio-emotional and psychological benefits of vacations, they also promote the sharing of duties; another employee is privy to the vacationing employee's work and records in their absence. 

If an employee routinely forgoes or refuses their vacation time, it could be a reflection of their unwillingness to share duties. This in itself doesn't promote a healthy work environment, and may mask an even more troubling concern: the employee doesn't want to share duties because of fear of detection. If you were fudging the books, how willing would you be to hand them over to another who might catch your intentional discrepancies? In this test, we'll look for employees who take less than a specified percentage (75%) of their allowable vacation. Keep in mind that any issues we identify are not necessarily signs of fraud, but in light of all of the benefits of vacations, it's a worthwhile test. Your organization will likely have its own vacation policies, so it's a good idea to become familiar with them and adjust the test accordingly.

Risk

Employees not taking vacation can be detrimental to quality of work and productivity, and may also be indicative of fraud. 

Objective

Identify any employees who take less than a specified percentage of their allowable annual vacation.

Preparing Data

  1. If you were with us for the test in our Payroll series, you've already created a relationship between the Empmast table and the Payroll table. If you're just joining us now, use Employee Number as the key field to create a relationship.

Analyzing Data

  1. In the Payroll table, calculate each employee's allowable vacation days and the number of vacation days taken by Summarizing on Employee Number, subtotaling the VacationPay and VacationDays fields, and selecting Salary and Vacation (Empmast table) as Other fields. Name the new table r_VacaByEmp.

    OPEN Payroll
    SUMMARIZE ON empno SUBTOTAL VacationPay VacationDays OTHER empmast.salary empmast.vacation TO "r_VacaByEmp.fil" OPEN PRESORT

  2.  To add some more context, we might want to calculate how many vacation days each employee has remaining, so let's define a computed field, c_VacaRemain:

    • (vacation) - (vacationdays)

    OPEN Payroll
    SUMMARIZE ON empno SUBTOTAL VacationPay VacationDays OTHER empmast.salary empmast.vacation TO "r_VacaByEmp.fil" OPEN PRESORT

    OPEN r_VacaByEmp

    DEFINE FIELD c_VacaRemain COMPUTED (vacation) - (vacationdays)

  3. Now, we'll create a computed to field, c_VacaUsedPercent, to calculate the percentage of allowable vacation used by each employee:

    • ( DEC( vacationdays, 2)) / DEC( vacation, 2)

    Since both our vacationdays and vacation fields don't have any decimals, we'll use the DEC( ) function so that our c_VacaUsedPercent field will have two decimal places. Why is this is important? When we apply our filter to look for employees who used less than 75% of their allowable vacation, we'll use .75 in the expression, so we'll want the field that we're filtering to have to two decimal places as well.

    OPEN Payroll
    SUMMARIZE ON empno SUBTOTAL VacationPay VacationDays OTHER empmast.salary empmast.vacation TO "r_VacaByEmp.fil" OPEN PRESORT

    OPEN r_VacaByEmp
    DEFINE FIELD c_VacaRemain COMPUTED (vacation) - (vacationdays)
    DEFINE FIELD c_VacaUsedPercent COMPUTED ( DEC( vacationdays, 2)) / DEC( vacation, 2)

  4. Next, we'll apply a filter to see which employees used less than a specific percentage of their allowable vacation, using this syntax: 

    • c_VacaUsedPercent < .75

    For our purposes we'll use 75%, but depending on your organization's structure and policies, you may use a different metric.

    OPEN Payroll
    SUMMARIZE ON empno SUBTOTAL VacationPay VacationDays OTHER empmast.salary empmast.vacation TO "r_VacaByEmp.fil" OPEN PRESORT

    OPEN r_VacaByEmp
    DEFINE FIELD c_VacaRemain COMPUTED (vacation) - (vacationdays)
    DEFINE FIELD c_VacaUsedPercent COMPUTED ( DEC( vacationdays, 2)) / DEC( vacation, 2)

    SET FILTER TO c_VacaUsedPercent < .75

  5. Extract your results to a new table, r_EmpVacaLessThan75.

    OPEN Payroll
    SUMMARIZE ON empno SUBTOTAL VacationPay VacationDays OTHER empmast.salary empmast.vacation TO "r_VacaByEmp.fil" OPEN PRESORT

    OPEN r_VacaByEmp
    DEFINE FIELD c_VacaRemain COMPUTED (vacation) - (vacationdays)
    DEFINE FIELD c_VacaUsedPercent COMPUTED ( DEC( vacationdays, 2)) / DEC( vacation, 2)

    SET FILTER TO c_VacaUsedPercent < .75

    EXTRACT RECORD TO "r_EmpVacaLessThan75.FIL"

Conclusion

Ensuring that employees take at least a certain percentage of their allowable vacation is good practice as it promotes a healthy work/life balance, increases quality and productivity, and helps protect against fraud. Sometimes, organizations will specify a minimum number of continuous vacation days to further promote a healthy life/work balance and protect against fraud. It's much easier to cover up illegal activity when you're only away from the office for a day or two; it becomes increasingly more difficult to do so the longer you're away.