Acetone Engineering firm would like to use Ms Excel to prepare a payroll for their employees. The following rules are used when determining how much each employee is paid: 1. Employees in management are paid R1500 per hour, other fulltime employees are paid R950 per hour and part time employees are paid R750 per hour. This is considered the basic income for each employee Fulltime employees are paid overtime when they work for more than 160 hrs per month and part-time employees are paid overtime when they work for over 120hrs per month. Overtime is paid at R1000 per hour. Employees in management are not paid any overtime. 11. ili. Management and fulltime employees are also paid a house allowance which is 15% of the basic pay, and a uniform allowance of 3% of the basic income. iv. The gross income is the total of the basic income, overtime, and the uniform allowance. The taxable income is the total of the basic income and overtime income. Each employee is subjected to the following deductions: v. a. Tax deductions based on the following tax bands Taxable Income Income Tax Deduction R 0 5000 5% R 5001 - 15000 10% R 15001 - 35000 15% R35001 and above 20% b. Social security deductions - 3% of gross income c. National Health Insurance deductions based on the gross income based on the following schemes Gross Income Deduction RO-R10000 R300 R10001 - R20000 R500 R20001 - R30000 R800 R30001 - R40000 R100 R40001 and above R1500 d. Unemployment fund 2% of gross income e. Skills development levy - 1% of taxable income f. Any loan repayment deductions requested by the employees vi The net income is calculated by subtracting all deductions from the gross income of each employee. Question 1 (36mks) Given the following employee information, create a Macro enabled workbook with macros to calculate the following items for each employee. Employee ID 55645 55061 Hours worked 170 160 Loan Repayment 5000 0 55168 0 1 2 3 4 5 6 166 170 55654 55866 55261 55628 55137 55396 Category Management Fulltime Fulltime Fulltime Part time Fulltime Fulltime Part time Fulltime Fulltime Management Fulltime Fulltime Part time Part time 120 160 155 130 7 8 9 3000 1500 4000 0 0 175 4000 166 0 10 11 8000 55835 55582 55799 55804 55371 55202 176 168 160 12 13 14 15 0 0 140 o 6000 128 X. 1. Basic income vii Tax deduction 11. House allowance vill. Social security deductions 111. Overtime payment ix. National health insurance deductions iv. Uniform allowance Unemployment fund deductions Gross income xi. Skills development levy vi. Taxable income xii. Net income For each macro created/recorded add a command button and link it to the Macro. V. Question 2 (14mks) Using the skills that you have learnt in Excel, create a payslip for each employee. Each employee's payslip should be created on a separate spreadsheet. The payslip should have the following information: basic income, overtime pay, house allowance, uniform allowance, gross income tax deduction, social security deductions, national health insurance deductions, unemployment fund deductions, skills development levy, total deductions, and net income. In each employee's payslip add a pie chart showing the proportion of the net income and the total deductions. Acetone Engineering firm would like to use Ms Excel to prepare a payroll for their employees. The following rules are used when determining how much each employee is paid: 1. Employees in management are paid R1500 per hour, other fulltime employees are paid R950 per hour and part time employees are paid R750 per hour. This is considered the basic income for each employee Fulltime employees are paid overtime when they work for more than 160 hrs per month and part-time employees are paid overtime when they work for over 120hrs per month. Overtime is paid at R1000 per hour. Employees in management are not paid any overtime. 11. ili. Management and fulltime employees are also paid a house allowance which is 15% of the basic pay, and a uniform allowance of 3% of the basic income. iv. The gross income is the total of the basic income, overtime, and the uniform allowance. The taxable income is the total of the basic income and overtime income. Each employee is subjected to the following deductions: v. a. Tax deductions based on the following tax bands Taxable Income Income Tax Deduction R 0 5000 5% R 5001 - 15000 10% R 15001 - 35000 15% R35001 and above 20% b. Social security deductions - 3% of gross income c. National Health Insurance deductions based on the gross income based on the following schemes Gross Income Deduction RO-R10000 R300 R10001 - R20000 R500 R20001 - R30000 R800 R30001 - R40000 R100 R40001 and above R1500 d. Unemployment fund 2% of gross income e. Skills development levy - 1% of taxable income f. Any loan repayment deductions requested by the employees vi The net income is calculated by subtracting all deductions from the gross income of each employee. Question 1 (36mks) Given the following employee information, create a Macro enabled workbook with macros to calculate the following items for each employee. Employee ID 55645 55061 Hours worked 170 160 Loan Repayment 5000 0 55168 0 1 2 3 4 5 6 166 170 55654 55866 55261 55628 55137 55396 Category Management Fulltime Fulltime Fulltime Part time Fulltime Fulltime Part time Fulltime Fulltime Management Fulltime Fulltime Part time Part time 120 160 155 130 7 8 9 3000 1500 4000 0 0 175 4000 166 0 10 11 8000 55835 55582 55799 55804 55371 55202 176 168 160 12 13 14 15 0 0 140 o 6000 128 X. 1. Basic income vii Tax deduction 11. House allowance vill. Social security deductions 111. Overtime payment ix. National health insurance deductions iv. Uniform allowance Unemployment fund deductions Gross income xi. Skills development levy vi. Taxable income xii. Net income For each macro created/recorded add a command button and link it to the Macro. V. Question 2 (14mks) Using the skills that you have learnt in Excel, create a payslip for each employee. Each employee's payslip should be created on a separate spreadsheet. The payslip should have the following information: basic income, overtime pay, house allowance, uniform allowance, gross income tax deduction, social security deductions, national health insurance deductions, unemployment fund deductions, skills development levy, total deductions, and net income. In each employee's payslip add a pie chart showing the proportion of the net income and the total deductions