Human resources

HR and Payroll

You run payroll for a 60-person company. For the June run you need each person’s tenure, monthly pay including overtime (paid at 1.5× the hourly rate, where hourly = salary ÷ 2080), tax from a bracket table, net pay and the annual bonus from their rating. Every rate lives in a cell so HR can change it without touching formulas.

Deliverable: Tenure, base, overtime, gross, tax rate, net pay and bonus columns, a payroll summary, a headcount PivotTable and the top six salaries highlighted.

1. 1Years of service10 pts
2. 2Base and overtime pay15 pts
3. 3Gross pay10 pts
4. 4Tax bracket and net pay20 pts
5. 5Annual bonus10 pts
6. 6Payroll summary15 pts
7. 7Headcount by department10 pts
8. 8Top six salaries10 pts