EA7-E1 Complete a Payroll Register for Ashley's Auto Sales (30 pts) In this exercise, you will complete a payroll register for Ashley's Auto Sales. You will use the techniques from this chapter to enter earnings, deduction, and net pay data. You will also modify the payroll register to ensure optimal layout and formatting. Open EA7-E1-Payroll. Use an IF function to populate the Overtime Earnings and Total Earnings columns. Ensure that N/A is displayed in the Overtime Earnings column for employees who did not work overtime hours during the period. Populate the FWT and SWT columns using these figures: Employee Name FWT SWT Check Number Stanley, J $24 $16 682 Campbell, Y $18 $12 683 Raimi, A $12 $8 684 Williams, N $33 $22 685 McCann, V $16 $10 686 Create a nested IF function to calculate Social Security tax and a separate formula to calculate Medicare tax for each employee. Use the appropriate LOOKUP function to calculate the charitable contributions and medical plan payment amounts for each employee. Note that the percentages in the range C25:D29 represent the percent of total earnings contributed to the respective voluntary withholdings. Use these in your LOOKUP function. Complete the payroll register by entering the check numbers, creating a formula to calculate each employee's net pay, and creating formulas to total columns where necessary. Adjust the worksheet layout by adding borders around distinct sets of data, adjusting column widths and/or row heights between sets of data, and minimizing blank space between data sets. Last, use appropriate formatting to distinguish the headers in rows 6-8 from the data below and conditional formatting to highlight the two largest Net Pay amounts. C Pay Period 3/27/20 3/19/20 Pay Date Employee Name Regular Hours Stanley 40 40 Campbell Y Ram, A 40 40 William N McCann, V 40 Totale Total tamings Map Con $ 100 450 500 550 600 $ $ $ 1 13 ENCO NOT 1.36 1.4% 194 D Regular Rate $8.30 $ 5 8.20 $ 8.20 S 8.20 5 328.00 8.20 S 328.00 1.640.00 $ 5 S Med Plan 1.00 NOT 20% 718 NOX 14 Regular $1 Earnings Overtime Overtime Rate Hours 5 12.30 5.12.30 5 12.30 5 13.30 5 13.30 328.00 325.00 338.00 10 20 2 60 11 Overtime Earrings Name Stanley Campbell, Y RAMLA Willa N McCann, V J Payroll Register Prior Earnings $ 28,500 102,000 $3 118.400 72,300 15 137.650 $ 4 FWT SWT Deductions Social Security Medicare Medica LASN NOT 6.30% 55W $ 137,700 Hospital Donation 0 Med Plan P Chack Number D NetPay Pat 521 10 11 12 11 14 33 B x f C 3/27/20 3/29/20 Pay Period Pay Date Employee Name Stanley, I Campbell, Y Raimi, A Williams, N McCann, V Total: 21 24 Total Earnings Hosp. Don. Med Plan 21 $ 300 0.5% 1.0% 21 5 450 1.0% 1.5% 27 $ 500 3.3% 2.0% 215 550 1.6% 2.5% " $ 600 1.9% 3.0% M Regular Hours 888882 40 40 40 40 40 6-A D [ Earnings Regular Race Hours Rate Regular Overtim Overtim Overtime Larnings $ 8.20 5328.00 Earnings 2 $12.30 $ 8.20 $ 328.00 7 $12.30 $ 8.20 5328.00 10 $12.30 $ 8.20 $ 328.00 $12.30 $8.20 $ 328.00 $12.30 $1,640.00 FR 20 Merge Center Payroll Register Total Earnings 9 11 PWT Name Prior Earnings Stanley, J. $ 28,500 Campbell, Y$ 102.000 Raimi, A $ 138,000 Williams, N $ 72,300 McCann, V $ 137,650 $-% K SWT 24 Deductions Social Security 55% 6.20% SS Wage Base $ 137,700 Medicare Medicare 1.45% a Conditions Formatting Table Styles Hospital Donation Med Plan Number Net Pay B Pay Period 3/27/20 Pay Date 3/29/20 Employee Name Regular Hours Stanley, J. 40 Campbell, Y 40 40 Williams, N 40 McCann, V 40 16 Totals: 18 19 20 21 22 23 24 Total Earnings Hosp. Don. 25 $ 300 0.5% 450 1.0% 500 1.3% 550 1.6% 600 1.9% 0 1 Raimi, A 2 3 4 15 600 56 26 27 28 8997 29 30 31 32 33 SSSSS $ $ $ $ D E Regular Rate Regular Earnings $ 8.20 $ 328.00 $ $ 328.00 $ 8.20 $ 328.00 8.20 $ 328.00 8.20 $ 328.00 $ 1,640.00 S55 $ $ Med. Plan 1.0% 1.5% 2.0% 2.5% 3.0% F Earnings Overtime Overtime Hours Rate 2 $ 12.30 7 $ 12.30 0 $ 12.30 9 $ 12.30 11 $ 12.30 Star Cam Rain Willi McC EA7-E1 Complete a Payroll Register for Ashley's Auto Sales (30 pts) In this exercise, you will complete a payroll register for Ashley's Auto Sales. You will use the techniques from this chapter to enter earnings, deduction, and net pay data. You will also modify the payroll register to ensure optimal layout and formatting. Open EA7-E1-Payroll. Use an IF function to populate the Overtime Earnings and Total Earnings columns. Ensure that N/A is displayed in the Overtime Earnings column for employees who did not work overtime hours during the period. Populate the FWT and SWT columns using these figures: Employee Name FWT SWT Check Number Stanley, J $24 $16 682 Campbell, Y $18 $12 683 Raimi, A $12 $8 684 Williams, N $33 $22 685 McCann, V $16 $10 686 Create a nested IF function to calculate Social Security tax and a separate formula to calculate Medicare tax for each employee. Use the appropriate LOOKUP function to calculate the charitable contributions and medical plan payment amounts for each employee. Note that the percentages in the range C25:D29 represent the percent of total earnings contributed to the respective voluntary withholdings. Use these in your LOOKUP function. Complete the payroll register by entering the check numbers, creating a formula to calculate each employee's net pay, and creating formulas to total columns where necessary. Adjust the worksheet layout by adding borders around distinct sets of data, adjusting column widths and/or row heights between sets of data, and minimizing blank space between data sets. Last, use appropriate formatting to distinguish the headers in rows 6-8 from the data below and conditional formatting to highlight the two largest Net Pay amounts. C Pay Period 3/27/20 3/19/20 Pay Date Employee Name Regular Hours Stanley 40 40 Campbell Y Ram, A 40 40 William N McCann, V 40 Totale Total tamings Map Con $ 100 450 500 550 600 $ $ $ 1 13 ENCO NOT 1.36 1.4% 194 D Regular Rate $8.30 $ 5 8.20 $ 8.20 S 8.20 5 328.00 8.20 S 328.00 1.640.00 $ 5 S Med Plan 1.00 NOT 20% 718 NOX 14 Regular $1 Earnings Overtime Overtime Rate Hours 5 12.30 5.12.30 5 12.30 5 13.30 5 13.30 328.00 325.00 338.00 10 20 2 60 11 Overtime Earrings Name Stanley Campbell, Y RAMLA Willa N McCann, V J Payroll Register Prior Earnings $ 28,500 102,000 $3 118.400 72,300 15 137.650 $ 4 FWT SWT Deductions Social Security Medicare Medica LASN NOT 6.30% 55W $ 137,700 Hospital Donation 0 Med Plan P Chack Number D NetPay Pat 521 10 11 12 11 14 33 B x f C 3/27/20 3/29/20 Pay Period Pay Date Employee Name Stanley, I Campbell, Y Raimi, A Williams, N McCann, V Total: 21 24 Total Earnings Hosp. Don. Med Plan 21 $ 300 0.5% 1.0% 21 5 450 1.0% 1.5% 27 $ 500 3.3% 2.0% 215 550 1.6% 2.5% " $ 600 1.9% 3.0% M Regular Hours 888882 40 40 40 40 40 6-A D [ Earnings Regular Race Hours Rate Regular Overtim Overtim Overtime Larnings $ 8.20 5328.00 Earnings 2 $12.30 $ 8.20 $ 328.00 7 $12.30 $ 8.20 5328.00 10 $12.30 $ 8.20 $ 328.00 $12.30 $8.20 $ 328.00 $12.30 $1,640.00 FR 20 Merge Center Payroll Register Total Earnings 9 11 PWT Name Prior Earnings Stanley, J. $ 28,500 Campbell, Y$ 102.000 Raimi, A $ 138,000 Williams, N $ 72,300 McCann, V $ 137,650 $-% K SWT 24 Deductions Social Security 55% 6.20% SS Wage Base $ 137,700 Medicare Medicare 1.45% a Conditions Formatting Table Styles Hospital Donation Med Plan Number Net Pay B Pay Period 3/27/20 Pay Date 3/29/20 Employee Name Regular Hours Stanley, J. 40 Campbell, Y 40 40 Williams, N 40 McCann, V 40 16 Totals: 18 19 20 21 22 23 24 Total Earnings Hosp. Don. 25 $ 300 0.5% 450 1.0% 500 1.3% 550 1.6% 600 1.9% 0 1 Raimi, A 2 3 4 15 600 56 26 27 28 8997 29 30 31 32 33 SSSSS $ $ $ $ D E Regular Rate Regular Earnings $ 8.20 $ 328.00 $ $ 328.00 $ 8.20 $ 328.00 8.20 $ 328.00 8.20 $ 328.00 $ 1,640.00 S55 $ $ Med. Plan 1.0% 1.5% 2.0% 2.5% 3.0% F Earnings Overtime Overtime Hours Rate 2 $ 12.30 7 $ 12.30 0 $ 12.30 9 $ 12.30 11 $ 12.30 Star Cam Rain Willi McC