Our Services

Get 15% Discount on your First Order

Follow the instructional steps to finish the project Exp19_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

Description College of Computing and Informatics Assignment 1 Deadline: Day 02/10/2024 @ 23:59 [Total Mark for this Assignment is 8] Student

Description College of Computing and Informatics Assignment 1 Deadline: Day 02/10/2024 @ 23:59 [Total Mark for this Assignment is 8] Student Details: Name: ### ID: ### CRN: ### Instructions: • You must submit two separate copies (one Word file and one PDF file) using the Assignment Template on Blackboard via

Cover the “The Use of Clinical Systems to Improve Outcomes and Efficiencies” New technology—and the application of existing technology—only appears

Cover the “The Use of Clinical Systems to Improve Outcomes and Efficiencies” New technology—and the application of existing technology—only appears in healthcare settings after careful and significant research. The stakes are high, and new clinical systems need to offer evidence of positive impact on outcomes or efficiencies. Nurse informaticists

Description Dis1. 2 file Accounting discussion : Financial accounting and managerial accounting are subdivisions of accounting and play an essential

Description Dis1. 2 file Accounting discussion : Financial accounting and managerial accounting are subdivisions of accounting and play an essential role within an organization. Discuss the major differences between managerial and financial accounting then apply your understanding to the following 4 scenarios. Discuss if these are managerial accounting or financial

Marketing for good or for ill? Klein (2000), Sidibe (2020) and Levitt (see Marketing Myopia video) provide different perspectives on the role of marketing

Marketing for good or for ill? Klein (2000), Sidibe (2020) and Levitt (see Marketing Myopia video) provide different perspectives on the role of marketing and branding. Using the critical thinking techniques learned in this course, compare their views and use your analysis to support your own ideas about the marketing

Topic: Child and Adolescent Psychology Part 1: Reflection: Description of OccupationThis reflection should be 2 to 3 pages and include the

Topic: Child and Adolescent Psychology Part 1: Reflection: Description of OccupationThis reflection should be 2 to 3 pages and include the following: Description of Occupation Why this occupation is important to you An accurate description of this occupation including the tasks a person in this occupation is required to perform A list

Discourse Community Outline please do not use chatgpt or AI and write a basic English like sample Purpose of the Assignment: To begin to generate content for

Discourse Community Outline please do not use chatgpt or AI and write a basic English like sample Purpose of the Assignment: To begin to generate content for your discourse community paper. Intended

Disclaimer: As you read about this theory, you might experience uncomfortable feelings. Remember that is normal and that if you need help dealing with

Disclaimer: As you read about this theory, you might experience uncomfortable feelings. Remember that is normal and that if you need help dealing with this feeling you can reach mental health services. Some of these services can be found on the syllabus and in Bb under the tab “Mental Health.” Learning

Assessment Description Refer to the GCU Counselor Dispositions (CMHC) or the Professional Dispositions of Learners (SC), located in Class Resources.

Assessment Description Refer to the GCU Counselor Dispositions (CMHC) or the Professional Dispositions of Learners (SC), located in Class Resources. Consider your dispositional development as a counselor in training along with ethical and legal implications. Create a 3-5-minute video discussing the following: Select three dispositions you consider to be strengths

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

Description ‫المملكة العربية السعودية‬ ‫وزارة التعليم‬ ‫الجامعة السعودية اإللكترونية‬ Kingdom of Saudi Arabia Ministry of Education Saudi Electronic University Department of Business Administration College of Administrative and Financial Sciences Assignment 1 MGT324 (1st Term 2024-2025) Public Management (MGT 324) Due Date: 5/10/2024 Course Name: Public Management Student’s Name: Course Code:

brand is: Jaguar the car company Description: Create a media mix for the assigned brand. The media mix need to include traditional media and digital

brand is: Jaguar the car company Description: Create a media mix for the assigned brand. The media mix need to include traditional media and digital media (2 of each). Justify your decision and the coverage and reach of the campaign.

When you think about the mission and vision on the organization that you currently work whether it is healthcare or not, does it match the strategic

When you think about the mission and vision on the organization that you currently work whether it is healthcare or not, does it match the strategic goals of the organization?

Instructions: Due Tuesday 07/31 At least 3 paragraphs long with 3 peer-reviewed references. Of the transactions that you reviewed in W5 Topic 1,

Instructions: Due Tuesday 07/31 At least 3 paragraphs long with 3 peer-reviewed references. Of the transactions that you reviewed in W5 Topic 1, select two companies, one that uses IFRS and the other that uses U.S. GAAP. Discuss the presentation of these companies’ finanial statement. What are the key differences

100 word response 1 reference/intext citation Due 7/3/2024 Harris The Miami Gardens Police Athletic League (PAL) is dedicated to building a stronger,

100 word response 1 reference/intext citation Due 7/3/2024 Harris The Miami Gardens Police Athletic League (PAL) is dedicated to building a stronger, more united community by fostering positive relationships between local law enforcement and the youth the community serves. The mission of PAL is to develop tomorrow’s leaders through education,

Can you calculate GMPV for the attached EXCEL sheet This file is too large to display.View in new window

Answer the three questions thoroughly and completely. All work will be submitted to Turnitin; there is no artificially intelligent (AI) writing

Answer the three questions thoroughly and completely. All work will be submitted to Turnitin; there is no artificially intelligent (AI) writing allowed.

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ 1. Performance outcomes Candidates analyze their teaching at the end of clinical

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ 1. Performance outcomes Candidates analyze their teaching at the end of clinical practice to reflect on their personal and professional growth since they began clinical practice What will you do? Write or record a reflection of 300-500 words

Description

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 cover page. Students

Instructions Choose a global (or large publicly traded) corporation and research a published article referencing a legal,

Instructions Choose a global (or large publicly traded) corporation and research a published article referencing a legal, ethical, or political issue that the corporation has been affected by, alleged of, or found responsible for based on the article. Objective The name of a global (or large publicly traded) corporation and