Test 2 - Departmental Comparison

One way to identify potential payroll issues is to calculate the amount spent on salaries by department periodically, such as monthly or quarterly. Then, we can compare each department period by period and identify excessive variations. With this type of comparison, it's important to understand the nature of each department because some might genuinely have greater periodic changes than others. A sales department, for example, will probably have significant swings  because a large portion of employee remuneration is often commission-based. 

With this type of analysis, it might also be useful to compare the total number as well as the total cost of payroll transactions for each department per period. Using the sales department example, you may see significant swings in pay amounts, but if the number of pay transactions is static, we know that the headcount hasn't changed. 

Risk

Significant swings in payroll costs and/or headcount from period to period could be the result of unauthorized payments.

Objective

Compare each department's total pay and number of transactions for each calendar quarter of year 2014 and identify issues where there is a significant change between quarter 1 and quarter 2.

Analyzing Data

  1. The first thing we want to do is calculate the total amount paid per paycheck issued, so let's create  a computed field in the Payroll table, c_TotalPay, that adds the GrossPay, VacationPay, Overtime, Commission, and Bonus fields.

    OPEN Payroll
    DEFINE FIELD c_TotalPay COMPUTED grosspay + vacationpay + overtime + commission + bonus

  2. Now, we'll want to identify the calendar quarter for each pay date by creating a conditional computed field, c_Quarter:
    • The default value will be "Invalid". 
    • "Quarter 1" IF BETWEEN( paydate, `20140101`, `20140331`)
    • "Quarter 2" IF BETWEEN( paydate, `20140401`, `20140630`)
    • "Quarter 3" IF BETWEEN( paydate, `20140701`, `20140930`)
    • "Quarter 4" IF BETWEEN( paydate, `20141001`, `20141231`) 

    OPEN Payroll
    DEFINE FIELD c_TotalPay COMPUTED grosspay + vacationpay + overtime + commission + bonus

    DEFINE FIELD c_Quarter COMPUTED
    AS "c_Quarter" 
    "Quarter 1" IF BETWEEN( paydate, `20140101`, `20140331`)
    "Quarter 2" IF BETWEEN( paydate, `20140401`, `20140630`)
    "Quarter 3" IF BETWEEN( paydate, `20140701`, `20140930`)
    "Quarter 4" IF BETWEEN( paydate, `20141001`, `20141231`)
    "Invalid"

  3. Now we want to calculate the number of paychecks issued and the total amount issued to each department each quarter. We'll do this by running the Cross-tab command and sending the result to a new table, r_PayrollByDeptQuarter. 

    • For the rows, select workdept (from the related table, Empmast)
    • For the columns, select c_Quarter
    • Subtotal c_TotalPay
    • Check the Include Count checkbox

    OPEN Payroll
    DEFINE FIELD c_TotalPay COMPUTED grosspay + vacationpay + overtime + commission + bonus

    DEFINE FIELD c_Quarter COMPUTED
    AS "c_Quarter"
    "Quarter 1" IF BETWEEN( paydate, `20140101`, `20140331`)
    "Quarter 2" IF BETWEEN( paydate, `20140401`, `20140630`)
    "Quarter 3" IF BETWEEN( paydate, `20140701`, `20140930`)
    "Quarter 4" IF BETWEEN( paydate, `20141001`, `20141231`)
    "Invalid"


    CROSSTAB ON Empmast.workdept COLUMNS c_Quarter SUBTOTAL c_TotalPay COUNT TO "r_PayrollByDeptQuarter.FIL" 

  4. In the r_PayrollByDeptQuarter table, we can create a computed field, c_AmountVarianceQ2, to calculate the pay variances between quarter 1 and quarter 2:

    • c_TotalPay_Quarter_2 - c_TotalPay_Quarter_1

    OPEN Payroll
    DEFINE FIELD c_TotalPay COMPUTED grosspay + vacationpay + overtime + commission + bonus

    DEFINE FIELD c_Quarter COMPUTED
    AS "c_Quarter"
    "Quarter 1" IF BETWEEN( paydate, `20140101`, `20140331`)
    "Quarter 2" IF BETWEEN( paydate, `20140401`, `20140630`)
    "Quarter 3" IF BETWEEN( paydate, `20140701`, `20140930`)
    "Quarter 4" IF BETWEEN( paydate, `20141001`, `20141231`)
    "Invalid"

    CROSSTAB ON Empmast.workdept COLUMNS c_Quarter SUBTOTAL c_TotalPay COUNT TO "r_PayrollByDeptQuarter.FIL" 

    OPEN r_PayrollByDeptQuarter
    DEFINE FIELD c_AmountVarianceQ2 COMPUTED c_TotalPay_Quarter_2 - c_TotalPay_Quarter_1


  1. We can create a second computed field, c_CountVarianceQ2, to calculate the variance in the number of checks issued between quarter 1 and quarter 2:
    • Count_Quarter_2 - Count_Quarter_1

    OPEN Payroll
    DEFINE FIELD c_TotalPay COMPUTED grosspay + vacationpay + overtime + commission + bonus

    DEFINE FIELD c_Quarter COMPUTED
    AS "c_Quarter"
    "Quarter 1" IF BETWEEN( paydate, `20140101`, `20140331`)
    "Quarter 2" IF BETWEEN( paydate, `20140401`, `20140630`)
    "Quarter 3" IF BETWEEN( paydate, `20140701`, `20140930`)
    "Quarter 4" IF BETWEEN( paydate, `20141001`, `20141231`)
    "Invalid"

    CROSSTAB ON Empmast.workdept COLUMNS c_Quarter SUBTOTAL c_TotalPay COUNT TO "r_PayrollByDeptQuarter.FIL" 

    OPEN r_PayrollByDeptQuarter
    DEFINE FIELD c_AmountVarianceQ2 COMPUTED c_TotalPay_Quarter_2 - c_TotalPay_Quarter_1
    DEFINE FIELD c_CountVarianceQ2 COMPUTED Count_Quarter_2 - Count_Quarter_1


Conclusion

Performing periodic departmental payroll comparisons can be useful in identifying potential issues. Even if you're not comparing payroll amounts and numbers, creating computed fields to break our data into quarters can come in handy for other analysis. We saw how the Cross Tabulate command can be used to produce a similar result to a pivot table so that the four quarters for each work department could be compared side-by-side. Remember that if you want to see the number of transaction in each category, as we did, you must select the optional "Include Count" checkbox. 

Having created the result of the Cross Tabulate command, we could then apply some filters to help identify some potential issues, such as a dollar change that exceeds a certain threshold. Additionally, we could search for situations where dollars increase while the number of items decrease. In the case of payroll, this would be an unusual situation in the normal course of events, but it is necessary to have an understanding of legitimate situations that need to be taken into consideration, such as a one time bonus being paid out in Quarter 1, as we saw in our test.