Please show in excel, Thank You!

Set the Freeze Panes command on the Payroll Details worksheet so that Rows 1 and 2 and Columns A and B are locked in place while scrolling through the worksheet.

Enter an IF function in cell G3 on the Payroll Details worksheet to calculate the Social Security tax. Social Security funds are used by the government to provide income for people who are retired, a beneficiary of a retiree, or disabled. An employer must withhold 4.2% of an employees weekly pay for Social Security. However, an employee is only taxed up to $100,000. The logical test of the IF function should assess if the value in the Pay Year to Date column is greater than or equal to 100000. If the logical test is true, the output of the function should be zero. Otherwise, the function should multiply the value in the Gross Pay This Week by 4.2%. Copy and paste the function into the range G4:G22.

Enter a formula in cell H3 on the Payroll Details worksheet that calculates the Medicare Tax. Medicare funds are used by the government to provide medical financial support to senior citizens. Your formula should multiply the Gross Pay This Week by 1.45%. Copy and paste this formula into the range H4:H22.

Enter a formula in cell I3 on the Payroll Details worksheet that calculates the total FICA tax (FICA stands for Federal Insurance Contributions Act). Your formula should add the Social Security Tax to the Medicare Tax. Copy and paste this formula into the range I4:I22.

Enter an IF function in cell J3 on the Payroll Details worksheet to calculate the Federal Tax. If the Gross Pay This Week is less than or equal to 1150, then the tax is calculated by multiplying the Gross Pay This Week by 20%. Otherwise, this tax is calculated by multiplying the Gross Pay This Week by 25%. Copy and paste the IF function into the range J4:J22.

If an employee has been working with the medical group for 1 or more years, the company will match 50% of the employees 401(k) retirement contributions. Calculate the companys 401(k) retirement contributions by entering an IF function in cell L3 on the Payroll Details worksheet. If the Years of Service is greater than or equal to 1, multiply the 401K Retirement value by 50%. Otherwise, the output of the function should be zero. Copy and paste the IF function into the range L4:L22.

The medical group offers its employees an additional retirement benefit based on the employees position and years of service. Employees who are with the practice 3 or more years will receive an additional contribution to their retirement account. Calculate this benefit by entering an IF function in cell M3 on the Payroll Details worksheet. If an employee is a physician and has been working in the medical group for 3 or more years, the function should multiply the Gross Pay This Week by 8%. For all other employees who have been working at the medical group for 3 or more years, the function should multiply the Gross Pay This Week by 5%. Copy and paste the function into the range M4:M22.

Enter a formula into cell N3 on the Payroll Details worksheet that calculates the Total Retirement Benefits. Your formula should add the 401K Company Match value to the Retirement Benefit value. Copy and paste the formula into the range N4:N22.

Enter a formula into cell O3 on the Payroll Details worksheet that calculates the Net Payment for each employee. Your formula should subtract the values in the range I3:K3 from the Gross Pay This Week in cell F3. The range I3:K3 includes the following: Total FICA (cell I3), Federal Tax (cell J3), and 401K Retirement (cell K3). Copy and paste this formula into the range O4:O22.

Enter a COUNTIF function into cell B3 on the Payroll Summary worksheet. The function should count the number of employees in the range A3:A22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range B4:B7.

Enter an AVERAGEIF function into cell C3 on the Payroll Summary worksheet. The function should calculate the average Years of Service in the range D3:D22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range C4:C7.

Enter a SUMIF function into cell D3 on the Payroll Summary worksheet. The function should sum the Total FICA tax in the range I3:I22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. However, the government requires that employers match the FICA tax that is withheld from employees paychecks. Therefore, multiply the result of this SUMIF function by 2. Copy and paste the function into the range D4:D7.

Enter a SUMIF function into cell E3 on the Payroll Summary worksheet. The function should sum the Federal Tax withholdings in the range J3:J22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range E4:E7.

Enter a SUMIF function into cell F3 on the Payroll Summary worksheet. The function should sum the Net Payments in the range O3:O22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range F4:F7.

Enter a SUMIF function into cell G3 on the Payroll Summary worksheet. The function should sum the Total Retirement Benefits in the range N3:N22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range G4:G7.

Enter a SUM function in cell B8 on the Payroll Summary worksheet that sums the values in the range B3:B7. Copy and paste this SUM function into the range D8:G8. Then format the range D8:G8 to the Accounting number format.

Little Hills Medical Group Payroll Detail
Employee Last Name, First Name	Position	Practice	Years of Service	Pay Year to Date	Gross Pay This Week	Social Security Tax	Medicare Tax	Total FICA	Federal Tax	401K Retirment	401 K Company Match	Retirement Benefit	Total Retirement Benefits	Net Payment
Rollet, Heather	PA	Orthopedics	0.1	$ 20,400	$ 600					$ 18.00
Lake, Connie	PA	Obstetrics	0.2	$ 22,950	$ 675					$ 27.00
Antoine, Gerry	PA	Adult Internal Med	0.5	$ 24,820	$ 730					$ 36.50
Kade, Michael	PA	Pediatrics	0.8	$ 29,410	$ 865					$ 51.90
Long, Shari	Physician	Adult Internal Med	1	$ 78,200	$ 2,300					$ 207.00
James, Don	Physician	Orthopedics	2	$ 71,910	$ 2,115					$ 190.35
Patel, Sanjev	Physician	Adult Internal Med	4	$ 91,800	$ 2,700					$ 216.00
Washington, Janet	Physician	Pediatrics	4	$ 62,050	$ 1,825					$ 127.75
Shin, Burt	Physician	Obstetrics	6	$ 104,550	$ 3,075					$ 317.00
Kumar, Denise	Physician	Obstetrics	7	$ 115,600	$ 3,400					$ 317.00
Xu, Weimin	Physician	Orthopedics	8	$ 118,150	$ 3,475					$ 317.00
Dixey, Bernard	Physician	Orthopedics	10	$ 137,190	$ 4,035					$ 317.00
Blane, John	Physician	Pediatrics	16	$ 147,050	$ 4,325					$ 317.00
Li, Wei	Staff	Group	0.7	$ 19,550	$ 575					$ 23.00
Blanch, Maurice	Staff	Adult Internal Med	1	$ 19,618	$ 577					$ 34.62
Wan, Stanley	Staff	Pediatrics	1	$ 29,410	$ 865					$ 43.25
Harlen, Lori	Staff	Group	2	$ 22,950	$ 675					$ 33.75
Hothe, Peggy	Staff	Obstetrics	4	$ 26,180	$ 770					$ 30.80
Michaels, Angela	Staff	Group	6	$ 36,040	$ 1,060					$ 31.80
Daniels, Max	Staff	Orthopedics	9	$ 39,270	$ 1,155					$ 69.30

Little Hills Medical Group Payroll Summary
Practice	Number of Employees	Average Years of Service	Total FICA	Federal Tax Withheld	Net Salary Payments	Retirement Benefit Payments
Pediatrics
Adult Internal Med
Obstetrics
Orthopedics
Group
Total