Excel Applications for Accounting Principles 4th Edition by Gaylord Smith

Exercise 3

PROBLEM DATA
Reno Graphics has five employees, and they are paid at the end of each month. Payroll data for November are as follows:

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.
REQUIREMENT
To make the worksheet reusable each month, the Social Security tax formulas should be designed to automatically compute whether (1) full tax is due, (2) no tax is due (e.g., cumulative gross pay is over the ceiling), or (3) some tax is due. =IF statements will be required. The =ROUND function should be used for FORMULAS 1 through 3 to eliminate rounding errors. FORMULAS 4 and 5 should also use the =IF function. FORMULA2 has been provided for you below. Review Appendix A of Excel Quick and explain the meaning of each part of the formula.

a.
b.
c.
d.
e.