Calculate the gross pay for each employee

Assignment Help Finance Basics
Reference no: EM132199130

Informatics and Financial Applications Assignment Task -

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. This workbook should be dynamic so if you change a value the change should flow to all affected values.

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 of the Gross pay and taxed at 15%. Overtime is calculated at time and a half.

The task starting workbook consists of two worksheets, named Employee Information and Payroll Calculator.

Task 1 -

Format the two worksheets to create professional looking work sheets for each of these two worksheets. At this stage, none of the data in the two worksheets should be modified. Please note the mobile phone number needs to start with a zero. This will also require that you modify row height and column width. It is best not to use Excel to create tables.

Those columns missing headings, please 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: Hide the columns that you no longer need as you have formatted them, do NOT delete them.

1. Join the State & Postcode data into one column. This must be done using the various text functions available in Excel. It will require that you insert one additional column into this worksheet. One column should be labelled "State Postcode". 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 green.

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 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 one named cells 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" work sheet called "Period_ending" and give it todays date using an Excel Function.

Task 4 -

1. On the "Employee information" worksheet, add a column and 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, add a column 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. 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 employees Hourly Rate (from the "Employee information" work sheet) 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 payment 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 and put in a well presented table:

1. Using an expression on the payroll summary sheet calculate the total gross pay for the company for this pay period, without putting a total on the Payroll Calculator.

2. Using an expression on the payroll summary sheet calculate the total net pay for the company for this pay period.

3. Using an expression on the payroll summary sheet calculate the total Tax and payments withheld from the employees, excluding Superannuation for the company for this pay period.

4. Using an expression on the payroll summary sheet calculate the total superannuation liability for the company for this pay period.

5. Using an expression on the payroll summary sheet calculate the number of employees who took sick hours for week 1 and separately for week 2 pay period.

6. Using an expression on the payroll summary sheet 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 on the payroll summary sheet calculate the number of hours of overtime worked for each week in this pay period.

8. Using an expression on the payroll summary sheet count the number of employees who worked overtime in each week in this pay period.

9. In the payroll summary sheet list the employees who worked 7 hours or more overtime in this pay period in a table (do this manually), then in two columns list how many hours they worked in week 1 in column 1 and week 2 in the second column, using their last name as a lookup value.

Attachment:- Assignment Files.rar

Reference no: EM132199130

Questions Cloud

Company for violating antitrust laws : In 1974, the U.S. attorney general filed suit against which telecom company for violating antitrust laws?
Discuss the difficulties that can occur if a partnership : Discuss the difficulties that can occur if a partnership with the family is not developed when the child becomes a part of the learning environment.
Write out or print out your function : Write a function in Matlab that takes as input s and a number x and outputs the floating point number of x using s-digit rounding.
Write a function in matlab that takes as input the number n : Write a function in Matlab that takes as input the number n, the size of matrices and vectors; m, the number of nonzero elements of a sparse matrix;
Calculate the gross pay for each employee : ACC508 Informatics and Financial Applications Assignment Task, University of the Sunshine Coast, Australia. Calculate the gross pay for each employee
Test your function with three different integer arrays : Write a function, named avg, that receives an array of integer values and returns the average of those values in that array.
Write a function in matlab that takes as input the number n : Write a function in Matlab that takes as input the number n, a sparse matrix given as r, c, v, m, an n-component column vector b, and a tolerance tol.
Write a function to remove duplicate entries in linked list : Write a function to remove duplicate entries in a linked list. For example, given the list (5,2,2,5,3,9,2) as input, your function should change the list.
How often the police should monitor the tickets : For the NJ Light Rail problem, assume the price of a ticket is $5.00, and that a passenger has to decide whether to buy a ticket or try to get a free ride.

Reviews

len2199130

12/20/2018 3:51:02 AM

The assignment MUST be submitted online using Blackboard and on time. 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.

len2199130

12/20/2018 3:50:55 AM

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.

len2199130

12/20/2018 3:50:47 AM

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

Write a Review

 

Finance Basics Questions & Answers

  What is the rate of return on stock

Company A just paid an annual dividend of $4.35 a share. The stock has a market price of $110 and a dividend growth rate of 6 percent. What is the rate of return on this stock?

  What is the pretax operating cash flow break-even point

Break-even point units: Ski & Surf manufactures snow boards. The firm has fixed costs of $1,090,275. The snow boards sell for $335 each and have a variable cost of $165 each. What is the pretax operating cash flow break-even point for Ski & Surf.

  Write a one to two page memorandum to be sent to the clients

Write a one to two page memorandum to be sent to these clients explaining the coming changes in lease accounting for lessees.

  Comparing two different capital structures

Franklin Corporation is comparing two different capital structures, an all-equity plan (Plan I) and a levered plan (Plan II). Under Plan I, the company

  Calculate the tax disadvantage to organization

Then recalculate the tax disadvantage using the same income but with the maximum tax rates that existed before 2003. These rates were 35% (Tcg=.15) on corporate profits and 38.6 % (Tp=.386) on personal investment income.

  What will be the internal rate of return

An investor plans to earn $30,000 next year and $15,000 the year after from two investments, $20,000 now and $20,000 next year. What will be the internal rate of return?

  Question is about the role of investment banks

What is the role of investment banks in IPOs? How do they make their money?

  Country risk analysis for the foreign country

Once a project is accepted, country risk analysis for the foreign country involved is no longer necessary, assuming that no other proposed projects are being evaluated for that country. Do you agree with this statement?

  Department of a large corporation

The finance department of a large corporation has evaluated a possible capital project using the NPV method, the Payback Method, and the IRR method. The analysts are puzzled, since the NPV indicated rejection, but the IRR and Payback methods both..

  Describe at least two types of loss that can occur

Describe at least two types of loss that can occur that a standard homeowners insurance policy does not cover. Recommend specific add-ons to the standard insurance policy that you believe would cover the types of losses that you have described.

  Management responsibility for the failure

In order to complete this assignment, you will need to search the Internet and Strayer databases for information related to Lehman Brothers' bankruptcy, the financial risk factors that contributed to the failure, and management's responsibility fo..

  Two independent economic factors

Suppose there are two independent economic factors, M1 and M2. The risk-free rate is 4%, and all stocks have independent firm-specific components

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd