2024: Week 6 - Staff Income Tax
Created by: Carl Allchin
Welcome to the first week of the Intermediary level challenges for 2024. This means we'll leave more space for you to work out the logic and be less specific about the techniques you are likely to need.
The end of January in the UK (where Prep Air is based) is when residents have to submit their income tax returns by. To help our team, we've offered to summarise their tax position for them. The UK income tax works by bands. Here's a summary table showing the percentage of tax for each pound earned in that bracket:
For example, if I earned £12,571. I would pay £0.20 of tax in total: £0 for the first £12,570 earned and then 20% of the £1 in the next tax band.
Input
One csv file containing the monthly salary for staff. If any team member has a change in their pay, their new salary is recorded as a later record but the input contains their former record based on what they would have been paid
Requirements
- Input the csv file
- Add a row number to the data set
- Find the latest row (largest row number) to capture the individuals correct salary information
- Find each team member's annual salary
- Find each team member's maximum tax band based on their annual salary
- 20% rate
- 40% rate
- 45% rate
- Work out how much tax an individual paid for each of the % bands. Call these fields:
- 20% tax rate paid
- 40% tax rate paid
- 45% tax rate paid
- Total the tax paid across all three % bands. Call this field 'Total Tax Paid'
- Output the data
Output
7 data fields:
- StaffID
- Salary
- Max Tax Rate
- Total Tax Paid
- 20% rate tax paid
- 40% tax rate paid
- 45% tax rate paid
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!
