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 the following requirements:

Requirements:

(a) Add a new column with a header labelled as Age to Table 1 and calculate the Age of each staff member.

(b) Add a new column with a header labelled as Employment Year to Table 1 and calculate the number of years each staff member has worked with the

company.

(c) Add a new column with a header labelled as Sales Commission to Table 1 and calculate the sales commission by multiplying the No of Units Sold by the appropriate Sales Commission Per Unit ($) according to the Product Code shown in Table 2. Use the vlookup function to search for the appropriate Sales Commission Per Unit ($).

(d) The staff are paid for their overtime work on public holidays. The overtime salaries are computed by multiplying their hourly rates according to their Staff Grade by the actual number of hours they have clocked in for the month respectively. Use the vlookup function to search for the appropriate Overtime Hourly Rate in relation to the Staff Grade provided by Table 3. Add a new column with a header labelled as Overtime Salary to Table 1 and show the Overtime Salary computed.

(e) The company has a special senior allowance for staff members who have joined the company for more than 10 years and the persons age is more than 60. The staff members will be given an additional $60 of monthly special allowance. Use the IF function in Excel to decide whether the person is eligible for the special allowance. Add a new column with a header called Special Allowance to Table 1 to show the amount of allowance.

(f) Add a new column with a header called Gross Salary to Table 1 and calculate the gross salary for each staff member by summing up the Basic Salary, Sales Commission, Overtime Salary and Special allowance.

Part 2 Computation CPF Contribution

You are required to calculate the CPF contribution for all staff in Part 1 based on their Gross Salaries. You will need to find out from the Singapore CPF website the actual employees and employers contribution rates. Compute the required employees and employers contributions for all staff according to their age. You should be using the vlookup function to find the appropriate CPF contribution rates. You will also need to calculate the Net Salary by using Gross Salary minus Employees Contribution to CPF.

Part 3 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:

Part 4 - 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.

Part 5 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.

Part 6 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.

Part 7 Security Threats and Security Measures

State two possible security threats to your e-commerce site and provide three possible security measures to minimize the threats.

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 Pay Slip M&N Health Pro Pte. Ltd IMM #08-111 2 Jurong East Street 21, 609601 Staff ID: Staff Name: 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) EAEAEAEAEAEAEAEA TA $ $ $ $ $ $ $ $ $