Our Services

Get 15% Discount on your First Order

Follow the instructional steps to finish the projectExp19_Excel_Ch02_ML1_Metropolitan_Zoo_Gift_Shop_Weekly_Payroll_Instructions.docx Grader –

April 10, 2024

Follow the instructional steps to finish the project

Exp19_Excel_Ch02_ML1_Metropolitan_Zoo_Gift_Shop_Weekly_Payroll_Instructions.docx

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.	0

Total Points

100

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

Pinckney_Exp19_Excel_Ch02_ML1_Payroll.xlsx

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

>Business & Finance homework help

Share This Post

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

Related Questions

see the Attached.1.2.3.image1.pngimage2.pngimage3.png

see the Attached. 1. 2. 3. image1.png image2.png image3.png

1. Due by the start of class. Write on readings from Weeks 5 and 6. The paper should be a FULL single-spaced page analysis of the

1. Due by the start of class. Write on readings from Weeks 5 and 6. The paper should be a FULL single-spaced page analysis of the readings. Reaction paper

DescriptionAssignment Part1 : Group Submission. This is a group project (2 or 3 students), the Group need to choose a company from the list below and

Description Assignment Part1 : Group Submission. This is a group project (2 or 3 students), the Group need to choose a company from the list below and make a study about the company for their virtual business processes. Each Group member needs to take their individual roles as per the

1. RBS Environmental Technologies Co., Ltd. Company Profile This document introduces RBS Environmental

1. RBS Environmental Technologies Co., Ltd. Company Profile This document introduces RBS Environmental Technologies Co., Ltd., focusing on the company’s efforts to provide environmentally sustainable solutions, particularly in the agricultural and organic waste sectors. The company is a leader in agricultural pollution control and organic resource recycling, offering innovative technologies

Write a report regarding the use of BIM or technology in construction to schedule, estimate, quantify, or improve quality on projects. Feel free to get

Write a report regarding the use of BIM or technology in construction to schedule, estimate, quantify, or improve quality on projects. Feel free to get creative on the subject matter. You may speak to current trends on the market, criticism from different parties, advantages to different platforms. Support your claims with

Write one page paper explaining how marketers are leveraging social media to engage customers. Include at least one supporting reference to back your

Write one page paper explaining how marketers are leveraging social media to engage customers. Include at least one supporting reference to back your opinions. Name, date, and title of assignment must appear in the footer.

Description‫المملكة العربية السعودية‬ ‫وزارة التعليم‬ ‫الجامعة السعودية اإللكترونية‬ Kingdom of Saudi Arabia Ministry of Education Saudi

Description ‫المملكة العربية السعودية‬ ‫وزارة التعليم‬ ‫الجامعة السعودية اإللكترونية‬ Kingdom of Saudi Arabia Ministry of Education Saudi Electronic University College of Administrative and Financial Sciences Assignment 1 Logistics Management (MGT 322) Due Date: 05/10/2024 @ 23:59 Course Name: Logistics Management Student’s Name: Course Code: MGT322 Student’s ID Number: Semester: First

Craft/write a written response to our assigned readings as if you are responding a parent or administrator. This will allow you to practice synthesizing

Craft/write a written response to our assigned readings as if you are responding a parent or administrator. This will allow you to practice synthesizing your learning and articulating it to a variety of audiences. Your original post will: Be around 250 -350 words is the “sweet spot” for length–enough to

Unit III Scholarly Activity Top of Form Bottom of Form Assignment Content Top of FormPurpose, Subject, Object, an

Unit III Scholarly Activity Top of Form Bottom of Form Assignment Content Top of Form Purpose, Subject, Object, and Ethics In this unit, you will determine the purpose, subject, object, and ethical considerations of your case study, then assemble your findings in the Unit III Case Study Outline Template .

attachedEnter group member names: Enter group number:Group Project Check-In: Research Summary PrepAs a group, complete

attached Enter group member names: Enter group number: Group Project Check-In: Research Summary Prep As a group, complete this weekly check-in form regarding group participation in the project. This will serve to keep you on track with the project and keep each other accountable for the work being completed. 1.

I need it in excel i have the answers ACT 500 – Module 06: Cost-Volume-Profit (CVP) Analysis

I need it in excel i have the answers ACT 500 – Module 06: Cost-Volume-Profit (CVP) Analysis Module 06: Discussion Submit as an excel document and show your work with formulas Fill-in-the-Blank Equations 1. __________ = Difference in Total Cost ÷ Difference in Units Produced Variable Cost per Unit

Description‫المملكة العربية السعودية‬ ‫وزارة التعليم‬ ‫الجامعة السعودية اإللكترونية‬ Kingdom of Saudi Arabia Ministry of Education Saudi

Description ‫المملكة العربية السعودية‬ ‫وزارة التعليم‬ ‫الجامعة السعودية اإللكترونية‬ Kingdom of Saudi Arabia Ministry of Education Saudi Electronic University College of Administrative and Financial Sciences Assignment One FIN402 (1st Term 2024-2025) Due Date: 28/09/2024 @ 23:59 Course Name: Financial Institutions Student’s Name: and Markets Course Code: FIN402 Student’s ID Number:

During this unit, we have focused on the financial costs of employee absenteeism and employee separations. From a financial perspective, these are the

During this unit, we have focused on the financial costs of employee absenteeism and employee separations. From a financial perspective, these are the financial burdens for the HR department and the entire organization. For this assignment, you will choose a company/industry that you are familiar with in order to calculate

Planning Organizational Assessment Based on Research Throughout this course, you will prepare assignments on an organization with which you are very

Planning Organizational Assessment Based on Research Throughout this course, you will prepare assignments on an organization with which you are very familiar. The assignments are designed to prepare you for the Unit VII Final Project in which you will write a culminating report. This assignment will consist of two parts.

DescriptionAction Items Access the discussion forum for this assignment by clicking on the discussion forum title.Click on “Create Thread.” Enter a

Description Action Items Access the discussion forum for this assignment by clicking on the discussion forum title. Click on “Create Thread.” Enter a title for your question in the “Subject” line. Type your response into the message field to the following discussion prompt: 1. Discuss three tier Client-Server DBMS architecture

How interlinked are sociology and criminology? The field of sociology studies life and any social changes that might impact a person’s life.

How interlinked are sociology and criminology? The field of sociology studies life and any social changes that might impact a person’s life. Criminology, as we know, is the scientific study of crime and everything that factors into it, including behavior. How do these fit together? Does one have to

Description The Assignment must be submitted on Blackboard (WORD format only) via allocated folder.  Assignments submitted through email will not

Description  The Assignment must be submitted on Blackboard (WORD format only) via allocated folder.  Assignments submitted through email will not be accepted.  Students are advised to make their work clear and well presented, marks may be reduced for poor presentation. This includes filling your information on the