Excel Lab $1$: MODIFIED: FALL SEMESTER $2023$

TAB "EMPLOYEES"

$1.$ Clean up the Employee Name in tab "EMPLOYEES" to be incompliance with the format "Doe, John"

$2.$ Break the Employee Name into First Name $($John$)$ and Last Name $($Doe$)--$ please note that no middle names or initials should be included in either column

$3.$ Create employee email address with format: FirstName.LastName@xyz$.$com

$4.$ Highlight employees having hour rate more than $$60$

TAB "PAYROLL"

$5.$ Calculate Gross Earnings

$6.$ Calculate Deduction amount $($Deduction amount $=$ HCSP $+$ Retirement $+401($k$)+$ Health Insurance $+$ Dental Insurance $+$ Parking$).$ Remember that employees might not have all of those deduction types.

$7.$ Calculate Withholding amount. $($Withholding amount $=$ State WH $+$ Federal WH $+$ Social Security Tax $+$ Medicare tax$)$

Please put totals in for the columns in requirements $5-7($use of the SUM function is recommended$).$ Your totals can appear at the top or bottom of the columns and should match the check figures provided.

Failure to include totals will result in the loss of points in accordance with the grading rubric.

Deduction Rate

Health Care Saving Plan $($HCSP$)2\%$ of Gross amount

Retirement $5\%$ of Gross amount

Health Insurance $($F $-$ Family$)$ $$80$ per pay period

Health Insurance $($S $-$ Single$)$ $$10$ per pay period

Dental Insurance $($F $-$ Family$)$ $$25$ per pay period

Dental Insurance $($S $-$ Single$)$ $$5$ per pay period

Parking $$35$ per pay period

Tax Withholding Rate

State $10\%$ of $($Gross $-$ Deduction$)$

Federal $20\%$ of $($Gross $-$ Deduction$)$

Social Security Tax $4\mathrm{.}20\%$ of $($Gross $-$ HCSP $-$ Health Insurance $-$ Dental Insurance$)$

Medicare Tax $1\mathrm{.}45\%$ of $($Gross $-$ HCSP $-$ Health Insurance $-$ Dental Insurance$)$