XYZ Manufacturers requested you to assist them to calculate their employee’s tax liability for the year. As a Microsoft Excel specialist, you advised them that the starting point would be to compile a spreadsheet from a logical flow of information and formulas.
In order to do that, XYZ Manufacturers provided you with the following information.
• For all employees :
o Employee Number
o Grade Code (the level at which they were appointed)
o Basic Annual Salary
• Travel allowance table (range A3:C8) indicating the travel allowance per annum for every grade code, eg the grade code “GRA” is granted a travel allowance of R60,000 per year (p/a).
• Tax rate table (range E3:G8) indicating the brackets to determine the tax rate to be used in the calculation of the employee’s tax liability, eg a taxable income of R600,000 falls within the R500,001 and R1,000,000 brackets, therefore this employees employee’s tax liability would be calculated using a tax rate of 38%.
• This structure of the employee number code (range A12:A18) is as follows :
| 1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
| BIRTH YEAR |
RANDOM CHARACTERS |
RANDOM CODE |
• XYZ Manufacturers further want to add a payroll function to their current accounting software package. This function requires a sixteen (16) character payroll system code for every employee. XYZ Manufacturers decided not to ad “XX” to the current fourteen (14) character number code to meet this requirement.
• The structure of the payroll system code will therefore be as follows:
| 1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
| BIRTH YEAR |
RANDOM CHARACTERS |
RANDOM CODE |
X |
X |
OPEN PDF HERE
Please login to get access to this quiz
