Assignment Information: The assignment consists of two questions: 1 Students are to formulate possible solutions in practical scenarios using appropriate software like Excel, with a range of formulae and functions for basic income, expense and profitability analysis. 2 A brief research report to explore the impact that emerging technologies like Business Intelligence, Cloud Computing can have on accounting and business decisions. You can suitably embed the research report into the Excel file on a separate sheet. You will be submitting only ONE Excel file for this assignment as an electronic assignment via the ACC 202 subject page on the student portal. Instructions are on the front page of the portal. Ensure that your work can be clearly identified with your student ID and FULL student name. Late penalties will apply for all late submissions. Any work which has been copied or shared between students will result in a Fail grade. This assignment must be your own work.

 

QUESTION 1 (55 marks) The following employees work for Super Contracts Ltd, a public company specialising in contract labour force for different IT projects. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 37 hours, they will be paid the overtime rate for the additional hours. The following table summarises the classifications and level of pay per classification:

 

TABLE A: The overtime hourly rate is $ 90 per hour for ALL employees, regardless of their classification level. Simple Contracts Ltd has the following employees: TABLE B: Employee name Classification Level Paris Holton 5 Ricky Mortini 4 Jennifer Leepoz 6 Selina Geemak 2 Willard Smith 1 Russell Creak 6 Rafael Nooderly 4 Novak Djoker 1 Lara Bangle 3 Kath Hudson 6

 

The hours worked for the week ended 30th June, 2017 are as follows: TABLE C: Employee name Hours worked Novak Djoker 37 Willard Smith 39 Lara Bangle 40 Kath Hudson 52 Selina Geemak 30 Paris Holton 45 Russell Creak 34 Jennifer Leepoz 36 Ricky Mortini 52 Rafael Nooderly 44 REQUIRED: One worksheet must be used to provide answers to this question. ? No marks will be awarded if the correct function or formula is not used or if data has been manually entered. ? Take care regarding professional presentation of your work at every step. All dollar amounts must be provided in currency format and 2 decimal places.

 

a) Copy Table A into your worksheet. Set up a ‘range’ for the data. Name the range ‘classification level’ b) Copy Table B to the same worksheet. ? Add a third column titled ‘hourly pay’. Use the VLOOKUP function to pick up the correct hourly pay rate for each employee from the ‘classification level’ range set up above. ? Create a ‘range’ for the data in these 3 columns. Name the range ‘pay rate table’. This range will be used in a question below. (10 marks)

 

21. Using Table C data and keeping employee order unchanged, set up a payroll table to calculate the total payroll for each employee. Your main payroll table should have the following headings: Employee Name Hours worked Regular Hours Overtime Hours Hourly Pay Base Amount Overtime Total Pay a) Use the IF function and absolute referencing to calculate the Regular Hours column. b) Use a suitable formula to calculate the Overtime hours. c) Use the VLOOKUP function with reference to the range created earlier to determine the Hourly Pay for each employee. d) Use suitable formulae to calculate the Base and Overtime amount for each employee. e) Use a formula to calculate the Total Pay f) Sort the table in order of employee names. All columns must be totalled as required. (30 marks)

 

3 a) Using another formula, identify the employee(s) that earned the maximum overtime. b) Discuss and illustrate one more function that can be included, in a case like this, to help with decision making. c) Using Drop down function and Vlookup, prepare a separate summary table to display, one employee at a time, as follows Employee Name Total Pay (15 marks) Q 2 [20 marks]

Research 1 – 2 commonly used Business Intelligence Tools that help extract intelligence from the business data to support decision-making through modelling and analysis. 1 List and briefly illustrate at least 2 features of commonly used Business Intelligence Tools that can support decision-making. (20 marks)

 

Your report need be:- ? Professionally presented, in your own words. Your research should include at least 2 references, such as the professional body articles as well as text books and peer reviewed academic journals. Vendor websites are not appropriate sources of information for this task. Use appropriate referencing in your report. ? Word limit : 450 ( excluding references, diagrams)

 

Answer: