• How compensation frameworks are developed by HR teams
• How comparative ratios and pay ranges help companies to ensure employee pay is compliant with regulations and internal policy
Scenario
As a C&B leader, you are expected to share insights and coach your HR customers (often times Managers and Business Leaders) on the upcoming salary planning cycle. Specifically, they want your advice on how salary increases should be distributed and to which employees. You will base your recommendations on data analysis, which includes determining comparative ratios (also known as compa-ratios) and understanding what they mean in context.
You will perform the data analysis in the spreadsheet provided in the case (GE Aerospace: Explore Human ResourcesLinks to an external site.). We know that not everyone is experienced with spreadsheet software like Excel, so this exercise can either be done using formulas or by looking up values and doing the calculations individually.
We suggest that everyone try to use formulas before attempting to do the calculations individually. Consider this a safe environment in which to learn more about spreadsheet software and challenge yourself!
You should formulate your suggestions by following the step-by-step instructions below. Please read the instructions carefully before making an attempt.
Step-By-Step Instructions
1. Download the Template in Task 3.
2. Start on the first tab, “Employee details”, and note that you have information about the jobs and salaries of fifteen employees. You will be coaching their manager on whether any of these employees requires a major pay increase this salary planning cycle.
3. Move over to the second tab – “All employees with pay ranges”. This document shows pay range information for every single employee in the company, including the fifteen on the team you are advising.
4. Move back to the “employee details tab”. If you scroll over to the right, you’ll see two blank columns, I and J. Your goal is to find or calculate the information that goes in these columns.
5. For column I, “Pay Range Mid Point”, you will need to use information from the “All employees with pay ranges” tab to find the pay range Midpoint associated with each of your fifteen employees.
1. Be sure to use employee ID rather than name to look up this information.
2. If you are experienced with Excel/spreadsheet software or would like to learn something new, you can use a “VLOOKUP” to do this very quickly. If you’d like to learn about VLOOKUP, go hereLinks to an external site..
6. After you have filled in the pay reference midpoint for each employee, find the comparative ratio (compa-ratio) for each.
1. The formula for this can be found in section 2 of the resource documents - these are the same as the documents provided in the previous step.
2. Again, you can calculate each by hand, or you can use a formula to move more quickly or challenge yourself.
7. Now we must tell the manager who requires a raise. To do this, segment the comparative ratio values into three segments (less than 80%, between 80%-100% and greater than 100%). Highlight those who are below 80% and those who are between 80-100% using two different colors.
1. You can highlight by hand or use conditional formatting to do it automatically. To learn more about conditional formatting, go hereLinks to an external site..
Write a 1-2 paragraph summary explaining who requires a substantial pay increase and what the manager might want to consider when determining if such an increase is required.