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

How to calculate federal withholding tax in excel with vlookup

22/10/2021 Client: muhammad11 Deadline: 2 Day

Excel Assignment

Grader - Instructions Excel 2019 Project

Exp19_Excel_Ch02_ML1_Metropolitan_Zoo_Gift_Shop_Weekly_Payroll
Project Description:
As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to include total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas.

Steps to Perform:
Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Exp19_Excel_Ch02_ML1_Payroll.xlsx. Grader has automatically added your last name to the beginning of the filename.

0

2

Use IF functions to calculate the regular pay and overtime pay based on a regular 40-hour workweek in cells E5 and F5. Pay overtime only for overtime hours. In cell G5, calculate the gross pay based on the regular and overtime pay. Abram’s regular pay is $398. With 8 overtime hours, Abram’s overtime pay is $119.40.

20

3

Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents (column B) by the deduction per dependent (B24) and subtract that from the gross pay. With two dependents, Abram’s taxable pay is $417.40.

10

4

Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. Use the tax rates from the range D21:E25. The VLOOKUP function returns the applicable tax rate, which you must then multiply by the taxable pay.

20

5

Calculate FICA in cell J5 based on gross pay and the FICA rate (cell B23), and calculate the net pay in cell K5. Copy all formulas down their respective columns to row 16.

10

6

With the range E5:K16 selected, use Quick Analysis tools to calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay on row 17. Note, Mac users, with the range selected, on the Home tab, in the Editing group, click AutoSum.

10

7

Apply Accounting Number Format to the range C5:C16. Apply Accounting Number Format to the first row of monetary data and to the total row. Apply the Comma style to the monetary values for the other employees. Apply the Total cell styles format to the range E17:K17.

10

8

Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (the range I21:K23) of the worksheet. Format the # of hours calculations as Number format with one decimal and the remaining calculations with Accounting number format.

15

9

Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of the worksheet.

5

10

Save and close the workbook. Submit the file as directed.

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:

Premium Solutions
Top Class Engineers
Homework Guru
Accounting Homework Help
Helping Hand
Exam Attempter
Writer Writer Name Offer Chat
Premium Solutions

ONLINE

Premium Solutions

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$24 Chat With Writer
Top Class Engineers

ONLINE

Top Class Engineers

As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget.

$34 Chat With Writer
Homework Guru

ONLINE

Homework Guru

Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading.

$26 Chat With Writer
Accounting Homework Help

ONLINE

Accounting Homework Help

As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients.

$32 Chat With Writer
Helping Hand

ONLINE

Helping Hand

I have read your project description carefully and you will get plagiarism free writing according to your requirements. Thank You

$46 Chat With Writer
Exam Attempter

ONLINE

Exam Attempter

I am a professional and experienced writer and I have written research reports, proposals, essays, thesis and dissertations on a variety of topics.

$48 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

If ssw = 126, n = 28, k = 4, then what is the value of sw2 - Level 14 501 swanston street - Lesson 4.7 flowchart thinking answers - What are the 7 principles of teaching - How much is 40 euros in us dollars - Harris company manufactures and sells a single product - Psychology ethical decision making model - The devil and tom walker reading skill interpret imagery answers - Bradfords building supplies plympton - Comprehensive women's health soap note - Btu lb f to kj kg k - Belfast met english courses - 4-6 page research paper on a myth - How do i register my pool in nsw - Hltaid003 provide first aid answers - Linux mv overwrite without asking - V for vendetta evey - What does radley balko claim in this essay - Test for fe3+ ions - Groundwater and wells johnson screens pdf - Target behavior in observable and measurable terms - Walmart supply chain management case study - Help with post - Slope of budget line calculator - Cellular king fairfield heights - Analysis of commercial vinegar lab report - Vce business management study design - Endeavour fresh cut citrus ale - Merit goods meaning in economics - Module 2.3 quiz answers drug and alcohol drive safely - Family Assessment Part II - Human resources managment legal ethical. deliverable 04 - Draw the monomer for the following polymer - Case study supply and demand - If you're happy and you know it jack hartmann - Vaccine preventable diseases evidence certification form - Extra crispy bp bonnyrigg - Tu lo sai baritone - 115 brysons road warrandyte south vic 3134 - Health communications assignment - Red veined sorrel wiki - Looking out looking in chapter 9 pdf - Discussion - Ari fleet phone number - Managing Criminal Justice Organizations - Basic Computer - Conspiracy Theory - Using prenumbered checks and having an approved invoice - The strange case of beriberi answers - Empiricist theory of francis bacon - Psycology - Hu2000 week 5 assignment worksheet - Rokeach value survey example - Private insurer reimbursement rates for psychiatrists - Ipl2 literary criticism - Healthcare reimbursement - Ethical issues uber - NURS-6050N-66/NURS-6050C-66-Policy & Advocacy - Cafs year 11 syllabus - Characteristics of adulthood ppt - 13 sentinels juro cafeteria - Which way does the wind usually blow - Southworth company uses a job order costing system - Chain driven limited slip differential - Cloud computing security and integrity - Ieee two column template - Rebekah age when she married isaac - Mc escher floor tiles - International causes of the great depression - Dbms book by navathe pdf - Acting character development worksheet - Betterup whole peron model - Hbr the core competence of the corporation - An intensive distribution strategy serves well for - Italian oral exam topics - Faraday's iron ring experiment - Mintzberg managerial roles ppt - Tuesdays with morrie table of contents - #{91 =9876751387}{ Love marriage specialist baba ji in Faridabad - Why were books banned in fahrenheit 451 according to beatty - Michael daffey net worth - Bsn nursing capstone project examples - 40 cents aud to usd - Nurs 6650 midterm exam questions and answers - Cruzan v. Director, Missouri Department of Health, 497 U.S. 261 (1990) - Clearwater fine foods inc - Lcm of 24 and 30 - Interrelationships Reflection - 23 essex street chambers - How many public baths were there in ancient rome - Endeavour research fellowship australia - Org Behavior Reflection, Discussion and Assignment - Bad tv reception at night - Disadvantages of segmentation in os - A juggler throws a ball into the air - States of matter virtual lab glencoe - Bus.com 4.2 - Dissemination of evidence based practice in nursing - Final Draft: Expository Essay - Annual report ratio analysis assignment