2017 S01 P03 EXCEL PAPER 2

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
s2Member®