Assessment Information

Subject Code:                     CISM4000

Subject Name:                    Accounting Information Systems

Assessment Title:               Excel Assignment

Learning Outcome 5: Develop practical skills in using excel spreadsheets to collate, manage and present accounting information to users in an acceptable format.

Introduction to the individual assignment:

This assignment must be completed using Microsoft Excel. These introductory excel skills you learn will be used in later units and in your professional life. Employers expect students to have good worksheet skills.

 

BUSINESS SCHOOL
AUSTRALIA

QUESTION 1

The following employees work for Simple Contracts Ltd, a public company specialising in contract labour force for different projects in construction industry. The employees are paid an hourly rate, based on their Job Classification level. If an employee works more than 36 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:

Classification Levels
Level Hourly pay
1 $30.00
2 $40.00
3 $50.00
4 $60.00
5 $70.00
6 $80.00

 

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

 

2 Using Table C data and keeping employee order unchanged, set up a payroll table to calculate 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
Over time Total
Pay
a)      Use the IF function and absolute referencing to calculate the Regular Hours column.

b)     Use a 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.)

 

3 a) Using another formula, identify the employee(s) that earned maximum overtime.
b) Using Drop down function and Vlookup, prepare a separate summary table to display, one employee at a time, as follows
Employee Name  Total Pay

 

4 a) Discuss and illustrate one more function that can be included, in a case like this, to help with decision making.
b) What amount should be charged, by Simple Contracts Ltd, to ABL Constructions that hired all the employees at level 4 and above this week? Justify your answer briefly.

 

New version of this question is available here: https://myassignmentguru.com/assignments/new-accounting-information-system-excel-assignment-simple-contracts-ltd-a-public-company-specialising-in-contract-labour-force/

 

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies.

After making payment, solution is sent within 2 to 5 minutes on your Email ID. But it may take up to 1 hour in case of high load on server. Solution is available in Word or Excel format unless otherwise specified.

-----------------------------------------------------------------------------------------------------------------------------

If you question is slightly different from the given above, please contact us at info@myassignmentguru.com with your version of question.