Acc508 - The Assignment Assessment Answer

MAS5563

Financial Assignment – Case Study:

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 (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 of 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.

Questions: 

1) Format the two worksheets to create professional looking worksheets for each of these two worksheets. 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.

2) After completing task 1, a sequence of tasks needs to be completed on the Employee information worksheet. These tasks are listed below: Hide the columns that you no longer need as you have formatted them, do not delete them.

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 the 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 blue.

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”. This will put the employee number out of order.

Task 3

1. Add a 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 one named cells one called “Medicare_Levy” with a value of 1.5%. This cell should be appropriately labelled in your worksheet.
3. Add another named cell to the “Payroll summary” worksheet called “Period_ending” and give it today date using an Excel Function.

Task 4

1. On the “Employee information” worksheet, using an expression, calculate the “Total Tax Rate” 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 but should include “Other regular deductions” and
“Medical insurance”.

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. A display on the “Payroll calculator” worksheet, in the title row, 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 employee’s Hourly Rate (from the “Employee information” worksheet) multiplied by the
value 1.5, you may care to make this a standard value in one of your worksheets. Please note that the Payroll Calculator Sheet must be left in Employee number order.

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 paid for overtime. This must be done using an expression.

5. Using an expression calculate the total taxes and deduction for each employee (We are assuming that the tax rate is flat and this must include the Medicare levy).

6. Using an expression calculate the nett pay for each employee. Please note the Superannuation is not deducted.

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. Create a new column and IF the employee received sick pay in the week place “Sick Pay” in this column using an expression. If they did not receive sick pay nothing should be in this column.

Task 6

On the Payroll summary worksheet do the following:
1. Using an expression calculate the total gross pay for the company for this pay period, without putting a total on the Payroll Calculator.
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, excluding Superannuation 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 at work and has not been on leave (no holiday pay) 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 8 hours or more overtime in this pay period.