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.
- 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.
- All columns should be formatted to use the correct data type for their contents.
- Using conditional formatting, identify any cells with missing mobile phone numbers and change their background colour to light green.
- 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.
- Sort the data alphabetically on “Last Name”. This will put the employee number out of order.
Task 3
- Add ta new worksheet to your Excel workbook, called "Payroll summary". This should be the first worksheet in your workbook.
- 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.
- Add another named cell to the “Payroll summary” work sheet called “Period_ending” and give it todays date using an Excel Function.
Task 4
- 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.
- 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
- On the “Payroll calculator” worksheet, retrieve the “Last Name” and “Given Name for each employee. This must be done using a Vlookup expression.
- 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.
- 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.
- 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.
- 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).
- Using an expression calculate the nett pay for each employee. Please note the Superannuation is not deducted.
- 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.
- 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:
- 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.
- Using an expression on the payroll summary sheet calculate the total net pay for the company for this pay period.
- 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.
- Using an expression on the payroll summary sheet calculate the total superannuation liability for the company for this pay period.
- 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.
- 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.
- Using an expression on the payroll summary sheet calculate the number of hours of overtime worked for each week in this pay period.
- Using an expression on the payroll summary sheet count the number of employees who worked overtime in each week in this pay period.
- 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.
Medicare Levy | 1.50% | Employees worked 7 hours or more overtime in this pay period | ||||||||||||||||
Period ending | 11-10-2018 | Given Name | Last Name | Week 1 OT Hours | Week 2 OT Hours | |||||||||||||
Overtime Rate | 1.5 | Smith | Aeddula | 8 | 3 | |||||||||||||
Total Gross Pay for the company for this pay period | $55,281.96 | Jones | Arora | 7 | 4 | |||||||||||||
Total Net Pay for the company for this pay period | $36,092.85 | Jared | Smith | 6 | 5 | |||||||||||||
Total tax and payments withheld from employees, excluding superannuation for this pay period | $19,189.11 | Pramod Kumar | Mudigonda | 3 | 4 | |||||||||||||
Total superannuation liability for the company for this pay period | $5,009.68 | Nishan | Bista | 4 | 4 | |||||||||||||
Number of employees who took sick hours in week 1 pay period | 3 | Toby Peter | Wong | 7 | 3 | |||||||||||||
Number of employees who took sick hours in week 2 pay period | 2 | Aaron | Hunter | 7 | 5 | |||||||||||||
Number of employees currently at work | 22 | Robert | Taylor | 6 | 5 | |||||||||||||
Number of hours of overtime worked in week 1 | 71.00 | Paul | Batten | 4 | 5 | |||||||||||||
Number of hours of overtime worked in week 2 | 60.00 | Mohammed Mujtaba | Ottal | 3 | 4 | |||||||||||||
Number of employees worked overtime in week 1 | 14 | |||||||||||||||||
Number of employees worked overtime in week 2 | 16 | |||||||||||||||||
Employee ID | First Name | Last Name | Address | City | State | Postcode | State Postcode | Phone Number | Hourly wage | Income tax | Superannuation Rate | Medical insurance | Other Regular Deductions | Total Tax Rate | Total Regular Deductions | |||
24 | Kiran | Kiran | Achhami | Achhami | 37 Weppner Drive | 37 Weppner Drive | Belford | Nsw | 2335 | NSW 2335 | 0494780929 | $29.45 | 25.00% | 1.00% | $25.00 | $23.00 | 26.50% | $48.00 |
1 | smith | Smith | Aeddula | Aeddula | 15 Salomon Lane | 15 Salomon Lane | Belconnen | act | 2617 | ACT 2617 | 0401916011 | $18.00 | 28.00% | 9.50% | $40.00 | $10.00 | 29.50% | $50.00 |
3 | Jones | Jones | Arora | Arora | 931 Ferguson Circuit | 931 Ferguson Circuit | Bridgewater | VIC | 3516 | VIC 3516 | 0449124893 | $35.50 | 30.00% | 18.00% | $40.00 | $8.00 | 31.50% | $48.00 |
16 | paul | Paul | batten | Batten | 49 Macdonald Boulevard | 49 Macdonald Boulevard | Bray Park | QLD | 4500 | QLD 4500 | 0448378288 | $28.75 | 25.00% | 1.00% | $25.00 | $32.00 | 26.50% | $57.00 |
12 | Lovish | Lovish | Bhimavarapu | Bhimavarapu | 3 Field Street | 3 Field Street | Iluka | NSW | 2466 | NSW 2466 | 0451108913 | $31.20 | 35.00% | 9.50% | $40.00 | $12.00 | 36.50% | $52.00 |
11 | Nishan | Nishan | bista | Bista | 6 Creamer Close | 6 Creamer Close | Avon plains | VIC | 3477 | VIC 3477 | 0477905738 | $23.85 | 30.00% | 9.50% | $40.00 | $25.00 | 31.50% | $65.00 |
17 | Michael | Michael | bond | Bond | 35 Westcott Boulevard | 35 Westcott Boulevard | Iluka | NSW | 2466 | NSW 2466 | $19.65 | 25.00% | 1.00% | $25.00 | $35.00 | 26.50% | $60.00 | |
19 | Rehan shah | Rehan Shah | Cabillo | Cabillo | 10 Robertson Close | 10 Robertson Close | Nayook | vic | 3832 | VIC 3832 | 0415916843 | $26.96 | 25.00% | 1.00% | $25.00 | $15.00 | 26.50% | $40.00 |
10 | Ting | Ting | Fu | Fu | 40 Ball Close | 40 Ball Close | Belford | nsw | 2335 | NSW 2335 | 0487738599 | $23.85 | 30.00% | 9.50% | $40.00 | $22.00 | 31.50% | $62.00 |
14 | Aaron | Aaron | Hunter | Hunter | 1 Allen Drive | 1 Allen Drive | Belford | NSW | 2335 | NSW 2335 | 0480280872 | $32.87 | 30.00% | 18.00% | $55.00 | $18.00 | 31.50% | $73.00 |
9 | alice | Alice | janes | Janes | 8 Wieck Close | 8 Wieck Close | Belford | nsw | 2335 | NSW 2335 | 0470105100 | $25.75 | 30.00% | 9.50% | $45.00 | $30.00 | 31.50% | $75.00 |
21 | tamanna | Tamanna | kaur | Kaur | 38 Duffy Circuit | 38 Duffy Circuit | Areegra | VIC | 3480 | VIC 3480 | 0491575433 | $23.75 | 25.00% | 1.00% | $25.00 | $13.00 | 26.50% | $38.00 |
25 | Sungsam | Sungsam | Li | Li | 9 Buckle Crescent | 9 Buckle Crescent | Bamboo | QLD | 4873 | QLD 4873 | $23.25 | 25.00% | 1.00% | $25.00 | $25.00 | 26.50% | $50.00 | |
23 | urgen | Urgen | Maan | Maan | 51 Burton Crescent | 51 Burton Crescent | Alleena | NSW | 2671 | NSW 2671 | 0462804166 | $25.45 | 25.00% | 1.00% | $25.00 | $14.00 | 26.50% | $39.00 |
6 | karl Onga | Karl Onga | Moes | Moes | 6 Alwabel Road | 6 Alwabel Road | Aberfoyle | NSW | 2350 | NSW 2350 | 0401762725 | $22.50 | 28.00% | 9.50% | $40.00 | $10.00 | 29.50% | $50.00 |
7 | Pramod kumar | Pramod Kumar | Mudigonda | Mudigonda | 45 townes Way | 45 Townes Way | Aberfoyle | Nsw | 2350 | NSW 2350 | $22.80 | 30.00% | 9.50% | $40.00 | $15.00 | 31.50% | $55.00 | |
20 | Mohammed mujtaba | Mohammed Mujtaba | Ottal | Ottal | 7 Harrison Close | 7 Harrison Close | Bray Park | qld | 4500 | QLD 4500 | 0464152018 | $26.20 | 25.00% | 1.00% | $25.00 | $20.00 | 26.50% | $45.00 |
8 | Tianke | Tianke | Shah | Shah | 225 Yegiora Boulevard | 225 Yegiora Boulevard | bamboo | qld | 4873 | QLD 4873 | 0412230665 | $21.35 | 30.00% | 9.50% | $40.00 | $20.00 | 31.50% | $60.00 |
18 | Vanja | Vanja | Shin | Shin | 33 Karl boulevard | 33 Karl Boulevard | Bray park | Qld | 4500 | QLD 4500 | 0468532027 | $23.23 | 25.00% | 1.00% | $25.00 | $15.00 | 26.50% | $40.00 |
2 | Harpreet | Harpreet | Singh | Singh | 37 Morris Court | 37 Morris Court | Bridgewater | Vic | 3516 | VIC 3516 | 0477662767 | $23.00 | 28.00% | 18.00% | $40.00 | $15.00 | 29.50% | $55.00 |
22 | navjot | Navjot | Singh | Singh | 76 Ryan Avenue | 76 Ryan Avenue | Nayook | VIC | 3832 | VIC 3832 | 0427188473 | $26.35 | 25.00% | 1.00% | $25.00 | $14.00 | 26.50% | $39.00 |
4 | jared | Jared | Smith | Smith | 64 Coy Boulevard | 64 Coy Boulevard | Caliph | SA | 5310 | SA 5310 | 0444344327 | $41.00 | 32.00% | 18.00% | $40.00 | $15.00 | 33.50% | $55.00 |
5 | rosan | Rosan | subedi | Subedi | 773 Harrison Circuit | 773 Harrison Circuit | Black Hills | tas | 7140 | TAS 7140 | 0427580782 | $22.00 | 28.00% | 18.00% | $40.00 | $7.00 | 29.50% | $47.00 |
15 | Robert | Robert | taylor | Taylor | 9 Quade Street | 9 Quade Street | Aberfoyle | nsw | 2350 | NSW 2350 | 0443107622 | $41.36 | 28.00% | 18.00% | $45.00 | $8.00 | 29.50% | $53.00 |
13 | Toby Peter | Toby Peter | wong | Wong | 493 Lane Avenue | 493 Lane Avenue | Aberfoyle | NSW | 2350 | NSW 2350 | 0466896974 | $28.65 | 27.00% | 9.50% | $40.00 | $13.00 | 28.50% | $53.00 |
Period Ending | 11-10-2018 | |||||||||||||||||
Week 1 | ||||||||||||||||||
Employee ID | Given Name | Last Name | Regular Hours Worked | Vacation Hours | Sick Hours | Overtime Hours | Overtime Rate | Gross Pay | Deduction |
Total Taxes and Deductions | Superannuation Amount | Net Pay | Whether Sick Pay | |||||
1 | Smith | Aeddula | 37.50 | 0.00 | 0.00 | 8.00 | $27.00 | $891.00 | $0.00 | $312.85 | $84.65 | $578.16 | ||||||
2 | Harpreet | Singh | 37.50 | 0.00 | 0.00 | 5.00 | $34.50 | $1,035.00 | $35.00 | $395.33 | $186.30 | $639.68 | ||||||
3 | Jones | Arora | 37.50 | 0.00 | 0.00 | 7.00 | $53.25 | $1,704.00 | $0.00 | $584.76 | $306.72 | $1,119.24 | ||||||
4 | Jared | Smith | 37.50 | 0.00 | 0.00 | 6.00 | $61.50 | $1,906.50 | $0.00 | $693.68 | $343.17 | $1,212.82 | ||||||
5 | Rosan | Subedi | 0.00 | 0.00 | 37.50 | 0.00 | $33.00 | $825.00 | $0.00 | $290.38 | $148.50 | $534.63 | Sick Pay | |||||
6 | Karl Onga | Moes | 15.00 | 7.50 | 0.00 | 0.00 | $33.75 | $506.25 | $0.00 | $199.34 | $48.09 | $306.91 | ||||||
7 | Pramod Kumar | Mudigonda | 37.50 | 0.00 | 0.00 | 3.00 | $34.20 | $957.60 | $0.00 | $356.64 | $90.97 | $600.96 | ||||||
8 | Tianke | Shah | 0.00 | 37.50 | 0.00 | 0.00 | $32.03 | $800.63 | $0.00 | $312.20 | $76.06 | $488.43 | ||||||
9 | Alice | Janes | 37.50 | 0.00 | 0.00 | 0.00 | $38.63 | $965.63 | $0.00 | $379.17 | $91.73 | $586.45 | ||||||
10 | Ting | Fu | 0.00 | 37.50 | 0.00 | 0.00 | $35.78 | $894.38 | $50.00 | $393.73 | $84.97 | $500.65 | ||||||
11 | Nishan | Bista | 37.50 | 0.00 | 0.00 | 4.00 | $35.78 | $1,037.48 | $0.00 | $391.80 | $98.56 | $645.67 | ||||||
12 | Lovish | Bhimavarapu | 26.00 | 0.00 | 11.50 | 0.00 | $46.80 | $1,170.00 | $0.00 | $479.05 | $111.15 | $690.95 | Sick Pay | |||||
13 | Toby Peter | Wong | 37.50 | 0.00 | 0.00 | 7.00 | $42.98 | $1,375.20 | $0.00 | $444.93 | $130.64 | $930.27 | ||||||
14 | Aaron | Hunter | 37.50 | 0.00 | 0.00 | 7.00 | $49.31 | $1,577.76 | $0.00 | $569.99 | $284.00 | $1,007.77 | ||||||
15 | Robert | Taylor | 37.50 | 0.00 | 0.00 | 6.00 | $62.04 | $1,923.24 | $0.00 | $620.36 | $346.18 | $1,302.88 | ||||||
16 | Paul | Batten | 37.50 | 0.00 | 0.00 | 4.00 | $43.13 | $1,250.63 | $0.00 | $388.42 | $12.51 | $862.21 | ||||||
17 | Michael | Bond | 37.50 | 0.00 | 0.00 | 5.00 | $29.48 | $884.25 | $0.00 | $294.33 | $8.84 | $589.92 | ||||||
18 | Vanja | Shin | 37.50 | 0.00 | 0.00 | 0.00 | $34.85 | $871.13 | $0.00 | $270.85 | $8.71 | $600.28 | ||||||
19 | Rehan Shah | Cabillo | 37.50 | 0.00 | 0.00 | 0.00 | $40.44 | $1,011.00 | $0.00 | $307.92 | $10.11 | $703.09 | ||||||
20 | Mohammed Mujtaba | Ottal | 37.50 | 0.00 | 0.00 | 3.00 | $39.30 | $1,100.40 | $20.00 | $356.61 | $11.00 | $743.79 | ||||||
21 | Tamanna | Kaur | 37.50 | 0.00 | 0.00 | 3.00 | $35.63 | $997.50 | $20.00 | $322.34 | $9.98 | $675.16 | ||||||
22 | Navjot | Singh | 37.50 | 0.00 | 0.00 | 0.00 | $39.53 | $988.13 | $0.00 | $300.85 | $9.88 | $687.27 | ||||||
23 | Urgen | Maan | 37.50 | 0.00 | 0.00 | 3.00 | $38.18 | $1,068.90 | $10.00 | $332.26 | $10.69 | $736.64 | ||||||
24 | Kiran | Achhami | 37.50 | 0.00 | 0.00 | 0.00 | $44.18 | $1,104.38 | $0.00 | $340.66 | $11.04 | $763.72 | ||||||
25 | Sungsam | Li | 0.00 | 0.00 | 37.50 | 0.00 | $34.88 | $871.88 | $0.00 | $281.05 | $8.72 | $590.83 | Sick Pay |
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). Format Worksheets And Create A Professional Look - Tasks 1 To 6 - Payroll Summary Worksheet. Retrieved from https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/total-net-pay.html.
"Format Worksheets And Create A Professional Look - Tasks 1 To 6 - Payroll Summary Worksheet." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/total-net-pay.html.
My Assignment Help (2021) Format Worksheets And Create A Professional Look - Tasks 1 To 6 - Payroll Summary Worksheet [Online]. Available from: https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/total-net-pay.html
[Accessed 21 November 2024].
My Assignment Help. 'Format Worksheets And Create A Professional Look - Tasks 1 To 6 - Payroll Summary Worksheet' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/total-net-pay.html> accessed 21 November 2024.
My Assignment Help. Format Worksheets And Create A Professional Look - Tasks 1 To 6 - Payroll Summary Worksheet [Internet]. My Assignment Help. 2021 [cited 21 November 2024]. Available from: https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/total-net-pay.html.