Struggling with Regression Analysis for Compensation: How Do I Use Excel for This?

I am working on a compensation analysis for a large project. I am trying to figure out how to plug data into the regression analysis using annual salary as the dependent variable and years in the position as the independent variable. I see that the formula is y = 84.7x + 3033.6, with R squared = 0.399 on a sample on this site, but I don't know how to manipulate the numbers for my analysis. Does this need to be done in Excel? If so, how, and how do I use the numbers?

You can conduct a regression analysis using Excel. I have attached an Excel sample for you to play with. It's pretty straightforward.

Understanding Regression Analysis

In regression analysis, the lower the R-squared, the lower the correlation between the two variables. Since you are using Age & Annual Salary, you are basically trying to prove whether salary progression is dependent on age, i.e., the older you are, the higher your salary.

Hope this is useful.

Regards,
Autumn Jane

Thank you so much for responding! I have had the hardest time getting someone to help me on this project. I am so appreciative of your help. I hope I can ask you a few more questions.

Compensation Analysis Project

I am working on a compensation analysis for a company. I am comparing what their staff is currently making (annual salary) to a salary survey to see if they are paying their staff appropriately. I ran the numbers, and it looks like they are paying most of their staff at about 80% of the midpoint. I wanted to plot the employees on a scattergram by salary as the dependent variable and date of hire as the independent variable. Then I want to use a "line" to show where 80% of the midpoint is, 100% of the midpoint is, and 120%.

Questions on Data Visualization and Analysis

1. How do I get the lines in there, the 80%, 100%, 120%?

2. How do I put in the date of hire? Do I have to count up the number of years they have worked for the company, or can I use month/day/year?

3. Can a scattergram be used to show external equity, or is it only for internal equity? If it can be used for external equity, how do I do that?

4. Do I use regression to show the outliers? If so, how?

5. Any other advice/suggestions on where I need to use regression for a compensation study would be very much appreciated.

After we analyze their current salary, we might want to propose a new salary structure. Do we use regression for that? I'm sorry if my questions are not making sense. I just don't know enough about regression to ask the questions the right way. If you are able to call me, it might be easier. You are welcome to call me at [Phone Number Removed For Privacy Reasons].

Thank you, Autumn Jane!

I have updated the Excel spreadsheet to include both the 80% and 120% lines. Explore the formulas to know how these are computed. The markers are switched off because you only want to see the 100% markers as these reflect the actual and current salary of staff.

Salary Survey Methodology

1. Which salary survey are you using? By what methodology?

If you want to use the date of hire for your analysis, just change the column where you have your independent variable; that will do. You have to use the actual year. This is called Tenure Analysis. However, please note that there is a difference between Year of Hire and Year in Position.

Understanding Midpoint in Regression Analysis

2. You mentioned 80% of midpoint. Please clarify what this midpoint is.

In a regression analysis, the midpoint line is your 100% line. It is also your line of best fit and your current pay practice. Both the 80% and 120% lines are your maximum and minimum lines. They are also your "controlling lines." Any data points that fall outside these two controlling lines are outliers.

Regression analysis can be used for internal and external analysis. For external analysis, the survey provider must consolidate the midpoint equations of all the survey participants to provide Market Charts. Once there is a Market Chart, you can then superimpose your own midpoint equation on the chart to get your comparison. Your survey provider should be able to provide you not only the Market Charts but also the Market Formulas.

Alternative Approaches to Pay Competitiveness

Separately, using Age or Tenure Analysis is not the best approach to determine pay competitiveness or to determine overpaid/underpaid jobs. Instead of using Age or Tenure, use Job Size or Job Point or Job Grade. This will ensure an apples-to-apples comparison.

Yes, you can use Regression to conduct a salary structure. However, your market data must also be on a regression basis. The Market Formulas will be used to develop this salary structure. You have to talk to your survey provider again.

Internal Equity Analysis Using Scattergrams/Regression

Lastly, other uses for the internal equity analysis using scattergrams/regression:

1. It is also known as Peer Analysis.
2. It can also be used to determine missing grades (provided your variable is job point or job size or job grade).

Regards,
Autumn Jane

My suggestion would be to use more controls. At the moment, you are using one independent variable, i.e., years in the current position. Other confounding factors could be education, salary at joining, company size, etc. For example, a person with a master's degree and 5 years of education could be earning more than someone with a bachelor's degree and 5 years of experience. A person's salary in a blue-chip company will be very different than in an SME. SMEs tend to pay more as the risk premium, whereas blue-chip companies attract people at a lower salary because the brand name is a pull.

Autumn Jane, thank you so much for the new spreadsheet; it is very helpful!

Answering Your Questions

1. I am using a salary survey from CUPA (which stands for College and University Professional Association for Human Resources). The survey is filled with data from various universities across the United States. When I run a report, I ask it to compare "University X" (that I am working with) against 15 other universities they feel are similar in size, location, etc. The survey takes each person at "University X" and compares that person's salary with the average salary of others in the same title at the other 15 universities. The data is then downloaded into a spreadsheet, which automatically calculates the mean, median, etc., for the 15 universities and tells me how close "University X" employees are to that data.

2. 80% of the midpoint in this case refers to the CUPA survey. Typically, a compensation philosophy is that employers want to pay their employees close to 80% of the midpoint. So, CUPA averages, for example, everyone's salary in a "Professor" position for the 15 "peer universities." Then it takes the professor's salary at "University X" and compares it to the other "peer university" professors. If the professor at "University X" is paid within 80% of the average of all 15 peer universities, then they consider themselves to be paying their professors "fairly."

3. I am not sure what you mean by market chart?

4. If I understand you correctly, are you saying that regression analysis can be used for internal analysis to find the OUTLIERS, and OUTLIERS are determined by looking at the salaries that fall above or below the 80% and the 120% line?

a. If so, is this because the salary is at least two standard deviations from the mean?

b. We should use the year in position, not the year of hire. Is that correct?

5. I understand what you are saying about tenure analysis not being the best approach, but we do not have the job points, and the grades all need to be readjusted based on our analysis.

6. I am hoping to do a more detailed multiple regression with ONLY the people that are determined to be OUTLIERS. Is that acceptable?

a. If we have to do multiple regression on the OUTLIERS to see why they are OUTLIERS, we would have to get more data (confounding factors; job points, education, etc.) on those people from "University X" and from CUPA. I don’t think CUPA supplies the job points for the 15 peer universities. Can it be done without the confounding factors on the CUPA people?

b. If we were able to get more data/confounding factors for the people in CUPA so that we were comparing apples to apples, can you tell me how we would do multiple regression analysis with that data? Can you send me a spreadsheet with multiple regression? Can you explain how multiple regression in this format a little more for me?

Thank you! Thank you! Thank you!!!!

Regards, Michelle

I am using a salary survey from CUPA (which stands for College and University Professional Association for Human Resources). The survey is filled with data from various universities across the United States. When I run a report, I ask it to compare "University X" (that I am working with) against 15 other universities they feel are similar in size, location, etc. The survey takes each person at "University X" and compares that person's salary with the average of other people's salaries in the same title at the other 15 universities. The data is then downloaded into a spreadsheet, which automatically calculates the mean, median, etc., for the 15 universities and tells me how close "University X" employees are to that data.

Position Analysis

Autumn Jane: What you have described is known as a Position Analysis. I have attached a sample for your reference.

80% of the midpoint in this case refers to the CUPA survey. Typically, a compensation philosophy is that employers want to pay their employees close to 80% of the midpoint. So, CUPA averages, for example, everyone's salary in a "Professor" position for the 15 "peer universities." Then it takes the professor's salary at "University X" and compares it to the other "peer university" professors. If the professor at "University X" is paid within 80% of the average of all 15 peer universities, they consider themselves to be paying their professors "fairly."

Referring to the Position Analysis, the different market percentiles, i.e., P90 / Q3 / MD / AVG / Q1 / P10, are also midpoint values of the market. If a company decides that their pay policy is Market Q3, it basically means Q3 is the midpoint, and it is 100%. Taking yours as an example, when you take your salary and divide it by the average, this is known as a Market Index Analysis. For this index, any job(s) that fall below 80% is your underpaid job(s), and any job(s) that fall above 120% is your overpaid job(s). Any job(s) that has an index of between 115% to 120% are called "ceiling jobs" as they will max out or reach the max range in the near future. The market index analysis makes your salary planning much easier.

I am not sure what you mean by market chart?

Autumn Jane: I have attached a Market Chart for your reference. It is used to compare your overall Pay Practice against the Market.

If I am understanding you correctly, are you saying that regression analysis can be used for internal analysis to find the OUTLIERS, and OUTLIERS are determined by looking at the salaries that fall above or below the 80% and the 120% line?

Autumn Jane: Yes, your understanding is correct. The only concern is whether the 80% and 120% is the right range your company wants to adopt. A company can decide to go with 65% and 135%. The decisions depend very much on the company pay philosophy, market demand and supply, etc.

If so, is this because the salary is at least two standard deviations from the mean?

Autumn Jane: Yes, one of the reasons.

We should use year in position, not year of hire. Is that correct?

Autumn Jane: Yes, more accurate, but no, because it is still not by job sizing.

I am hoping to do a more detailed multiple regression with ONLY the people that are determined to be OUTLIERS. Is that acceptable?

Autumn Jane: Yes.

If we have to do multiple regression on the OUTLIERS to see why they are OUTLIERS, we would have to get more data (confounding factors; job points, education, etc.) on those people from "University X" and from CUPA. I don't think CUPA supplies the job points for the 15 peer universities. Can it be done without the confounding factors on the CUPA people?

Autumn Jane: Is the CUPA survey quite similar to the attached Position Analysis? If yes, without doing multiple regression, you can already have a good analysis by looking at the position profile, i.e., the age, years in company, and years in position.

If we were able to get more data/confounding factors for the people in CUPA so that we were comparing apples to apples, can you tell me how we would do multiple regression analysis with that data? Can you send me a spreadsheet with multiple regression? Can you explain how multiple regression in this format a little more for me?

Autumn Jane: You may want to Google "multiple regression." There are a lot of details and examples for you to pick up more tips.

Regards,
Autumn Jane