Requirement #4. Using the EA7-El-Payroll file, nish the partially completed payroll register to enter earnings, deductions. and net pay data. (Chapter 7) Modify the payroll register to ensure optimal layout and formatting. Use an IF function to populate the Overtime Earnings and Total Earnings columns so that MM 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 gures: Check Employee Name FWI' SWT Number Maroon. G $20 $14 402 Foreman, B $11 $8 403 Salinger, F $16 $10 404 Owens, T 523 $16 405 Willis, 5 $21 $7 406 Create a nested IF function to calculate Social Security tax and a separate formula to calculate Medicare tax for each employee. Use absolute cell references where appropriate. Use the appropriate LOOKUP function to calculate the charitable contributions and medical plan payment amounts for each employee. The percentages in the range C25:029 represent the percent of total earnings contributed to the respective voluntary withholdings; use these in your LOOKUP formulas. Also use an appropriate defined name for the table array referenced in your LOOKUP formulas. 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. Page K 6 > of 6 ZOOM + K Payroll Register Pay Period 1/31/2020 Pay Date 2/3/2020 Earnings Deductions Regular Regular Overtime Overtime Overtime Hospital Check Employee Name | Regular Hours Total Earnings FWT SWT Social Security Medicare Med. Plan Rate Earnings Hours Rate Earnings Donation Number Alarcon, G 40 $ 13.20 528.00 4 $ 19.80 Foreman, B 40 $ 13.20 $ 528.00 0 19.80 Salinger, F 40 $ 13.20 $ 528.00 1 $ 19.80 Owens, T 40 $ 13.20 528.00 6 19.80 Willis, S 40 $ 13.20 $ 528.00 10 19.80 Totals: $ 2,640.00 Total Earnings Hosp. Don. Med. Plan SS% Medicare % $ 500 0.8% 0.3% 6.20% 1.45% $ 650 1.1% 0.6% SS Wage Base $ 500 1.4% .9% S 137,700 650 1.7% 1.2% 700 2.0% 1.5% Name Prior Earnings Alarcon, G $ 137,500 Foreman, B $ 81,300 Salinger, F 54,000 Owens, T 151,800 Willis, S 104,200Page K 6 of 6 ZOOM + K Payroll Register 1/2020 3/2020 Earnings Deductions Regular Regular Overtime Overtime Overtime Check gular Hours Total Earnings FWT Hospital SWT Medicare Rate Social Security Med. Plan Net Pay Earnings Hours Rate Earnings Donation Number 40 $ 13.20 $ 528.00 4 $ 19.80 40 Ur 13.20 $ 528.00 0 $ 19.80 40 $ 13.20 $ 528.00 $ 19.80 40 $ 13.20 $ 528.00 6 $ 19.80 40 S 13.20 $ 528.00 10 S 19.80 $ 2,640.00 Hosp. Don. Med. Plan SS% Medicare % 0.8% 0.3% 6.20% 1.45% 1.1% 0.6% SS Wage Base 1.4% 0.9% 137,700 1.7% 1.2% 2.0% 1.5% or Earnings