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 Tax Withheld Cumulative Pay at Union 9/30/XX Member Amber Adilla Bobby Bonilla Candice Chameleon Donna Donilla Freddy Falcon $5,200 4,300 1,250 12,000 10,000 $785.00 731.00 112.50 1,875.00 1,560.00 $??? (See #1 below) Yes 38,700 No 6,750 No 119,000 No 110,000 Yes 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 PAYROLL6. 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 PAYROLLO. 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. PR Payroll Register Critter Crates Company Payroll Register October Gross Pay Federal Income Tax Employee Amber Adilla Bobby Bonilla Candice Chameleon Donna Donilla Freddy Falcon Union Yes No No No Yes 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 0.00 0.00 0.00 Tax Information Current Year Social security tax rate Medicare tax rate FICA tax maximum income 2017 6.20% 1.45% $118,500 GENERAL JOURNAL PAGE CREDITS DATE DESCRIPTION P/R DEBITS 1 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 Cumulative Payroll Taxes Series 1 -Series 1 Series 2 +-Series2 $7,000 $6,000 $5,000 $4,000 $3,000 $2,000 $1,000 $0 Series3 Series 3 Series4 Series 4 0 2 4 6 8 10 12 Months O OO 01 ANO Chart Data Table 0 0 1202 400 2404 750 3606 1000 4808 1175 5600 1175 6200 1175 6 8 10 12 112 210 280 400 400 400 0 280 560 840 1120 1400 1680 Chart Tickler Data Table Medicare Union Dues Net Pay Fed W/H Soc. Sec. 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 Tax Withheld Cumulative Pay at Union 9/30/XX Member Amber Adilla Bobby Bonilla Candice Chameleon Donna Donilla Freddy Falcon $5,200 4,300 1,250 12,000 10,000 $785.00 731.00 112.50 1,875.00 1,560.00 $??? (See #1 below) Yes 38,700 No 6,750 No 119,000 No 110,000 Yes 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 PAYROLL6. 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 PAYROLLO. 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. PR Payroll Register Critter Crates Company Payroll Register October Gross Pay Federal Income Tax Employee Amber Adilla Bobby Bonilla Candice Chameleon Donna Donilla Freddy Falcon Union Yes No No No Yes 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 0.00 0.00 0.00 Tax Information Current Year Social security tax rate Medicare tax rate FICA tax maximum income 2017 6.20% 1.45% $118,500 GENERAL JOURNAL PAGE CREDITS DATE DESCRIPTION P/R DEBITS 1 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 Cumulative Payroll Taxes Series 1 -Series 1 Series 2 +-Series2 $7,000 $6,000 $5,000 $4,000 $3,000 $2,000 $1,000 $0 Series3 Series 3 Series4 Series 4 0 2 4 6 8 10 12 Months O OO 01 ANO Chart Data Table 0 0 1202 400 2404 750 3606 1000 4808 1175 5600 1175 6200 1175 6 8 10 12 112 210 280 400 400 400 0 280 560 840 1120 1400 1680 Chart Tickler Data Table Medicare Union Dues Net Pay Fed W/H Soc. Sec