ACC508 - Informatics and Financial Applications Assignment 2

This assignment solution addresses ACC508 Informatics and Financial Applications Task 2, focusing on creating an Excel-based payroll calculator. The solution includes formatting worksheets, manipulating employee data (separating state and postcode, formatting names, and identifying missing data), creating formulas to calculate total taxes, total regular deductions, overtime rates, gross pay, net pay, and superannuation liability. The solution also incorporates VLOOKUP functions, conditional formatting, and the creation of a payroll summary worksheet that calculates company-wide financial metrics, such as total gross pay, net pay, and employee statistics. The assignment requires students to demonstrate proficiency in Excel functions, data manipulation, and financial calculations to automate and present payroll information effectively.

ACC508
Informatics and Financial
Applications
Task 2
Semester 2, 2016

Assessment and Submission Details
Marks: 30 % of the Total Assessment for the Course
Due Date: Friday 21st of October, Week 12
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.
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.
Page 2 of 6

ACC508 Informatics and Financial Applications Assignment 2
Background
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.
Please Note:
In Australia, permanent employees get given 20 days holiday pay (they get paid at their
normal rate while on holiday) and ten days sick pay (they get paid at their normal rate while
sick) 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 the Gross pay and taxed at
15%. Overtime is calculated at time and a half.
The task 2 starting workbook consists of two worksheets, named Employee Information and
Payroll Calculator.
Task 1
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. 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.
Those columns missing headings, give the columns appropriate headings.
Task 2
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. 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.
2. All columns should be formatted to use the correct data type for their contents.
3. Using conditional formatting, identify any cells with missing mobile phone numbers and
change their background colour to light red.
4. 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.
5. Sort the data alphabetically on “Last Name”
Task 3
1. Add ta new worksheet to your Excel workbook, 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 named cell to the “Payroll summary” work sheet called “Period_ending” and
give it todays date using an Excel Function.
Page 3 of 6

ACC508 Informatics and Financial Applications Assignment 2
Task 4
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.
Task 5
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 should 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 hours 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 employee’s gross pay multiplied by their individual superannuation rates.
8. Using an expression create a new column and IF the employee received sick pay in the week
place “Sick Pay” in this column. If they did not receive sick pay nothing should be in this
column.
Task 6
On the Payroll summary worksheet do the following and presented in a table:
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 deductions 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.
Page 4 of 6

ACC508 Informatics and Financial Applications Assignment 2
Assignment Requirements and Deliverables
The report is to be prepared as a single Microsoft Excel.
Submission
The completed assignment is to be submitted by the due date 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
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.
Page 5 of 6

ACC508 Informatics and Financial Applications Assignment 2
Marking Sheet for ACC508 S2.2016 Task 2
Student name:
Marking Criteria Maximum
Marks
Marks
Obtained
Excel Spreadsheet
ANSWERS
Task 1
>
1
Task 2
>
15
Task 3
>
9
Task 4
>
8
Task 5
>
31
Task 6 36
Total (I) = 100 0
/30%
OVERALL COMMENTS:
Page 6 of 6