Aniruddah Alam
Excel is an excellent tool to calculate and track records of employee performance, salaries, and other related data. In this article, we will learn how we can create a salary increase matrix in excel step by step. We will also provide a formula and a related example to demonstrate how to calculate salary increases using a salary increase matrix.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
What Is a Salary Increase Matrix?
A salary increase matrix sometimes referred to as a merit increase matrix or merit matrix, is a mathematical grid that compensation specialists offer to assist business managers in precisely and successfully giving salary raises to an organization’s personnel. There are two types of salary increase matrices.
- Standard Salary Increase Matrix. (Only employee performance is considered)
- Two-Variable Salary Increase Matrix. (Employee performance and compa-ratio are considered)
Currently, the Two Variable Salary Increase Matrix system is more popular than the standard one. Hence, we will learn how to create a salary increase matrix based on two variables. They are Employee Performance and compa-ratio.
Two Variable Salary Increase Matrix
In this type of salary increase matrix, there are usually two criteria based on which salary is increased.
Employee Performance: Usually employee performance is categorized into 5 levels from 1 to 5 where 1 being the lowest performer and 5 being the best performer.
Compa-Ratio: It is the short form of a comparative ratio. it is the measurement of current salary and market average expressed in percentage. The formula of the compa ratio is below
Compa ratio= (Current Salary/ Market Average) * 100
In the matrix, there will be 2 axes. One is vertical and the other is horizontal. See the figure below.
Now, we need to put the values into those cells in such a way that every cell is dependent on the anchor cell.
Step-by-Step Procedures to Create a Salary Increase Matrix in Excel
In this section, we will demonstrate how we can establish relationships between the anchor cell (the cell on which values of all the other cells will be calculated) and create a salary increase matrix.
Step 01: Selection of Anchor Cell
- Here, we will first choose an anchor point. It is the point based on which all the other cell values will be calculated.
- For convenience, we should choose the cell in the middle of the matrix. Here we choose cell It has a 90 to 100% compa ratio and Employee Performance of 3.
- Then, we need to input the percentage increase in salary for this cell. For demonstration purposes, we take the value of 3%.
Read More: How to Calculate Average Salary in Excel (6 Effective Methods)
Step 02: Setting Weightage Factors for Horizontal & Vertical Scale
- After that, we need to set values to the other cells with respect to the value in cell F8. To do that, we first need to establish the relationship based on some weightage factors.
- For instance, we usually would like to give more raise to a level 5 performer than a level 1 As a result, level 5 will have more weightage factor and it will gradually decrease from level 4 to 1.
- On the other hand, the employee who is underpaid compared to the current market (compa-ratio of 90% or less), deserves more raises than those who are already paid better(compa-ratio of 100% or more).
- By taking those considerations, we take some dummy weightage percentages for each axis. (see the figure below)
Read More: How to Calculate Net Salary in Excel (With Easy Steps)
Similar Readings
- How to Make Salary Sheet in Excel with Formula (with Detailed Steps)
- Attendance Sheet with Salary in Excel Format (with Easy Steps)
- How to Calculate Income Tax on Salary with Example in Excel
- Leave Salary Calculation in Excel (With Easy Steps)
- How to Calculate Bonus on Salary in Excel (7 Suitable Methods)
Step 03: Application of Formula in the Cells
- Now that we have taken the weightage factor, we will use them to calculate the value for each cell.
- First, go to cell D3 and write down the following formula.
=$F$8*$B6*D$3Here, we multiplied the anchor value F8 with vertical weightage factor B6 and horizontal weightage factor D3. Please notice that we have locked the cell reference properly so that we can copy the formula to other cells as well.
- After that, you should have the following results.
- Moreover, use the Fill Handle to auto-fill the formula in other cells.
- Now that all the cell values are connected with the anchored value if we change the value in F9 (the anchor) then all other cell values will also subsequently get changed.
- Now, our Salary Increase Matrix is ready to use.
Read More: How to Calculate Gross Salary in Excel (3 Useful Methods)
Step 04: Use of Salary Increase Matrix to Calculate Salary.
- Suppose, we have a list of employees, their IDs, Employee Performance, Compa Ratio, and basic salary. (See the figure below)
- Then, we want to determine the %Raise for each employee. To do that, we have to find out the corresponding value according to Employee Performance and Compa-Ratio in the Salary Matrix.
- To automatically determine the value, apply the following formula in cell. It uses the INDEX and the MATCH functions.
=INDEX($D$6:$H$10,MATCH($L6,$C$6:$C$10,0),MATCH($M6,$D$5:$H$5,0))🔎How Does the Formula Work?
- MATCH($M6,$D$5:$H$5,0)
It searches the Compa Range (M6) in the matrix Horizontal axis of the Compa Ratio(D5:H5) and returns the column number.
- MATCH($L6,$C$6:$C$10,0)
It searches the Employee Performance (L6) in the matrix vertical axis of the Employee Performance (C6:C10) and returns the row number.
- INDEX($D$6:$H$10,MATCH($L6,$C$6:$C$10,0),MATCH($M6,$D$5:$H$5,0))
This returns the value of the cell of the array D6:H10 corresponding to the column number and row number input by Two MATCH functions.
- You should get the following result.
- After that, to get the Raised Amount, multiply the %Raise by the Basic Salary. Use the following formula below.
=$N6*$O6
- Now, use the Fill Handle to copy the formula to other cells. Consequently, you will get the following result.
- So, our final result should look like this.
- To verify, we can check our calculation. For instance, Joe Trump, having Employee ID 1105, has Employee Performance Rating of 4 and a Compa Ratio in the range of Above 120%. If we see at the matrix, we will discover that it has %Raise of 3.2%.
This is how we can construct a Salary Increase Matrix and use it to calculate the salary increase for all the employees.
Read More: How to Calculate Monthly Salary in Excel (with Easy Steps)
Things to Remember
- We can always change the anchor point to adjust all the %Raise all by once.
- We can also adjust the weightage factor according to our needs.
Conclusion
That is the end of this article. Hopefully, now you have a fair idea of how to construct a salary increase matrix in excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.
Related Articles
- How to Create a Monthly Salary Sheet Format in Excel (with Easy Steps)
- How to Calculate Basic Salary in Excel (3 Common Cases)
- Daily Wages Sheet Format in Excel (with Quick Steps)
- How to Calculate DA on Basic Salary in Excel (3 Easy Ways)
- How to Calculate HRA on Basic Salary in Excel (3 Quick Methods)
- Per Day Salary Calculation Formula in Excel (2 Suitable Examples)
