Capstone · Week 7
Payroll Plan
Set wage bands with lookups and compute gross to net using tax tables. Provide weekly and monthly summaries.
Scoring is on the capstone Rubrics.
What to include
Clear logic and totals
- Wage bands and XLOOKUP (or VLOOKUP) for pay rates.
- Tax tables and formulas for deductions.
- Register with per‑employee detail and totals.
- Short note: assumptions that affect payroll most.
Excel Requirements
Lookups, deductions, and summaries
- Employee table: name, role, band, hourly rate (via XLOOKUP on band table).
- Gross pay: hours × rate; apply overtime rules if used.
- Deductions: use tax brackets or a table; compute net pay = gross − deductions.
- Weekly and monthly summaries by employee and by role (use
SUMIF/SUMIFS).
Quality Checks
Accuracy and fairness
- Rates pull correctly from the wage band table.
- Tax math is consistent; spot‑check one employee across weeks.
- Totals match across weekly and monthly summaries.
Submission
What to turn in
- Excel file with wage bands, payroll register, and summaries.
- Short note (3–5 sentences) on payroll risks or edge cases.