2021: Week 49 Departmental December Human Resources
Created by: Carl Allchin
This week's challenge continues Departmental December. Human Resources data is often required in lots of different formats depending on the questions being asked of it. The output from this week is a summarised data set used for analysis.
Input
This data set is about Chin & Beard Suds Co. dedicated sales team. They have helped establish some of our wholesale clients so we want to understand more about their tenure with us and the actual cost we have spent on them (salary plus bonus).
Requirements
- Input data
- Create the Employment Range field which captures the employees full tenure at the company in the MMM yyyy to MMM yyyy format.
- Work out for each year employed per person:
- Number of months they worked
- Their salary they will have received
- Their sales total for the year
- For each Reporting Year (the individual year someone worked for us), calculate their cumulative months (called Tenure)
- Determine the bonus payments the person will have received each year
- It's 5% of their sales total
- Round Salary Paid and Yearly Bonus to two decimal places
- Add Salary Paid and Yearly Bonus together to form Total Paid
- Output the data
Output
- Name
- Employment Range
- Reporting Year
- Tenure by End of Reporting Year
- Salary Paid
- Yearly Bonus
- Total Paid
10 rows (11 rows incl. headers)
You can download the full output here.
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
