Reference no: EM133142673

BUS0117 Information Systems for Business Assignment - SIM Global Education

Section A - MS Excel

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

Table 1: Staff Salary Table

Staff ID	Name	Date Joined	Date of Birth	Staff Grade	Basic Pay Per Month	Over Time for the month (hours)	Product Code	No of Units Sold
S01	John Sim	12/08/2019	20/09/1972	2	$2,800	4	X01	58
S02	Keith Chan	23/02/2004	02/11/1979	1	$2,400	9	X01	60
S03	Chandra	16/05/2014	19/03/1982	3	$2,100	8	X02	78
S04	Siti Binti Saad	03/04/2012	16/05/1959	1	$3,100	5	X03	89

Table 2: Sales Commission Table

Product Code	Sales Commission Per Unit ($)
X01	2.50
X02	3.00
X03	3.50

Table 3: Staff Rating and Overtime Rate Per Hour Table

Staff Grade	Overtime Hourly Rate
1	$18
2	$14
3	$12

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 person's 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 employee's and employer's contribution rates. Compute the required employee's and employer's 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 Employee's 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: (see attached file)

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.

Section B - E-Commerce Site Design

Part 7 - Online Site Development

You have been approached by Ms Susan Lim to design an E-commerce site for the company. The main product categories of your site would include the following:

Product Catalogues	Examples
Vitamin	Vitamin C, Vitamin D, Multivitamins
Herbs	Dandelion Roots, Cinnamon Bark, Bilberry Extract
Antioxidants	CoQ-10, Lutein, Resveratrol, Grape Seed

The objective of the E-commerce site is to promote the company's brand name and products online by providing information pertaining to the company profile, history, and product catalogues. including photos, descriptions, and prices. You should also provide any other information that may be useful for attracting customers to the online site.

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

Section C - Presentation

Each student will be required to make a presentation about their assignment during the last few lessons. Your lecturer will provide the actual dates for the presentation. Each student will be given 10 minutes to present the assignment.

Attachment:- Information Systems for Business Assignment File.rar