The regular pay = maximum hours worked (40) multiplied by the wage rate. This can be computed in MS Excel by using the if formula. This formula was used in computing the regular pay =IF (D5>40, 40*C5, IF (D5 < 40, D5*C5, IF (D5 = 40, D5*C5))).
The overtime pay is computed based on any hours worked above the maximum weekly hours (40). The excess time is multiplied by wage rate at 150%. The excel formula used was as follows = IF (D5 >40, (D5-40) *1.5*C5, IF (D5 <= 40, D5 *0)). The formula is meant to compute the excess hours and then multiply it with 1.5 of the hourly wage. If there was no excess amount of time the rate would be 0.
The taxable pay is the difference between the gross pay and the product of the number of dependents and deductions per dependent. The taxable pay was calculated using excel. The formula applied =G5-(B5*$B$26). The product is the answer which you get after multiplying two values. In this case you are multiplying the number of dependents and the cost of $50.
The VLOOOKUP is an abbreviation of vertical lookup. It is used to search a certain value and then give the result of the search. For example if you need to search a rate which will be used at a certain taxable income, you can use the VLOOKUP. This formula was used in our computation to determine the Federal Withholding Tax. The formula used is as follows =VLOOKUP (H5, $D$23: $E$28, 2, TRUE)*H5. In this formula it searched for the rate applicable at the a certain taxable pay and then multiplied it(tax rate) with the taxable pay. The value 2 is used because you are having a range of two columns and the word True is used so that we can get the result as defined by formula.
The total in row 17 is the sum of the values in the columns. It is calculated using the formula of sum.
The COUNTIF formula is used to count certain values based on a certain criteria. The COUNTIF formula was used to determine the number of employees at the range of hours.
The net pay is based on the gross pay, federal withholding tax and FICA. That is net pay = gross pay –federal withholding Tax – FICA. The formula used in excel is =G5-I5-J5
QUESTION TWO
The standard pay = maximum hours (40)* hourly rate. This can be calculated using the following formula IF (E4>40, $L$7*D4, IF (E4<40, D4*E4, IF (E4=40, D4*E4)))
Overtime pay is based on the excess of maximum. The formula used =IF (E4>$L$7, (E4-$L$7)*1.5*D4, IF (E4<=$L$7, D4*0)).
The total pay = standard pay + overtime +bonus pay
Bonus pay = 20% * standard pay. It was received by the employees who sold above the target. It was computed using the following formula
Bonus pay =IF (B4 >C4,0.2*F4, IF (B4 <=C4, F4*0))
