HR Payroll Processor — TechBridge Solutions
Beginner
20 min
15 views
0 solutions
Overview
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.
Case Details
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.
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.
What You'll Learn
- Problem-solving and analytical thinking
- Data-driven decision making
- Business strategy development
- Professional report writing
0
Solutions Submitted
Difficulty
Beginner
Estimated Time
20 minutes
Relevance
Fresh
Source
Google Sheets Module - Puneet Arora