BUS0117 Information Systems for Business
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
BUS0117 Information Systems for Business (April 2023)
Continual Assessment 2
Individual Assignment (40%)
Submission date: 9 May 2023, 11.59am.
Total marks: 100 marks
Section A – MS Excel (30 marks)
Part 1 – Computation of Salaries using Excel (20 marks)
House of Lights Pte Ltd is one of the best lighting shops in Singapore for pendant lights, ceiling lights, wall lights, table lamps, and floor lamps. The lighting store offers a wide range of energy efficient products along with the creation of lighting ambience. The company has been well known for offering the most extensive range of lightings collection at competitive prices.
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 ID |
Name |
Date of Birth |
Date Joined |
Basic Salary Per Month |
Overall Sales for the month |
S01 |
Aaron Ang |
10/01/1968 |
01/07/2008 |
$3800 |
$8600 |
S02 |
Belinda Beh |
20/05/1993 |
15/02/2018 |
$1600 |
$5000 |
S03 |
Cassey Bte Cashim |
30/07/1988 |
01/08/2013 |
$2300 |
$8000 |
S04 |
Duranjaya |
28/09/1982 |
15/05/2015 |
$2500 |
$6500 |
S05 |
Elisa Eng |
01/12/1960 |
01/10/2010 |
$3000 |
$9800 |
Performance Incentive Table
Overall Monthly Sales in $ |
Performance Incentive |
0 to 3999 |
Additional 0% of basic pay for the month |
4000 to 5999 |
Additional 2% of basic pay for the month |
6000 to 7999 |
Additional 4% of basic pay for the month |
8000 to 9999 |
Additional 6% of basic pay for the month |
10000 and above |
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. (4 marks)
(c) Add a new column to the Staff Salary Table to compute the
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)
(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:
Pay Slip |
|
Light House Pte Ltd |
|
18 Boon Lay Way, #01- 100 |
|
TradeHub 21 |
|
|
|
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) |
$ |
(5 marks)
Part 2 - Chart and Other Statistics (5 marks)
(a) Create a suitable chart to show the distribution of Net Salary paid to each staff. (3 marks)
(b) Use a suitable Excel function to compute the Total Gross Salary for staffs above the age of 50. (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 $55,000 at a very favourable annual interest rate of 3.38%.
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)
Section B – Ecommerce Website Development (35 marks)
House of Lights Pte Ltd is one of the best lighting shops in Singapore for pendant lights, ceiling lights, wall lights, table lamps, and floor lamps. The lighting store offers a wide range of energy efficient products along with the creation of lighting ambience. The company has been well known for offering the most extensive range of lightings collection at competitive prices. The company currently has a physical retail store located at TradeHub 21 at Boon Lay Way.
Mr. Lionel Lim, the Managing Director of the company, hopes to boost the company’s sales revenue by developing an online e-commerce website. You have been appointed to design and develop the company’s website.
The main objective of the e-commerce website is to promote the company’s brand name and products online by providing information pertaining to the company’s profile, history and product catalogues, including product photos, descriptions and retail prices. You should also provide any other relevant information that can be useful for attracting customers to the website.
The website should include the following pages:
Main Page (Home Page)
• A general description of your website, special promotions that would serve as a key attraction for the site visitors.
About Us
• A general description of the company’s profile, including vision, mission and core value statements.
• Sections such as “Awards” . It’s perfectly appropriate to mention past achievements provided they’re relevant and significant.
Product Catalogues
• Photos, descriptions and retail prices of all popular products currently available to the customers.
Shopping Cart
• Customers can purchase products they like online.
• Customers will be able to select the various options related to the products.
• Customers will be given promotional discount by keying in the promotional code, if applicable.
Contact
• A description and hyperlink for visitors to contact you (i.e. address, phone number, email, opening hours).
• Google Map that shows the location of the company.
• Include an online form that will allow customers to make enquiries.
Logo
You are required to design a new logo for the company.
Blog
The website should include a Blog for customers to post and discuss their opinions about the products they have purchased or to provide suggestions on the types of products they would like the company to sell.
Section C – Ecommerce Discussions (10 marks)
(a) Ecommerce Success
Discuss FIVE key factors to consider for measuring ecommerce success.
(b) Ecommerce Security
Discuss FIVE security measures the company should implement to minimise possible cybersecurity threats to the ecommerce sites.
Section D – Presentation (25 marks)
Each student will be required to make a presentation (video presentation) about their assignments during the last few lessons. Your lecturer will provide more details about the format and structure of the presentation.
Each student will be given 5- 10 minutes to present the assignments.
Website Design Software
Students are encouraged to use the free version of online website builders such as Wix.com or site123.com, for this assignment.
Please consult your lecturer first if you intend to use other website design applications or web authoring tools.
Submission Guidelines
You will need to upload the following files to the Canvas (Learning Management System) dropbox by the assignment due date:
1. MS Excel file.
2. MS Word file containing the following:
a. screenshots of the Mail Merge Template and the generated Pay Slips
b. discussions on Ecommerce for Section C
c. ecommerce website address
2023-05-08