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

Waverley bc refuse collection - How does fiber absorbency affect the properties of a textile - 600-900 APA style paper Healthcare management - How to name branched alkanes - Tandberg 1700 mxp factory reset - 8732 rainbow ave kings beach ca 96143 - Big hairy audacious goal worksheet - Cybersecurity RACI Chart - Audit evidence is usually considered sufficient when - Clinical Field Experience C - Cracking the code of life video questions worksheet answers - Manage knowledge and information bsbinm601a - The key study guide - Nick sharratt drawing tips - Agar diffusion lab answers - 25545 inkster farmington hills mi - Arbonne fizz sticks side effects - Article on impact of watching tv on students - Aami flexi premium excess - Cypress college course catalog - Afs wall installation guide - Security analysis - Jim turin and sons - Martin luther king letter from birmingham jail essay - Personality inventories are designed to assess several ________ at once - Advocating for the counseling profession paper - Mgt 330 week 2 discussion 1 - Fly me to the moon jerry nowak - Christ be our light easter vigil - 2006 ap calculus ab free response - Free bulky waste collection derby - AVD6 - Beach a river of sand - Schoolrack com sign up handout - Gordon rule essay example - Poli 330 final exam quizlet - Graduate self service cambridge - Escience lab 2 chemistry of life answers - Vern stand by me - Owasp broken web application download - How do the aacn essentials apply to nursing practice - Ponderosa racing pigeons stud - Aace class 4 estimate - How adverse selection might impact the market for unemployment insurance - How to find the range of a reciprocal function - How much is 1.5 kg in grams - What is a small scale research project - Dynatussin for dry cough - Information Technology Importance in Strategic Planning - Flinders download microsoft office - How to linear programming in excel - Dada art movement history - Tesla diversification strategy - Negative backpressure egr valve - Chest pain nsw health - Situational leadership theory pros and cons - How do vitamins work ted ed - Ambulance stretcher dimensions australia - Music man wiring diagram - Goodman and snyder 2013 - 1 2-dichlorobutane structural formula - Electrical safety regulation bc - Find highest and lowest of five integers using java loops - How to win friends and influence people questions - Order 2420519: Maximize Instruction through PD - Netbeans visual paradigm plugin - Six sigma project examples manufacturing ppt - Bma australia trading docklands - Hunger games background information - Site safe task analysis - Swot analysis for nursing shortage - An inconvenient truth study guide - Mcmillan's analysis of restroom spacing etiquette exemplifies the communication concept of - Ivy lee four important contributions to public relations - Quarry brae primary school - Two paragraphs - Which inequality is true for x 5 - Consider a process consisting of three resources - El hombre les muestra su hotel - Caterpillar organizational structure - A maternity line for American Apparel - Ps2100 week 4 assignment worksheet - The herfindahl index for a pure monopolist is - OE-paper16 - Acid and metal reactions - ?same-day +27833173182 Hluti - Shiselweni ABORTION CLINIC // PILLS,,,, - U verse channel lookup - Saber tooth curriculum analysis pdf - Four traits of organizational learning - Joel best social problems 3rd edition ebook - Econ 304 psu - 2-3 page . criminal case study and analysis - Donate bras uk freepost - Chemical equilibrium and le chatelier's principle lab answers - 01.14 beginning your narrative - QA - Assignment - Canadian Human Resource Management - Q3 - Social media's small positive role in human relationships summary