Using the EA7-E1-Payroll file, finish 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 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
Alarcon, G	$20	$14	402
Foreman, B	$11	$8	403
Salinger, F	$16	$10	404
Owens, T	$23	$16	405
Willis, S	$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:D29 represent the percent of total earnings contributed to the respective voluntary withholdings; use these in your LOOKUP formulas. Use Voluntary Withholdings as the 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 employees 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. Format row 6 of the table to be bold with white text and dark blue fill. Format row 7 and 8 of the table with a light blue fill, bold and italic text. Finally use conditional formatting to highlight the three smallest Net Pay amounts with yellow fill and dark yellow text.