Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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.

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: Hide the columns that you no longer need as you have formatted them, do 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 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” work sheet called “Period_ending” and give it todays 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. 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:

  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 week 1 and separately for week 2 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 each week in this pay period.
  8. Using an expression count the number of employees who worked overtime in each week in this pay period.
  9. Using expressions list the employees who worked 8 hours or more overtime in this pay period in a table, then in two columns list how many hours they worked in.
Medicare Levy 1.50%                                  
Period ending 27-09-2018                                  
Overtime Rate 1.5                                  
      Employees worked 8 hours or more overtime in this pay period                              
Total Gross Pay for this pay period $54,463.86                                  
Total Net Pay for this pay period $35,192.08   Given Name Last Name Overtime Hours in Week 1 Overtime Hours in Week 2                        
Total tax and payments withheld from employees excluding Superannuation $19,271.78   Harish Aeddula 10 2                        
Total superannuation liability for this pay period $4,923.76   Arif Arora 7 5                        
      John Smith 5 6                        
Number of sick hours in week 1 86.50   Toby Peter Wong 6 2                        
Number of sick hours in week 2 49.00   Aaron Hunter 7 4                        
Number of employees currently at work 21   Robert Taylor-Jones 5 4                        
Number of overtime hours in week 1 63.00   Paul Batten 6 4                        
Number of overtime hours in week 2 53.00   Mohammed Mujtaba Ottal 4 4                        
Number of employees worked overtime in week 1 14                                  
Number of employees worked overtime in week 2 16                                  
                                     
Employee ID First Name1 First Name Last Name1 Last Name Address1 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 ######## $29.45 25.00% 1.00% $23.00 $22.00 26.50% $45.00
1   harish Harish Aeddula Aeddula    15 Salomon     Lane 15 Salomon Lane Belconnen act 2617 ACT 2617 ######## $17.00 28.00% 9.50% $50.00 $5.00 29.50% $55.00
3 Arif Arif Arora Arora 931 Ferguson Circuit 931 Ferguson Circuit Bridgewater VIC 3516 VIC 3516 ######## $36.50 30.00% 18.00% $50.00 $15.00 31.50% $65.00
16 paul  Paul  batten Batten 49 Macdonald Boulevard 49 Macdonald Boulevard Bray Park QLD 4500 QLD 4500 ######## $28.75 25.00% 1.00% $23.00 $35.00 26.50% $58.00
12   Lovish Lovish Bhimavarapu Bhimavarapu 3 Field          Street 3 Field Street Iluka NSW 2466 NSW 2466 ######## $31.20 35.00% 9.50% $50.00 $14.00 36.50% $64.00
11 Nishan Nishan  bista Bista 6 Creamer Close 6 Creamer Close Avon plains VIC 3477 VIC 3477 ######## $21.85 30.00% 9.50% $50.00 $47.00 31.50% $97.00
17 Michael Michael  bond Bond 35 Westcott Boulevard 35 Westcott Boulevard Iluka NSW 2466 NSW 2466   $19.65 25.00% 1.00% $23.00 $36.00 26.50% $59.00
19   Rehan     shah Rehan Shah Cabillo Cabillo 10 Robertson Close 10 Robertson Close Nayook vic 3832 VIC 3832 ######## $26.96 25.00% 1.00% $23.00 $20.00 26.50% $43.00
10 Ting Ting Fu Fu 40 Ball Close 40 Ball Close Belford nsw 2335 NSW 2335 ######## $21.85 30.00% 9.50% $50.00 $25.00 31.50% $75.00
14 Aaron Aaron Hunter Hunter 1 Allen Drive 1 Allen Drive Belford NSW 2335 NSW 2335 ######## $33.87 30.00% 18.00% $45.00 $20.00 31.50% $65.00
9     alice Alice  jones Jones 8 Wieck Close 8 Wieck Close Belford nsw 2335 NSW 2335 ######## $25.75 30.00% 9.50% $55.00 $33.00 31.50% $88.00
22 navjot Navjot   kaur Kaur 76 Ryan Avenue 76 Ryan Avenue Nayook VIC 3832 VIC 3832 ######## $27.35 25.00% 1.00% $23.00 $15.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% $23.00 $24.00 26.50% $47.00
23 urgen Urgen Maan Maan 51 Burton Crescent 51 Burton Crescent Alleena NSW 2671 NSW 2671 ######## $26.45 25.00% 1.00% $23.00 $15.00 26.50% $38.00
6 karl Onga Karl Onga Moes Moes 6 Alwabel Road 6 Alwabel Road Aberfoyle   NSW 2350 NSW 2350 ######## $22.20 28.00% 9.50% $50.00 $14.00 29.50% $64.00
7 Pramod    kumar Pramod Kumar Mudigonda Mudigonda 45    townes     Way 45 Townes Way Aberfoyle Nsw 2350 NSW 2350   $21.80 30.00% 9.50% $50.00 $17.00 31.50% $67.00
20 Mohammed    mujtaba Mohammed Mujtaba Ottal Ottal 7 Harrison Close 7 Harrison Close Bray Park   qld 4500 QLD 4500 ######## $26.20 25.00% 1.00% $23.00 $18.00 26.50% $41.00
8 Tianke Tianke Shah Shah 225 Yegiora Boulevard 225 Yegiora Boulevard bamboo qld 4873 QLD 4873 ######## $21.35 30.00% 9.50% $50.00 $22.00 31.50% $72.00
18 Vanja Vanja Shin Shin         33 Karl boulevard 33 Karl Boulevard Bray park  Qld 4500 QLD 4500 ######## $22.23 25.00% 1.00% $23.00 $20.00 26.50% $43.00
2 Harpreet Harpreet Singh Singh 37 Morris Court 37 Morris Court Bridgewater  Vic 3516 VIC 3516 ######## $22.00 28.00% 18.00% $50.00 $10.00 29.50% $60.00
21  tamanna Tamanna Singh Singh 38 Duffy Circuit 38 Duffy Circuit Areegra VIC 3480 VIC 3480 ######## $23.75 25.00% 1.00% $23.00 $16.00 26.50% $39.00
4  john John Smith Smith 64 Coy Boulevard 64 Coy Boulevard Caliph   SA  5310 SA 5310 ######## $42.00 32.00% 18.00% $50.00 $20.00 33.50% $70.00
5  rosan Rosan subedi Subedi 773 Harrison Circuit 773 Harrison Circuit Black Hills tas 7140 TAS 7140 ######## $21.87 28.00% 18.00% $50.00 $5.00 29.50% $55.00
15 Robert Robert taylor-Jones Taylor-Jones 9 Quade Street 9 Quade Street Aberfoyle nsw 2350 NSW 2350 ######## $41.36 28.00% 18.00% $50.00 $10.00 29.50% $60.00
13 Toby Peter Toby Peter wong Wong 493 Lane Avenue 493 Lane Avenue Aberfoyle      NSW 2350 NSW 2350 ######## $28.65 27.00% 9.50% $50.00 $15.00 28.50% $65.00
                                     
Period Ending 27-09-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 Harish Aeddula 37.50 0.00 0.00 10.00 $25.50 $892.50 $0.00 $318.29   $84.79 $574.21          
2 Harpreet Singh 37.50 0.00 0.00 2.00 $33.00 $891.00 $35.00 $357.85   $160.38 $533.16          
3 Arif Arora 37.50 0.00 0.00 7.00 $54.75 $1,752.00 $0.00 $616.88   $315.36 $1,135.12          
4 John Smith 37.50 0.00 0.00 5.00 $63.00 $1,890.00 $0.00 $703.15   $340.20 $1,186.85          
5 Rosan Subedi 0.00 0.00 37.50 0.00 $32.81 $820.13 $0.00 $296.94   $147.62 $523.19 Sick Pay        
6 Karl Onga Moes 15.00 7.50 0.00 0.00 $33.30 $499.50 $0.00 $211.35   $47.45 $288.15          
7 Pramod Kumar Mudigonda 37.50 0.00 0.00 1.00 $32.70 $850.20 $0.00 $334.81   $80.77 $515.39          
8 Tianke Shah 0.00 37.50 0.00 0.00 $32.03 $800.63 $0.00 $324.20   $76.06 $476.43          
9 Alice Jones 37.50 0.00 0.00 0.00 $38.63 $965.63 $0.00 $392.17   $91.73 $573.45          
10 Ting Fu 0.00 37.50 0.00 0.00 $32.78 $819.38 $50.00 $383.10   $77.84 $436.27          
11 Nishan Bista 37.50 0.00 0.00 3.00 $32.78 $917.70 $0.00 $386.08   $87.18 $531.62          
12 Lovish Bhimavarapu 26.00 0.00 11.50 0.00 $46.80 $1,170.00 $0.00 $491.05   $111.15 $678.95 Sick Pay        
13 Toby Peter Wong 37.50 0.00 0.00 6.00 $42.98 $1,332.23 $0.00 $444.68   $126.56 $887.54          
14 Aaron Hunter 37.50 0.00 0.00 7.00 $50.81 $1,625.76 $0.00 $577.11   $292.64 $1,048.65          
15 Robert Taylor-Jones 37.50 0.00 0.00 5.00 $62.04 $1,861.20 $0.00 $609.05   $335.02 $1,252.15          
16 Paul Batten 37.50 0.00 0.00 6.00 $43.13 $1,336.88 $0.00 $412.27   $13.37 $924.60          
17 Michael Bond 37.50 0.00 0.00 3.00 $29.48 $825.30 $0.00 $277.70   $8.25 $547.60          
18 Vanja Shin 37.50 0.00 0.00 0.00 $33.35 $833.63 $0.00 $263.91   $8.34 $569.71          
19 Rehan Shah Cabillo 37.50 0.00 0.00 0.00 $40.44 $1,011.00 $0.00 $310.92   $10.11 $700.09          
20 Mohammed Mujtaba Ottal 37.50 0.00 0.00 4.00 $39.30 $1,139.70 $20.00 $363.02   $11.40 $776.68          
21 Tamanna Singh 37.50 0.00 0.00 2.00 $35.63 $961.88 $20.00 $313.90   $9.62 $647.98          
22 Navjot Kaur 37.50 0.00 0.00 0.00 $41.03 $1,025.63 $0.00 $309.79   $10.26 $715.83          
23 Urgen Maan 37.50 0.00 0.00 2.00 $39.68 $1,071.23 $10.00 $331.87   $10.71 $739.35          
24 Kiran Achhami 37.50 0.00 0.00 0.00 $44.18 $1,104.38 $0.00 $337.66   $11.04 $766.72          
25 Sungsam Li 0.00 0.00 37.50 0.00 $34.88 $871.88 $0.00 $278.05   $8.72 $593.83 Sick Pay        
                                     
                                     
                                     
Week 2                                    
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 Harish Aeddula 37.50 0.00 0.00 2.00 $25.50 $688.50 $0.00 $258.11   $65.41 $430.39          
2 Harpreet Singh 37.50 0.00 0.00 0.00 $33.00 $825.00 $35.00 $338.38   $148.50 $486.63          
3 Arif Arora 37.50 0.00 0.00 5.00 $54.75 $1,642.50 $0.00 $582.39   $295.65 $1,060.11          
4 John Smith 37.50 0.00 0.00 6.00 $63.00 $1,953.00 $0.00 $724.26   $351.54 $1,228.75          
5 Rosan Subedi 37.50 0.00 0.00 2.00 $32.81 $885.74 $0.00 $316.29   $159.43 $569.44          
6 Karl Onga Moes 22.50 15.00 0.00 2.00 $33.30 $899.10 $0.00 $329.23   $85.41 $569.87          
7 Pramod Kumar Mudigonda 37.50 0.00 0.00 3.00 $32.70 $915.60 $0.00 $355.41   $86.98 $560.19          
8 Tianke Shah 37.50 0.00 0.00 2.00 $32.03 $864.68 $0.00 $344.37   $82.14 $520.30          
9 Alice Jones 37.50 0.00 0.00 0.00 $38.63 $965.63 $0.00 $392.17   $91.73 $573.45          
10 Ting Fu 0.00 37.50 0.00 0.00 $32.78 $819.38 $50.00 $383.10   $77.84 $436.27          
11 Nishan Bista 37.50 0.00 0.00 4.00 $32.78 $950.48 $0.00 $396.40   $90.30 $554.08          
12 Lovish Bhimavarapu 26.00 0.00 11.50 0.00 $46.80 $1,170.00 $0.00 $491.05   $111.15 $678.95 Sick Pay        
13 Toby Peter Wong 37.50 0.00 0.00 2.00 $42.98 $1,160.33 $0.00 $395.69   $110.23 $764.63          
14 Aaron Hunter 37.50 0.00 0.00 4.00 $50.81 $1,473.35 $0.00 $529.10   $265.20 $944.24          
15 Robert Taylor-Jones 37.50 0.00 0.00 4.00 $62.04 $1,799.16 $0.00 $590.75   $323.85 $1,208.41          
16 Paul Batten 37.50 0.00 0.00 4.00 $43.13 $1,250.63 $0.00 $389.42   $12.51 $861.21          
17 Michael Bond 37.50 0.00 0.00 0.00 $29.48 $736.88 $0.00 $254.27   $7.37 $482.60          
18 Vanja Shin 37.50 0.00 0.00 3.00 $33.35 $933.66 $0.00 $290.42   $9.34 $643.24          
19 Rehan Shah Cabillo 37.50 0.00 0.00 0.00 $40.44 $1,011.00 $0.00 $310.92   $10.11 $700.09          
20 Mohammed Mujtaba Ottal 37.50 0.00 0.00 4.00 $39.30 $1,139.70 $20.00 $363.02   $11.40 $776.68          
21 Tamanna Singh 37.50 0.00 0.00 3.00 $35.63 $997.50 $20.00 $323.34   $9.98 $674.16          
22 Navjot Kaur 37.50 0.00 0.00 0.00 $41.03 $1,025.63 $0.00 $309.79   $10.26 $715.83          
23 Urgen Maan 37.50 0.00 0.00 3.00 $39.68 $1,110.90 $10.00 $342.39   $11.11 $768.51          
24 Kiran Achhami 37.50 0.00 0.00 0.00 $44.18 $1,104.38 $0.00 $337.66   $11.04 $766.72          
25 Sungsam Li 0.00 0.00 37.50 0.00 $34.88 $871.88 $0.00 $278.05   $8.72 $593.83 Sick Pay        
                                     
Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Format Worksheets And Modify Column Width And Row Height. Retrieved from https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/company.html.

"Format Worksheets And Modify Column Width And Row Height." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/company.html.

My Assignment Help (2021) Format Worksheets And Modify Column Width And Row Height [Online]. Available from: https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/company.html
[Accessed 21 June 2024].

My Assignment Help. 'Format Worksheets And Modify Column Width And Row Height' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/company.html> accessed 21 June 2024.

My Assignment Help. Format Worksheets And Modify Column Width And Row Height [Internet]. My Assignment Help. 2021 [cited 21 June 2024]. Available from: https://myassignmenthelp.com/free-samples/acc508-informatics-and-financial-applications/company.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close