PLEASE HELP ME ANSWER THIS, Will upvote!

(More Information)

Problem Data The Critter Crates Company has five employees and they are paid on the last day of each month. Payroll data for October of this year is as follows: Employee Gross Pay for October Federal Income Cumulative Pay at Union Tax Withheld 9/30/XX Member Amber Adilla $5,200 $785.00 $??? (See #1 below) Yes Bobby Bonilla 4,300 731.00 38,700 Candice Chameleon 1,250 112.50 6,750 No Donna Donilla 12,000 1,875.00 119,000 Freddy Falcon 10,000 1,560.00 110,000 Yes No No Other Information regarding payroll rates are as follows: a. 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. b. Medicare taxes are 1.45% on monthly gross pay with no upper limit. C. A $50 monthly deduction is made for union dues for all union members. d. 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, 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/30/XX Social Security Medicare Case 1 $90,000 Case 2 $115,000 Case 3 $119,000 2. You have been asked to record the October payroll information using a payroll register and a general journal. Review the file PAYROLL which is a computerized payroll register that follows these requirements. The columns will automatically re-total as new entries are made. Assume Adilla's Cumulative Gross Pay at September 30 could be $90,000, $115,000 or $119,000. 3. 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 (e.g. 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. 4. 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 113). 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. 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: a. 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. b. On the tab called Employer's Tax JE prepare the journal entry to record the employer's payroll taxes for October. c. 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 PAYROLLE. 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. 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. 9. 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 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 PAYROLLS. A B C D E E F G H 1 2 3 4 PR Payroll Register 5 Critter Crates Company Payroll Register October Gross Pay Federal Income Tax Union Yes No Deductions Social Cum. Security Medicare Union Gross Pay Tax Tax Dues Net Pay to Sept. 30 FORMULA 1 FORMULA 3 FORMULA 4 FORMULA 6 ??? FORMULA 2 0.00 FORMULA 5 0.00 38,700 0.00 0.00 0.00 6,750 0.00 0.00 0.00 0.00 119,000 0.00 0.00 0.00 0.00 110,000 0.00 0.00 0.00 0.00 No 0.00 No Yes 0.00 0.00 6 7 8 9 10 11 12 Employee 13 Amber Adilla 14 Bobby Bonilla 15 Candice Chameleon 16 Donna Donilla 17 Freddy Falcon 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 2017 Tax Information Current Year Social security tax rate Medicare tax rate FICA tax maximum income 6.20% 1.45% $118,500 Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee A B D E F G H I J K L M N O P 1 NAME GENERAL JOURNAL PAGE CREDITS DATE DESCRIPTION PR DEBITS 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 DI Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee + A B C D E E F G I L 1 2 3 4 5 Cumulative Payroll Taxes 6 Series 1 7 8 Series 2 9 $7,000 $6,000 $5,000 $4,000 $3,000 $2,000 $1,000 $0 -Series1 Series2 Series3 Series 10 Series 3 11 12 Series 4 0 2 4 6 8 10 12 Months 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 2 4 6 8 10 12 0 1202 2404 3606 4808 5600 6200 Chart Data Table 0 400 750 1000 1175 1175 1175 0 112 210 280 400 400 400 0 280 560 840 1120 1400 1680 Chart Tickler Data Table Medicare Union Dues Fed W/H Soc. Sec. Net Pay Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee + Problem Data The Critter Crates Company has five employees and they are paid on the last day of each month. Payroll data for October of this year is as follows: Employee Gross Pay for October Federal Income Cumulative Pay at Union Tax Withheld 9/30/XX Member Amber Adilla $5,200 $785.00 $??? (See #1 below) Yes Bobby Bonilla 4,300 731.00 38,700 Candice Chameleon 1,250 112.50 6,750 No Donna Donilla 12,000 1,875.00 119,000 Freddy Falcon 10,000 1,560.00 110,000 Yes No No Other Information regarding payroll rates are as follows: a. 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. b. Medicare taxes are 1.45% on monthly gross pay with no upper limit. C. A $50 monthly deduction is made for union dues for all union members. d. 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, 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/30/XX Social Security Medicare Case 1 $90,000 Case 2 $115,000 Case 3 $119,000 2. You have been asked to record the October payroll information using a payroll register and a general journal. Review the file PAYROLL which is a computerized payroll register that follows these requirements. The columns will automatically re-total as new entries are made. Assume Adilla's Cumulative Gross Pay at September 30 could be $90,000, $115,000 or $119,000. 3. 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 (e.g. 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. 4. 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 113). 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. 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: a. 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. b. On the tab called Employer's Tax JE prepare the journal entry to record the employer's payroll taxes for October. c. 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 PAYROLLE. 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. 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. 9. 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 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 PAYROLLS. A B C D E E F G H 1 2 3 4 PR Payroll Register 5 Critter Crates Company Payroll Register October Gross Pay Federal Income Tax Union Yes No Deductions Social Cum. Security Medicare Union Gross Pay Tax Tax Dues Net Pay to Sept. 30 FORMULA 1 FORMULA 3 FORMULA 4 FORMULA 6 ??? FORMULA 2 0.00 FORMULA 5 0.00 38,700 0.00 0.00 0.00 6,750 0.00 0.00 0.00 0.00 119,000 0.00 0.00 0.00 0.00 110,000 0.00 0.00 0.00 0.00 No 0.00 No Yes 0.00 0.00 6 7 8 9 10 11 12 Employee 13 Amber Adilla 14 Bobby Bonilla 15 Candice Chameleon 16 Donna Donilla 17 Freddy Falcon 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 2017 Tax Information Current Year Social security tax rate Medicare tax rate FICA tax maximum income 6.20% 1.45% $118,500 Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee A B D E F G H I J K L M N O P 1 NAME GENERAL JOURNAL PAGE CREDITS DATE DESCRIPTION PR DEBITS 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 DI Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee + A B C D E E F G I L 1 2 3 4 5 Cumulative Payroll Taxes 6 Series 1 7 8 Series 2 9 $7,000 $6,000 $5,000 $4,000 $3,000 $2,000 $1,000 $0 -Series1 Series2 Series3 Series 10 Series 3 11 12 Series 4 0 2 4 6 8 10 12 Months 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 2 4 6 8 10 12 0 1202 2404 3606 4808 5600 6200 Chart Data Table 0 400 750 1000 1175 1175 1175 0 112 210 280 400 400 400 0 280 560 840 1120 1400 1680 Chart Tickler Data Table Medicare Union Dues Fed W/H Soc. Sec. Net Pay Worksheet Payroll JE Employers Payroll JE Payment JE Analyze New Employee +