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. 


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


  • 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


Note: your output may have some rounding differences and that's ok as it will depend on the tool you use

7 data fields: 
  • StaffID 
  • Salary
  • Max Tax Rate
  • Total Tax Paid
  • 20% rate tax paid
  • 40% tax rate paid
  • 45% tax rate paid
You can download the outputs from here. If you want to check your results. 

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! 

Popular posts from this blog

2023: Week 1 The Data Source Bank

2023: Week 2 - International Bank Account Numbers

How to...Handle Free Text