Worksheet 4, Task 4: 1 question (2 marks) Cells Task/problem Ques tion 1 L4:L11 Calculate gross weekly pay: Calculate the staff's Gross Pay (i.e., the hourly rate appropriate to their job, multiplied by the hours worked). For hours worked on Saturday, pay is increased by 50%, and there is a 100% increase for Sunday hours. Example Excel Functions/features to you should use Employee 1908 worked 12 SUM, VLOOKUP, the hours Monday to Friday, and operator (&), YEAR, 1 hour on a Sunday. TODAY, IFERROR Therefore, employee 1908 should paid a for 12 hours at Define the named range the standard hourly rate and A17:B32 as Pay_rates" 1 hour at the rate of 100% and use this in your extra. VLOOKUP Note that JobCodes in B4:B11 refer to the type of position. In the "Pay_rates table", the Job Code has a number, 1-4 after it (e.g., AC1), signifying the pay bracket (hourly rate). The bracket is simply the number of years since the employee was hired. Since employee 1908 has a job code of PT and started working in 2017, their Pay Code from the Pay Rates table is PT4. Therefore, their standard hourly rate is $152.00. Thus, their Gross Pay is $2,127.94 For example, BB1 means that an employee works in the Job Code BB, and has been working for one year. Next year, he/she will become BB2 and earn more money. Therefore, you need to include in your formula a way to determine the Pay Code based on the Job Code and the year the employee was hired (until the current year). If a JobCode does not appear in the pay rates table, the cell should show "Manual review". Note: that no staff member has been hired less than a year ago, or more than four years ago (i.e., pay bracket will only be from 1-4, e.g., BB1 to BB4). JX A B D E F G H 1 J K L M $ Mobile medical services payroll Hours Year Hired Office Mon Tue Wed Thu Fri Sat Sun 2017 Melbourne 3 2 0 4 3 0 1 2018 Melbourne 6 1 5 1 1 8 1 2019 Brisbane 3 0 0 6 4 5 2 2017 Sydney 2 8 1 0 7 3 1 2020 Melbourne 7 7 6 1 1 5 2 2019 Brisbane 0 7 6 2 4 6 8 2017 Melbourne 4 1 8 8 8 3 6 2019 Brisbane 2 8 6 5 6 2 5 $ $ $ $ $ Pay Gross pay 4.00 2,127.94 3,716.93 2,840.45 1,282.79 837.50 2,994.01 1,514.01 | $ $ 1 2 EmployeeID JobCode 3 4 1908 PT 5 1096 6 1901 PT 7 1657 BB 8 1849 AC 9 1569 KE 10 1603 AC 11 1760 XP 12 13 14 15 Pay_rates table 16 Pay code Hourly 17 AC1 $ 25.00 18 AC2 $ 27.50 19 AC3 $ 30.25 20 AC4 $ 33.28 $ 37.27 22 BB2 $ 41.74 23 BB3 $ 46.75 24 BB4 $ 52.36 25 KF1 $ 59.69 26 KF2 $ 68.05 27 KF3 $ 77.57 28 KF4 $ 88.43 29 PT1 $ 101.25 30 PT2 $ 115.94 31 PT3 $ 132.75 32 PT4 $ 152.00 33 BB1 34 35 36