AIN3701
Assignment 2 2024
Unique number: 592338
Deadline: 12 July 2024, at 23:00
,Data analysis and automation
, Task Overview
You have been tasked with creating a solution in Excel using VBA (Visual Basic for
Applications) to calculate income tax based on provided tax brackets. Here's a
structured approach to achieve this:
1. Understanding the Requirements
• You need to calculate income tax based on different tax brackets provided in
an Excel spreadsheet (Assignment_2.xlsx).
• The tax calculation involves:
o Determining the applicable tax bracket based on the employee's yearly
salary.
o Calculating the tax amount using the formula provided (base tax
amount plus a percentage of the income above a threshold).
• Age-based rebates should be considered:
o Primary rebate: R17,235 for everyone.
o Secondary rebate: R9,444 for taxpayers aged 65 and older.
o Tertiary rebate: R3,145 for taxpayers aged 75 and older.
2. Setting Up Your Excel Workbook
• Download the provided Excel file (Assignment_2.xlsx).
• Create a new worksheet named "Taxtables" where you will perform your
calculations.
3. Approach to Solve the Problem
a. Identify Tax Brackets
• Locate the tax brackets in columns A to E of the "Taxtables" worksheet.
• Each row represents a tax bracket with the minimum and maximum income
limits, base tax amount, and percentage applicable.
b. Calculate Tax Function
• Create a function in VBA (Visual Basic for Applications) to calculate tax based
on the provided tax brackets and income:
o Determine the tax bracket based on the employee's yearly salary.
o Calculate the tax amount using the formula:
Tax=(Income−Base Amount)×Percentage100+Base Tax Amount\text{T
ax} = (\text{Income} - \text{Base Amount}) \times
\frac{\text{Percentage}}{100} + \text{Base Tax
Amount}Tax=(Income−Base Amount)×100Percentage
+Base Tax Amount
o Apply age-based rebates:
▪ Subtract R17,235 (primary rebate) from the calculated tax.
Assignment 2 2024
Unique number: 592338
Deadline: 12 July 2024, at 23:00
,Data analysis and automation
, Task Overview
You have been tasked with creating a solution in Excel using VBA (Visual Basic for
Applications) to calculate income tax based on provided tax brackets. Here's a
structured approach to achieve this:
1. Understanding the Requirements
• You need to calculate income tax based on different tax brackets provided in
an Excel spreadsheet (Assignment_2.xlsx).
• The tax calculation involves:
o Determining the applicable tax bracket based on the employee's yearly
salary.
o Calculating the tax amount using the formula provided (base tax
amount plus a percentage of the income above a threshold).
• Age-based rebates should be considered:
o Primary rebate: R17,235 for everyone.
o Secondary rebate: R9,444 for taxpayers aged 65 and older.
o Tertiary rebate: R3,145 for taxpayers aged 75 and older.
2. Setting Up Your Excel Workbook
• Download the provided Excel file (Assignment_2.xlsx).
• Create a new worksheet named "Taxtables" where you will perform your
calculations.
3. Approach to Solve the Problem
a. Identify Tax Brackets
• Locate the tax brackets in columns A to E of the "Taxtables" worksheet.
• Each row represents a tax bracket with the minimum and maximum income
limits, base tax amount, and percentage applicable.
b. Calculate Tax Function
• Create a function in VBA (Visual Basic for Applications) to calculate tax based
on the provided tax brackets and income:
o Determine the tax bracket based on the employee's yearly salary.
o Calculate the tax amount using the formula:
Tax=(Income−Base Amount)×Percentage100+Base Tax Amount\text{T
ax} = (\text{Income} - \text{Base Amount}) \times
\frac{\text{Percentage}}{100} + \text{Base Tax
Amount}Tax=(Income−Base Amount)×100Percentage
+Base Tax Amount
o Apply age-based rebates:
▪ Subtract R17,235 (primary rebate) from the calculated tax.
