Compensation, Compensation Administration, Compensation Planning

How to Update Aging Salary Data for Your Organization’s Jobs

If you work in HR then you’re always busy and, as a result, your job grade data might be a smidge past its shelf life. It’s not that you don’t want up-to-the-minute salary range data but there’s almost always something else more pressing that needs your attention so it’s really easy to tell yourself—if it ain’t broke, don’t fix it—year after year, after year.

Tomwang112 / iStock / Getty Images Plus

Let’s face facts. HR can be a tough gig. There’s always more to do than time allows so it’s really easy to let anything that’s working just fine slip through the cracks. But, sooner or later, everything needs attention, including rate range data that hasn’t been updated since 2003. So, let’s get to it.

Time Traveling Is Really Iffy

Do yourself a favor and don’t attempt to “age” that old data. Realistically, you can age salary data up to 24 months and has a calculator to help you do just that, but anything beyond 2 years very well may turn into journey through futility from beginning to end. Speaking of the beginning, with that data that old, what could you possibly use as an aging factor?

Does your company have a lot of data, but don’t know what to do with it? Attend, “Workforce Intelligence: 5 Steps to Making HR Analytics a Reality” at BLR’s THRIVE event, held May 11-12 in Las Vegas. This session will cover 5 steps for effectively analyzing HR data so you make better business-centric decisions that significantly impact the workforce.

While we’re on that topic, let’s talk about aging factors. Some employers use the annual Consumer Price Index (CPI) as an aging factor, while others use the annual Cost of Living Adjustment (COLA), and some just use whatever fits into their budget.

Any of these can be viable aging factors but if you’re trying to age salary data from 10+ years ago, which one do you use for which year(s)? And, what about the Great Recession? How does that impact aging that ancient salary data? Stepping back in time and trying to figure out how to age really old salary data just isn’t practical, so what’s the solution?

A Fresh Start

Your best bet is to scrap that old data—sort of—and tie your existing job grade structure to current market data. To do that, you’ll need a list of all the jobs in your organization along with their job grades. It should look something like this …

You’ll also need columns for:

  • Employee name
  • Current pay rate
  • Job title
  • Job grade
  • Minimum pay for the job grade
  • Midpoint for the job grade
  • Maximum pay for the job grade

You should be able to retrieve this data from your Human Resources Information System (HRIS). If you outsource payroll, the service provider should be able to provide the data in an Excel spreadsheet.

If you utilize job grade families, sort the data by that column first, with a secondary sort by job grade. Next, within each job grade, sort by job title and then by current pay rate.

This allows you to group all the jobs in each grade, and title, together so you can see the range of actual pay and determine which, if any, employees are currently under or over the range for their position. You can add columns for compa-ratio and range penetration, if you’d like to track that metric as well.

Next Steps

You’ll need to add columns for the min, mid, and max for current market data in preparation for conducting the market research.  Your data sheet should now look like this …

Depending on how many jobs there are in your organization, you should research the market data for every single position. If time just doesn’t permit that, however, you need a good sampling of current market data for the jobs in each job grade. For example, if you have 10 positions assigned to a single job grade, you’ll need to find current market data for at least half of them. Finding data for two-thirds, though, is better because it gives you a broader sampling of the market.

Most salary surveys provide salary data as a “mean” along with 10th, 25th, 50th, 75th, and 90th percentiles. You’ll need to decide which parts of the data you want to use for your min, mid, and max. (Note: The 50th percentile is generally considered the midpoint of the market.)

Depending your organization’s compensation philosophy, not to mention your current pay rates, using the 25th or 75th percentile as the midpoint may be a better fit for your organization. With that said, if you’re using the 50th percentile, which percentiles do you use as the min and max?

Whether you use the 10th and 90th or the 25th and 75th percentiles depends on how broad a range you want for each job grade. The rule of thumb is the higher the job level, the broader the range spread. If you decide to use either the 25th or the 75th percentile as the midpoint, you could select the 10th and 50th or the 50th and 90th, respectively, as the min and max.

Another option is to use the mean, or your percentile of choice, as the midpoint of the grade range and calculate the min and max, depending on the range spread you wish to use for each job grade family.

Here’s a peek at the math for calculating range spread.

Let’s use $135,000 as the midpoint of our range. To find the minimum, divide the midpoint by 1.00 + ½ of the range spread. To find the maximum, multiply the minimum times 1.00 plus the range spread. This creates a salary range that has a minimum of $108,000, a mid-point of $135,500, and a maximum of $162,000.

This simple formula can be used to establish a salary range for any job based on the mid-point of available salary market data. If you’d like to see this formula in action, check out the BLR® Job Grade Rate Range Calculator.

Getting Back to the Market Data

There are a number of sources out there for salary market data and many of them are quite good, including our own For the most accurate view of the salary market, use multiple resources to create a composite rate.

Whichever source(s) you decide to use, be sure the data provides the demographic elements most relevant to the position you’re researching. The norm is company size, industry, and location. You can use a composite of all those demographic elements or opt to use only one of them. For many jobs, location is the most relevant. If the position is industry specific, however, that data becomes more relevant.

Your next step is to add the market data to your spreadsheet, which, when completed, should look like this …


In this example, I used the mean for the local demographic element as the midpoint for the current market data and calculated the min and max using the formula described above, with a 40% range spread.

If the salaries for the jobs in your organization haven kept within, or at least near, the rate range between the origin date, which in this example is 2003, and the current date (2017), then your new rate ranges should be pretty solid and this project is pretty much done. Except …

Always Sanity Check the Data

You‘ll need to analyze the data, comparing where the salaries are in relation to the new job grade ranges. You may have some employees whose pay rate is now either under or above the new range for their position and green circling or red lining may be appropriate. If your pay philosophy states that you pay at the 50th percentile and current pay rates are not in line with that, you may need to make salary or rate range adjustments to accommodate that requirement.

You could also have job grades that have flipped ranges where, as in the example above, the market value for the jobs in grade two is now higher than the market value for the jobs in grade three. Even if your job grade ranges from years ago were based on market rates at that time, the market value relationship between jobs can evolve.

Again, using the example shown in the chart above, there could have been a surplus of tool and die makers looking for work in 2003 but are now in short supply, pumping up their value in the current market. Which means if the value of jobs has changed significantly, you’ll need to realign jobs with the job grades.

Adding to the mix, you need to consider each job’s value to the organization. You could have some jobs with a lower market rate now but are integral to your organization and, as a result, are worth more to the company than is reflected by the market data for those positions.

End of the Road

Updating really old job grade ranges can be a challenging project, which can become a balancing act that requires careful consideration and attention to detail. It’s not a lot of fun, unless you’re a bit of a comp geek like me, and, I suppose for some, it can even be downright cringe inducing. It does take a good bit of time but can be accomplished in several short sessions rather than one long adventure. Either way, for me at least, cookies and milk always make the process more enjoyable.

Sharon McKnightSharon L. McKnight, CCP, SPHR, draws from more than 20 years of management experience, including 6 years as a director of human resources, to develop compensation administration tools and write about compensation issues. Her experience in both operational and HR management provides her with a practical approach to providing online resources that address the challenges facing compensation and human resource professionals.