Expert guide
Salon Commission Tracker in Excel
Every stylist paid by the same rules: tiers, retail, product deductions and wage guarantees
Call us at +1 (385) 386-3860
Short answer: import the POS or booking system sales export (staff, date, line type, amount, tips) into an Excel Table each pay period; keep each stylist's plan (tier table, retail rate, back-bar deduction, base hourly guarantee) in a Plans table; calculate tiered service commission with SUMPRODUCT, add retail commission, compare with the base wage guarantee, and export the result for payroll. For payroll itself, see the Excel employee payroll manager.
Commission is the most sensitive number in a salon. Stylists check it carefully, and a mistake or a rule applied differently for two people damages trust quickly. Many salon owners calculate it by hand from POS reports each pay period, which takes hours and invites errors when tiers, retail rates and product deductions all apply. A commission workbook built on the raw export applies the rules identically every time.
The Inputs
| Table | Columns |
|---|---|
| tblSales (POS export) | Date, Staff, Client, LineType (Service/Retail), Item, Amount, Discount, Tip |
| tblPlans | Staff, PlanName, RetailRate, BackBarPerService, BaseHourly |
| tblTiers | PlanName, From, To, Rate |
| tblHours | Staff, PeriodStart, HoursWorked |
| Example tier plan | From | To | Rate |
|---|---|---|---|
| Senior stylist | $0 | $2,000 | 35% |
| Senior stylist | $2,000 | $3,500 | 40% |
| Senior stylist | $3,500 | no limit | 45% |
Per-Stylist Calculation
Period in Settings!PeriodStart and Settings!PeriodEnd. On a Payroll sheet, one row per stylist:
Service revenue (net of discounts):
=SUMIFS(tblSales[Amount], tblSales[Staff], [@Staff], tblSales[LineType], "Service",
tblSales[Date], ">=" & Settings!PeriodStart, tblSales[Date], "<=" & Settings!PeriodEnd)
- SUMIFS(tblSales[Discount], tblSales[Staff], [@Staff], tblSales[LineType], "Service",
tblSales[Date], ">=" & Settings!PeriodStart, tblSales[Date], "<=" & Settings!PeriodEnd)
Service count:
=COUNTIFS(tblSales[Staff], [@Staff], tblSales[LineType], "Service",
tblSales[Date], ">=" & Settings!PeriodStart, tblSales[Date], "<=" & Settings!PeriodEnd)
Commissionable service revenue (after back bar):
=MAX(0, [@[Service revenue]] - [@[Service count]] *
XLOOKUP([@Staff], tblPlans[Staff], tblPlans[BackBarPerService], 0))
Tiered service commission:
=LET(plan, XLOOKUP([@Staff], tblPlans[Staff], tblPlans[PlanName]),
rev, [@[Commissionable revenue]],
f, FILTER(tblTiers[From], tblTiers[PlanName] = plan),
t, FILTER(IF(tblTiers[To] = "", 9E+99, tblTiers[To]), tblTiers[PlanName] = plan),
r, FILTER(tblTiers[Rate], tblTiers[PlanName] = plan),
SUMPRODUCT(IF(rev > f, IF(rev < t, rev, t) - f, 0) * r))
Retail commission:
=SUMIFS(tblSales[Amount], tblSales[Staff], [@Staff], tblSales[LineType], "Retail",
tblSales[Date], ">=" & Settings!PeriodStart, tblSales[Date], "<=" & Settings!PeriodEnd)
* XLOOKUP([@Staff], tblPlans[Staff], tblPlans[RetailRate], 0)
Total commission: =[@[Service commission]] + [@[Retail commission]]
Base wage: =XLOOKUP([@Staff], tblHours[Staff], tblHours[HoursWorked], 0)
* XLOOKUP([@Staff], tblPlans[Staff], tblPlans[BaseHourly], 0)
Gross pay (higher of): =MAX([@[Total commission]], [@[Base wage]])
Tips: =SUMIFS(tblSales[Tip], tblSales[Staff], [@Staff], ...same period...)With revenue of $4,000 on the plan above, the tiered commission is 35% × $2,000 + 40% × $1,500 + 45% × $500 = $700 + $600 + $225 = $1,525. A worked example like this, in a test cell, is the best way to confirm the formula before payroll day.
Stylist Statements
One printable or PDF statement per stylist: services, revenue, back-bar deduction, each tier's slice, retail, tips and gross pay.
Showing each tier's contribution makes the number easy to check and reduces questions.
Lock the period once payroll is submitted; corrections go in the next period as adjustments.
Management View
| Metric | Why it matters |
|---|---|
| Service revenue per hour worked | Productivity per stylist |
| Retail as % of service revenue | Retail habit; often a training opportunity |
| Rebooking rate | Clients booking their next visit before leaving |
| Commission as % of revenue | Payroll cost control |
| Average ticket | Upselling of add-on services |
Where AI Helps
AI can explain a stylist's statement in plain language, summarise the period for the owner (top performers, retail trends, anomalies such as unusual discounts) and help design a new commission plan by modelling its cost against past sales. The modelling itself should be done with the formulas above on real data. Packaging the tier logic as a reusable function is shown in the Excel LAMBDA guide.
Multi-location groups consolidating several POS exports can automate the import with Excel data consolidation; agencies with similar pay-versus-bill logic should read the staffing timesheet tracker.
Designing a Fair Commission Plan With Your Own Data
Before changing a commission plan, model it on the last six to twelve months of real sales. A new plan that looks generous on paper can cost far more than expected, or quietly cut the pay of your best stylists.
Copy the Plans and Tiers tables to a Scenario sheet and adjust thresholds and rates there.
Recalculate each stylist's pay for every past pay period under both the current and proposed plan.
Compare total commission cost as a percentage of service revenue, and each stylist's change in pay.
Check retail incentives: does the new plan reward rebooking and product sales the way you intend?
Share the per-stylist comparison privately before announcing the change.
This turns a sensitive conversation into a transparent one: each stylist can see exactly how the new plan would have paid them last quarter. It also protects the business from rolling out a plan whose cost only becomes clear after the first month.
FAQ
Salon commission tracker FAQ
Tiers, retail, back bar and tips