New Receiving Delivery Manifest (Read-Only) - Excel Review View Acrobat Tell me what you want to do , payroll Register (p x: Microsoft Word. Problem 8-Pe x nloads/Problem%208%20-%20Payroll%20Register.pdf | a n: LABRADA-Lean -D Microsoft Word-Problem 7-A/X O Califomis State UniA Enrolled Agent (EA)... Rideshare Program Other Information regarding payroll rates are as follows Social Security taxes are 6.2% of monthly gross pay up to a cumulative total pay of $118,500. Both employer and employees must pay this for each employee up to the maximum given. Medicare taxes are 1.45% on monthly gross pay with no upper limit. A S50 monthly deduction is made for union dues for all union members. Unemployment taxes are paid on monthly gross pay up to the first $7,000 earned by each employee each year. State and local rates are 5.4% and .8% respectively. a. b. c. d. REQUIREMENTS: 1. Based on the rates provided, use a calculator to compute how much would be withheld from Adilla's October paycheck in the following three cases. (Round to the nearest penny.) Cumulative Gross Pay at 9/30px $90,000 $115 119,000 Social Security Medicare Case 1 Case 2 Case 3 New Receiving Delivery Manifest (Read-Only)-Excel Review View Acrobat Tell me what you want to do. ayroll Reg ster(p. D Mcrosoft Word-Problem 8-Pa x Microsoft Word . Problem 7- ads/Problem%208%20-%20Payro0%20Registerpdf O Caldoma State Uni.. A Envrlled Agent (EA) Rideshare Program | a Amazon.com: LABRADA-Lean E You have been asked to record the October and a general journal. Review the file PAYROLL which is a computerized payroll register that follows these requirements. The columns will automatically re-total a are made. Assume Adila's Cumulative Gross Pay at September 30 could be $90,000, $115,000 or $119,000. To make the worksheet re-usable each month, the Social security tax formulas should be designed to automatically compute whether (1) the full tax is due (2) no tax is due (eg.the cumulative gross wages are over the maximum) or (3) some of the tax is due. IF statements will be required. The -ROUND function should also be used on any formulas that multiply by decimals to eliminate rounding errors. PLEASE SEE THE SEPARATE HANDOUT THAT WILL EXPLAIN THESE FORMULAS IN MORE DETAIL Open up the PAYROLL file. Enter the gross pay and federal income tax withheld for each employee. Enter $90,000 for Adilla's cumulative gross pay. (Cell 13). Then enter the formulas where indicated on the worksheet. As the formulas are entered, the cells that contain zeroes will be automatically filled. Enter your name as a header on the workbook. Save the completed file as PAYROLL4, Make sure the worksheet prints to one page. Check figure: Adilla's net pay in cell H13 is $3,967.20. 2. payroll information using a payroll register s new entries 3. 4. 5. What If Analysis-Verify that the formulas for Adilla work by entering $115,000 and $119,000 in cell 113. Compare your withholding amounts to the answers you computed in requirement 1. Correct any formulas as necessary. (Reset 113 back to $90,000 after you have verified that your formulas are working correctly.) Save as PAYROLLS 6. Journal Entries: On the tab called Payroll JE, record the journal entry to record the October payroll for the employees assuming that the payroll is paid on 10/31/xx and that Adilla's Cumulative Gross Pay through 9/30 is $90,000. On the tab called Employer's Tax JE prepare the journal entry to record the employer's payroll taxes for October In addition, on the tab called Payment JE, also prepare the journal entries to record the PAYMENT of all taxes and union dues assuming they will be paid on November 15. Save your updated file as PAYROLL6 a. b. c. 7. Update for Current Rates: Copy the contents of your "Worksheet" into a new tab that you will call "Update". Then revise the data in cells G23-G25 using updated tax information for the current year New Receiving Delivery Manifest (Read-Onlyl- Excel Review View Acrobat Tell me what you want to do. ACK a Amazo com: ABRADA Les D Microsoft word Problem, 8-Pe Mer soft word-Problem 7 x Pa ol Regster P. oads/Problem%208%20-%20Payro11%20Register.pdf Caiforna State Un. A Enrolled Agent(EA- Rideshare program 8. Analyze: Click the Analyze" Tab. On the screen is a chart of the four payroll taxes that a company might incur in total during the year. The four employer taxes are federal unemployment, state unemployment, social security, and Medicare. By the behavior shown on the chart, Identify IN THE TAB where indicated which of the four taxes each represents. Manipulate the Data: Copy the most recent register over to the tab called "New Employee." To that payroll register, add the information for a new employee as follows Her name is Gloria Gazelle. She is a union member. Her gross pay for October was $6,700. Her federal income tax is $990. Adjust any formulas. (HINT. Consider copying the formulas you created for Adilla down to the new employee. Make sure you TEST your formulas to make sure they work. HINT 2: Make sure that the totals of the 9. register capture the new employee that you added. If they do not, then don't forget to adjust them so that they are accurate.) Preview the printout to make sure it fits on one page. Save the file as PAYROLL9. 10. Create a Chart: Create a new tab called Chart. In that tab, using the most recent file, (PAYROLL9) prepare a 3-D pie chart showing the percentage of Freddie Falcoln's Gross Pay that is withheld for income taxes, social security and all other deductions and how much is left over as net pay. (Consider creating a data table to make this chart easier.) Make sure you use a proper 3-line title on your chart (Who, What, When.) Make sure that your chart will print out correctly when activated. Save the file as PAYROLL10 11. SHOW YOUR FORMULAS: Create a new tab called "Formulas." Copy the PAYROLL REGISTER from your most recent file over to the new tab. Use Control to show the formula contents of the register. You may have to widen the columns so I can see the entire formula. Re-save your work Upload your File: Afler reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as PAYROLL FINAL+Your last name. Upload the final file using Canvas for grading Summary of What's Due: (I will be looking at your one submitted file However, make peane oul ViewSonic New Receiving Delivery Manifest (Read-Only) - Excel Review View Acrobat Tell me what you want to do , payroll Register (p x: Microsoft Word. Problem 8-Pe x nloads/Problem%208%20-%20Payroll%20Register.pdf | a n: LABRADA-Lean -D Microsoft Word-Problem 7-A/X O Califomis State UniA Enrolled Agent (EA)... Rideshare Program Other Information regarding payroll rates are as follows Social Security taxes are 6.2% of monthly gross pay up to a cumulative total pay of $118,500. Both employer and employees must pay this for each employee up to the maximum given. Medicare taxes are 1.45% on monthly gross pay with no upper limit. A S50 monthly deduction is made for union dues for all union members. Unemployment taxes are paid on monthly gross pay up to the first $7,000 earned by each employee each year. State and local rates are 5.4% and .8% respectively. a. b. c. d. REQUIREMENTS: 1. Based on the rates provided, use a calculator to compute how much would be withheld from Adilla's October paycheck in the following three cases. (Round to the nearest penny.) Cumulative Gross Pay at 9/30px $90,000 $115 119,000 Social Security Medicare Case 1 Case 2 Case 3 New Receiving Delivery Manifest (Read-Only)-Excel Review View Acrobat Tell me what you want to do. ayroll Reg ster(p. D Mcrosoft Word-Problem 8-Pa x Microsoft Word . Problem 7- ads/Problem%208%20-%20Payro0%20Registerpdf O Caldoma State Uni.. A Envrlled Agent (EA) Rideshare Program | a Amazon.com: LABRADA-Lean E You have been asked to record the October and a general journal. Review the file PAYROLL which is a computerized payroll register that follows these requirements. The columns will automatically re-total a are made. Assume Adila's Cumulative Gross Pay at September 30 could be $90,000, $115,000 or $119,000. To make the worksheet re-usable each month, the Social security tax formulas should be designed to automatically compute whether (1) the full tax is due (2) no tax is due (eg.the cumulative gross wages are over the maximum) or (3) some of the tax is due. IF statements will be required. The -ROUND function should also be used on any formulas that multiply by decimals to eliminate rounding errors. PLEASE SEE THE SEPARATE HANDOUT THAT WILL EXPLAIN THESE FORMULAS IN MORE DETAIL Open up the PAYROLL file. Enter the gross pay and federal income tax withheld for each employee. Enter $90,000 for Adilla's cumulative gross pay. (Cell 13). Then enter the formulas where indicated on the worksheet. As the formulas are entered, the cells that contain zeroes will be automatically filled. Enter your name as a header on the workbook. Save the completed file as PAYROLL4, Make sure the worksheet prints to one page. Check figure: Adilla's net pay in cell H13 is $3,967.20. 2. payroll information using a payroll register s new entries 3. 4. 5. What If Analysis-Verify that the formulas for Adilla work by entering $115,000 and $119,000 in cell 113. Compare your withholding amounts to the answers you computed in requirement 1. Correct any formulas as necessary. (Reset 113 back to $90,000 after you have verified that your formulas are working correctly.) Save as PAYROLLS 6. Journal Entries: On the tab called Payroll JE, record the journal entry to record the October payroll for the employees assuming that the payroll is paid on 10/31/xx and that Adilla's Cumulative Gross Pay through 9/30 is $90,000. On the tab called Employer's Tax JE prepare the journal entry to record the employer's payroll taxes for October In addition, on the tab called Payment JE, also prepare the journal entries to record the PAYMENT of all taxes and union dues assuming they will be paid on November 15. Save your updated file as PAYROLL6 a. b. c. 7. Update for Current Rates: Copy the contents of your "Worksheet" into a new tab that you will call "Update". Then revise the data in cells G23-G25 using updated tax information for the current year New Receiving Delivery Manifest (Read-Onlyl- Excel Review View Acrobat Tell me what you want to do. ACK a Amazo com: ABRADA Les D Microsoft word Problem, 8-Pe Mer soft word-Problem 7 x Pa ol Regster P. oads/Problem%208%20-%20Payro11%20Register.pdf Caiforna State Un. A Enrolled Agent(EA- Rideshare program 8. Analyze: Click the Analyze" Tab. On the screen is a chart of the four payroll taxes that a company might incur in total during the year. The four employer taxes are federal unemployment, state unemployment, social security, and Medicare. By the behavior shown on the chart, Identify IN THE TAB where indicated which of the four taxes each represents. Manipulate the Data: Copy the most recent register over to the tab called "New Employee." To that payroll register, add the information for a new employee as follows Her name is Gloria Gazelle. She is a union member. Her gross pay for October was $6,700. Her federal income tax is $990. Adjust any formulas. (HINT. Consider copying the formulas you created for Adilla down to the new employee. Make sure you TEST your formulas to make sure they work. HINT 2: Make sure that the totals of the 9. register capture the new employee that you added. If they do not, then don't forget to adjust them so that they are accurate.) Preview the printout to make sure it fits on one page. Save the file as PAYROLL9. 10. Create a Chart: Create a new tab called Chart. In that tab, using the most recent file, (PAYROLL9) prepare a 3-D pie chart showing the percentage of Freddie Falcoln's Gross Pay that is withheld for income taxes, social security and all other deductions and how much is left over as net pay. (Consider creating a data table to make this chart easier.) Make sure you use a proper 3-line title on your chart (Who, What, When.) Make sure that your chart will print out correctly when activated. Save the file as PAYROLL10 11. SHOW YOUR FORMULAS: Create a new tab called "Formulas." Copy the PAYROLL REGISTER from your most recent file over to the new tab. Use Control to show the formula contents of the register. You may have to widen the columns so I can see the entire formula. Re-save your work Upload your File: Afler reviewing your most recent file for completeness, and making sure all parts have been answered, Save the file as PAYROLL FINAL+Your last name. Upload the final file using Canvas for grading Summary of What's Due: (I will be looking at your one submitted file However, make peane oul ViewSonic