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
