Computation of Salary Using Excel

M&N Health Pro Pte. Ltd is a company that sells health supplements. Ms. Susan Lim, the Operations and Finance Manager of the company, is responsible for managing the payroll of all sales staff in the company.The staff in the company are paid a basic monthly salary shown in the Staff Salary Table below. In addition to the basic salary, they are also given a commission for a specific product that is under their charge.You are required to help Susan to compute the monthly payroll for the staff members using Excel. Create the following tables in Excel and perform

1. Pay Slip

Singapore Labour Law requires all companies to generate monthly pay slips for the staff members. Develop a mail merge template in Microsoft Words linking to your Excel table to help generate the pay slip for each staff member based on the following format:

2.Bar Chart and Other Statistics

(a) Create a Bar Chart to show the overall sales by all staff for the month.

(b) Use the excel function to count the number of staff with Gross Salary less than $3000.

(c) Use the excel function SUMIF to compute the total Gross Salary for staff with age of 55 and below.

3. Computation of Loans

The management has recently approved a 3-year special loan for one employee who wanted to pursue a fulltime Health Management Course. The total amount of the loan is $20,000. He was given a very favourable annual interest rate of 1.1%. The management has decided to deduct the repayment of the loan by following an affordable monthly installment plan over 36 months from the staff monthly salary. You are required demonstrated how Excel can be used to compute the monthly amount to be deducted from the staff salary.

4. Other Applications

Develop one new business application using "vlookup" function that can improve the operations of the company. You will need to show your application in Excel

and explain how this application can improve the business operations.

Table 1: Staff Salary Table Name Staff ID Date Joined Date of Birth Staff Grade Basic Pay Product Code No of Units Per Sold Over Time for the month (hours) 4 Month S01 John Sim 2 $2,800 X01 58 12/08/ 2019 23/02/ 2004 20/09/ 1972 02/11/ 1979 SO2 1 1 $2,400 9 X01 Keith Chan 60 S03 Chandra 3 $2,100 8 X02 78 16/05/ 2014 03/04/ 2012 19/03/ 1982 16/05/ 1959 S04 1 $3,100 5 X03 89 Siti Binti Saad Table 2: Sales Commission Table Product Code X01 X02 X03 Sales Commission Per Unit ($) 2.50 3.00 3.50 Table 3: Staff Rating and Overtime Rate Per Hour Table Staff Grade 1 2 3 Overtime Hourly Rate $18 $14 $12 LES Staff salary Table-1 table CPF CONTRIBUTION Over Staff Id Name Date Joined Date Of birth Staff Grade Basic Pay Per Month Time for the No Product of SALE OVRE code units COMMISSION TIME sold SALARY AGE EMPLOYMENT SPECIAL GROSS YEARS ALLOWANCE SALARY NET SALARY EMPLOYEE'S EMPLOYER'S month SOI John Sim 12-Aug-19 20-Sep-72 2 $2,800 4 X01 sa $145 $56 49.00 2.7 years S S 3,00100 S 600.20 s 51017 $ 2,400.80 Keith chan SO2 23-Feb-04 11-Feb-79 1 $2,400 9 X01 60 $150 $162 43.00 18 years S 2712.00 s S 542.40 $ 46104 $ 2,169.60 SO3 Chandra 16-May-14 19-Mar-82 3 3 $2,100 6 XO2 78 $234 $96 40.00 796 years S 2,430,00 S 486.00 $ 413.10 $ 1,944.00 Siti Binti Saad S04 3-Apr-12 16-May-59 1 $3,100 5. X03 89 $311.50 63.00 10 years 60.00 $90 $ 302.73 3.561.50 $ s 356.15 S 3.258.77 Age of employee CPF contribution by employer Total CPF contribution rate Up to 55 years old 17% 37% 55 to 60 years old 14% 28% 60 to 65 years old 20% 18.50% 65 to 70 ent 8% 149 Pay Slip M&N Health Pro Pte. Ltd IMM #08-111 2 Jurong East Street 21, 609601 Staff ID: Staff Name: $ Al Al $ $ 1. Staff Basic Salary 2. Sales Commission 3. Overtime Salary 4. Special Senior Allowance 5. Gross Salary (1+2+3+4) 6. Employee's Contribution to CPF 7. Employer's Contribution to CPF 8. Total CPF Contribution (6+7) 9. Net Salary (5-6) $ $ $ $ Table 1: Staff Salary Table Name Staff ID Date Joined Date of Birth Staff Grade Basic Pay Product Code No of Units Per Sold Over Time for the month (hours) 4 Month S01 John Sim 2 $2,800 X01 58 12/08/ 2019 23/02/ 2004 20/09/ 1972 02/11/ 1979 SO2 1 1 $2,400 9 X01 Keith Chan 60 S03 Chandra 3 $2,100 8 X02 78 16/05/ 2014 03/04/ 2012 19/03/ 1982 16/05/ 1959 S04 1 $3,100 5 X03 89 Siti Binti Saad Table 2: Sales Commission Table Product Code X01 X02 X03 Sales Commission Per Unit ($) 2.50 3.00 3.50 Table 3: Staff Rating and Overtime Rate Per Hour Table Staff Grade 1 2 3 Overtime Hourly Rate $18 $14 $12 LES Staff salary Table-1 table CPF CONTRIBUTION Over Staff Id Name Date Joined Date Of birth Staff Grade Basic Pay Per Month Time for the No Product of SALE OVRE code units COMMISSION TIME sold SALARY AGE EMPLOYMENT SPECIAL GROSS YEARS ALLOWANCE SALARY NET SALARY EMPLOYEE'S EMPLOYER'S month SOI John Sim 12-Aug-19 20-Sep-72 2 $2,800 4 X01 sa $145 $56 49.00 2.7 years S S 3,00100 S 600.20 s 51017 $ 2,400.80 Keith chan SO2 23-Feb-04 11-Feb-79 1 $2,400 9 X01 60 $150 $162 43.00 18 years S 2712.00 s S 542.40 $ 46104 $ 2,169.60 SO3 Chandra 16-May-14 19-Mar-82 3 3 $2,100 6 XO2 78 $234 $96 40.00 796 years S 2,430,00 S 486.00 $ 413.10 $ 1,944.00 Siti Binti Saad S04 3-Apr-12 16-May-59 1 $3,100 5. X03 89 $311.50 63.00 10 years 60.00 $90 $ 302.73 3.561.50 $ s 356.15 S 3.258.77 Age of employee CPF contribution by employer Total CPF contribution rate Up to 55 years old 17% 37% 55 to 60 years old 14% 28% 60 to 65 years old 20% 18.50% 65 to 70 ent 8% 149 Pay Slip M&N Health Pro Pte. Ltd IMM #08-111 2 Jurong East Street 21, 609601 Staff ID: Staff Name: $ Al Al $ $ 1. Staff Basic Salary 2. Sales Commission 3. Overtime Salary 4. Special Senior Allowance 5. Gross Salary (1+2+3+4) 6. Employee's Contribution to CPF 7. Employer's Contribution to CPF 8. Total CPF Contribution (6+7) 9. Net Salary (5-6) $ $ $ $