This is a fringe benefit that is allocated to every worker in order to encourage workers and enhance their performance in the company or organization.
This is a compulsory levy that is deducted from a worker’s basic salary at the end of the month in order to generate revenue for the state and federal government.
5. GROSS PAY:
This is the summation of or the total of a worker’s basic salary and all the allowances he is entitled to at the end of every month.
6. NET PAY:
This is Gross pay minus all deductions such as tax and loans. It is the final take-home of a worker at the end of the month.
PRACTICAL ILLUSTRATION OF A PAYROLL SYSTEM
The manager of JOE-LINKS SERVICES wants to computerize the payroll system of the company. As a spreadsheet expert, design a payroll system to his recommendation and approval to pay his workers on monthly basis such that basic salary is influenced by qualification as follows:
WASSCE = $20,000
OND = $25,000
NCE = $28,000
HND = $32,000
BSc = $40,000
MSc = $50,000
PhD = $80,000 respectively.
Marital allowance would be 8% and 10% for single and married workers respectively. Transport, Feeding, Dressing and Housing allowances would be 6%, 4%, 5%, and 8% of their basic salaries respectively.
Compute a tax deduction such that:
WASSCE = 5%, OND = 6%, NCE = 8%, HND = 10%, BSc = 12%, MSc = 15% and PhD = 20% of their basic salaries respectively.
Calculate the Gross pay and Net pay for each worker.
Setup an advanced query or filter to filter all the married workers with qualification of BSc.
SOLUTION TO THE PAYROLL SYSTEM
You should first create a table like the one contained in the spreadsheet in the figure below:
Payroll System – Table 1
Type in the following formulas in the appropriate cells and use the hand fill tool to generate the results for the subsequent cells in that column:
1. For the BASIC SALARY column, place your cursor in cell D3 and type the following formula:
=IF(C4="WASSCE",20000,IF(C4="OND",25000,IF(C4="NCE",28000,IF(C4="HND",32000,IF(C4="BSc",40000,IF(C4="MSc",50000,IF(C4="PhD",80000,0))))))) press Enter.
2. For the MARITAL ALLOWANCE column, place your cursor in cell E3 and type the following formula:
=IF(B3="Single",D3*8%,IF(B3="Married",D3*10%,0)) press Enter.
3. For the TRANSPORT ALLOWANCE column, place your cursor in cell F3 and type the following formula:
=D3*6% press Enter.
4. For the FEEDING ALLOWANCE column, place your cursor in cell G3 and type the following formula:
=D3*4% press Enter.
5. For the DRESSING ALLOWANCE column, place your cursor in cell H3 and type the following formula:
=D3*5% press Enter.
6. For the HOUSING ALLOWANCE column, place your cursor in cell E3 and type the following formula:
=D3*8% press Enter.
7. For the TAX column, place your cursor in cell J3 and type the following formula:
=IF(C3="WASSCE",D3*5%,IF(C3="OND",D3*6%,IF(C3="NCE",D3*8%,IF(C3="HND",D3*10%,IF(C3="BSc",D3*12%,IF(C3="MSc",D3*15%,IF(C3="PhD",D3*20%,0))))))) press Enter.
8. For the GROSS PAY column, place your cursor in cell K3 and type the following formula:
=SUM(D3:I3) press Enter.
9. For the NET PAY column, place your cursor in cell L3 and type the following formula:
=K3-J3 press Enter.
The completed Payroll System is shown below.
10. Create the three ranges of data. Type “Married” in the M Status column under the criteria range. Type “BSc” in the Qual column under the criteria range.
Use the procedure for performing Advance filter to filter the database based on the specified criteria.
SUBTOTAL AND HOW TO APPLY IT IN AN EXCEL WORKSHEET
Subtotal is an Excel tool used to calculate the total value or amount spent in a particular group of records in a database. It is mainly used in Payroll and Budget Control Systems to calculate the total amount spent in each department of a company.
To Apply Subtotal to a Database in an Excel Worksheet:
1. Prepare and activate the worksheet containing the database you wish to use, for example, a payroll database.
2. Sort the records using the Department column as your sorting field by clicking Sort located in the Sort & Filter group in the Data tab. Select the sort column from the Sort By drop down menu and click OK as shown below.
3. Click on Subtotal located in the Outline group of Data tab. The subtotal dialogue box appears.
4. Specify the changing field from the AT EACH CHANGE IN drop down menu which in this case should be the Department column.
5. Specify the ADD SUBTOTAL TO field. In the case of a payroll system, specify either the Gross Pay or the Net Pay column depending on your intention.
6. Put a check the Replace current subtotals and Page break between groups boxes and click OK as shown below.
Excel does the calculation for you and returns the values in
a row below the last record in each group as shown below.
SOME SAMPLE QUESTIONS
UNDER GRADE LIST SYSTEM AND PAYROLL SYSTEM
Please attempt these sample questions:
1. Government Secondary School Owerri wants to computerize her students result in the following subjects:
English, Mathematics, Chemistry, Physics, Biology, Economics, Literature and Government.
a.) As a Spreadsheet Manager, design a result System to compute the Total Score and Average Score.
b.) Using IF function, generate the grade letters for each subject and the overall grade using the following grade Scheme:
0 – 39.9 = Fail
40 – 49.9 = Pass
50 – 59.9 = Credit
60 – 69.9 = Upper Credit
70 – 100 = Distinction
c.) Generate 30 records and Set up an advance filter to filter Science Students from Imo State.
2. a.) Zenith Bank PLC wants to computerize her worker’s payroll system to compute the Basic Salary based on the following qualifications:
SSCE = $1200
NCE = $1300
HND = $1500
BSc = $1800
MSc = $2200
PhD = $2800
b.) Compute 8%, 12%, 7.5%, 6% and 5% of the Basic Salary as Housing, Transport, Medical, Feeding and Dressing allowances.
c.) Tax deduction should be calculated as follows:
SSCE = 1.5%
NCE = 2%
HND = 2.5%
BSc = 3%
MSc = 3.5%
PhD = 4%
d.) Calculate the Gross and Net Pay.
e.) Set up a subtotal to calculate the total amount spent in each department.
This is the end of this part.
Recommended MS Excel Textbook
Now go over to the next part
of this tutorial chapter: