Using Transparent California Data to Calculate Average Wage Growth

Just for anyone interested, here’s a quick tutorial on how to use Transparent California’s data sets to compare individual pay from year to year to determine the average growth rate of individual pay in a jurisdiction, along with a template I’ve put together to do that.

To clarify, the objective is to see what the average pay and benefits change is for individuals who have been with the jurisdiction from the beginning of the examination period through the end.

If we were to simply calculate the overall average pay for the entire jurisdiction, that average number can be misleading – affected by employees retiring or quitting and being replaced with lower-paid, newer employees. That change might result in a result that shows lower overall pay levels for the jurisdiction and an overall lower average pay per person, even though each individual may have received a raise.

A simple example of why that is misleading…

Suppose at the beginning of your chosen period you have 100 employees who all make $100,000/year. The “average pay” for that group would therefore be $100,000/year.

During the examination period (call it a year), 50 of those employees quit and are replaced with starting employees at starting wage. To make the math easy, we’ll say the starting wage is $50,000/year.

Now let’s assume everyone in the district gets a 10% raise, so at the end of the year everyone who was making $100K is making $110, and everyone who was making $50K is making $55K.

At the start of this period you had 100 employees making $100K/year, for an average of $100K each.

At the end of this period you would have 50 employees making $110K and 50 employees making $55K. That means the average pay is…

((50*$110,000)+(50*$55,000))/100 = $82,500/person.

So… even though in reality every single person in the district got a 10% raise that year, if you’re just looking at the average it appears the average district employee actually took a 27.5% pay CUT, despite the fact that in reality everyone got a 10% RAISE…

The point is to better understand what the average pay raise looks like for people who have been with a jurisdiction from whatever you choose as the earliest period to the most recent period, which gives you a more accurate view of the total cost of pay raises during this time.

The example below uses Excel, however likely that Google Sheets will work in very similar ways.

Go to transparentcalifornia.com and download the appropriate data set. For our purposes I’m going to use the Sweetwater Union High school district in San Diego but any set will do.The latest data set available as of this writing is 2016. Click “download records” for this year, which will create a data file on your computer in the .csv format (which is Excel and Sheets compatible).Then download some prior year. I’m going to use the earliest available – 2012.

Paste the data from the earliest period into the “Earliest” tab, and the data from the most current data into the “Latest” tab.

The results will be shown in the “Summary” tab.

In my example, as you can see in the Sweetwater District, for people who have been with the district from 2012 through 2016, the average individual increase in base pay was a whopping 12.70%, with the total increase including benefits of 13.14% per year.

And, during this same time, according to the Social Security Administration’s numbers on average wage growth for the country (https://www.ssa.gov/oact/cola/awidevelop.html), the average for “everyone else” has been 2.36%.

In other words, Sweetwater employees are getting raises at a rate that is over 10% PER YEAR higher than the average worker.

If we look strictly at the Teacher group we see the average base pay rate is similar – at 12.59%/year, but the average total pay and benefits lags a bit at 12.51%/year.

Which, although lagging the overall total, is not exactly a small number.

When I present these numbers, a common objection is that the increase number includes people who have gotten promotions or have obtained certifications or degrees that result in higher pay. Two comments on this.

A raise is defined by the dictionary (and most people) as “an increase in pay”, it does not matter why. If one was promoted and given more money, when telling friends or a spouse about it they would say “and I got a raise!”…

The SSA’s numbers are calculated in exactly the same way – the total pay for the entire workforce of America, with no differentiation of what part of that increase might have been due to promotions, job changes, or anything else. This makes it truly and apples-to-apples comparison.

Methodology Notes

Regrettably it is necessary to make some assumptions with the data to get accurate totals, since some data that would be handy to fine tune the numbers is not available in the TransCal data sets.

The data matches employees by name. If an employee’s name is entered differently in one of the data sets, there will be no match. Commonly seen issues with this are inclusion (or not) of middle initials, periods, or name changes due to marriage or for other reasons.If there are two employees with the same name in the district, it is possible the match may connect two different people…It would be nice to have an “employee ID” to be certain about the match but that is not in the data.

All calculations are made for ONLY employees whose names appear in both data sets and match. Again, we are not attempting to calculate an overall average for the entire district, but an average rate of pay growth for employees who have worked full time during the entire period examined.

In both data sets (“All” and “Teachers”) I have excluded anyone whose pay growth is negative during the examination period.It is certainly possible that an employee would have taken a pay cut for some reason, however in my experience with the data I have never heard of a jurisdiction giving it’s employees a pay cut without massive publicity. If your jurisdiction gave an across-the-board pay cut during the period examined, you would know about it.Meanwhile, more commonly pay decreases are for voluntary reasons – an employee retired or quit before a fulll year was done, transferred elsewhere, quit and was rehired later in a different position with lower pay, or took a job that perhaps had less responsibility and more flexible scheduling for family reasons.

“Teachers” are defined in the only way I can from this data – people who have “Teacher” in their job title. Note that is not synonymous with “Certificated”, which also often includes other job titles that do not have “Teacher” in them.

Regrettably there is no way to separate out the Admin or Classified groups from this data in any easy way…