- Task 2 Worksheet 2 1 Question 4 Marks Your Employees Are Assigned Different Amounts Of Money To Cover Their Work Re 1 (23.95 KiB) Viewed 54 times
- Task 2 Worksheet 2 1 Question 4 Marks Your Employees Are Assigned Different Amounts Of Money To Cover Their Work Re 2 (67.75 KiB) Viewed 54 times
Task 2 (Worksheet 2). 1 question (4 marks) Your employees are assigned different amounts of money to cover their work-related expenses, based on their job roles. Jobs in city locations have a larger expense budget than those in regional locations (since regional locations are generally cheaper).
Cells Task/problem Example Q1 E4:G11 Functions/features you should use Define 12:L18 as a Named Range called "Budget" and use this in your VLOOKUP. 4 marks Define N2:06 as a Named Range called "Locations" and use Write a formula to extract the expense As Naomi's job role in C3 is amount for 2022 to 2024 for the Job Role "Executive officer (ExOf)", in column C (e.g., Naomi is an Executive we need to match this with Officer) from the Expense Budget table in the "EO" in the Expense columns I:L. Budget table and use the Locations table to find the Note that each job role has two rows in right expense budget "City". the Expense Budget table, depending For Naomi the Budget role is upon whether it is in a city or regional EO (City) and using the location. For example, as Naomi is in Expense Budget Table we Milton, which is listed as a city location in can find that the amount for the locations table (columns N:O) her 2022 is $25,000, for 2023 it 2022 budget is $25,000. is $26,250 and for 2024 it is $27,562. If there is an error, e.g. #N/A, the result of the formula should be a question mark Therefore, these values ("?"), which will indicate to staff that should be displayed in E4, something went wrong and should be F4, G4. checked. this in your VLOOKUP. You must include functionality in your formula to automatically select the appropriate year column from the table. Do not manually edit these for each column. Enter the formula into E4 and copy the formula across and down for all three years for all employees.
EMPLOYEES Employeeld Name Job role Location Expense budget 2023 2022 2024 LOCATIONS Location Category Milton City Broadbeach City Toowoomba Regional Lucia City 123A 123B 123G 1230 123E 123F 123H 123D Naomi James Amos Alex Bobi Clarissa Fred Drummer Executive officer (Exof) Administration assistant (AdAs) Project manageer (PrMa) Research manager (ReMa) Accounts manager (AcMa) Team lead (Tele) Finance officer (Fiof) Head of division (HeDi) Milton Milton Broadbeach Milton Broadbeach St Lucia Milton Toowoomba Budget role EO (City) EO (Regional) AA (City) AA (Regional) RM (City) RM (Regional) AM (City) AM (Regional) HD (City) HD (Regional) PM (City) PM (Regional) TL (City) TL (Regional) FO (City) FO (Regional) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ EXPENSE BUDGET 2022 2023 25,000 $ 26,250 $ 17,000 $ 19,000 $ 2,000 $ 2,100 $ 1,200 $ 1,260 $ 15,000 $ 15,750 $ 9,000 $ 9,450 $ 8,000 $ 8,400 $ 4,800 $ 5,040 $ 20,000 $ 21,000 $ 12,000 $ 12,600 $ 15,000 $ 15,750 $ 9,000 $ 9,450 $ 5,000 $ 5,250 $ 3,000 $ 3,150 $ 2,500 $ 2,625 $ 1,500 $ 1,575 $ 2024 27,562 22,100 2,205 1,323 16,537 9.922 8,820 5,292 22,050 13,230 16,537 9,922 5,512 3,307 2,756 1,653