Employee Name	Hourly or Salary	Filing Status	Number of Allowances for Federal and State Withholding.	Pay rate/hour	Hourly worked For the first payroll period	Hourly worked For the second payroll period	Over time	Vacation hours
Applie, Suzie	Hourly	Single	0	15	50	45	10	3
Banana, Tim	Hourly	Married	1	25	70	60		3
Catalope, Cheri	Salary	Married	3	20	60	70		3
Dill, Sadie	Salary	Head of Household	3	35	70	65		3
Eggplant, Bill	Hourly	Single	0	13	55	45	20	3
Fort, Kyle	Hourly	Married	0	15	45	55		3
Gould, Matt	Salary	Married	4	30	60	65		3
Hamm, Mason	Hourly	Single	1	15	65	56		3
Igloo, Alice	Hourly	Married	2	15	56	70	25	3
Jasper, Alex	Hourly	Single	2	25	55	45		3

Employee Name	Healthcare,	union	Dental	Vision	Dependednt Education Fund	Accident Insuranc	401K
Applie, Suzie	0	0	0	0	0	0	0
Banana, Tim	$10	0	$5	$5	0	0	$20
Catalope, Cheri	0	$5	10	6	0	0	12
Dill, Sadie	15	0	5	7	5	7	25
Eggplant, Bill	0		0	0	0	0	0
Fort, Kyle	0	0	0	0	0	0	15
Gould, Matt	20	0	9	8	0	0	15
Hamm, Mason	10	0	0	0	0	0	0
Igloo, Alice	0	0	0	0	0	0	15
Jasper, Alex	12	0	5	8	0	0	18

• All of the employees are paid on a weekly basis on Friday.
• This project will focus on payroll for the dates ending 01/15/20 and 01/24/2020.
• All employees earn 3 hours of vacation each pay period.
• For all voluntary benefits, there is a 50/50 cost sharing agreement.
• Please include calculations for Social Security and Medicare regardless if these are put on hold because of the Corona Virus.

• Students are to open an Excel spreadsheet and create two tabs at the bottom one for each payroll period.

  2. Students are to create columns for the employee name, filing status, pay rate, gross pay, mandatory voluntary deductions, vacation, and gross pay. Assume that the employees have no cumulative earnings prior to these payroll periods.

  4. Columns must also be created for hours worked, overtime, and Net Pay.

  6. Students must include the mandatory withholdings. Use 6.2 percent for Social Security and

  1.45 percent for Medicare for each employee.

  7. For FUTA, use .08 of the first $7,000 of each employee. For SUTA, use 5.4% of the first $8,500 of each employee.

  8. In calculating totals in the worksheet, students must use Excel formulas including SUM, SUMIF, SUMIFS, and Subtraction. A formula should also be included to tie information between two worksheets.

  9. Students must also include conditional formatting with at least two rules applied.

  10. Students should use the formatting features of Excel to give the worksheet a professional appearance.

  11. Following completion of the two worksheets, students should prepare two-word documents showing general journal entries for the employee and employer side, payment of wages, accrual of vacation time, and payment of tax liabilities.