A tutoring group · Hong Kong

Enrolment Excel and FPS exports: match a parent's payment to siblings

Enrolment Excel and FPS bank exports match the parent, not just the student: one transfer covers siblings after the sibling discount.

172.5 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 tutoring group runs 3 centres serving 650 families. Enrolment is kept in Excel; payments arrive in the FPS bank export under a parent's name. The account must connect the payer to the children and apply the sibling discount before matching the transfer.

At a glance

The files they already use

  • Enrolment Excel
  • FPS bank export
AI systembuilt around their rules
  • Parent is the payer; students remain separate fee records
  • Apply the sibling discount before matching
  • One transfer can settle siblings together

Done automatically

  • Family payment allocation and receipt drafts
  • Exceptions for review

Accounts staff verify family links and uncertain payments before approving receipts; a person sends the reminders.

How they used to do it

The office filters the enrolment workbook by student, then looks for a matching name in the bank export. The payer is often the parent, so the student's name alone cannot identify the incoming payment.

A family can pay for siblings together. The clerk checks the discount, allocates the transfer to each child, prepares receipts and updates the reminder list. Until allocation is complete, a paid child can still appear as outstanding.

The difficulties

  • Identify the family Student names and bank payer names are compared manually.
  • Apply sibling discount The clerk checks the discount separately before adding the fees.
  • Allocate FPS A combined transfer has to be split by hand across student accounts.
  • Prepare receipts The same payment is retyped into receipts after reconciliation.
  • Prepare notices Reminders can be based on a fee list that has not been reconciled.

Their formula, broken down

The Wong family pays for siblings in one FPS transfer. The discount is applied before the family total is matched.

Their formulaFamily amount due = first child's fee + second child's discounted fee; allocate the parent's transfer to both
  1. Keep the first child's fee

    The first child owes HK$3,300.

    HK$3,300HK$3,300

  2. Apply the sibling discount

    The second child's HK$3,300 fee receives a 10% sibling discount.

    HK$3,300 × (1 − 10%)HK$2,970

  3. Total and match the family payment

    The parent's single FPS transfer is matched to the combined amount due.

    HK$3,300 + HK$2,970HK$6,270

  4. Allocate to both children

    Both child records retain their own fee and the shared bank reference.

    HK$6,270 → HK$3,300 + HK$2,970Matched to both children

Family transfer matchedHK$6,270

What the system produces

sample.alveek.ai/tutoring-feesSample data

Source records

Enrolment ExcelFPS bank export
SourceRecord
First child's feeHK$3,300
Second child's fee / discountHK$3,300 / 10%
Parent's FPS transferHK$6,270

Family payment allocation and receipt drafts

Review sheet (Excel)Output for approval (PDF)
ItemResult / basis
First child allocationHK$3,300
Second child allocationHK$2,970
PayerWong family parent
Matched transferHK$6,270

How it was calculated

Family amount due = first child's fee + second child's discounted fee; allocate the parent's transfer to both

HK$6,270

Flagged for a person to confirm

  • A payer without a confirmed family link remains unmatched; staff confirm the relationship rather than assuming a student name.

In this example: The output retains the source records and the rule behind each item. Accounts staff verify family links and uncertain payments before approving receipts; a person sends the reminders.

How we helped

We connect the existing source files to a review sheet, with the company's rules recorded beside the results. Accounts staff verify family links and uncertain payments before approving receipts; a person sends the reminders.

  1. Identify the family The enrolment record links each child to the parent who pays.
  2. Apply sibling discount Sibling fees show the discount and the family amount due before bank matching.
  3. Allocate FPS An approved parent transfer is allocated to the linked children, keeping the bank reference.
  4. Prepare receipts Receipt drafts use the confirmed allocation without retyping the transfer.

What the system handles

Identify the family

The enrolment record links each child to the parent who pays.

Apply sibling discount

Sibling fees show the discount and the family amount due before bank matching.

Allocate FPS

An approved parent transfer is allocated to the linked children, keeping the bank reference.

Prepare receipts

Receipt drafts use the confirmed allocation without retyping the transfer.

Prepare notices

Outstanding-fee reminders and term notices are drafted from the reviewed family records for a person to send.

Before and after

TopicBeforeAfter
Identify the familyStudent names and bank payer names are compared manually.The enrolment record links each child to the parent who pays.
Apply sibling discountThe clerk checks the discount separately before adding the fees.Sibling fees show the discount and the family amount due before bank matching.
Allocate FPSA combined transfer has to be split by hand across student accounts.An approved parent transfer is allocated to the linked children, keeping the bank reference.
Prepare receiptsThe same payment is retyped into receipts after reconciliation.Receipt drafts use the confirmed allocation without retyping the transfer.
Prepare noticesReminders can be based on a fee list that has not been reconciled.Outstanding-fee reminders and term notices are drafted from the reviewed family records for a person to send.

How much time it saves

  • Matching: 650 × 6 min = 65 h; receipts: 650 × 5 min ≈ 54.2 h.
  • Reminders: 180 × 10 min = 30 h; term notices: 650 × 4 min ≈ 43.3 h; existing total = 192.5 h.
  • Exceptions take 20 h; estimated time saved: 192.5 − 20 = 172.5 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.5 h × HK$110 = HK$18,975, about HK$19,000 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

  • What if the transfer names the parent?
    The confirmed family link connects the payer to the children. Unconfirmed names wait for accounts staff.
  • Can one payment cover siblings?
    Yes. The family amount due is matched first and then allocated to the children's fee records.
  • Are reminders sent automatically?
    No. The system prepares drafts from reviewed records; a person checks and sends them.

The results

  • The enrolment record links each child to the parent who pays.
  • Sibling fees show the discount and the family amount due before bank matching.
  • An approved parent transfer is allocated to the linked children, keeping the bank reference.
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 →