BUS117 Information Systems for Business (Oct 2015)

Diploma in Management Studies Continual Assessment 2 Individual Assignment (20%) Deadline: 21 Dec 2015, 11.59am Total marks: 100 marks Section A -­- MS Access (50 marks) AsiaSportW Pte Ltd, is a company that is selling a range of sportswear online. The range of products carried by the company include: • Top’s, T-­-Shirts and Polo Shirts • Sports Shoes • Sports Accessories Mr. Paul Tan, the Director of Marketing has recruited you to create a Sales and Inventory Database Management System using Microsoft Access. The purpose of the database is to allow the company to keep track of data pertaining to the customers, suppliers, payments, delivery, quantity of products available in the inventory and quantity of items being purchased by each customer. Part 1 – Design of Database (12 marks) You are required to design the following tables in a database for the company and create the SIX tables from (a) to (f) in Microsoft Access. (a) Customer Profile Table with relevant fields for capturing information related to the customers such as customers’ address, phone number, payment method and delivery address. (2 marks) (b) Customer Order Table with relevant fields for capturing the products and quantity sold to each customer, delivery schedule and the 3rd party logistic company being used to do the delivery. Each customer may buy more than one item and at different times. (2 marks) (c) Customer Relationship Table with relevant fields for capturing the dates and various promotions messages being sent to the customers. (2 marks) (d) Supplier Profile Table with relevant fields for capturing information related to the suppliers such as suppliers’ address, phone number, fax number, email, payment terms and contact person. (2 marks) (e) Product Inventory Table with relevant fields for capturing the product details, product image, quantity available, cost price and selling price and supplier name. (2 marks) (f) 3rd Party Logistic Companies Table with relevant files for capturing information related to the logistic companies being engaged for deliveries. (2 marks) You may need to add other relevant fields for tables (a) to (f). Part 2 – Primary Keys and Foreign Keys (12 marks) (a) Identify the relevant primary and foreign keys for all of the tables above. (6 marks) (b) Define the primary keys in the MS Access tables. (6 marks) You should also create relevant data for your tables. Part 3 – Forms (10 marks) Create SIX forms to improve the user interface for data entry for each of the table. You will need to modify the title of the forms to include the company’s name. Your forms should include the navigation buttons to allow users to scroll up, scroll down, move to next and previous records, add record, delete record and closing of the form. (10 marks) Part 4 –Queries (12 marks) (a) Create a query that will show the purchase done by a given customer based on his name. (4 marks) (b) Create a query that will allow management to find out all customer information, product information, and deliveries to be carried out for a given month. (4 marks) (c) Create a query that will show the products that have less than a given minimum quantity. (4 marks) Part 5 – Main Menu (4 marks) Create a main menu using the blank form function. This form will serve as a guided menu for the user to open the forms created in Part 3 and queries in Part 4. (4 marks) Section B – Excel (50 marks) Part 6 – Computation of customers’ special promotional rebates (20 marks) (a) Mr. Paul Tan is responsible for managing the customer special promotional rebates where customers are given a rebate voucher every month based on their total overall monthly purchase. You are required to calculate the total rebate for the following customers using the “vlookup” function: Customers Rebates Cust omer ID Name Address Date Total Purchase for the month Total Rebate for the month C001 Jonny Tan 21 Bukit Merah, Blk 211, #01-­-02, S898792 30 Oct 2015 300 C002 Susan Lim 1 Jalan Besi, Blk 201, #02-­-23, S78768 30 Oct 2015 1000 C003 Abu Bakar 232, Clementi St 2, S64929 30 Oct 2015 50 S004 Ananda Raj 8 Bukit Batok St 24 Blk 266, #08-­-10, S66161 30 Oct 2015 100 Rebate Table Overall Purchase in $ Rebate Amount 0 to 99 1% of total purchase for the month 100 to 199 2% of total purchase for the month 200 to 299 3% of total purchase for the month 300 to 399 4% of total purchase for the month 400 to 499 5% of total purchase for the month 500 and above 6% of total purchase for the month (10 marks) (b) Develop a mail merge template in Microsoft Words linking to your Excel table to help generate the voucher for each customer based on the following format: Rebate Voucher AsiaSportW Pte. Ltd. #06-­-08 E-­-Centre, Bukit Merah 107880 Customer Name : Customer Address: Date : Voucher Amount: $ (10 marks) Part 7 -­- Pie Chart (5 marks) Create a Pie chart to show the relative total rebates for all customers. (5 marks) Part 8 – Computation of salary using Excel (10 marks) All staff members 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 help Paul to compute the monthly payroll of the staff members using Excel. Staff Basic Salary Table Staff ID Name DOB Date Basic Pay Per Month Overall Sales for the month S01 Jennifer Tan 12/02/1965 30/10/2015 $2,200 5000 S02 Lily Lim 16/07/1976 30/10/2015 $1,500 9000 S04 Conrad D’Souza 09/03/1980 30/10/2015 $1,400 14000 S05 Aliza Bte Khalid 08/08/1958 30/10/2015 $2,100 8000 Performance Incentive Table Overall Sales in $ Performance Incentive 0 to 5999 Additional 0% of basic pay for the month 6000 to 6999 Additional 1% of basic pay for the month 7000 to 7999 Additional 2% of basic pay for the month 8000 to 8999 Additional 3% of basic pay for the month 9000and above Additional 5% of basic pay for the month Create a table to compute the Gross Salary for each staff including the performance incentive. Gross Salary is computed by adding the basic salary and monthly performance incentives. You should use the “vlookup” function in Excel to automate the search for the respective percentage of performance incentive based on the staff member’s sales performance. The computed amount of the performance incentive should be included in the gross pay before calculating the CPF contributions. You will need to find out from the Singapore CPF website the actual employee’s and employer’s contributions based on the person’s age. Compute the required employee’s and employer’s contribution. You should be using the “vlookup” function to dynamically determine the amount based on their age. (10 marks) Part 8 – Computation of Loans (5 marks) The management has recently approved a 10-­-year study loan for one of the staff member who wanted to pursue his post-­-graduate studies. The total amount of the loan is $100,000 at a very favourable annual interest rate of 2%. The management has decided to deduct the repayment of the loan by following an affordable monthly installment plan over 120 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. (5 marks) Part 9 – Other Applications (10 marks) Discuss or show some other applications of Excel that can improve the management of the business operations. (10 marks) Submission Format You are required to answer all parts in a Word Document report. You should capture the screenshots of your development for both Access and Excel and paste it in your final report according to the requirement of each question. Type your final report using Microsoft Word. You should submit your final report via D2L by the due date. You will also need to upload your Excel and MS Access files to D2L’s dropbox. Information System for Business Continual Assessment 2 Individual Assignment (20%) Marking Scheme Student Name: ___________________________ Description Marks Very Good (5) Good (4) Above Average (3) Average (2) Below Average (1) Remarks Part 1 – Design of Database Tables with relevant fields for the following: (a) Customer Profile (b) Customer Order (c) Customer Relationship (d) Supplier Profile (e) Product Inventory (f) 3PL 12 2 2 2 2 2 2 Part 2- Primary Keys and Foreign Keys (a) Primary keys and Foreign keys (b) Define primary keys in Access 12 6 6 Part 3 – Forms Create Five form with relevant header, fields and buttons for each form: - Header - fields - Scroll up - Scroll down - Add record - Delete record - Closing of form - Correct forms - Layout 10 1 1 1 1 1 1 1 2 1 Part 4 - Queries Create queries for the following: (a) Purchase done by a given customer (b) All customer info, product and deliveries for a given month. (c) Inventory lower than minimum quantity 12 4 4 4 Part 5 – Main Menu Main Menu with proper header and buttons 4 4 Part 6 – Computation of customers’ special promotional rebates (a) Calculation of the total rebate for the month (b) Mail merge template for voucher 20 10 10 Part 7 – Pie Chart Relative rebates to all customers 5 5 Part 8 – Computation of Salary Using Excel Table to compute the Gross Salary -­- Vlookup command and computation of relevant columns - CPF Contribution Table. Proper checking of Age and computation for Employer’s and Employee’s contributions. 10 5 5 Part 9 – Computation of Loan Computation of Loan 5 5 Part 10 – Other Applications Discuss or show some other application of Excel for the business. 10 10 Total 100 Remarks