Using the attached template, you are to compute the gross pay, federal withholding, Social Security withholding,...

Using the attached template, you are to compute the gross pay, federal withholding, Social Security withholding, Medicare withholding and the net payment for this company. Assume there is no state income tax. Overtime is paid at a rate of 1.5 times the normal wage rate.

NOTE: PLEASE DONT FORGET THE PIVOT TABLE!

In addition, compute the total gross pay, federal withholding, Social Security withholding, Medicare withholding and net payment for all the employees. Further, create a pivot table indicating the total gross pay, Social Security withholding, Medicare withholding and net pay for wage employees and for salary employees. Please note you should use auto fill to complete this assignment in an efficient manner. Use the following Excel template:

						Tax Rates
							6,20%	1,45%
Employee Numbers	Pay Rate	Regular Hours Worked	Overtime Hours Worked	Gross Pay	Federal W/H Rate	Federal W/H	SS W/H	Medicare W/H	Net Pay	Type
1	$44,00	40	6		0,25					Wage
2	$56,00	40			0,28					Wage
3	$35,00	40	9		0,15					Wage
4	$3 200,00	1			0,25					Salary
5	$23,00	40	2		0,25					Wage
6	$19,00	1			0,15					Wage
7	$33,00	40	9		0,28					Wage
8	$3 100,00	1			0,28					Salary
9	$2 400,00	1			0,28					Salary
10	$32,00	40	6		0,25					Wage
11	$23,00	40			0,28					Wage
12	$37,00	40	9		0,15					Wage
13	$2 450,00	1			0,25					Salary
14	$41,00	40	2		0,25					Wage
15	$23,00	1			0,15					Wage
16	$36,00	40	9		0,28					Wage
17	$4 100,00	1			0,28					Salary
18	$2 100,00	1			0,28					Salary
19	$40,00	40	6		0,25					Wage
20	$27,00	40			0,28					Wage
21	$31,00	40	9		0,15					Wage
22	$2 200,00	1			0,25					Salary
23	$26,00	40	2		0,25					Wage
24	$18,00	1			0,15					Wage
25	$31,00	40	9		0,28					Wage
26	$2 900,00	1			0,28					Salary
27	$2 850,00	1			0,28					Salary
28	$30,00	40	6		0,25					Wage
29	$41,00	40			0,28					Wage
30	$36,00	40	9		0,15					Wage
31	$2 560,00	1			0,25					Salary
32	$22,00	40	2		0,25					Wage
33	$42,00	1			0,15					Wage
34	$31,00	40	9		0,28					Wage
35	$1 800,00	1			0,28					Salary
36	$1 900,00	1			0,28					Salary
37	$35,00	40	6		0,25					Wage
38	$53,00	40			0,28					Wage
39	$32,00	40	9		0,15					Wage
40	$3 600,00	1			0,25					Salary
41	$25,00	40	2		0,25					Wage
42	$19,00	1			0,15					Wage
43	$29,00	40	9		0,28					Wage
44	$5 000,00	1			0,28					Salary
45	$1 900,00	1			0,28					Salary
46	$22,00	40	6		0,25					Wage
47	$31,00	40			0,28					Wage
48	$52,00	40	9		0,15					Wage
49	$1 900,00	1			0,25					Salary
50	$22,00	40	2		0,25					Wage
51	$18,00	1			0,15					Wage
52	$28,00	40	9		0,28					Wage
53	$3 300,00	1			0,28					Salary
54	$2 100,00	1			0,28					Salary
55	$37,00	40	6		0,25					Wage
56	$52,00	40			0,28					Wage
57	$19,00	40	9		0,15					Wage
58	$4 200,00	1			0,25					Salary
59	$15,00	40	2		0,25					Wage
60	$21,00	1			0,15					Wage
61	$35,00	40	9		0,28					Wage
62	$3 500,00	1			0,28					Salary
63	$2 200,00	1			0,28					Salary
64	$38,00	40	6		0,25					Wage
65	$51,00	40			0,28					Wage
66	$41,00	40	9		0,15					Wage
67	$1 875,00	1			0,25					Salary
68	$27,00	40	2		0,25					Wage
69	$33,00	1			0,15					Wage
70	$27,00	40	9		0,28					Wage
71	$3 700,00	1			0,28					Salary
72	$2 600,00	1			0,28					Sala

Expert Solution

ekkarill92 answered 1 year ago

1: Phil McGlynn (Married; 3 federal withholding allowances) earned weekly gross pay of $790. Federal income...

1: Phil McGlynn (Married; 3 federal withholding allowances) earned weekly gross pay of $790. Federal income tax withholding = $ 2: Gary Williams (single; 2 federal withholding allowance) earned biweekly gross pay of $1,840. He participates in a flexible spending account, to which he contributes $50 during the period. Federal income tax withholding = $ 3: Lila Downing (single; 5 federal withholding allowances) earned monthly gross pay of $5,460. For each period, she makes a 401(k) contribution of 8% of...

Q1a. Thomas Fortuna (married; 4 federal withholding allowances) earned weekly gross pay of $765. For each...

Q1a. Thomas Fortuna (married; 4 federal withholding allowances) earned weekly gross pay of $765. For each period, he makes a 401(k) retirement plan contribution of 11% of gross pay. Using wage-bracket method: Federal income tax withholding = $_________________ Q1b. Barbara Houlihan (single; 1 federal withholding allowance) earned daily gross pay of $310. For each period, she makes a 401(k) contribution of 11.5% of gross pay. Using wage-bracket method: Federal income tax withholding = $ ______________ Using percentage method: Federal income...

1: Michael Daugherty (married; 5 federal withholding allowances) earned weekly gross pay of $855. For each...

1: Michael Daugherty (married; 5 federal withholding allowances) earned weekly gross pay of $855. For each period, he makes a 403(b) retirement plan contribution of 12% of gross pay. Using wage-bracket method: Federal income tax withholding = $ Using percentage method: Federal income tax withholding = $ 2: Carol Harrison (single; 0 federal withholding allowance) earned daily gross pay of $220. For each period, she makes a 401(k) contribution of 12.5% of gross pay. Using wage-bracket method: Federal income tax...

13. Employers who have less than $2,500 due in federal income tax withholding and Social Security...

13. Employers who have less than $2,500 due in federal income tax withholding and Social Security and Medicare taxes at the end of the quarter should journalize the debt and pay at the end of the a. next banking day. b. year. c. quarter. d. month following the end of the quarter. 14. An Employer Identification Number (EIN) is obtained by the employer from the a. nearest local federally insured bank. b. CIA. c. nearest Federal Reserve Bank. d. Internal...

BA 177- Payroll Accounting 1: Barbara Houlihan (single; 1 federal withholding allowance) earned daily gross pay...

BA 177- Payroll Accounting 1: Barbara Houlihan (single; 1 federal withholding allowance) earned daily gross pay of $320. For each period, she makes a 401(k) contribution of 12% of gross pay. Using the wage-bracket method: Federal income tax withholding = $ Using percentage method: Federal income tax withholding = $ BA 177- Payroll Accounting 2: Marcus Xavier (married; 5 federal withholding allowances) earned monthly gross pay of $3,650. He participates in a flexible spending account, to which he contributes $200...

Suppose a pay-as-you-go social security system where social security is funded by a lump sum tax...

Suppose a pay-as-you-go social security system where social security is funded by a lump sum tax (t1) on the young and on the old. Retirement benefits are given out as a fixed amount b to each old consumer. Can social security work to improve welfare for everyone under these conditions? Use diagrams.

How the pay as you go social security system increase or may not increase the social...

How the pay as you go social security system increase or may not increase the social welfare and how the fully funded social security system increase or may not increase the social welfare

Template for Case Study for Financial Literacy The attached template will give you a framework for...

Template for Case Study for Financial Literacy The attached template will give you a framework for preparing your case study for your assignment. What you as a consumer need to know how to do is apply the financial planning process to making decisions about your own affairs. Even though you are mostly very young people, you still need to plan for your retirement. Only a few dollars put aside now will make a big difference to you and how well...

Federal Income Tax Withholding Bob Avery's weekly gross earnings for the present week were $2,740. Avery...

Federal Income Tax Withholding Bob Avery's weekly gross earnings for the present week were $2,740. Avery has three exemptions. Using the wage bracket withholding table in Exhibit 2 with a $75 standard withholding allowance for each exemption, what is Avery's federal income tax withholding? Round your answer to two decimal places.

Compute the deductions, net pay, and accumulated gross earnings for each weekly pay period. Assume that...

Compute the deductions, net pay, and accumulated gross earnings for each weekly pay period. Assume that Joseph is single and that $28.25 was withheld for medical insurance (pre-tax) and $20 for his United Way contribution. Use the wage bracket method to determine federal income tax. Round answers to the nearest hundredth. Gross Earnings(650.95) Allow.(1) Fed. Inc. Tax Social Security (6.2%)Medicare (1.45%) Medical Insurance Pre-tax United Way Total Deductions Net Pay Accum. Gross Earnings ($1,558.90)

Question