**For Payroll**

The firm of Scrooge & Marley offers accounting services and money lending. Employees are paid weekly. All employees receive time and a half for all hours worked over 40. Payroll Period ending December 25, 2021

Employee	Filing Status	Beginning Cumulative	Hourly Rate	Total Clock Hours	Saving 5.25%	Medical Insurance	Charitable Contribution
John Smith	S	$138,375.38	$58.00	45
J. Marley	S	$136,880.18	$47.50	42
M.Fezziwig	M	$134,128.67	$15.25	40
M.Crumb	S	$78,104.14	14.10	46
B.Cratchit	M	$110,075.38	$12.75	44.50
T.Tim	S	$48,378.14	$14.75	37

Assume that the ceiling for Social Security - OASDI is $137,700.

The following rates are in effect:

FICA OASDI 6.2% of taxable Social Security wages

FICA HI 1.45% of taxable Medicare wages

Federal withholding 12.5% of gross wages

State withholding 4.35% of gross wages

Medical Insurance $40.00 for Married employees

$25.00 for Unmarried employees

Charitable Donation Applies only when employee gross pay for the current period is greater than $5,000. The contribution amount is $10.00 per employee whose gross pay for the pay period is greater than$5,000.

Pension, 401K Contribution 8% of gross wages. Applies only if gross wages are greater than $4,000

Required:

1Prepare a payroll register using Excel for December 25 pay period. Use the excel template provided. You may need to modify this template as needed.

2. Use an IF function for the following calculations:

a. Overtime hours

b. Social Security and Medicare Withholding

c. Medical Insurance

d. Charitable Donation

e. Pension Contribution 401(k)

3. Use the ROUND function when calculating the following:

a. Overtime Rate

b. All pay deduction

Thank you