PAYROLL INSTRUCTIONS

BONUS REPORT

What function would I use for task 1? XLOOKUP?

6 7 Task 1 8 On tab Payroll Instructions are some bonuses that have been input into our HR Information System (HRIS). We have pulled a report of this input into tab Bonus Report. The bonuses are to be paid and awarded in March 2021. Please can you use Excel formulae double check the Bonus Report against the Payroll Instructions and highlight in yellow any errors in the Bonus report with the description of the error in a new column" Comments" in the Bonus report. 9 10 11 Task 2 12 Using Tab Headcount as of Date, please create 3 pivot tables in a single new tab called PIVOT ANALYSIS. Then use these pivots to answer the questions below: 13 14 Metric Your answer 15 what was the total headcount (number of employees) employed on 01 Jan 2019 16 what was the total headcount employed on 31 Dec 2019 17 What is the average of the above 2 headcounts 18 What number of leavers was there in 2019 what was the percentage turnover of employees in 2019 (leavers in 2019 divided by the average 19 headcount in 2019) 20 21 C E E A B 1 Payroll ID Employee Name 2 1003768 A Uddin 3 1003783 B Jones 4 1003799 C Taylor 5 1003800 D Williams 6 1003805 E Harris 7 1003822 F Chan 8 1003921 G Ali 9 1004104 H de Silva 10 1004116 | Khatun 11 1004155 J Mohamed 12 1004163 K Bibi 13 1004167 L Johnson 14 1004176 M Patel 15 1004182 N Smith 16 1004183 O Baker 17 1004187 P Martin 18 1004190 Q Brown 19 1004196 R Johnson 20 1004208 S Ahmed Bonus Type Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus Annual Bonus D Amount (GBP) 250 100 2000 100 750 3000 27128 200 100 150 75 5000 450 375 900 2000 22750 800 300 21 22 23 24 25 26 27 28 29 30 31 A B D E G H Job Level 1 Payroll Employee Name 2 1004163 K Bibi 3 1003783 B Jones 4 1003800 D Williams 5 1004116 | Khatun 6 1004155 J Mohamed 7 1004104 H de Silva 8 1003768 A Uddin 9 1004208 S Ahmed 10 1004182 N Smith 11 1004176 M Patel 12 1003805 E Harris 13 1004196 R Johnson 14 1004183 O Baker 15 1003799 C Taylor 16 1004187 P Martin 17 1003822 F Chan 18 1004167 L Johnson 19 1004190 Q Brown 20 21 22 23 24 25 26 27 28 Date of birth Effective Date Base Salary 01/01/1978 01/03/2021 02/01/1978 01/03/2021 03/01/1978 01/03/2021 04/01/1978 01/03/2021 05/01/1978 01/03/2021 06/01/1978 03/01/2021 07/01/1978 01/03/2021 08/01/1978 01/03/2021 09/01/1978 01/03/2021 10/01/1978 01/03/2021 11/01/1978 01/03/2021 12/01/1978 01/03/2021 13/01/1978 01/03/2020 14/01/1978 01/03/2021 15/01/1978 01/03/2021 16/01/1978 01/03/2021 17/01/1978 01/03/2021 18/01/1978 01/03/2021 FTE 25000.00 30000.00 24000.00 28000.00 27000.00 22000.00 38000.00 29000.00 28000.00 76000.00 25000.00 42000.00 31000.00 47000.00 46000.00 52000.00 58000.00 120000.00 Bonus Type 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 1.00 Annual Bonus 0.80 Annual Bonus 1.00 Annual Bonus Account Executive Account Manager Account Executive Account Executive Account Executive Account Executive Account Manager Account Executive Account Executive Business Director Account Executive Account Manager Account Manager Account Director Account Director Business Director Business Director Head Amount Currency 75.00 GBP 100.00 GBP 100.00 GBP 100.00 GBP 100.00 GBP 200.00 GBP 250.00 Euro 300.00 GBP 375.00 GBP 450.00 GBP 750.00 GBP 800.00 GBP 900.00 GBP 2000.00 GBP 2000.00 GBP 3000.00 GBP 5000.00 GBP 22750.00 GBP 29 30 31