HR Payroll Processor — TechBridge Solutions

TechBridge Solutions needs a fully formula-driven payroll sheet for 12 employees. Students build dynamic columns for HRA, PF, TDS, Net Pay, and Grade using ARRAYFORMULA and nested IF, create a live VLOOKUP lookup tool, apply conditional formatting for payslip status, and use QUERY as a bonus.

CONTEXT: TechBridge Solutions HR currently calculates payroll manually. The intern must automate the entire calculation — manager only updates Basic Salary and Days Present, everything else computes automatically.

DATASET: 12 employees across Sales, Tech, Operations. Columns: Emp ID, Employee Name, Department, Basic Salary (₹25,000–₹60,000), Days Present (out of 26), HRA, Gross Salary, PF Deduction, TDS, Net Pay, Salary Grade, Payslip Status.

TASK 1 — ARRAYFORMULA COLUMNS: Col F: HRA = 40% of Basic. Col G: Gross = Basic + HRA. Col H: PF = 12% of Basic. All as single ARRAYFORMULA each.

TASK 2 — TDS: Col I — ARRAYFORMULA + IF: 5% TDS if Gross > ₹30,000, else 0.

TASK 3 — NET PAY: Col J = Gross - PF - TDS via ARRAYFORMULA.

TASK 4 — GRADE: Col K — nested IF: 'A' if Net Pay > ₹40,000, 'B' if > ₹25,000, 'C' otherwise.

TASK 5 — LOOKUP TOOL: N1:O3 — enter Emp ID in O1; O2 auto-shows Name via VLOOKUP; O3 shows Net Pay.

TASK 6 — PAYSLIP STATUS + FORMATTING: Col L — 'Payslip Ready' if Days Present ≥ 20, else 'Hold — Attendance Low'. Conditional formatting: green = Ready, red = Hold.

BONUS: QUERY to extract all 'Sales' department employees sorted by Net Pay descending.

https://docs.google.com/spreadsheets/d/107K5ZdDy8q6CXl_PJn0oL66_CNhPMgnwWWwRVT0KPM0/edit?usp=sharing

Payroll Automation Framework: Separate inputs (Basic Salary, Days Present) from calculated columns. Never hardcode a calculation.

Error Prevention Framework: Wrap every ARRAYFORMULA with IF(col="",formula) to suppress errors on blank rows.

Lookup & Audit Framework: Every payroll system needs a quick-lookup tool for managers to verify individual records instantly.