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