Excel The assignment of a payroll calculator demonstrates Excels ability to automate and present financial calculation information clearly. The ability to build and professionally present a workbook is an important skill. Please note: that where the assignment states that you must use an expression, we mean a function.
High Distinction required
Assessment and Submission Details
Marks: 30 % of the Total Assessment for the Course
Due Date: Friday, 22rd October 2015
The assignment MUST be submitted online using Blackboard and on time
The submission link will be open at least a week before the due date. Please follow the submission instructions in Blackboard. THIS OPTION IS PERFERRED BY THE TEACHING TEAM.
DO NOT SUBMIT THE ASSIGNMENT TO THE
COURSE CO-ORDINATOR OR TUTOR.
The assignment will be marked out of a total of 100 marks and forms 30 % of the total assessment for the course. Once marked, ALL assignments will be checked for plagiarism and/or collusion between individuals.
Refer to your Course Outline or the Course Web site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines.
Note: Each student MUST make a second copy of the assignment and this copy MUST be produced within 24 hours of it being requested by the Course Co-ordinator. Failure to produce the second copy of the assignment when requested may result in loss of marks or a fail grade for the assignment.
Assignment submission extensions will only be made using the official Faculty of Arts & Business Guidelines.
Requests for an extension to an assignment extension MUST be made prior to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances. Please note you are expected to manage your work load so other assignments being due is not sufficient reason to be granted an extension.
The assignment of a payroll calculator demonstrates Excels ability to automate and present financial calculation information clearly. The ability to build and professionally present a workbook is an important skill. Please note: that where the assignment states that you must use an expression, we mean a function.
In Australia, permanent employees get given 20 days holiday pay and ten days sick pay in each year that they are employed. This means that they get paid even though they are away from work. Superannuation is calculated as a percentage of the Gross pay and taxed at 15%. Overtime is calculated at time and a half.
The assignment 2 starting workbook consists of two worksheets, named Employee Information and Payroll Calculator.
Format the two worksheets to create professional looking tables for each of these two worksheets. This formatting should be done using an appropriate table format selected from the home ribbon in Excel 2010. At this stage none of the data in the two worksheets should be modified. This will also require that you modify row height and column width.
After completing task 1, a sequence of tasks needs to be completed on the Employee information worksheet. These tasks are listed below:
1. Separate the State & Postcode data into two columns. This must be done using the various text functions available in Excel 2010. It will require that you insert two additional columns into this worksheet. One column should be labelled “Postcode” and the other column should be labelled “State”. In addition all excess spaces should be removed from the text and all “State” text should be full upper case. This must be done using functions from Excel 2010.
2. All columns should be formatted to use the correct data type for their contents.
3. The mobile phone number must be in the format “1234 567 890” and this must be done using the format cells function.
4. Using conditional formatting, identify any cells with missing mobile phone numbers and change their background colour to light red.
5. Format the data in both “First Name” and “Last Name” and so that it begins with a capital letter. This must be done by using the appropriate Excel function.
6. Sort the data alphabetically on “Last Name”
1. Add ta new worksheet to your excel workbook, one called “Payroll summary”. This should be the first worksheet in your workbook.
2. On the “Payroll summary” worksheet add two named cells one called “Medicare_Levy” with a value of 1.5% and the other cell called “Overtime_Rate” with a value of 1.5. These cells should be appropriately labelled in your worksheet.
3. Add another name cell to the “Payroll summary” work sheet called “Period_ending” and give it todays date using an Excel Function.
1. On the “Employee information” worksheet, using an expression, calculate the “Total Taxes Withheld” for each employee. This should be the total of the respective employees’ income tax and the medicare levy. The resulting value should be correctly formatted.
2. On the “Employee information” worksheet, using an expression, calculate for each employee the value of their “Total Regular Deductions”. This should not include taxes or the superannuation levy.
1. On the “Payroll calculator” worksheet, retrieve the “Last Name” and “Given Name for each employee. This must be done using a Vlookup expression.
2. Display on the “Payroll calculator” worksheet, in cell L2, the date value from the “Period_ending” named cell in the “Payroll summary” worksheet. This must be done using an expression.
3. On the “Payroll calculator” worksheet, in the column labelled “Overtime Rate”, using an expression calculate the overtime rate for each employee This can be done by multiplying each employees Hourly Rate ( from the “Employee information” work sheet) multiplied by the overtime rate from the summary worksheet.
4. Calculate the gross pay for each employee based on the number of hour worked + sick hours+ vacation hours. Added to this value must also be payment for overtime. This must be done using an expression.
5. Using an expression calculate the total taxes and deduction for each employee.
6. Using an expression calculate the nett pay for each employee.
7. Using an expression calculate the superannuation liability for each employee which should be done based on each employees gross pay multiplied by their individual superannuation rates.
On the Payroll summary worksheet do the following:
1. Using an expression calculate the total gross pay for the company for this pay period.
2. Using an expression calculate the total net pay for the company for this pay period.
3. Using an expression calculate the total Tax and payments withheld from the employees for the company for this pay period.
4. Using an expression calculate the total superannuation liability for the company for this pay period.
5. Using an expression calculate the number of sick hours taken by employees for the company for this pay period.
6. Using an expression count the number of employees currently on leave for this pay period.
7. Using an expression calculate the number of hours of overtime worked for this pay period.
8. Using an expression count the number of employees who worked overtime for this pay period.
9. Using an expression count the number of employees who worked 10 hours or more overtime in this pay period.
Assignment Requirements and Deliverables
The report is to be prepared as a single Microsoft Excel.
The completed assignment is to be submitted by the due date of 5:00pm Friday, 23rd of October 2015 and is to be an electronic submission of assignments please upload your Excel file. The file naming format should be lastName_FirstName_StudentNumber_ACC508_Assn2.
The assignment will be assessed according to the marking sheet (Appendix A). Late submission of the assignment will result in a deduction of 10% of the available marks for each day that the assignment is late. This includes Saturday and Sunday.
Assignment Return and Release of Grades
Assignment grades will be available on the course web site on 6th of November 2015 at the latest. An electronic assignment marking sheet will be available at this time.
Where an assignment is undergoing investigation for alleged plagiarism or collusion the grade for the assignment and the assignment will be withheld until the investigation has concluded.
This assignment will take a number of weeks to complete and will require a good understanding of Enterprise Architecture and associated architectures for successful completion. It is imperative that students take heed of the following points in relation to doing this assignment:
1. Ensure that you clearly understand the requirements for the assignment – what has to be done and what are the deliverables.
2. If you do not understand any of the assignment requirements – Please ASK the Course Co-ordinator or your tutor.
3. Each time you work on any aspect of the assignment reread the assignment requirements to ensure that what is required is clearly understood.
Marking Sheet for ACC508 S2.2015 Assignment 2
Marking Criteria Maximum Marks Marks Obtained
ANSWERS Task 1
Task 6 36
Total (I) = 100 0.0
1 Robin horsburgh 15 Salomon Lane Belconnen act 2617 0401 915892 18 0.28 0.095 50 40
2 Ammad Bin Huang 37 Morris Court Bridgewater VIC 3516 0477 662648 22 0.28 0.18 50 56
3 Benjamin Allan Patel 931 Ferguson Circuit Bridgewater VIC 3516 0449 124774 36.5 0.3 0.18 50 78
4 Qun Begg 64 Coy Boulevard Caliph SA 5310 0444 344208 42 0.32 0.18 50 120
5 Elvin Denver khadka 773 Harrison Circuit Black Hills tas7140 0427 580663 21.87 0.28 0.18 50 12
6 karl Onga Moes 6 Alwabel Road Aberfoyle NSW 2350 0401 762606 22.2 0.28 0.095 50 18
7 Pramod Kumar Shah 45 townes Way Aberfoyle NSW 2350 21.6 0.28 0.095 50 52
8 Tianke Karki 225 Yegiora Boulevard bamboo qld 4873 0412 230546 21.25 0.28 0.095 50 16
9 Runsen Mudigonda 8 Wieck Close Belford NSW 2335 0470 104981 22.75 0.28 0.095 55 23
10 Ting Fu 40 Ball Close Belford nsw 2335 0487 738480 21.65 0.28 0.095 50 35
11 Mohammed Mujtaba Guo 6 Creamer Close Avon plains VIC 3477 0477 905619 19.85 0.28 0.095 50 45
12 Arun Kumar Bhimavarapu 3 Field Street Iluka NSW 2466 0451 108794 21.2 0.28 0.095 50 45
13 Toby Peter wong 493 Lane Avenue Aberfoyle NSW 2350 0466 896855 18.65 0.27 0.095 50 52
14 Sarita Hunter 1 Allen Drive Belford NSW 2335 0480 280753 31.87 0.3 0.18 45 12
15 Lovish Xu 9 Quade Street Aberfoyle nsw 2350 0443 107503 51.36 0.28 0.18 50 45
16 Reshmi Saniv Otto 49 Macdonald Boulevard Bray Park QLD 4500 0448 378169 18.75 0.22 0.095 23 25
17 Michael Javellana 35 Westcott Boulevard Iluka NSW 2466 19.45 0.22 0.095 23 45
18 Vanja Shin 33 Karl boulevard Bray park QLD 4500 0468 531908 17.23 0.22 0.095 23 10
19 Rehan Shah Cabillo 10 Robertson Close Nayook vic 3832 0415 916724 18.96 0.22 0.095 23 9
20 John William Ottal 7 Harrison Close Bray Park QLD 4500 0464 151899 18.2 0.22 0.095 23 8
21 Junyu Vikash 38 Duffy Circuit Areegra VIC 3480 0491 575314 17.75 0.22 0.095 23 12
22 Saranya eyles 76 Ryan Avenue Nayook VIC 3832 0427 188354 18.25 0.22 0.095 23 15
23 vandhana Huo 51 Burton Crescent Alleena NSW 2671 0462 804047 18.45 0.22 0.095 23 15
24 Bimal Novosel 37 Weppner Drive Belford NSW 2335 0494 780810 17.45 0.22 0.095 23 19
25 Sandeepkumar Khadari 9 Buckle Crescent Bamboo QLD 4873 18.25 0.22 0.095 23 19