Compute Salary Payable based on Attendance and Performance Data

l  Problem

The finance department of an enterprise needs to compute the staff salary and export the data to the bank. The salary is calculated chiefly based on the employee absenteeism rate and work performance. The rule is as follows:

Basic salary * (1- absenteeism rate + performance)

Next deduct the personal income tax from the amount. The personal income tax is calculated in this way: first deduct 2,000 dollars, which is the tax-free part, from the income, and then calculate the tax charged on the remaining part,which is the tax base, according to different tax brackets in a cumulative way. The tax rate for each bracket is as given below:

 Levels Containing tax grade difference Tax rate(%) Tax reduction factor 1 below 500 5 0 2 500-2000 10 25 3 2000-5000 15 125 4 5000-20000 20 375 5 20000-40000 25 1375

For example, suppose an employee’s salary is 6,000 dollars. Subtracting 2,000 dollars, we will have a taxable amount of 4,000 dollars According to the tax brackets, the tax payable is:

500*5%+1500*10%+2000*15%=475 dollars

You can also use the tax reduction factor to compute:

4000*15%-125=475 dollars

The table below is the employee information table of the enterprise:

The table below is the Absenteeism rate table of this month. If an employee was not absent in this month, then no record of the employee will be found in this table.

The table below is the Performance table of employees in this month. Similarly, not all employees have their performance records in this table.

Please compute the salary amount payable for every employee, and export the result as a TXT file for the bank. The format of TXT file should be:

Company name

TOAM= Total payroll

COUT= Number of employees

---------------------------------------

Bank account | Salary payable | Name

Bank account | Salary payable | Name

……

l  Tip

1.  Create a new table sequence based on the Employee table and keep the Name, BasePay, and AccountNo fields. Add the Absenteeism, Performance, and SalaryPayable fields, in which the values of Absenteeism field are the corresponding records from the Absenteeism table, and those of Performance field are the corresponding records from the Performance table. As for the SalaryPayable field, write 0 to it.

2.  Compute the salary payable for every employee with the formula, and write it to the SalaryPayable field. As for employees without absence or performance record, the Absenteeism or Performance field will be empty and be taken as 0 during computation, which meets our requirement.

3.  Copy the TaxRate table to the code cellset and convert it to a table sequence to facilitate the computation.

4.  Compute the personal income tax for every employee. Firstly, find the salary bracket to which the employee belongs; secondly, deduct the tax reduction factor from the tax base directly to get the tax amount; finally, subtract it from the salary.

5.  Export to the TXT file. Firstly, create a TXT file object, then write the company name, salary payable in total, the number of persons  to whom money will be transferred and the separator line.

6.  Loop through the above table and write the records to the TXT file one by one in the required format.

l  Code

 A B C 1 =file("C:\\txt\\Employees.txt").import@t() Employee table 2 =file("C:\\txt\\Absenteeism.txt").import@t() Absenteeism table 3 =file("C:\\txt\\Performance.txt").import@t() Performance table 4 /Salary /Tax Rate(%) /Tax Reduction Factor Tax rate table 5 500 5 0 6 2000 10 25 7 5000 15 125 8 20000 20 375 9 40000 25 1375 10 D:\Bank Pay File.txt Path to export TXT file 11 =A1.new(Name,A2.select@1(Employee==ID).Absenteeism:Absenteeism,A3.select@1(Employee==ID).Performance:Performance,BasePay,AccountNo,0:SalaryPayable ) Based on the Employee table, newly create the table sequence of the new field structure and select the absenteeism and attendance data 12 >A11.run(SalaryPayable=BasePay*(1-Absenteeism+Performance)) Compute the salary payable of each employee 13 =create(Salary,TaxRate,TRF).record([A5:C9],0) Convert the TaxRate table to the table sequence 14 for A11 =A13.select@1(Salary>=A14.SalaryPayable) Cycle the Salary table and find the tax bracket for each employee 15 >A14.SalaryPayable=round(A14.SalaryPayable-(A14.SalaryPayable*B14.TaxRate/100-B14.TRF),2) Compute the tax for each employee and deduct it from the salary payable 16 =file(A10) Create the file object 17 >A16.write("RAQSOFT INCORPORATION") Write the company name 18 >A16.write@a("TOAM="+string(round(A11.sum(SalaryPayable),2))) Write the total salary payable continuously 19 >A16.write@a("COUT="+string(A11.count())) Write the total persons to transfer 20 >A16.write@a("---------------------------------------") Write the separator line 21 >A11.run(A16.write@a(string(AccountNo)+" | "+string(SalaryPayable)+" | "+Name)) Respectively write the account, salary and name of each employee

l  Result