Gifts Connect Pte Ltd is a local company that offers a fantastic range of inspired, stylish...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Gifts Connect Pte Ltd is a local company that offers a fantastic range of inspired, stylish and unique gifts for all occasions. Gifts Connect specialised in gifts relating to Home Décor, Gifting and Lifestyle. All staff members of the company are paid according to their basic monthly pay plus an incentive component based on their overall sales for the month. The performance incentive is computed based on an additional percentage of their basic pay if they were able to achieve the minimum amount of targeted overall sales amount indicated in the Performance Incentives table below. You are required to develop a worksheet to compute the monthly payroll of the staff members using MS Excel. Staff Salary Table Staff Name ID 801 Aaron Ang Beverly Beh Casmin Bie Cassim S04 Duranjaya S05 Elisa Eng 802 S03 Date of Birth 20/01/1968 09/09/1993 02/02/1988 15/05/1960 01/12/1982 Date Joined Basic Salary Per Month $3800 01/07/2012 01/02/2018 $2300 01/08/2012 $3300 01/12/2015 $2500 01/03/2010 $3500 Overall Sales for the month $8000 $5000 $12000 $6500 $10000 1 Performance Incentive Table Overall Monthly Sales in $ 0 to 3999 4000 to 5999 6000 to 7999 8000 to 9999 10000 and above Performance Incentive Additional 0% of basic pay for the month Additional 2% of basic pay for the month Additional 4% of basic pay for the month Additional 6% of basic pay for the month Additional 8% of basic pay for the month (a) Add a new column to the Staff Salary Table to compute the Performance Incentive by using the "VLOOKUP” function in Excel to automate the search for the respective performance incentives based on the staff member's overall sales performance for the month. (3 marks) (b) Add two new columns to the Staff Salary Table. In the first new column, find the number of years each staff member has worked for the company based on the date he or she has joined the company. In the second new column, use the IF function in Excel to determine the long service bonus for each staff. A staff will be given a monthly long service bonus of $100 if he or she has worked for the company for more than 10 years. (c) Add a new column to the Staff Salary Table to compute the (4 marks) Gross Salary for each staff including the performance incentive and long service bonus. Gross Salary is computed by adding the basic salary, monthly performance incentives and the long service bonus. (3 marks) (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. Compute 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 (e) 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: Gifts Connect Pte Ltd Suntec City, #01-18 3 Temasek Boulevard, Singapore 038983 Staff ID: Staff Name: Date: 1. Staff Basic Salary 2. Performance Incentive 3. Long Service Bonus 4. Gross Pay (1+2+3) 5. Employee's Contribution to CPF 6. Employer's Contribution to CPF 7. Total CPF Contribution (5+6) 8. Net Salary (4-5) Pay Slip (b) $ $ $ $ $ $ $ $ Part 2 - Column Chart and Other Statistics (5 marks) (a) (5 marks) Create a Column chart to show the distribution of Net Salary paid to each staff. (3 marks) Use the Excel function of "SUMIF" to compute the Total Gross Salary for staff having the age of 50 and above. (2 marks) Part 3 - Computation of Loans (5 marks) The management has recently approved a 10-years study loan for one of the staff member who wanted to pursue his post-graduate studies. The total amount of the loan is $65,000 at a very favourable annual interest rate of 2.20%. The management has decided to deduct the repayment of the loan by following an affordable monthly installment plan over 120 months from the staff's monthly salary. You are required to demonstrate how to use the PMT function in Excel to compute the monthly loan repayment amount to be deducted from the staff's salary. (5 marks) 3 Gifts Connect Pte Ltd is a local company that offers a fantastic range of inspired, stylish and unique gifts for all occasions. Gifts Connect specialised in gifts relating to Home Décor, Gifting and Lifestyle. All staff members of the company are paid according to their basic monthly pay plus an incentive component based on their overall sales for the month. The performance incentive is computed based on an additional percentage of their basic pay if they were able to achieve the minimum amount of targeted overall sales amount indicated in the Performance Incentives table below. You are required to develop a worksheet to compute the monthly payroll of the staff members using MS Excel. Staff Salary Table Staff Name ID 801 Aaron Ang Beverly Beh Casmin Bie Cassim S04 Duranjaya S05 Elisa Eng 802 S03 Date of Birth 20/01/1968 09/09/1993 02/02/1988 15/05/1960 01/12/1982 Date Joined Basic Salary Per Month $3800 01/07/2012 01/02/2018 $2300 01/08/2012 $3300 01/12/2015 $2500 01/03/2010 $3500 Overall Sales for the month $8000 $5000 $12000 $6500 $10000 1 Performance Incentive Table Overall Monthly Sales in $ 0 to 3999 4000 to 5999 6000 to 7999 8000 to 9999 10000 and above Performance Incentive Additional 0% of basic pay for the month Additional 2% of basic pay for the month Additional 4% of basic pay for the month Additional 6% of basic pay for the month Additional 8% of basic pay for the month (a) Add a new column to the Staff Salary Table to compute the Performance Incentive by using the "VLOOKUP” function in Excel to automate the search for the respective performance incentives based on the staff member's overall sales performance for the month. (3 marks) (b) Add two new columns to the Staff Salary Table. In the first new column, find the number of years each staff member has worked for the company based on the date he or she has joined the company. In the second new column, use the IF function in Excel to determine the long service bonus for each staff. A staff will be given a monthly long service bonus of $100 if he or she has worked for the company for more than 10 years. (c) Add a new column to the Staff Salary Table to compute the (4 marks) Gross Salary for each staff including the performance incentive and long service bonus. Gross Salary is computed by adding the basic salary, monthly performance incentives and the long service bonus. (3 marks) (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. Compute 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 (e) 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: Gifts Connect Pte Ltd Suntec City, #01-18 3 Temasek Boulevard, Singapore 038983 Staff ID: Staff Name: Date: 1. Staff Basic Salary 2. Performance Incentive 3. Long Service Bonus 4. Gross Pay (1+2+3) 5. Employee's Contribution to CPF 6. Employer's Contribution to CPF 7. Total CPF Contribution (5+6) 8. Net Salary (4-5) Pay Slip (b) $ $ $ $ $ $ $ $ Part 2 - Column Chart and Other Statistics (5 marks) (a) (5 marks) Create a Column chart to show the distribution of Net Salary paid to each staff. (3 marks) Use the Excel function of "SUMIF" to compute the Total Gross Salary for staff having the age of 50 and above. (2 marks) Part 3 - Computation of Loans (5 marks) The management has recently approved a 10-years study loan for one of the staff member who wanted to pursue his post-graduate studies. The total amount of the loan is $65,000 at a very favourable annual interest rate of 2.20%. The management has decided to deduct the repayment of the loan by following an affordable monthly installment plan over 120 months from the staff's monthly salary. You are required to demonstrate how to use the PMT function in Excel to compute the monthly loan repayment amount to be deducted from the staff's salary. (5 marks) 3
Expert Answer:
Answer rating: 100% (QA)
To compute the monthly payroll of the staff members using MS Excel you can follow these steps 1 Open a new Excel worksheet 2 Create a table with the following columns Staff ID Name Date of Birth Date ... View the full answer
Related Book For
Financial Reporting and Analysis Using Financial Accounting Information
ISBN: 978-1439080603
12th Edition
Authors: Charles H Gibson
Posted Date:
Students also viewed these finance questions
-
The following account balances were taken from the general ledger accounts of theEllen Corporation. January 1 December 31 Materials P60,000 P80,000 Work in Process 85,000 110,000 Finished Goods...
-
We will be working with a company called Global Bike Inc., (GBI). Information regarding GBI follows. Company History Global Bike Inc. has a pragmatic design philosophy that comes from its deep roots...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
GoFast Corp. wishes to renovate the property so that he can sell it at a higher price.The after-tax cash flow over next year up to RM320,000 if the property was renovated.The renovation cost is...
-
In Exercises 1-3, find the orthogonal decomposition of v with respect to W. 1. 2. 3. 2 |, W = span| | | 423
-
What are advantages of real estate investments?
-
Graphic solution for a linear programming problem Refer to the data about the production of sealers for Sharped Company in Exercise 25-10. Exercise 25-10 Setting up a linear programming problem...
-
Southern Exposure Ltd. begins operations on January 2, 2016. During the year, the following transactions affect shareholders equity: 1. Southern Exposures articles of incorporation authorize the...
-
Suppose the labor cost (in dollars) for manufacturing a camera can be approximated by L(x,y)=3/2x^2+y^24x4y2xy+104 where x is the number of hours required by a skilled craftsperson and y is the...
-
The COVID-19 pandemic affected companies in the food industry in unique ways, particularly during 2020. Ruth's Hospitality Group (Ticker: RUTH) develops and operates fine dining restaurants under the...
-
State where the following people are going. Marcos/library Marcos va a la biblioteca. My grandmother/our house ______________________________________________________. I/my school...
-
An organisation with weak transport management suffers as it gives worse performance than more competent competitors and becomes uncompetitive; one with strong transport management may be diverting...
-
The essential qualities of the internal auditor do not include: a. Adaptability. b. Determination. c. Understanding. d. Education. e. Objectivity.
-
For most organisations road transport is the only realistic mode of transport. If this is true, why do so many people disapprove of heavy lorries? Are there any noticeable trends in the choice of...
-
There is a growing number of free trade areas. Why? Do they really allow free trade between members? If free trade is such a good idea, why do countries not simply remove all their duties and tariffs...
-
To build a superior staff, one must first know: a. The kinds of auditors available in the marketplace. b. The kinds of courses being taught in business administration schools. c. The standards for...
-
Analyzing changes in unit costs The costs of materials consumed in producing good units in the Forming Department of Thomas Company were $4,680 and $11,620 for September and October, respectively....
-
During the month, services performed for customers on account amounted to $7,500 and collections from customers in payment of their accounts totaled $6,000. At the end of the month, the Accounts...
-
Briefly describe the difference between accounting for intangibles for an insurance company under GAAP and under SAP.
-
Please complete the Web case that covers material discussed in this chapter at www.cengage.com/ accounting/Gibson. You'll be using Thomson ONE Business School Edition, a powerful tool that combines a...
-
The following information was obtained from the accounts of Alleg, Inc., as of December 31, 2010. It is presented in scrambled order. Common stock, authorized 21,000 shares at $1 par value, issued...
-
What will the Railway's operating income be if they sell 1,001 tickets in one month? a. \(\$ 50\) b. \(\$ 10\) c. \(\$ 60\) d. \(\$ 60,060\)
-
If the Grand Canyon Railway expects to serve 1,200 passengers next month, what is the margin of safety? a. 200 passengers b. 1,000 passengers c. 1,200 passengers d. 2,200 passengers
-
Compute the breakeven point in sales dollars. a. \(\$ 300,000\) b. \(\$ 60,000\) c. \(\$ 50,000\) d. \(\$ 100,000\)
Study smarter with the SolutionInn App