Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Excel Lab 1 : MODIFIED: FALL SEMESTER 2 0 2 3 TAB EMPLOYEES 1 . Clean up the Employee Name in tab EMPLOYEES to be

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.20% of (Gross - HCSP - Health Insurance - Dental Insurance)
Medicare Tax 1.45% of (Gross - HCSP - Health Insurance - Dental Insurance)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting An Introduction

Authors: Eddie McLaney, Peter Atrill

2nd Edition

0273655507, 978-0273655503

More Books

Students also viewed these Accounting questions