Ch 6 Lab 1 Instructions
docx
keyboard_arrow_up
School
Delaware Technical Community College *
*We aren’t endorsed by this school
Course
MISC
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
3
Uploaded by DukeKouprey3794
Payroll Taxes for a Medical Group
Starter File Name: Ch 6 Lab 1 File
The Excel skills demonstrated in this chapter for the purpose of conducting
exception analytics can also be used to calculate payroll taxes and benefits for
the employees of a medical group. Medical groups are common in the health
care industry and can range in a variety of sizes. The defining trait of a medical
group is that more than one doctor is on the staff for a particular practice,
which creates more flexible hours for patients and physicians. In addition, a
medical group can provide a variety of medical services in one location. The
purpose of this exercise is to use the Excel and exception analytics skills to
prepare a payroll tax and benefits worksheet.
1.
Set the Freeze Panes command on the Payroll Details worksheet so that
rows 1 and 2 and columns A and B are locked in place while scrolling
through the worksheet.
2.
Enter an IF function in cell G3 on the Payroll Details worksheet to
calculate the Social Security tax. Social Security funds are used by the
government to provide income for people who are retired, a beneficiary
of a retiree, or disabled. An employer must withhold 6.2% of an
employee’s weekly pay for Social Security. However, an employee’s
salary is taxed up to $100,000. The logical test of the IF function should
assess if the value in the Pay Year to Date column is greater than or
equal to 100000. If the logical test is true, the output of the function
should be zero. Otherwise, the function should multiply the value in the
Gross Pay This Week by 6.2%. Copy and paste the function into the range
G4:G22.
3.
Enter a formula in cell H3 on the Payroll Details worksheet that
calculates the Medicare Tax. Medicare funds are used by the government
to provide medical financial support to senior citizens. Your formula
Adopted from Joseph M. Manzo. (2019).
Data Analytics Using Microsoft® Excel®: With
Accounting and Finance Datasets
. Boston, MA: FlatWorld
Chapter 6 DA Exercise 2
Payroll Taxes for a Medical Group
should multiply the Gross Pay This Week by 1.45%. Copy and paste this
formula into the range H4:H22.
4.
Enter a formula in cell I3 on the Payroll Details worksheet that calculates
the total FICA tax (FICA stands for Federal Insurance Contributions Act).
Your formula should add the Social Security Tax to the Medicare Tax.
Copy and paste this formula into the range I4:I22.
5.
Enter an IF function in cell J3 on the Payroll Details worksheet to
calculate the Federal Tax. If the Gross Pay This Week is less than or equal
to 1150, then the tax is calculated by multiplying the Gross Pay This
Week by 20%. Otherwise, this tax is calculated by multiplying the Gross
Pay This Week by 25%. Copy and paste the IF function into the range
J4:J22.
6.
If an employee has been working with the medical group for 1 or more
years, the company will match 50% of the employee’s 401K retirement
contributions. Calculate the company’s 401K retirement contributions by
entering an IF function in cell L3 on the Payroll Details worksheet. If the
Years of Service is greater than or equal to 1, multiply the 401K
Retirement value by 50%. Otherwise, the output of the function should
be zero. Copy and paste the IF function into the range L4:L22.
7.
The medical group offers its employees an additional retirement benefit
based on the employee’s position and years of service. Employees who
are with the practice for three or more years will receive an additional
contribution to their retirement account. Calculate this benefit by
entering an IFS function in cell M3 on the Payroll Details worksheet. If
an employee is a physician and has been working in the medical group
for five or more years, the function should multiply the Gross Pay This
Week by 8%. For all other employees who have been working at the
medical group for three or more years, the function should multiply the
Gross Pay This Week by 5%. Copy and paste the function into the range
M4:M22.
Adopted from Joseph M. Manzo. (2019).
Data Analytics Using Microsoft® Excel®: With
Accounting and Finance Datasets
. Boston, MA: FlatWorld
Chapter 6 DA Exercise 2
Payroll Taxes for a Medical Group
8.
Enter a formula into cell N3 on the Payroll Details worksheet that
calculates the Total Retirement Benefits. Your formula should add the
401K Company Match value to the Retirement Benefit value. Copy and
paste the formula into the range N4:N22.
9.
Enter a formula into cell O3 on the Payroll Details worksheet that
calculates the Net Payment for each employee. Your formula should
subtract the values in the range I3:K3 from the Gross Pay This Week in
cell F3. The range I3:K3 includes the following: Total FICA (cell I3),
Federal Tax (cell J3), and 401K Retirement (cell K3). Copy and paste this
formula into the range O4:O22.
10.
Save and close your file.
Adopted from Joseph M. Manzo. (2019).
Data Analytics Using Microsoft® Excel®: With
Accounting and Finance Datasets
. Boston, MA: FlatWorld
Chapter 6 DA Exercise 2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help