Description
Calculate merit increases based on performance ratings
This excel spreadsheet template is set up to calculate your employee merit increases, by calculating a recommended increase based on employee performance ratings and where their salaries fall in relation to your current salary band structure. There is also a place to enter actual merit increases, if managers want to change it up from the recommended amounts you set.
See an example demonstration of this template:
The merit increase worksheet has been set up so that you can easily add your employee information, and set recommended guidelines for increases based on several factors, including individual performance and where the incumbent pay is relative to their salary band range.
The template consists of 4 pages/tabs that are linked together to save you time. Everything is color-coded so that you know where your information should be entered, and where there is a formula.
Salary Ranges Page
Here you can enter up to 20 of your salary bands or grades, and the associated minimum, midpoint and maximum per band.
Merit Increase Matrix Key
Here you will define recommended salary increases based on where they fall in relation to their salary midpoint and performance rating. Incumbents will fall into 3 categories which you can set/define:
o Below Market/Midpoint
o Within Market/Midpoint
o Above Market/ Midpoint
You can set the compa-ratio range for each of the 3 categories. For example, you can set everyone with a comp-ratio of 70% or less as ‘Below’ market, and everyone with a compa-ratio of 120% is ‘Above’ market, and anyone in between those are ‘within’ market.
You can set the percentages for what makes sense in your organization.
You can also set the performance ratings and recommendation for merit increase based on what they scored in your performance management system.
The template is set up to be compatible with any type of ratings system, and there is space for up to 20 possible performance ratings.
Performance Ratings Page
All that’s needed here is the employee ID and their performance rating.
Employee Detail Page
This is the page where everything comes together!
Employee Data section – In the first section, you will paste the relevant employee details into the file. The columns highlighted green are the main things you’ll need to be sure to include: Employee ID, Salary band or grade, and the employee salary or base pay
Merit Increase section – this section is mostly formulas that will pull in data from the other tabs and link in the salary range data, performance ratings, recommended increase amounts, compa-ratio’s and will also flag if the new salary falls below the band minimum or above the band maximum.
Retro payment amount – If you will be including a retro payment for the increases, be sure to fill out the chart at the top of column AG.
The formulas are set to be used for up to 1,000 employees, however if you need more in the file I can add more options as requested.
Each template comes personalized with the company name of your choice. All templates are to be used for 1 company only and are not to be distributed outside the organization.
Upgrade Option: Merit Program Analysis
After you complete a merit program, or if your program has already been completed, the next step is an analysis of how the program went. With this upgrade option, you can quickly summarize the budget and actual increases per Department, Job Title and Gender.
After saving your data you’ll just assign which Departments and Job Titles you want to summarize, by adding them to column A in the corresponding pages:
After the data is in the template, there are multiple pages of charts that are already set to link to your information.
You can also set the file to view the counts of how many people in each department or job are receiving a certain amount of increase. With this example, we’ve set it to count everyone who has received a 10% increase, or over 100k in new salaries:
There are also extra charts to do a gender pay analysis, with the same data you’ve already entered. These will show the totals per male and female, per department and per job analysis:
The formulas are set to be used for up to 2500 employees, however more options can be added upon request.
Template Details
Custom Changes – I would love to help customize a spreadsheet for your specific situation! My background is in Compensation/HR – with about 7 years compensation experience. I’d love to put my CCP (certified compensation professional) and PHR certifications to good use. I’ve been creating templates and custom excel work for all industries since 2013. Feel free to contact me here for more information or a custom quote.
You will receive an Excel spreadsheet, along with PDF directions via email.
Thanks for visiting!
Want to try a free comp metrics template?
