Excel Test for HR & Payroll Specialists

Under the US Fair Labor Standards Act (FLSA) regulations for non-exempt hourly employees, at what point does overtime pay at 1.5 times the regular rate typically apply?

In target cell D2, calculate the regular pay for an employee who worked 40 standard hours at $25.00/hour by multiplying Regular Hours (B2) by Hourly Rate (C2).

Which statement accurately describes the distinction between Gross Pay and Net Pay in corporate payroll processing?

Which of the following items are standard mandatory statutory payroll deductions withheld from an employee's gross paycheck in the United States? (Select all that apply)

In target cell D2, calculate the overtime pay for 8 overtime hours (B2) at an hourly rate of $30.00 (C2) at time-and-a-half (1.5x).

An HR Analyst needs to calculate an employee's completed years of service between Hire Date (A2) and Evaluation Date (B2). Which formula returns the exact number of full completed years?

Which of the following formula techniques can accurately calculate full completed years of employee tenure between Hire Date (A2) and Current Date (B2)? (Select all that apply)

In target cell C2, calculate the completed years of tenure between Hire Date (A2) and As of Date (B2) using DATEDIF with the "Y" unit.

If cell A2 contains Starting PTO Hours, B2 contains Accrued PTO, and C2 contains Used PTO Hours, which formula correctly computes the Remaining PTO Balance?

When setting up an automated spreadsheet for monthly employee vacation accrual, which parameters are necessary to calculate an employee's ending leave balance accurately? (Select all that apply)

In target cell E2, calculate the ending PTO balance by adding Carried Hours (B2) and Accrued Hours (C2), then subtracting Used Hours (D2).

An HR Director wants to count how many active employees belong to the "Engineering" department in a roster where column B lists employee departments (B2:B50). Which formula is best?

Which Excel functions are commonly used by HR Operations to prepare departmental headcount and salary summaries? (Select all that apply)

In target cell E2, calculate the total headcount for the "Sales" department from the staff roster in B2:B7 using COUNTIF.

An employee has a gross taxable wage of $3,500.00 in cell A2. If their effective withholding tax rate is 15% (cell B2), which formula correctly computes the tax withholding amount?

Which financial elements are included when calculating an organization's Total Cost of Workforce beyond employee base gross salary? (Select all that apply)

In target cell D2, calculate Net Pay by subtracting Tax Withholding (C2) from Gross Salary (B2).

What is the key structural difference between a bi-weekly payroll cycle and a semi-monthly payroll cycle over a calendar year?

In automated timesheet modeling, if total weekly hours are in cell B2 (e.g., 46 hours), which formulas correctly calculate the 6 overtime hours worked beyond the 40-hour limit? (Select all that apply)

In target cell C2, calculate the capped regular hours (maximum 40) from Total Hours Worked (B2) using the MIN function.

When using VLOOKUP to match Employee IDs against an HR roster, newly hired contractors not yet in the employee roster table trigger #N/A errors. Which function cleanly replaces #N/A with "Not Enrolled"?

Which of the following statements regarding US Federal Insurance Contributions Act (FICA) payroll taxes are true? (Select all that apply)

In target cell C2, calculate the overtime hours worked beyond 40 from Total Hours (B2) using MAX.

An employee with a monthly salary of $6,000 starts work on day 16 of a standard 30-day payroll month. Assuming equal daily rate allocation (30 days in the month), what is the correct prorated gross pay for their 15 active days?

Scenario Background: The plant manager submitted bi-weekly timesheets for hourly technicians. Staff receive base hourly wages up to 40 regular hours, and 1.5x time-and-a-half for hours worked beyond 40. HR must calculate each worker's gross wage.

In target cell D2, calculate technician Sandra Lee's gross wage for 46 total hours worked (B2) at a $25/hr base rate (C2) using: =40*C2 + (B2-40)*C2*1.5

Scenario Background: At fiscal year-end, HR must calculate the company's financial balance-sheet liability for unused Paid Time Off (PTO). Each employee's remaining PTO balance is valued at their current hourly base pay rate.

In target cell E2, calculate Alicia Gomez's total unused PTO financial liability by multiplying her remaining leave hours (Total Earned in C2 minus Used in D2) by her Hourly Rate (B2).

An employee earning a monthly gross salary of $5,000 contributes 5% to a traditional pre-tax 401(k) retirement plan. How does this contribution affect their federal taxable gross wage for income tax withholding?

Scenario Background: The VP of HR requested an executive workforce breakdown report showing total engineering department monthly payroll commitment.

In KPI cell E2, calculate the total monthly payroll committed to the "Engineering" department across the employee roster in C2:D6 using SUMIF.

Scenario Background: The Payroll Accounting team needs to calculate net wages disbursed to staff after accounting for base salaries, overtime wages, federal tax withholding, and FICA deductions.

In target cell F2, calculate Liam Nelson's net disbursed pay: (Base Salary in B2 + OT Pay in C2) minus (Withholding Tax in D2 + FICA in E2).

Scenario Background: Corporate HR policy awards a $5,000 milestone bonus to employees who have achieved at least 5 completed years of service. HR Operations needs to audit the bonus qualification for candidate Oliver Queen.

In target cell D2, assign a bonus of 5000 if Completed Years (C2) is greater than or equal to 5, otherwise return 0 using an IF function.