A beauty salon group · Hong Kong

POS and booking sales Excel to commission on treatments consumed

No clock: POS and booking sales exports distinguish consumed treatments from packages sold, with tiered commission and refund clawbacks.

172 hoursestimated time saved a month
TargetTraceablerules shown with the output
TargetReviewedexceptions reach a person

Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.

A beauty salon group has 4 shops and 38 therapists. Payroll uses POS and booking sales export Excel, not attendance punches. A package sale and a treatment consumed are different events, and only the right event should enter the commission calculation.

At a glance

The files they already use

  • POS / booking sales export Excel
AI systembuilt around their rules
  • Commission follows treatments consumed, not packages sold
  • Tiered rates are the company's own rules
  • Previously commissioned refunds produce a clawback

Done automatically

  • Therapist commission breakdown and payslip
  • Exceptions for review

The salon manager confirms consumption and refunds before payroll staff approve the payslip.

How they used to do it

The clerk gathers sales exports and treatment-use records, then works out what each therapist has actually delivered. Package sales can look like commissionable sales unless the office separates money received from treatments consumed.

Tiered treatment commission, product commission and refunds are calculated in separate sections. A refunded package commissioned previously needs a visible clawback, rather than being silently netted against current sales.

The difficulties

  • Separate consumption Package sales and consumed treatments are mixed in the sales workbook.
  • Apply tiers The clerk recalculates the treatment tiers by hand.
  • Keep product commission separate Product commission is merged into the treatment total without its own basis.
  • Trace refund clawbacks A refunded package requires a search for the earlier commission.
  • Review pay The payslip does not explain which transactions created the commission.

Their formula, broken down

Therapist Lam has base pay of HK$13,000. The tiered commission rates below are the salon's own rules.

Their formulaGross pay = base + consumed-treatment commission + product commission − refund clawback
  1. Split consumed treatments into tiers

    Consumed treatments total HK$92,400: first HK$80,000 at 8%, remaining HK$12,400 at 10%.

    HK$6,400 + HK$1,240HK$7,640

  2. Add products; exclude unused packages

    Products are HK$6,850 at 10%; packages sold but unused are HK$18,000 and earn HK$0 this month.

    HK$6,850 × 10%HK$685

  3. Claw back the refund

    The HK$3,200 refunded package was commissioned last month at 8%.

    HK$3,200 × 8%−HK$256

  4. Combine pay and deduct MPF

    Gross HK$21,069.00; employee MPF HK$1,053.45.

    HK$13,000 + HK$7,640 + HK$685 − HK$256 − HK$1,053.45HK$20,015.55

Net pay to LamHK$20,015.55

What the system produces

sample.alveek.ai/payroll-salonSample data

Source records

POS / booking sales export Excel
SourceRecord
Treatments consumedHK$92,400
ProductsHK$6,850
Packages sold but unusedHK$18,000
Previously commissioned refundHK$3,200

Therapist commission breakdown and payslip

Review sheet (Excel)Output for approval (PDF)
ItemResult / basis
Treatment commissionHK$7,640
Product commissionHK$685
Unused packages this monthHK$0
Refund clawback−HK$256
Gross / employee MPFHK$21,069.00 / HK$1,053.45
Net payHK$20,015.55

How it was calculated

Gross pay = base + consumed-treatment commission + product commission − refund clawback

HK$20,015.55

Flagged for a person to confirm

  • A refund without its earlier commission record waits for confirmation. Package sales do not become treatment consumption by assumption.

In this example: The output retains the source records and the rule behind each item. The salon manager confirms consumption and refunds before payroll staff approve the payslip.

How we helped

We connect the existing source files to a review sheet, with the company's rules recorded beside the results. The salon manager confirms consumption and refunds before payroll staff approve the payslip.

  1. Separate consumption POS and booking exports separate treatments consumed from packages still unused.
  2. Apply tiers Consumed treatment value is split into the company's commission tiers, with each band shown.
  3. Keep product commission separate Product sales retain their own rate and commission line.
  4. Trace refund clawbacks A previously commissioned refund appears as a separate clawback for the manager to verify.

What the system handles

Separate consumption

POS and booking exports separate treatments consumed from packages still unused.

Apply tiers

Consumed treatment value is split into the company's commission tiers, with each band shown.

Keep product commission separate

Product sales retain their own rate and commission line.

Trace refund clawbacks

A previously commissioned refund appears as a separate clawback for the manager to verify.

Review pay

Base pay, treatment commission, product commission, clawback and MPF stay visible together.

Before and after

TopicBeforeAfter
Separate consumptionPackage sales and consumed treatments are mixed in the sales workbook.POS and booking exports separate treatments consumed from packages still unused.
Apply tiersThe clerk recalculates the treatment tiers by hand.Consumed treatment value is split into the company's commission tiers, with each band shown.
Keep product commission separateProduct commission is merged into the treatment total without its own basis.Product sales retain their own rate and commission line.
Trace refund clawbacksA refunded package requires a search for the earlier commission.A previously commissioned refund appears as a separate clawback for the manager to verify.
Review payThe payslip does not explain which transactions created the commission.Base pay, treatment commission, product commission, clawback and MPF stay visible together.

How much time it saves

  • Existing work: 38 × 4.5 h + 4 shops × 6 h = 195 h.
  • Review: 38 × 30 min + 4 × 1 h = 23 h.
  • Estimated time saved: 195 − 23 = 172 h a month.
  • The estimate values office time at HK$110 an hour (about HK$19,000 a month for a clerk, MPF included); it is not an audited cash return.
  • 172 h × HK$110 = HK$18,920, about HK$18,900 a month.

These are the working assumptions behind the figure, not an audited result.

How a project like this runs

  1. Read the actual source files. We review the existing inputs with the staff who prepare them and identify where each field comes from.
  2. Record the rules. We turn the agreed rules into visible checks, using completed records to compare the output with the office's own calculation.
  3. Run alongside the existing process. The office keeps its normal process while the system handles the same files. Staff compare the results and resolve differences before switching.
  4. Use and maintain it. Once the results agree, staff use the review sheet and handle its flags. Changes to source formats or company rules are checked before they are applied.

Questions owners usually ask

  • Do package sales earn commission immediately?
    This example commissions treatments consumed. Unused package sales are kept out of the month's treatment commission.
  • Are the tiers prescribed by law?
    No. They are the salon's own commission rules and are confirmed before use.
  • What happens to a refunded package?
    If it was previously commissioned, the prior rate and commission record support a separate clawback for review.

The results

  • POS and booking exports separate treatments consumed from packages still unused.
  • Consumed treatment value is split into the company's commission tiers, with each band shown.
  • Product sales retain their own rate and commission line.
PythonClaudeExcelWhatsAppEmail

Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.

← Back to all case studies

Does your company have a formula like this?

Send us one real example — a quotation, an invoice or a month-end statement — and we will show you how the system would work it out. The assessment is free.

Get a free assessment →