Can you explain what formulas were used to calculate the numbers in the gray boxes. Specifically the 7 gray boxes in the excel worksheet.

. 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 Federal Income Cumulative Gross Pay at Employee November Tax Withheld 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. G A B D E F 1 2 PR 3 Payroll Register 4 5 Reno Graphics 6 Payroll Register 7 November 8 9 Deductions 10 Federal Social 11 Gross Income Security Medicare 12 Employee U Pay Tax Tax Tax 13 Jones Y 9,000.00 1,948.00 378.00 130.50 14 Miller N 1,530.00 137.00 64.26 22.19 15 Smith Y 1,210.00 89.00 50.82 17.55 16 Williams N 12,000.00 2,788.00 0.00 174.00 17 Zlotorzynski N 4,200.00 661.00 176.40 60.90 18 27.940.00 5,623.00 669.48 405.14 19 20 21 Tax Information 22 Current Year 23 Social security taxes - employee Social security taxes - employer 25 Medicare tax rate 26 FICA tax maximum income 27 28 29 Union Dues Net Pay 25.00 6,518.50 0.00 1,306.55 25.00 1,027.63 0.00 9,038.00 0.00 3,301.70 50.00 21,192.38 Cum. Gross Pay to Oct 31 85,000 15,570 6,550 120,000 55,000 24 2011 4.20% 6.20% 1.45% $106,800