80.2K

Verified Solution

Link Copied!

can you help me please

open an Excel spreadsheet and create two tabs at the bottom one for each payroll period. 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. created for hours worked, overtime, and Net Pay.

use Excel formulas including SUM, SUMIF, SUMIFS, and Subtraction. A formula should also be included to tie information between two worksheets.

.include conditional formatting with at least two rules applied. and use the formatting features of Excel to give the worksheet a professional appearance

information

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

1.45 percent for Medicare for each employee. 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.All of 2- the employees are paid on a weekly basis on Friday.

3-This project will focus on payroll for the dates ending 01/15/20 and 01/24/2020.

4- For all voluntary benefits, there is a 50/50 cost sharing agreement.

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 hours	earn hours of vacation each pay period
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

Answer & Explanation Solved by verified expert

See Answer