### Our Services

Get 15% Discount on your First Order

Follow the instructional steps to finish the project

Grader – Instructions Excel 2019 Project

## 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. 0
 Total Points 100

Created On: 01/25/2022 1 Exp19_Excel_Ch02_ML1 – Metropolitan Zoo Gift Shop Weekly Payroll 1.6

## Payroll Data

 Metropolitan Zoo Gift Shop Department Name No. of Dependents Hourly Wage Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Federal Withholding Tax FICA Net Pay Abram 2 9.95 48 Acosta 1 9.55 48 Bordeaux 3 11.75 50 Higinbotham 1 11.75 35 Ianziti 1 10 40 Jaussi 1 9.55 44 Ling 3 15.75 40 Munoz 2 12.25 20 Riley 3 13.35 38 Sabey 2 10 45 Trujillo 1 9.95 15 Weston 2 11.75 41 Totals Assumptions Taxable Pay Tax Rate Summary Statistics # of Hours Gross Pay Net Pay Base Work Hours 40 \$0 15% Average Overtime rate 1.5 \$250 22% Highest FICA rate 7.65% \$300 25% Lowest Deduct per Depend \$ 50.00 \$450 28% \$525 31% Notes 1. The base workweek is 40 hours. Regular pay is based on hourly wage and hours worked up to 40. 2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate. 3. The gross pay is the total of the regular pay and overtime pay. 4. The taxable pay is the difference between the gross pay and the product of the number of dependents and deduction per dependent. 5. Federal withholding tax is calculated on the taxable pay based on the tax table. 6. FICA is calculated on the employee’s gross pay. 7. The net pay is based on the gross pay, federal withholding tax, and FICA.

## Exp19_Excel_Ch02_ML1_Payroll_final_result.jpg

Order a Similar Paper and get 15% Discount on your First Order

## Related Questions

### 1600 words MINIMUM (NOT including cover/reference pages or questions) Requirement is at least five scholarly articles, ONE maybe the course

1600 words MINIMUM (NOT including cover/reference pages or questions) Requirement is at least five scholarly articles, ONE maybe the course textbook BELOW. Textbook(s) Ferrell, O.C., & Hartline, M. D. (2017). Marketing strategy: Text and cases (7th ed.) SouthWestern/Cengage Learning. Unit 4 Complete 1. Water Works Plumbing Company is a small

### they’re two parts for this final.  Lehman College MSC 220 – Introduction to Public Relations Spring Semester 2024 Final Examination – 100 points

they’re two parts for this final.  Lehman College MSC 220 – Introduction to Public Relations Spring Semester 2024 Final Examination – 100 points Due via email Word attachment and Turnitin by 11:59 p.m., Friday, May 17. This final examination is open book, open notes, open Blackboard, open Internet. You may

### In the reading by Mahutga, the author discusses three basic predictions about how globalization might impact the power of labor globally. Using one sentence

In the reading by Mahutga, the author discusses three basic predictions about how globalization might impact the power of labor globally. Using one sentence per prediction, state what they are and briefly describe them. In the reading by Kay, the author argues that global governance institutions lead to labor internationalism

### Sustainable Tourism Part III 1. Based on your previous findings and analysis, you need to present your recommendations on actions that the destination’s

Sustainable Tourism Part III 1. Based on your previous findings and analysis, you need to present your recommendations on actions that the destination’s tourism developers, managers, and marketers should take to improve the destination’s sustainability at the social, cultural, economic, and environmental levels and on advancing the sustainability of its

### Attached Overview As you prepare to be a social work leader in the disaster and emergency relief field, you will first engage one of themost

Attached Overview As you prepare to be a social work leader in the disaster and emergency relief field, you will first engage one of themost recognized national disaster relief organizations, the American Red Cross.While social work curriculum and this DSWprogram provide you with the foundation and generic skills needed, youmust

### Culture, Engagement, and Results The article by Kantor & Streitfeld from the  New York Times in

Culture, Engagement, and Results The article by Kantor & Streitfeld from the  New York Times in this week’s readings discusses culture and leadership at Amazon. · Applying leadership concepts we have covered in this course, what is your perspective on how culture drove results at Amazon at the time this article

### Competency-Based Training by What is Competency Based Training? Competency-based training entails facilitating the development of specified,

Competency-Based Training by What is Competency Based Training? Competency-based training entails facilitating the development of specified, well thought, and applied competence. Vare et al. (2019) imply that competence means that one has the needed skills and capabilities to complete a task in accordance with a set design to achieve a

### nursing Capstone Foundation V Phase E Evaluation 1.Describe specifically how you would evaluate the outcome of your Capstone project, if it were

nursing Capstone Foundation V Phase E Evaluation 1.Describe specifically how you would evaluate the outcome of your Capstone project, if it were to be implemented. To do this, refer to the objectives (personal as well as project goals) you developed as part of the proposal. Evaluations must be clear and

### Computer Specifications Instructions: In a Word document, provide a response to the following: Describe the main

Computer Specifications Instructions: In a Word document, provide a response to the following: Describe the main components of a computer. What is their purpose?  What are the specifications of your computer? Give as much information as possible. At a minimum, you should identify the CPU type and speed, RAM amount

### Write a minimum of 2-3 paragraphs for each discussions with their references. Week 1 Discussion 1 This week, you will be submitting a research topic and

Write a minimum of 2-3 paragraphs for each discussions with their references. Week 1 Discussion 1 This week, you will be submitting a research topic and problem statement for use in your research proposal to your instructor for approval. Share your proposed research topic and problem statement with your classmates.

### Approved Topics for Journal Article Review 1.      What are the major risks in ambulatory care? Provide examples.  Must be at least 8 pages, excluding

Approved Topics for Journal Article Review 1.      What are the major risks in ambulatory care? Provide examples.  Must be at least 8 pages, excluding works cited (This is based on typed word, not tables or charts.) Include page numbers Be double spaced Include all section headings Use a font no greater

### ATTACHED Chapter 1: What If Scenario Instructions: Please answer the following two questions base on how you would

ATTACHED Chapter 1: What If Scenario Instructions: Please answer the following two questions base on how you would handle a certain situation or circumstance. Please make sure that your response is 150 words . 1. You are the mayor, and your city is suffering from severe economic hardship due to a decrease in the

### Complete the Excel Sheet based on the instruction attachment Whenever you have the word “net” in front of something, think of it as what is left

Complete the Excel Sheet based on the instruction attachment Whenever you have the word “net” in front of something, think of it as what is left over after deducting expenses from revenue or deducting depreciation from equipment/property. For Excess of revenues over expenses, subtract total expenses from total revenue. For

Post Your Introduction [WLO: 4] [CLO: 3] Introduce yourself to your fellow classmates. Your introduction should include the following: Information about yourself (What state you live in, your hobbies,

### Professional Development Paper Complete a professional development paper in APA style with 5-8 pages. Answer the following in the paper: · How will you

Professional Development Paper Complete a professional development paper in APA style with 5-8 pages. Answer the following in the paper: · How will you develop yourself as a professional in the field of counseling? · What are your values? · How may they interfere with or promote our own ethical

### Please see attachment a) details about the ethical considerations in embarking upon a qualitative research project;  b)  proposes a qualitative

Please see attachment a) details about the ethical considerations in embarking upon a qualitative research project;  b)  proposes a qualitative project via two well-written research questions  (RQ) and 2-3 semi-structured interview questions for each (RQ); and  c)  identifies the target populations, as well as any ethical challenges  you might expect

### 0verview In this module, you will turn in the first part of your paper. The first part of your paper includes: Introduction, Research Question/Topic and

0verview In this module, you will turn in the first part of your paper. The first part of your paper includes: Introduction, Research Question/Topic and purpose, methodology, and your literature revie

### 1. Centering cost of living increase as one major pressure on consumers how do you relate premiumization with this pressure into buying behavior for

1. Centering cost of living increase as one major pressure on consumers how do you relate premiumization with this pressure into buying behavior for FMCG? Which strategies would help companies in quality, differentiation and pricing 2. What are the components of Retailer (Customer) Economic Model? Please explain how they are