Oregon Auditing

How To: Analyze payroll data using Excel’s SUMIFS function

A few years ago, I worked on an audit at the Oregon Department of Corrections (DOC). Elected officials were concerned that the DOC was spending too much money on overtime. We used a combination of ACL and Excel to conduct this audit work. We followed the National Institute of Corrections Net Annual Work Hours Post Relief Factor methodology.

First, we accessed our state’s central payroll database. We pulled the tables we needed that recorded hours worked by pay code by month. The way the table was organized was not right for Excel, so we first needed to prepare the information by using the Summarize function in ACL. For this example, you will need to summarize on employee classification, pay code, and location. Aggregate both hours and dollars.
With the data prepared, it should like something like this:

Table 1 – Data

The first column denotes the prison. The next is the classification of the correctional employees (e.g. sergeant, corporal, officer etc.). The following column shows pay codes – we had 50 different pay codes in our data set. The next three are straight forward – dollars, hours, and counts. Lastly, we have a short description of the pay codes. For example, CD is career development/training.

After developing this table, I calculated the average for each row by dividing by the FTE in each classification at each prison. For example, there were 3881.5 hours of CD and 184 FTE, yielding an average of 21 CD hours per staff. Now that I have my data ready I can start analyzing it. I want to know if there are differences between locations, classifications, and pay codes to see if this is driving any overtime.

I set up a table in excel, shown below. CCCF stands for Coffee Creek Correctional Institution and TRCI is Two Rivers Correctional Institution. Pay code descriptions are above. Classifications range from officer (C6775) to captain (X6780).

Table 2 – PRF

I can now use this table within my SUMIFS function to pull average hours from the other table.
The SUMIFS function has three main parameters: Sum_range, Criteria_range, and Criteria.

Sum range is the range of data you want summarized. In this case, I wanted to pull average hours from my payroll data. You only have one sum_range, although you can have as many criteria as you want. I will have three criteria. I want the average to come from (1) the correctional institution (2) the employee classification and (3) the pay code.

Columns a, b, and c, from the table 1 will each be a criteria range. I will use table 2 as my criteria. Here is what the formula looks like. Not I use a combination of absolute and relative references (our next post will delve into this in more detail). Absolute references ‘lock’ in cell ranges in a formula so when you drag the formula it does not change. Absolute references are denoted by a “$”.

SUMIFS function

Reading left to right, the function is asked to summarize the averages calculated from the table 1. It is to look at the first column for the prison acronym to match to “CCCF” highlighted in blue. Next it searches employee classification in column b for “C6775”. Finally, it matches pay code “CD” highlighted in purple.

Once I have the one formula set up, I can drag it over and down and calculate over 500 different averages in a few seconds. After setting up one year of data, all you need to do is copy the tab and re-link to the next data and you can compare year-over-year trends in minutes.

There’s a few steps I’m skipping over, but the end result from these calculations looks something like this:

So what did we find in our audit? Overtime is not as big of a problem as people perceive it.

Most people think that overtime has to be more expensive because you are paying time and a half. What is often left out is the cost of leave time and other benefits, which often add up close to 50% of salary making the pay difference negligible. Furthermore, if you hire an officer to replace overtime you must pay them for about 2,000 hours per year. Whereas with overtime, you only need to pay it when you need it. What is cheaper? $65,000 for a new officer or $25,000 for 500 hours of overtime?

So if you pay only 500 hours of overtime per year in a given shift, it doesn’t make sense to hire an officer to cover that time, because they would be paid for hours not needed. Below is a great example.

Overtime at CCCF

You can see that overtime is quite varied. It peaks around hunting and fishing seasons, flu season, and winter holidays. This is not that surprising as more people are calling in sick during these times and someone needs to work the overtime to replace them.

If CCCF hired an additional officer to work these hours, they would only reduce overtime by a small fraction. At best, CCCF could eliminate all of the overtime between 0 and 8 hours. To eliminate all overtime, CCCF would need to hire 6 FTE on the graveyard shift, or 48 hours of coverage, which is vastly more expensive than the cost.

As we found out in this audit, sometimes your gut, i.e. overtime is costly, is wrong.

Post prepared by Guest Blogger and OAD Senior Auditor Ian Green