Match Factor and Match Weight

I received a question from a subscriber today regarding the difference between the Match Factor % and the Match Weight % in the My Matches table in NextComp. Here was my reply. I hope it may be of help to other subscribers as well!

Percent Factor: The data in a survey reported for a particular job represents the compensation market for that job assuming a solid match to the survey benchmark's job description. If the internal job's description matches that survey benchmark description then the percent factor of match = 100%. If the internal job's description is less than the survey benchmark's description, includes fewer duties or the work is less complex, then the percent factor of match can be set to less than 100%. If the internal job's description is bigger than the survey benchmark's description, includes more duties or the work is more complex, then the percent factor of match can be set to greater than 100%. The percent factor of match is used to size the market data to be a closer match to the internal job's work scope and complexity. This is not an automatic function of NextComp but rather a judgement call on the part of the analyst making the match.

Percent Weight: The percent weight affects the overall rolled up survey data for an internal job's survey matches. If an internal job has only one match, then the percent weight will have no affect on the overall rolled up average for that job's survey data. The weighting only affects the rolled up average when matching two or more survey jobs to an internal job. For instance, if you have a job in your organization that is a combination of two different types of work, let's say an "Application Programmer" and a "Traffic Analyst" (a job common in larger city governments). You may find matches in the market for either job, but it may be difficult to find one match that combines both types of work. You can match two jobs from the surveys, one for the application programming work and one for the traffic analyst (say in a city government setting). You could then weight the matches to reflect the mix of work in your organization. Perhaps in this case, the job is 75% applications programming and 25% traffic analyst. You would set the weighting appropriately in NextComp to reflect this mix. NextComp would then place 75% of the emphasis or weight on the applications programming survey data and 25% of the emphasis or weight on the traffic analyst. Another way to think of this is that the overall average data would be pulled toward the more heavily weighted job. Here's an example with some made up market data.

Applications Analyst
Average Pay: $65,000
Weight = 75%

Traffic Analyst
Average Pay: $55,000
Weight = 25%

NextComp complete the following computation to arrive at the overall weighted average data.

Applications Analyst average $65,000 multiplied by 75 = 4,875,000
Traffic Analyst average $55,000 multiplied by 25 = 1,375,000
Sum of the multiplied amounts = 6,250,000
Sum of the multiplied amounts divided by sum of weights (100) = 6,250,000 / 100 = $62,250

The overall weighted average data for this combination of survey jobs is $62,250.

You can see in this example that the data is pulled toward the more heavily weighted job. By way of comparison, the straight or un-weighted average for these jobs would be $60,000.

Here's a link to a help file in NextComp that shows a graphic example of this concept.

Excel Tips and Tricks for salary survey data

In this video I demonstrate the most common keyboard shortcuts I use when working with salary survey data files, a really cool and fast technique for converting data from annual to hourly (or vice versa), and a useful formula for combining data from two or more cells into one cell.



For reference, here are the keyboard shortcut combinations that I demonstrate in the video.

Moving around the worksheet:

control and down arrow (move to bottom of a list)
control and up arrow (move to the top of a list)
control and right arrow (move to the right of a table)
control and left arrow (move to the left of a table)

Selecting cells:

shift and control and arrow key (selects a contiguous set of data)
shift and arrow key (expands or contracts a selection of cells)

Copy and paste:

control and c (copy)
control and v (paste)

Converting values:

paste special -> operation -> divide (divide a range of values by the copied value)
paste special -> operation -> multiply (multiply a range of values by a copied value)

Converting formula results to values:

paste special - as Values (takes the results of a formula and pastes it as the value)

Combining data from multiple cells into one cell:

If you have three cells of data that you want to combine into one cell, you can use this formula

= cellref1&" - "&cellref2&" - "&cellref3

In this example, cellref is the cell reference for the cells that you want to combine.
If cellref1 = 1.01 and cellref2 = Administrative Assistant and cellref3 = the job's summary, then the result of the formula will be 1.01 - Administrative Assistant - job summary

Building A New Compensation Structure - Part 1

This is the first in a series of articles on the typical steps used in designing a new compensation structure. This is based on my 20+ years of experience in compensation design. I'm going to go through the process step-by-step using plenty of screenshots and videos along the way. Here's an outline for the series of articles. This may change or evolve as I go through the process, but it's a starting point.

1. Understanding the current or existing structure
2. Plotting the market data against the current structure
3. Costing models - Moving all the way to market
4. Costing models - Alternatives
5. Finalizing the design
6. Presenting the process and recommendations

So let's kick this off with Part 1 - Understanding the current or existing structure.

The assumption in this tutorial is that we have an existing pay structure in our organization that hasn't been fully reviewed compared to market in several years. There have been a few spot checks as new hires were brought on board, but we don't know for sure if the entire structure is market competitive.

Here's what the current structure looks like in Excel. I've annotated the table to explain a few important details.

I've formatted the grade column with the "text" format option. The following two screenshots show how to format the cells as "text". This will allow you to enter leading zeros without Excel automatically removing them when you press Enter on the keyboard.

Highlight the cells that you want to format.

Right click over the selected cells.

Choose the "Format Cells..." option.

Choose the Text option from the Category: list.

Click OK

The following areas of the structure are important to understand before we can begin to design a new structure based on the market data.

A. The midpoint growth and the CEILING formula

The current structure is based on the midpoint of Grade 01. In a sense, the Grade 01 midpoint is the lynchpin of the design. By changing this figure the entire structure would be affected. The midpoint of grade 02 is approximately 12% greater than the midpoint of grade 01. Similarly, the midpoint of grade 03 is approximately 12% greater than the midpoint of grade 02.

Why approximately and not exactly 12% greater? Well, I wanted to make the midpoints "pretty" by rounding them to the next higher $100. I used a formula in Excel to accomplish this automatically. The formula is called CEILING.

In effect, the CEILING formula rounds the midpoints up to the next $100. This makes for a cleaner and easier to explain structure.

Communication is something that we need to be aware of as we design the new structure. It has to be clear and explainable to the organization's employees. I've never been a big fan of midpoints that are exact to a dollar, that's a personal opinion, but one that is based on experience. Exact dollar amounts convey a sense that the market data is that precise, when in fact it's not, we can get close but the market data is best expressed in terms of ranges of pay for a job, hence the need for pay ranges in the grade structure.

B. Range width and minimum and maximums

The next important aspect of the current structure is the range width. The width describes the breadth of the range from the minimum to the maximum. It is defined by the minimum dollars as a percentage of the midpoint and by the maximum dollars as a percentage of the midpoint.

The current design has a minimum that is 85% of the midpoint. The maximum is 115% of the midpoint. By default, the midpoint is defined as being 100%. Typically the 100% is tied to the market data in some fashion. The way to understand the structure, and ultimately explain it to employees, is to tie the midpoints to the market data. So when an employee's pay is at the midpoint we can say that the employee is at 100% of the market.

The formula for calculating the width of the range is:

(Range Maximum Minus Range Minimum) Divided By Range Minimum

Typical range widths are from 35% to 65%. The current ranges are 35% in width.

C. Charting the ranges

We'll be using several charts to show how the ranges compare to the market data and the average employee data.

We'll use a "Line with Markers" chart.

The first step is to select the structure in the Excel file, including the column headers and the grades.

From the Insert ribbon, click on the Line button and then click on the "Line with Markers" chart type.

Excel will place a default chart onto the worksheet.


Next, right click on the chart and click the "Move chart..." menu item. If you don't see the "Move chart..." item, then click away from the chart onto the worksheet to deselect the chart, then click on it again, then right click to display the pop-up menu.

Choose to move the chart to a "New Sheet". You can also name the sheet in this step.

Excel will create a new sheet with the chart as the only object on the sheet.

We'll want to change the format of the lines so that each type of data is color coded. We'll be adding market data as well as average employee pay data to the chart in subsequent steps.

Here's how to format the chart lines.

Click on the bottom line, then right click on the same line. A pop-up menu will appear. Click on the Format Data Series... menu item.

From the Marker Options tab, choose None for Market Type.

From the Line Color tab, click on the Color menu item and then choose a blue color. I used the leftmost blue in the top row of colors.

Click close.

Repeat the above steps for the middle line. Only this time, you'll also want to change the Line Style to dashed.

Repeat the steps again for the top line, only don't change the Line Style for the top line.

When you're finished you'll end up with a nice clean looking chart similar to the one below.

Video Walk-through

View Larger

Next Steps

What we don't know at this point is whether or not the current midpoints really are at 100% of the market. At some point in the past, when the ranges were originally designed, our assumption is that they were pegged to market.

In the next article I'll discuss adding the market data results to the chart and comparing the market data to the current ranges.

FAQ: How to convert values to text in Excel using the TEXT function.

Here's a tip for Excel to allow you to load organizational or survey job codes that are a mix of number codes and letter codes. For example, some surveys have job numbers similar to the following:

• 1000
• 1000R

or the format may look like this

• 10.10
• 10.11
• 10.12

What can happen in Excel is that the trailing zero is removed and the list ends up looking like this:

• 10.1
• 10.11
• 10.12

In the above examples the job codes with an R at the end are considered text data by Excel. The job codes without the R are considered numeric data by Excel. Similarly, the job codes with the decimal point are all considered values by Excel, when in reality you want them to load into NextComp as text.

NextComp will read only the numeric OR text depending on how the data is sorted in the import template. You’ll need to convert all of the job codes to text data in Excel. The image below gives a quick visual summary of the process. Click the image for a larger version.


Here's a step-by-step description for Excel 2003 and prior versions. The same techniques work in Excel 2007, although the menu names may be slightly different.

Step 1: Select all of column B by clicking in column B’s header

Step 2: Select the Insert menu in Excel, then select Columns

Step 3: Select the Format menu item in Excel and then select the Cells menu item

Step 6: Select cell B2

Step 7: Enter the formula shown below into cell B2. Here's a description of the TEXT function on the Microsoft Office website.

Step 8: Copy cell B2 down all the way to the last row of job code data

Step 9: Select data in column B from cell B2 through to the last row of data in the column

Step 10: Select the Edit menu item in Excel and then select the Copy menu item

Step 11: Select cell A2

Step 12: Select the Edit menu item in Excel and then select the Paste Special... menu item

Step 13: Choose “Values” as the Paste type

Step 14: Click the OK button

Step 15: Select all of column B by clicking on the column header for B

Step 16: Select the Edit menu in Excel and then select the Delete menu item

Step 17: Select the File menu item in Excel

Step 18: Select the Save item in the File menu

2 New Tips and Tricks in the Help Desk

I've been adding new items to the NextComp help desk as I get questions or comments from subscribers. There are two new items today, one based on a question from a subscriber and one as a result of the online training.

If you haven't signed into the help desk before, it's really easy.


Visit this website: http://nextcompnet.zendesk.com


On the right side of the page, click the Sign Up link.
Enter your name, email address and a password.


Then you'll be able to view the online knowledge base and submit and track help requests through the system.

Of course, contacting us through plain old e-mail works great too!

Excel Tip: Paste Special | Multiply

Here's another trick that I've been using in preparing data files for loading into NextComp.

Many surveys display the survey result amounts in 1,000's, for example, "$54,500" is displayed as "54.5" in the Excel report from the publisher.

NextComp expects the survey data to be in hourly or annual amounts. If we load the data as it is displayed, NextComp will assume that the data represents an hourly amount of $54.50.

You can use the Copy, Paste Special | Multiply function to convert all the data in a column or a group of columns to an annual figure.

Here's how...

The image below shows a column of data before being converted to annual figures.



I entered the number 1000 in the empty cell at the top of the column, as shown below.



I copied the cell with the 1000 in it. I selected all the data in column AK. And I right clicked over the selected cells to bring up the menu shown below.



I chose the Paste Special... command from the menu. In the Paste Special dialog box, I selected the "Multiply" operation.



After clicking the OK button, the data in the selected cells is multiplied by the copied value, in this case 1000.



You can use this same method to divide data as well. Just choose the "Divide" operation in the Paste Special dialog box. This can be useful when loading data that NextComp expects to be in a percent format. Some surveys display 100% as the number 100 rather than 1 with formatting applied.