Ranking of Employees in Excel (Management Consulting Projects)

Description
Ranking of Employees in Excel (Management Consulting Projects)
During consulting projects, you will have to take into account many criteria to make the optimal solutions. You will have to deal with complex issues, analyze them and present them in an easy, digestible way to your customers. One of the most useful tools that will help you achieve that is a ranking. It can turn complex reality into one metric that can be easily understood.
In this tool, I will show you how you can rank different employees using standardized criteria.
The tool will help you to rank the employees from the best to the worst. This is the starting point of a lot of discussions, especially around topics like:
1. Who should we fire and who should we promote
2. What support we should provide and to whom
3. Which employees have improved their position in the ranking

A few words about the ranking.
1. Input Data. The data about the employees should be put in the sheet “Data”. In B example we rank different data scientists. We have 4 criteria: Analytical Skills (column “G”), Programming Skills ((column “H”), How universal he or she is? (column “I”), # of completed analyses (column “J”). For every criterion, you rank every employee from 1 to 5. Afterward, in row 3 you define the weights for every criterion. Based on that in column “K” the tool calculates the average weighted score for every employee. In column “L” the employee is assigned to a category (based on the score)
2. Ranking. In the sheet “Ranking” you have a ranking of all employees. You have also slicers that enable you to browse through the data. If you change the data in the sheet “Data” refresh the Pivot Table to get the adjusted results
3. Analysis. We have also added analysis of the groups – it shows you what percentage of all data scientists are in a specific group
4. Criteria. The exact definition of every level for the criteria is done in the sheet “criteria”