Question

1.Calculate the Regular Pay and Overtime Pay based on a regular 40-hour workweek in cells F5 and G5 respectively. Pay overtime only for overtime hours. Note that the base work hours and overtime rate is given under the Assumptions section of the Payroll Data worksheet. In cell H5, calculate the Gross Pay based on the regular and overtime pay.

2. Write a formula in cell I5 to calculate the Taxable Pay. Multiply the number of dependents (given in column C) by the deduction per dependent (given in cell B24) and subtract that from the gross pay.

3.Calculate the Withholding Tax in cell J5 using the formula given below: Withholding Tax = Taxable Pay * Tax Rate The Taxable Pay is given in Column I and the Tax Rates for various Taxable Pay are given in the range F21:G25. You are required to use VLOOK function to retrieve the applicable tax rate from the range F21:G25.

4.Perform the same calculation as above (i.e. calculate the Withholding Tax) in cell K5 using IF function and compare the results. They should be the same.

5.Calculate Insurance Withholding (IW) in cell L5 using the formula given below: IW = gross pay * IW rate (given in cell B23). Next, calculate the Net Pay in cell M5.

6.In the range F5:M16, calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, IW, and net pay. Next, in the range F17:M17 calculate the totals of each respective column.

7. Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (range K21:M23).

8.A list of staff (identified by their respective Staff IDs) in range A3:A6 are recommended for additional payment. Using appropriate functions, display their names in the range B3:B6 after checking against the Payroll Data worksheet. Your formula should display “No such Staff” if a Staff ID is invalid.

9. Determine if a staff qualifies for additional payment in the range C3:C6 based on the following rules: Display "Yes" if: a staff has more than 2 dependents AND his/her Net Pay is less than $300 OR if staff has either 1 or 2 dependents, AND their net pay is less than $400 Else Display “No” Display "No such Staff" if the Staff ID is invalid

Name Staff ID No. of Dependents Regular Pay Overtime Pay G 6 Abram s Acosta - Bordeaux 8 Higinbotham lanziti 0 Jaussi 1 Ling 2 Munoz 3 Riley 4 Sabey 5 Trujillo 6 Weston 7 Totals 8 9 769 844 849 772 613 870 602 874 881 693 886 636 2 1 3 1 1 1 3 2 3 2 1 2 Hourly Wage 9.95 9.95 9.95 9.95 9.95 9.95 9.95 9.95 9.95 9.95 9.95 9.95 Hours Worked 48 48 50 35 40 44 40 20 38 45 15 41 Taxable Pay Tax Rate 0 250 300 450 525 0.15 0.22 0.25 0.28 0.31 0 Assumptions 1 Base Work Hours 40 2 Overtime rate 1.5 3 IW rate 0.0765 -4 Deduction per Dependent 50 5 6 7 Notes 8 1. The base workweek is 40 hours. Regular pay is based on hourly wage and hours worked up to 40. 2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate. If there are no overtime 9 hours, then overtime pay is $0. 0 3. The gross pay is the total of the regular pay and overtime pay 4. The taxable pay is the difference between the gross pay 1 and the product of the number of dependents and deduction per dependent. 2 5. Withholding tax is calculated on the taxable pay based on the tax table. 13 6.IW is calculated on the employee's gross pay 4 7. The net pay is based on the gross pay, withholding tax, and IW 35 A Payroll Data Additional Payment +

Answer 1

(NOTE: Please note that you have posted a question with more than 4 subparts, in a single post. The first 4 subparts are answered below. Please ask the remaining subparts in a separate post.)

The formulas below are shown for row 5. Just copy-paste or drag these formulas in bottom rows to populate them as well:

4 Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Withholding Tax (using vlookup) Withholding tax (using IF) 48 =D5*MIN(E5,40) =MAX(E5-$B$21,0)*D5*$B$22 =F5+G5 =H5-C5*$B$24 =15*VLOOKUP(15,$F$21:$G$25,2) =15*IF(15<$F$22,$G$21,IF(15<$F$23,$G$22,IF(15<$F$24,$G$23,IF(15<$F$25,$G$24,$G$25))))

Part 1:

The formulas for F, G & H columns are as shown in the above screenshot. We use min function to select the minimum of 40 or actual hours. So if an employee has worked less than 40 hours, he will get paid for that.

Part 2:

We need to use absolute addressing for cell B24 as that must remain the same when we copy this formula in bottom rows

=H5-C5*$B$24

Part 3:

=I5*VLOOKUP(I5,$F$21:$G$25,2)

Part 4:

For this, we need to use nested if conditions as shown below. We start comparing the taxable pay with each slab boundary and select the tax rate if the condition is met otherwise go to next nested if statement.

=I5*IF(I5<$F$22,$G$21,IF(I5<$F$23,$G$22,IF(I5<$F$24,$G$23,IF(I5<$F$25,$G$24,$G$25))))

These formulas, copied to next rows below look like as shown below:

4 Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Withholding Tax (using vlookup Withholding tax (using IF 5 48 =D5*MIN(E5,40) =MAX(E5-$B$21,0)*D5*$B$22 =F5+G5 =H5-C5*$B$24 =15*VLOOKUP(15,$F$21:$G$25,2) =15*IF(15<$F$22,$G$21,IF(15<$F$23,$G$22,IF(15<$F$24,$G$23,IF(15<$F$25,$G$24,$G$25)))) 6 48 =D6*MIN(E6,40) =MAX(E6-$B$21,0)*D6*$B$22 =F6+G6 =H6-C6*$B$24 =16*VLOOKUP(16,$F$21:$G$25,2) =16*IF(16<$F$22,$G$21,IF(16<$F$23,$G$22,IF(16<$F$24,$G$23,IF(16<$F$25,$G$24,$G$25)))) 50 =D7*MIN(E7,40) =MAX(E7-$B$21,0)*D7*$B$22 =F7+G7 =H7-C7*$B$24 =17*VLOOKUP(17,$F$21:$G$25,2) =17*IF(17<$F$22,$G$21,IF(17<$F$23,$G$22,1F(17<$F$24,$G$23,IF(17<$F$25,$G$24,$G$25)))) 8 35 =D8*MIN(E8,40) =MAX(E8-$B$21,0)*D8*$B$22 =F8+G8 =H8-C8*$B$24 =18*VLOOKUP(18,$F$21:$G$25,2) =18*IF(18<$F$22,$G$21,IF(18<$F$23,$G$22,IF(18<$F$24,$G$23,IF(18<$F$25,$G$24,$G$25))))

Sample result for rows 5 to 8:

A 4 Name 5 Abram 6 Acosta 7 Bordeaux 8 Higin B D F Staff ID No. of Dependents Hourly Wage Hours Worked Regular Pay Overtime Pay 769 2 9.95 48 398 844 1 9.95 48 398 849 3 9.95 50 398 772 1 9.95 35 348.25 Н. Gross Pay Taxable Pay Withholding Tax (using vlookup) 119.4 517.4 417.4 104.35 119.4 517.4 467.4 130.87 149.25 547.25 397.25 99.31 0 348.25 298.25 65.62

(Note: If any doubt, please let me know in the comments)