16. 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:

9-1

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.

31-1

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

31-2

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