ACCT 133
Spreadsheet Accounting
P10 (Payroll Register)
OBJECTIVE:
This project develops an automated payroll register that will calculate the deductions and net pay (take-home pay) for employees, given the gross (or total) pay. It will also introduce you to one of the most powerful features in Excel; the =IF function. In fact, this problem requires the use of "nested" =IF functions, because there are more than two possible results.
CONCEPTS:
The payroll function is important for both employees and employers, and therefore is something that you need to understand.
Since most of you will contribute to the Federal Insurance Contributions Act (which is commonly referred to as Social Security), you need to understand how deductions are calculated. Both employees and employers pay Social Security taxes, and the 2011 rate for the tax is 6.2% for employers on all earnings up to a maximum of $106,800 and 4.2% on employees up to a maximum of $106.800. Medicare taxes are 1.45% on all earnings with no upper limit, paid by both employees and employers. (We will complete the problems with rates quoted in the book.) Since the book quotes current rates, there is no optional #8 requirement.)
As you will discover in this problem, the $106,800 limit (outdated, but used in your text) on Social Security taxes can create three possible different situations:
Here are the different situations in this problem.
1. An employee (paid monthly) has cumulative earnings of $85,000 before this payroll period and earns $9,000 during the month. The amount of Social Security taxes for the month to be withheld from the employee would be $378 ($9,000 times 4.2%) since the employee has not reached the $106,800 limit.
2. An employee (paid monthly) has cumulative earnings of $105,000 before this payroll period and earns $9,000 during the month. The amount of Social Security taxes for the month would be $75.60 ($1800 times 4.2%). Note that the $1,800 taxable amount increases the earnings to the maximum for Social Security which is $106,800 in your text. His next check will not be taxed.
3. An employee (paid monthly) has cumulative earnings of $125,000 before this payroll period and earns $9,000 during the month. The amount of Social Security taxes for the month would be 0 since the employee has already reached and passed the maximum limit. His checks will not be taxed for the remainder of the year.
Developing a formula in a payroll register that can be used to meet all three situations given above requires the use of an IF function and is given in your text as FORMULA 2 on page 67. You are to develop FORMULA 1, but I think you will need some help! The formula for FORMULA 2 is given below: (note that the only difference between this formula and FORMULA 1 given in the book is the cell reference which is 113 instead of 114.
FORMULA 2:
=ROUND(IF(I14>G26,0,IF((I14+C14)>G26,(G26-I14)*G26,C14*G26)),2)
! ! ! !
(a) (b) (c) (d) (e)
Here is the logic of this formula (and aren’t you glad you won’t have to develop a formula like this in the class?).
(a) This is the rounding function and rounds to a specified number of decimal places. Since payroll calculations involve dollars and cents, this function is necessary. The number of decimal places specified is placed at the end of the formula. See letter (e). In this case the number of decimal places is 2, which will round to the nearest cent.
(b) This is the first condition of the IF statement. It says if I14 (which is the cumulative gross pay prior to this pay period) is greater than G26 (which is the maximum amount subject to the FICA tax ), then the amount to be placed in the cell is 0. In other words, since the employee is over the limit, he/she is not taxed and a 0 will be placed in the cell.
(c) This says that if I14 (the cumulative gross pay) plus C14 (which is the pay for this period) adds up to more than the maximum amount for Social Security, then only tax the amount below the limit and do not tax the amount above the limit. The amount which is subject to the tax is calculated by taking the difference between G26 (the maximum) and I14 (which is the cumulative gross pay). This difference is then multiplied by the tax rate found in G23.
(d) This says that if the other two conditions mentioned above are not met, then the employee is under the limit and the entire gross pay for the period must be taxed. The gross pay (C14) will be multiplied by the tax rate found in G23.
(e) This specifies the number of decimal places (in this formula, round to 2 places.)
A very simple example of the IF function is FORMULA 4 (and formula 5) which can be used to calculate the union dues. The following formula would insert $25 in the cell for a union employee or 0 in the cell for a nonunion employee.
=IF(B13=”Y”,25,0)
When entering this formula in cell G13, be sure that you type it exactly as shown. Do not leave any spaces between the numbers or symbols.
FORMULA 3 and FORMULA 6 are very straight forward and are like formulas that we have written before.
One last helpful hint—You must complete all the formulas before any of the author designated formulas will become active. If you insert your formulas first, and complete the calculations for Jones (row 13), then you will notice that the rest of the calculations will be completed automatically.
The use of absolute references within a formula can be utilized so that the formulas can be copied to the new employee that the tickler will have you add to your template. Also, utilizing absolute references where necessary will allow you to copy this same formula down to the second employee, rather than have you re-entering it again. Absolute references will allow certain designated cells to remain constant -- or not change -- as they are copied, (as opposed to relative cell addresses that will change as they are copied.) Absolute references can be designated by either placing a dollar sign before the part of the address that you do not want to change. Or you can use the F4 key as you are entering the cell to automatically place the absolute reference on the cell address.