Part 1 - Competatinn of Sallaries asing Eved (20 marks) Time-in-Motion Phe led is a fast-growing local watch retailer that offers a wide range of iatemationally resognised brands of watches. The company has hect =ell kingan for offering the most extensive rage of fashicen and tranded watches af coenpetitive prices. The company currently has a physical retail store locatod in the Paragon Shopping Centre at Orchard koad. All staff members of the company are paid accondeng to their baise muathly pay plus an incentive componem based an their orerall sales for the moeth. The perfisinance incentive is computed based on an additional perecntage of their basic poy if they were able to achicve the munimam amount of targeted overall stles amount anceated is the Performance facentives table below, You are rycuired to develop a wurksheer to compate the monthly paysoll of the stafi members using MS Exal. Siefil Salary Table. Performance Incentive Table (a) Adul a mew column to the Seaff Salafy Table wo compute the 1 Performance Incentive by using the -VLOOKUP" function in Frocl to astomate the scarch for the respective performance incentives based on the staff member's overall sales performance for the moeth. (3) marks) (b) Add fuo nce columns to the Staff Salary Table. In the finst new column, find the number of years each stafi member has asrived for the company fascd on the dafe he or she has joined the cocrecuny. In the second new solumn, thse the If function in Fucel to determine the foef. service bonas foe each staff. A staff will be given a meathly liveg service bonas of $100 if he or she has woeked for the company for more than 10 yoirs. (4 marks) (c) Add a new column to the Saff Salary Table to compute the Gross Salary for each saff including the perfurmance acentive aad laeg service bonus. Gross Salary is computed by adding she hasic salary, morthly performance incentives and the long servioe bonas. (3 marks) (b) Add iwo ncw colamns so the Senfr Galary Takle. In the fink new oolumn, find the namher of years cack atulf member has worked for the company based oe the date be ere she has joincd the compuny. Ia the scconal ncw column, ase the lf fanction in frest to detirmine the loeg service boeus for cach staff. A staff w be be sisen a marnthly long service botus of 5100 if be oe she has workod for the concopany for more than 10 years. (4 marks) (e) Aid a new columa to the SEaff Salary Table to compute the Gieoss $a lary for each staff anclucing the perfoetnamce incentive aral long ucrvice bones. Gicoss Salary is twenferdd by adeing the basic salary, monthly performance incentives and the boeg service bowios. (3 marks) (d) AdA four nce columns to the 5 taff Salary Takle for calculating Salary. The Ape of Staff can be concesed by usine the date of birth in zelation to curtent dale asing the "- Todayt)" finctise a frect. Yea will alvo necd to ealculate the Empleyee"s CPt and Emeleser" C.PF coetributiens baved on the Giess Salary times the rates pohliahod by Sinzapove CPF Board. Co to Siegapore CPF Board wehsile to fiel out the nest cardent empleryce"s and enyployer's CPF contrikations rafes in nclation ho the person's age. Compute the required employec's and cinployer"s eoelentutioess by asites the "VLOOKUP" funcion wo dynansically determiee the ansen based ea a talle that shows the age and the respective CPF mes you found from the CPF Board wobsia. The Net Salary can be computed by erieg the Cieoss Salary minus the Fimployec's CPF contribution ameant. (5 marka) 2 (c) Sineapore Labour Law foquines all coespunies so getlerate a monthly pay slis for the staff, Develop a Mail Merge Template in Mictosed Words linking to your Excel table to belf generate the fay shep for each staff based on the following fortnat: Part 1 - Computation ef Salaries using Excel (20 marks) Time-in-Motion Phe LAd is a fasl-grewing local watch retailer that offers a wide range of iaternationally recognised beands of watehes. The company has becn w ell known for offering the most cxiensive range of faskion and beanded watches at coanpetitive prices. The company currently has a physical retail store located in the Paragon Shoppinte Cemure af Orchard Road. All staff members of the company are paid according is their basic monthly pay plan an incentive component based an their everall sales for the moeth. The performance incentive is computed based on an additional percentage of their hasic pay if they were ahle to achicve the minimum amocat of targeted everall sales amouat indicated ia the Performance lncestives table below, You are required to develop a worksheet to compute the monthly puyroll of the staff menhers using MS Eucel. Siaff Silary Table Perforimance Insentive Table (a) Add a new column to the Staff Salary Table to cenpute the 1 Perfoemance Incentive by using the "VLLOOKUP" function in Fucel to automate the search for the respective performance incentives hased on the waff member's overall sales performance for the month (3) marks) (b) Add twe new columns to the Staff Salary Table. In the fins new colsamn, find the namber of ycars cach staff member has workod for the coanpany based on the dase he or she has jeined the cenpany. In the second new column, use the fF function in Excel bo dstermine the long service boeus for each staff. A staff will be gived a monthly krng service bomas of $100 if he or she has worked for the company fir more than 10 years. (4 markic) (c) Add a ecw column to the Siaff Salary Table we compate the Gross Salary for cach staft including the performance incentive and long service bonus. Gioss Salary is computcd by adding the hasic salary, monthly. performance incentives and the long service beens. (d) Add four new columns to the Staff Salary Table for calculating the Age of Staff, Employee's CPF and Employer's CPF contributions and Net Salary. The Age of Staff can be computed by using the date of birth in relation to current date using the "- Today()' function in Excel. You will also need to calculate the Employee's CPF and Employer's CPF contributions based on the Gross Salary times the rates published by Singapore CPF Board. Go to Singapore CPF Board website to find out the most current employee's and employer's CPF contributions rates in relation to the person's age. Compate the required employee's and employer's contributions by using the "VLOOKUP" function to dynamically determine the amount based on a table that shows the age and the respective CPF rates you found from the CPF Board website. The Net Salary can be computed by using the Gross Salary minus the Employee's CPF contribution amount. (5 marks) 2 (c) Singapore Labour Law requires all companies to generate a monthly pay slip for the staff. Develop a Mail Merge Template in Microsoft Words linking to your Excel table to help generate the pay slip for each staff based on the following format: (5 marks) Part 1 - Competatinn of Sallaries asing Eved (20 marks) Time-in-Motion Phe led is a fast-growing local watch retailer that offers a wide range of iatemationally resognised brands of watches. The company has hect =ell kingan for offering the most extensive rage of fashicen and tranded watches af coenpetitive prices. The company currently has a physical retail store locatod in the Paragon Shopping Centre at Orchard koad. All staff members of the company are paid accondeng to their baise muathly pay plus an incentive componem based an their orerall sales for the moeth. The perfisinance incentive is computed based on an additional perecntage of their basic poy if they were able to achicve the munimam amount of targeted overall stles amount anceated is the Performance facentives table below, You are rycuired to develop a wurksheer to compate the monthly paysoll of the stafi members using MS Exal. Siefil Salary Table. Performance Incentive Table (a) Adul a mew column to the Seaff Salafy Table wo compute the 1 Performance Incentive by using the -VLOOKUP" function in Frocl to astomate the scarch for the respective performance incentives based on the staff member's overall sales performance for the moeth. (3) marks) (b) Add fuo nce columns to the Staff Salary Table. In the finst new column, find the number of years each stafi member has asrived for the company fascd on the dafe he or she has joined the cocrecuny. In the second new solumn, thse the If function in Fucel to determine the foef. service bonas foe each staff. A staff will be given a meathly liveg service bonas of $100 if he or she has woeked for the company for more than 10 yoirs. (4 marks) (c) Add a new column to the Saff Salary Table to compute the Gross Salary for each saff including the perfurmance acentive aad laeg service bonus. Gross Salary is computed by adding she hasic salary, morthly performance incentives and the long servioe bonas. (3 marks) (b) Add iwo ncw colamns so the Senfr Galary Takle. In the fink new oolumn, find the namher of years cack atulf member has worked for the company based oe the date be ere she has joincd the compuny. Ia the scconal ncw column, ase the lf fanction in frest to detirmine the loeg service boeus for cach staff. A staff w be be sisen a marnthly long service botus of 5100 if be oe she has workod for the concopany for more than 10 years. (4 marks) (e) Aid a new columa to the SEaff Salary Table to compute the Gieoss $a lary for each staff anclucing the perfoetnamce incentive aral long ucrvice bones. Gicoss Salary is twenferdd by adeing the basic salary, monthly performance incentives and the boeg service bowios. (3 marks) (d) AdA four nce columns to the 5 taff Salary Takle for calculating Salary. The Ape of Staff can be concesed by usine the date of birth in zelation to curtent dale asing the "- Todayt)" finctise a frect. Yea will alvo necd to ealculate the Empleyee"s CPt and Emeleser" C.PF coetributiens baved on the Giess Salary times the rates pohliahod by Sinzapove CPF Board. Co to Siegapore CPF Board wehsile to fiel out the nest cardent empleryce"s and enyployer's CPF contrikations rafes in nclation ho the person's age. Compute the required employec's and cinployer"s eoelentutioess by asites the "VLOOKUP" funcion wo dynansically determiee the ansen based ea a talle that shows the age and the respective CPF mes you found from the CPF Board wobsia. The Net Salary can be computed by erieg the Cieoss Salary minus the Fimployec's CPF contribution ameant. (5 marka) 2 (c) Sineapore Labour Law foquines all coespunies so getlerate a monthly pay slis for the staff, Develop a Mail Merge Template in Mictosed Words linking to your Excel table to belf generate the fay shep for each staff based on the following fortnat: Part 1 - Computation ef Salaries using Excel (20 marks) Time-in-Motion Phe LAd is a fasl-grewing local watch retailer that offers a wide range of iaternationally recognised beands of watehes. The company has becn w ell known for offering the most cxiensive range of faskion and beanded watches at coanpetitive prices. The company currently has a physical retail store located in the Paragon Shoppinte Cemure af Orchard Road. All staff members of the company are paid according is their basic monthly pay plan an incentive component based an their everall sales for the moeth. The performance incentive is computed based on an additional percentage of their hasic pay if they were ahle to achicve the minimum amocat of targeted everall sales amouat indicated ia the Performance lncestives table below, You are required to develop a worksheet to compute the monthly puyroll of the staff menhers using MS Eucel. Siaff Silary Table Perforimance Insentive Table (a) Add a new column to the Staff Salary Table to cenpute the 1 Perfoemance Incentive by using the "VLLOOKUP" function in Fucel to automate the search for the respective performance incentives hased on the waff member's overall sales performance for the month (3) marks) (b) Add twe new columns to the Staff Salary Table. In the fins new colsamn, find the namber of ycars cach staff member has workod for the coanpany based on the dase he or she has jeined the cenpany. In the second new column, use the fF function in Excel bo dstermine the long service boeus for each staff. A staff will be gived a monthly krng service bomas of $100 if he or she has worked for the company fir more than 10 years. (4 markic) (c) Add a ecw column to the Siaff Salary Table we compate the Gross Salary for cach staft including the performance incentive and long service bonus. Gioss Salary is computcd by adding the hasic salary, monthly. performance incentives and the long service beens. (d) Add four new columns to the Staff Salary Table for calculating the Age of Staff, Employee's CPF and Employer's CPF contributions and Net Salary. The Age of Staff can be computed by using the date of birth in relation to current date using the "- Today()' function in Excel. You will also need to calculate the Employee's CPF and Employer's CPF contributions based on the Gross Salary times the rates published by Singapore CPF Board. Go to Singapore CPF Board website to find out the most current employee's and employer's CPF contributions rates in relation to the person's age. Compate the required employee's and employer's contributions by using the "VLOOKUP" function to dynamically determine the amount based on a table that shows the age and the respective CPF rates you found from the CPF Board website. The Net Salary can be computed by using the Gross Salary minus the Employee's CPF contribution amount. (5 marks) 2 (c) Singapore Labour Law requires all companies to generate a monthly pay slip for the staff. Develop a Mail Merge Template in Microsoft Words linking to your Excel table to help generate the pay slip for each staff based on the following format