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 position as the independent variable. I see that the formula is y=84.7x + 3033.6 R squared = 0.399 on a sample on this site but I don't know how to manipulate the numbers for my own analysis. Does this need to be done in excel? If so how and how do I use the numbers?
From United States, Chesterfield
From United States, Chesterfield
Dear MPERRIS
You can conduct a regression analysis using excel. I have attached an excel sample for you to play with. Pretty straight-forward.
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.
Autumn Jane
From Singapore, Singapore
137You can conduct a regression analysis using excel. I have attached an excel sample for you to play with. Pretty straight-forward.
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.
Autumn Jane
From Singapore, Singapore
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.
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 mid point. I wanted to plot the employees on a scattergram by salary as the dependant variable and date of hire as the independant variable. Then I want to use a "line" to show where 80% of the midpoint is, 100% of the mid point is and 120%.
So:
1. How do I get the lines in there, the 80%, 100%, 120%?
2. How do I put in 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 analyize 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 314-440-0752.
Thank you Autumn Jane!
From United States, Chesterfield
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 mid point. I wanted to plot the employees on a scattergram by salary as the dependant variable and date of hire as the independant variable. Then I want to use a "line" to show where 80% of the midpoint is, 100% of the mid point is and 120%.
So:
1. How do I get the lines in there, the 80%, 100%, 120%?
2. How do I put in 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 analyize 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 314-440-0752.
Thank you Autumn Jane!
From United States, Chesterfield
You are welcome.
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 reflects the actual and current salary of staff.
1. Which salary survey are you using? By what methodology?
If you want to use the date of hire for your analysis, just change column where you have your independent variable will do. You have to use the actual year. This is call Tenure Analysis. However, please note that there is a difference between Year of Hire and Year in Position.
2. You mentioned 80% of midpoint. Please clarify what is this midpoint?
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 falls 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 a Market Charts. Once there is a Market Chart, you then can super-imposed 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 Formulae.
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 apple to apple comparison.
Yes, you can use a Regression to conduct a salary structure. However, your market data must also be on a regression basis. The Market Formulae will be used to develop this salary structure. You have to talk to your survey provider again.
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)
Autumn Jane
From Singapore, Singapore
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 reflects the actual and current salary of staff.
1. Which salary survey are you using? By what methodology?
If you want to use the date of hire for your analysis, just change column where you have your independent variable will do. You have to use the actual year. This is call Tenure Analysis. However, please note that there is a difference between Year of Hire and Year in Position.
2. You mentioned 80% of midpoint. Please clarify what is this midpoint?
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 falls 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 a Market Charts. Once there is a Market Chart, you then can super-imposed 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 Formulae.
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 apple to apple comparison.
Yes, you can use a Regression to conduct a salary structure. However, your market data must also be on a regression basis. The Market Formulae will be used to develop this salary structure. You have to talk to your survey provider again.
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)
Autumn Jane
From Singapore, Singapore
My suggestion would be to use more controls. At the moment you are using one independent variable i.e years in current position. Other confounding factors could be education, salary at joining, company size etc. For example, a person with masters and 5 years of education could be earning more than someone with a bachelors degree and 5 years of experience. A person's salary in blue chip company will be very different than an SME. SMEs tend to pay more as the risk premium whereas blue chip companies attract people at lower salary because brand name is a pull.
From India, Gurgaon
From India, Gurgaon
Autumn Jane, Thank you so much for the new spreadsheet, that is very helpful!
To answer your questions:
1. I am using a salary survey from CUPA (which stands for College and Universal Professional Association for Human Resources). The survey is filled with data from various Universities from all over the United States. When I run a report I ask it to compare "University X" (that I am working with) against 15 other Universities that they feel are similar to theirs in size, location, etc. The survey takes each person at "University X" and compares that persons salary with the average of other peoples salary in that same title at the other 15 Universities. The data then is downloaded into a spreadsheet, the spreadsheet 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 is referring 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 Professors 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 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?
a. If so, is this because the salary is at least two standard deviations from the mean?
b. We should use year in position not 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 re-adjusted 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!!!!
Michelle
From United States, Chesterfield
To answer your questions:
1. I am using a salary survey from CUPA (which stands for College and Universal Professional Association for Human Resources). The survey is filled with data from various Universities from all over the United States. When I run a report I ask it to compare "University X" (that I am working with) against 15 other Universities that they feel are similar to theirs in size, location, etc. The survey takes each person at "University X" and compares that persons salary with the average of other peoples salary in that same title at the other 15 Universities. The data then is downloaded into a spreadsheet, the spreadsheet 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 is referring 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 Professors 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 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?
a. If so, is this because the salary is at least two standard deviations from the mean?
b. We should use year in position not 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 re-adjusted 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!!!!
Michelle
From United States, Chesterfield
Dear Michelle
1. I am using a salary survey from CUPA (which stands for College and Universal Professional Association for Human Resources). The survey is filled with data from various Universities from all over the United States. When I run a report I ask it to compare "University X" (that I am working with) against 15 other Universities that they feel are similar to theirs in size, location, etc. The survey takes each person at "University X" and compares that persons salary with the average of other peoples salary in that same title at the other 15 Universities. The data then is downloaded into a spreadsheet, the spreadsheet automatically calculates the mean, median etc. for the 15 Universities and tells me how close "University X" employees are to that data.
Autumn Jane: What you have described is known as a Position Analysis. I have attached a sample for your reference.
2. 80% of the midpoint in this case is referring 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 Professors 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".
Autumn Jane: 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 example, when you take your salary and divided by 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 falls above the 120% is your overpaid job(s). Any job(s) that has an index of between 115% to 120% are call “ceiling jobs” as they will max out or reach the max range in near future. The market index analysis makes your salary planning very much easier.
3. I am not sure what you mean by market chart?
Autumn Jane: Have attached a Market Chart for your reference. It is used to compare your overall Pay Practice against the Market.
4. 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 decides to go with 65% and 135%. The decisions depend very much on the company pay philosophy, market demand and supply, etc.
5. If so, is this because the salary is at least two standard deviations from the mean?
Autumn Jane: Yes, one of the reason.
6. 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.
7. 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.
8. 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?
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.
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?
Autumn Jane: You may want to Google “multiple regression”. A lot of details and examples for you to pick up more tips.
Regards
Autumn Jane
From Singapore, Singapore
1. I am using a salary survey from CUPA (which stands for College and Universal Professional Association for Human Resources). The survey is filled with data from various Universities from all over the United States. When I run a report I ask it to compare "University X" (that I am working with) against 15 other Universities that they feel are similar to theirs in size, location, etc. The survey takes each person at "University X" and compares that persons salary with the average of other peoples salary in that same title at the other 15 Universities. The data then is downloaded into a spreadsheet, the spreadsheet automatically calculates the mean, median etc. for the 15 Universities and tells me how close "University X" employees are to that data.
Autumn Jane: What you have described is known as a Position Analysis. I have attached a sample for your reference.
2. 80% of the midpoint in this case is referring 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 Professors 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".
Autumn Jane: 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 example, when you take your salary and divided by 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 falls above the 120% is your overpaid job(s). Any job(s) that has an index of between 115% to 120% are call “ceiling jobs” as they will max out or reach the max range in near future. The market index analysis makes your salary planning very much easier.
3. I am not sure what you mean by market chart?
Autumn Jane: Have attached a Market Chart for your reference. It is used to compare your overall Pay Practice against the Market.
4. 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 decides to go with 65% and 135%. The decisions depend very much on the company pay philosophy, market demand and supply, etc.
5. If so, is this because the salary is at least two standard deviations from the mean?
Autumn Jane: Yes, one of the reason.
6. 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.
7. 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.
8. 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?
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.
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?
Autumn Jane: You may want to Google “multiple regression”. A lot of details and examples for you to pick up more tips.
Regards
Autumn Jane
From Singapore, Singapore
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
Data Storage.pdf
New MIS reports Master Data.xls