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.
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
- 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.
Keep the first child's fee
The first child owes HK$3,300.
HK$3,300HK$3,300
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
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
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
What the system produces
Source records
| Source | Record |
|---|---|
| First child's fee | HK$3,300 |
| Second child's fee / discount | HK$3,300 / 10% |
| Parent's FPS transfer | HK$6,270 |
Family payment allocation and receipt drafts
| Item | Result / basis |
|---|---|
| First child allocation | HK$3,300 |
| Second child allocation | HK$2,970 |
| Payer | Wong family parent |
| Matched transfer | HK$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.
- 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.
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
| Topic | Before | After |
|---|---|---|
| Identify the family | Student names and bank payer names are compared manually. | The enrolment record links each child to the parent who pays. |
| Apply sibling discount | The clerk checks the discount separately before adding the fees. | Sibling fees show the discount and the family amount due before bank matching. |
| Allocate FPS | A 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 receipts | The same payment is retyped into receipts after reconciliation. | Receipt drafts use the confirmed allocation without retyping the transfer. |
| Prepare notices | Reminders 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
- Read the actual source files. We review the existing inputs with the staff who prepare them and identify where each field comes from.
- Record the rules. We turn the agreed rules into visible checks, using completed records to compare the output with the office's own calculation.
- 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.
- 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.
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.
Related case studies
Also useful: Back-office outsourcing · Try a working demo · Free tools · 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 →