Pandora Fitness: 12 hours → 15 minutes
Automated monthly commission calculations for 30+ personal trainers across 2 branches. Eliminated salary disputes. 7 iterations over 6 months to production.
The Problem
Manual commission calculations were breaking trust.
Pandora Fitness Center operates two branches — ADV and PMH — with over 30 personal trainers and membership sales (MBS) staff. Every month, the management team spent 12+ hours manually calculating commissions in Google Sheets.
The process was painful:
- Export sales data, attendance records, and staff lists from multiple Google Sheets
- Cross-reference each trainer's sales against commission rate tiers
- Calculate teaching salaries based on attendance logs with different formats per branch
- Manually verify package prices against bill data to catch mismatches
- Generate separate reports for each branch, with branch-specific rules
The result? A 5-10% error rate. When errors hit salaries, trainers complained. Disputes eroded trust. Managers dreaded the end of every month.
The Solution
A Python-based automation system with built-in verification.
I built a 3-file Python architecture that reads directly from the exported Google Sheets data, applies complex commission rules, and generates production-ready Excel reports — complete with color-coded verification.
Key Technical Features
Multi-Branch Architecture
Shared library (commission_lib.py) with branch-specific scripts.
ADV and PMH have different bill filters, attendance tab formats,
and manager calculation rules.
Fuzzy Name Matching
Vietnamese names are hard to match exactly. The system uses diacritic stripping and token-level similarity scoring to match staff names across different data sources, even with typos.
Color-Coded Verification
Output spreadsheets highlight mismatches automatically. Red rows = price mismatch between auto-calculated and input data. Yellow rows = package not found in reference data.
Auto-Versioning
Every run generates a new version (v1, v2, v3...). No file ever gets
overwritten. Full audit trail. The --temp flag lets you
test without incrementing.
Per-Staff Salary Reports
Any staff member can request a detailed breakdown of their salary. A single command generates their individual report with all line items.
Operator Guide
Complete documentation: month-by-month checklist, configuration changes, troubleshooting for common issues (duplicate names, manager mismatches).
The Evolution
7 versions. Each one solved a real problem.
Single file, ADV only
CODE normalization. First working automation.
ADV + PMH support
BRANCH_CONFIG pattern. Manual manager overrides.
Pack verification
PMH branch got package price verification.
Auto price + color coding
Automated price lookup. Color-coded error highlighting.
3-file split architecture
Shared library + branch scripts. Auto-versioning. Per-staff salary detail reports. Production-grade.
The Results
Numbers that speak for themselves.
Tech Stack
Your team's spreadsheet nightmare looks familiar?
I've built this system for a fitness center. The same pattern works for retail, hospitality, professional services — any business with recurring calculations.
Let's Automate It