August 1, 2019

Analyzing base pay by department

Analyzing base pay by department

Let's say we want to compare the base pay of employees by department.  

We start by importing the data into an R project:


Let's see how's the distribution of the base pay by depts like:


We can see that Production dept has the highest median base pay, and Service & Support dept has the lowest.

Finance dept and the Service & Support dept has the biggest distance between those in 3rd quartile and the median base pays.  

That means the difference is the biggest among the departments.  Usually the difference is used to reward the high performance individuals from the typical in the dept.

Let's create a metric measuring the ratio between the median of those in quartile 4 and the median of the dept.  Let's call it compensationDiff.  For example, a ratio of 1.2 means that the median of high performance receive 20% more compensation than the median o the dept.

Finding the departments' median pay is easy, as shown by the following table (table a):

In order to find the median of quartile 4, we'd need to first group the base pay by dept and then find the the quartile (quartile 1, quartile 2, quartile 3, and quartile 4) the base pay belongs to:


After that we do a group by of (Dept, quantile), and find the median pay:


Quartile4's median is found by selecting the maximum value among's the quartiles' median, as shown by the table (table b) below:


In order to find the ratio, we would combine table a and table b together, using a left join.  And then we'd just need to calculate the ratio:


In order to communicate the compensation difference among the depts easier, we'd use some charts:


But you can see that it is not too useful because the x Axis is sorted by Dept (A-Z).  It might be better to sort the departments by compensationDiff, and to show the ratio clearer.  Let's do it another way, using the Cleveland dot plot:


We can make a conclusion from the Cleveland dot plot that Service Support & Finance depts have the ratio of 1.3.  That mean's the high performers draw a base pay 30% higher than a typical employee in their respective departments.

Is it justifiable for that premium?  I think this is a difficult question to answer.  Please let @mig2cloud know if you have any suggestions.

Find out how Mig2 Cloud's analytics services can help you automate your monthly reports.