University Singapore Institute of Management (SIM)
Subject BUS0117: Information Systems for Business

Section A – MS Excel

Sports-in-Motion Pte Ltd is a fast-growing local sports retailer that offers a wide range of high-performance sporting apparel, footwear, and accessories. It also offers a wide selection of sportswear and sports gear across sports disciplines from running, fitness, swimming, boxing, basketball, badminton, and tennis. The company currently has a physical retail store located at the Kallang Wave Mall.

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 Basic Monthly Salary Table

StaffNameDate of BirthDate JoinedBasic SalaryOverall Sales
IDPer Monthfor the month
S01Elena Tan20/01/196801/02/2005$2800$8000
S02Faith Chang08/08/199015/01/2015$1300$5000
S03Gabriel10/02/197801/07/2010$1800$12000
Monteiro
S04Haslinda Bte15/12/195915/08/2000$2500$7000
Azim
S05Isabella Zhang01/07/198601/03/2006$2300$10000

Performance Incentive Table

Overall MonthlyPerformance Incentive
Sales in $
0 to 3999Additional 0% of basic pay for the month
4000 to 5999Additional 2% of basic pay for the month
6000 to 7999Additional 4% of basic pay for the month
8000 to 9999Additional 6% of basic pay for the month
10000 and aboveAdditional 8% of basic pay for the month
  • Add a new column to the Staff Basic Monthly 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.
  • Add two new columns to the Staff Basic Monthly 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. Staff will be given a month-long service bonus of $100 if he or she has worked for the company for more than 10 years.
  • Add a new column to the Staff Basic Monthly 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.
  • Add four new columns to the Staff Basic Monthly 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 the Singapore CPF Board. Go to the 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
  • Singapore Labour Law requires all companies to generate a monthly payslip for the staff. Develop a Mail Merge Template in Microsoft Words linking to your Excel table to help generate the payslip for each staff based on the following format:

Singapore Labour Law requires all companies to generate a monthly pay slip

Stuck with a lot of homework assignments and feeling stressed ? Take professional academic assistance & Get 100% Plagiarism free papers

Part 2 – Column Chart

Create a Column chart to show the distribution of Net Salary paid to each staff.

Part 3 – Computation of Loans

The management has recently approved a 10-years study loan for one of the staff members who wanted to pursue his post-graduate studies. The total amount of the loan is $70,000 at a very favorable annual interest rate of 1%.

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.

Part 4 – Other Applications

Construct a new business application for the company using Excel. This new application can be implemented to improve the daily operations of the business.

Section B – Ecommerce Site Development

Part 5 – Website Design

Sports-in-Motion Pte Ltd is a fast-growing local sports retailer that offers a wide range of high-performance sporting apparel, footwear, and accessories. It also offers a wide selection of sportswear and sports gear across sports disciplines from running, fitness, swimming, boxing, basketball, badminton, and tennis. The company currently has a physical retail store located at the Kallang Wave Mall.

Mr. Jonathan Chang, the Managing Director of the company, hopes to increase the company’s sales revenue by developing an online e-commerce website. Your team has been appointed to design and develop the company’s website.

The main product categories of the company include the following:

Product CategoriesProduct Types
MenTops, Shorts, Hoodies, Jackets, Shoes
WomenTops, Shorts, Hoodies, Jackets, Shoes
AccessoriesBags, Backpacks, Caps, Headbands, Water Bottles

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 catalogs, 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” and “Press”. 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 a promotional discount by keying in the promotional code.

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 inquiries.

Logo

You are required to design a new logo for the company.

Forum/Blog

The website should include a Forum/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.

Social Networking site

Your website should provide a hyperlink to the company’s social networking site on Facebook OR Instagram. Social media sites are being used as additional channels to encourage interaction, participation, and collaboration among customers and the company. You will use the various social media sites such as Facebook OR Instagram to provide information relating to activities being organized and the latest promotions offered by the company.

Website Design Software

Students are encouraged to use online website builders such as Wix.com, for this assignment.

Please consult your lecturer first if you intend to use other website design applications or web authoring tools.

Get Help By Expert

You will be happy with our assistance in (BUS0117) information systems for business assignments. With our professional assignment writers' help, you can save your time and also save your money with our affordable information security assignment writing services.

Looking for Plagiarism free Answers for your college/ university Assignments.

Hire An Assignment Writer

Add More