Prepare and use a payroll register including Social Security and Medicare withholdings. Use =IF and =ROUND to design the Social Security tax and union dues formulas. Record journal entries for payroll, employer's tax liability, and payment of payroll taxes. Identify behavior patterns among payroll taxes over time. Alter the worksheet to include an additional employee. Create a chart showing the percentage of an employee's gross pay that is withheld for taxes and other such deductions, and how much is left over as net pay. PROBLEM DATA Reno Graphics has five employees, and they are paid at the end of each month. Payroll data for November are as follows: Gross Pay for Employee November Federal Income Tax Withheld Cumulative Gross Pay at October 31 Jones $9,000 $1,948 $ ? Miller 1,530 137 15,570 Smith 1,210 89 6,550 Williams 12,000 2,788 120,000 Zlotorzynski 4,200 661 55,000 Other information (using 2011 rates) is as follows: a. Social security taxes are 4.2% of monthly gross pay up to a cumulative total pay of $106,800 for 2011. Employers must pay 6.2% of monthly gross pay up to a cumulative total pay of $106,800 for each employee. b. Medicare taxes are 1.45% on monthly gross pay with no upper limit. c. A $25 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 federal rates are 2.7% and .8%, respectively. A B C D E F G H 1 PR 3 Payroll Register 4 5 Reno Graphics 6 Payroll Register 7 November 8 9 Deductions 10 Federal Social 11 Gross 13 Jones 14 Miller 15 Smith 12 Employee U Pay Y 9,000.00 N 1,530.00 Y 1,210.00 16 Williams 17 Zlotorzynski N 12,000.00 N 4,200.00 27.940.00 Income Tax 1,948.00 137.00 64.26 89.00 50.82 2,788.00 0.00 661.00 176.40 5,623.00 Security Medicare Union Cum. Gross Pay Tax Tax 378.00 130.50 Dues 25.00 Net Pay to Oct 31 6,518.50 85,000 22.19 0.00 17.55 25.00 174.00 0.00 9,038.00 60.90 0.00 3,301.70 669.48 405.14 50.00 21,192.38 1,306.55 15,570 1,027.63 6.550 120,000 55,000 622222222222 Tax Information Current Year 2011 Social security taxes - employee 4.20% 24 Social security taxes - employer 6.20% Medicare tax rate 1.45% FICA tax maximum income $106,800 J K Can you explain what formulas were used to calculate the numbers in the gray boxes. Specifically the 7 gray boxes in the excel worksheet.