Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Excel

11/10/2020 Client: sarah Deadline: 3 days

 


Problem


Medical groups are common in the health care industry and range in size. The defining trait of a medical group is that there is more than one doctor on 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. In this assignment, you need to summarize payroll details for employees working in a medical group.


Instructions: Perform the following tasks. Data file shows the basic information you will need for this project.



  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 4.2% of an employee’s weekly pay for Social Security. However, an employee is only 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 4.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 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 401(k) retirement contributions. Calculate the company’s 401(k) 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 3 or more years will receive an additional contribution to their retirement account. Calculate this benefit by entering an IF function in cell M3 on the Payroll Details worksheet. If an employee is a physician and has been working in the medical group for 3 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 3 or more years, the function should multiply the Gross Pay This Week by 5%. Copy and paste the function into the range M4:M22.

  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. Enter a COUNTIF function into cell B3 on the Payroll Summaryworksheet. The function should count the number of employees in the range A3:A22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range B4:B7.

  11. Enter an AVERAGEIF function into cell C3 on the Payroll Summaryworksheet. The function should calculate the average Years of Service in the range D3:D22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range C4:C7.

  12. Enter a SUMIF function into cell D3 on the Payroll Summaryworksheet. The function should sum the Total FICA tax in the range I3:I22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. However, the government requires that employers match the FICA tax that is withheld from employees’ paychecks. Therefore, multiply the result of this SUMIF function by 2. Copy and paste the function into the range D4:D7.

  13. Enter a SUMIF function into cell E3 on the Payroll Summaryworksheet. The function should sum the Federal Tax withholdings in the range J3:J22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range E4:E7.

  14. Enter a SUMIF function into cell F3 on the Payroll Summaryworksheet. The function should sum the Net Payments in the range O3:O22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range F4:F7.

  15. Enter a SUMIF function into cell G3 on the Payroll Summaryworksheet. The function should sum the Total Retirement Benefits in the range N3:N22 on the Payroll Details worksheet for each Practice listed in Column A on the Payroll Summary worksheet. Copy and paste the function into the range G4:G7.

  16. Enter a SUM function in cell B8 on the Payroll Summary worksheet that sums the values in the range B3:B7. Copy and paste this SUM function into the range D8:G8. Then format the range D8:G8 to the Accounting number format.

  17. Save the workbook and submit the completed Excel file.

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Isabella K.
Assignments Hut
Instant Assignments
Calculation Guru
Helping Hand
Solution Provider
Writer Writer Name Offer Chat
Isabella K.

ONLINE

Isabella K.

Hi there! I saw you have posted a project, which is according to my skills. I am a native level English speaking writer. I came across your post and found that my expertise is the best fit for this job. I have solid experience writing high quality, engaging, and SEO optimized content for business sites. I know Chinese but I am fluent in writing in Chinese. So, I would like to work with you to provide the highest quality of work. So, please send me a message so that we can discuss it more.

$55 Chat With Writer
Assignments Hut

ONLINE

Assignments Hut

I am 100% sure that I will meet your expectations in writing the essay . I have written over 4000 high quality plagiarism free papers and deliver within stipulated deadlines. I am familiar with a number of citation styles among them MLA, APA, APSA, Oxford, Chicago, OSCOLA, Harvard and Turabian. I believe that I am the right candidate for this project, and I guarantee quality work. I have a wide knowledge in this subject and I believe this will come a long way in giving you quality work. Thank you in advance

$70 Chat With Writer
Instant Assignments

ONLINE

Instant Assignments

Good day dear client, I am a full-time freelance writer with years of experience. My strongest strengths are strong dedication to duty, reliability, quality and excellence. I am going to do an outstanding job for you, kindly PM me and let us commence. Thank you so much in anticipation of your positive response. Thank you very much.

$70 Chat With Writer
Calculation Guru

ONLINE

Calculation Guru

I see that your standard of work is to get content for articles. Well, you are in the right place because I am a professional content writer holding a PhD. in English, as well as having immense experience in writing articles for a vast variety of niches and category such as newest trends, health issues, entertainment, technology, etc and I will make sure your article has all the key pointers and relevant information, Pros, Cons and basically all the information that a perfect article needs with good research. Your article is guaranteed to be appealing, attractive, engaging, original and passed through Copyscape for the audience so once they start reading they keep asking for more and stay interested.

$70 Chat With Writer
Helping Hand

ONLINE

Helping Hand

Hello, I an ranked top 10 freelancers in academic and contents writing. I can write and updated your personal statement with great quality and free of plagiarism as I am a master writer with 5 years experience in similar ps and research writing projects. Kindly send me more information about your project. You can award me any time as I am ready to start your project curiously. Waiting for your positive response. Thank you!

$70 Chat With Writer
Solution Provider

ONLINE

Solution Provider

Hi.I have read your descriptions and i will provide you quality work.I am a Ph.D. writer with 5-6 years of experience. I have worked on several similar projects and can deliver quality works in a tight deadline. I am interested to hear more about the project and about the subject. If you award me the project I would be very glad to discuss this further and get started for you as soon as possible. Thanks You!

$70 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Network security - Types of reimbursement models in healthcare - What is a nursing practice problem - The urban areas native pass act 1909 - Homeopathic remedies for pinched nerve in lower back - Earthquake homework help - C++ basic - 350 words - Hcos mark sheet - Aginares a la polita - Looking for richard documentary - How do you divide scientific notation - Cell theory 3 parts - Marketplace live simulation tips quarter 2 - Yahoo messenger server ip address - Rite choice property management - Phonics screening test 2014 - Mcconnell brue flynn economics answers - Interpersonal communication chapter 5 language - Pancho's title loans sunland park - Http www calstatela edu library guides 3asa pdf - The johns hopkins nursing evidence based practice rating scale - Alert and oriented x4 questions - Potential rating scale appraisal problems - Week 3 Discussion - Choosing a career path worksheet - O level english oral topics 2018 - Decision making under certainty uncertainty and risk - Understand how the Brain copes from adverse Childhood Experience - Chitling test answers - Data Sharing and Bioinformatics - Thesis statement about social media - Framing paper religious education in australian catholic schools - Belfast met graphic design - Approaches to Addressing Issues - ( C/C++ ) Design and implement a Disassembler for the XE variant of the SIC/XE machines ( C/C++ ) - Artifact of human creative expression examples - Joy luck club food - Vending machine program in python - Business Intelligence_ week 7 - Us v carroll towing - Laural home smoky x ray of eucalyptus leaves shower curtain - Anthem chapter 1 pdf - Bases of market segmentation for toothpaste - Grow management consultants - The redstockings manifesto - Predictive Methods - How does a franchise work ppt - Euler method, SEIR model - Mason gmu edu montecin web eval sites htm - Topas aix cpu usage - +91-8306951337 kala jadu specialist astrologer IN Davanagere - Adscititious in a sentence - Hcf health insurance packages - Culturally encapsulated counselors would be most likely to - Case 8 data analytics of boutique hotel reservations - Prior service cost journal entry - Sink or swim case study - Rewrite 10 1 3 as an improper fraction - Plant and animal cells worksheet answer key - Cleaning supervisor job duties - Margaret atwood scarlet ibis - Library administrator job description - Home automation using nodemcu and google assistant - Global lunar crescent visibility maps - How to get an isbn number uk - Access 2016 module 1 sam project 1a - One flew over the cuckoo's nest chapter 25 summary - The first cross cultural missionary team was - How to Get Assignment Help - Singapore business culture and practices - What is a provider sponsored organization course hero - Chapter 36 patient interview and history - League of 3 emperors - Christian Heritage Exam 1 - List of aircraft by radar cross section - When introducing new fruits and vegetables in preschool - Discussion - Apple consumer behaviour case study - Articulate the PR-Indian responses - Los padres de mariela boletos para el partido de futbol - Bim employers information requirements - Goldfish vs cheez its - Tan of a complex number - Cooling curve lab report - Hoppers crossing radiology 267 heaths road - Fiddes payne cajun seasoning - Vincent brody documentation - Difference between multiprogramming and multitasking operating system in points - Accountant client termination letter template - Ford motor annual report 2018 - Forecasting excel assignment - Signature Assignment: Strategic Plan - Implementation Plan, Strategic Controls, and Contingency Plan Analysis - Ali macgraw sex addiction - 350 words - Unit VII Assignment - Clinical Supervision - Summarizing and Analyzing Sources - Netiquette questions and answers - Week 8 discussion