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.
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.
Create a Salary Increase Matrix in Excel: Step-by-Step Procedures
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 Salary Increase Percentage in Excel
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)
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.
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
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
lConclusion
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.
Related Articles
- How to Calculate Net Salary in Excel
- How to Calculate Gross Salary in Excel
- How to Calculate Basic Salary in Excel
- Salary Deduction Formula in Excel for Late Coming
- How to Model Salary Regression Analysis in Excel
- How to Calculate Prorated Salary in Excel
- How to Calculate Midpoint of Salary Range in Excel
- How to Calculate Average Salary in Excel
- Daily Wages Sheet Format in Excel
- How to Calculate DA on Basic Salary in Excel
- How to Calculate HRA on Basic Salary in Excel
- Leave Salary Calculation in Excel
