Reconciling spa packages is not about counting again, it is about finding which lines you should not trust. A duplicate entry, a session used after expiry, a session recorded before the package was even sold: each one makes the "remaining" number wrong without anyone noticing. AI reads the whole ledger fast and points out the odd lines; the spreadsheet does the adding; the person checking the documents decides which line is an error.
This is article 2 in a two-part series on spa packages. The ledger needs to be in event-line form, as in article 1.
The task and the outputs
- A per-package table: total sessions granted, used, refunded, remaining per ledger, expiry, status.
- Formulas so you can recalculate it yourself.
- An exceptions list: which line, why, who checks which document.
- How the remaining count changes if an exception is an entry error.
- A reconciliation sheet handed to the person in charge.
Step 1: prepare the ledger and the answer
Export the ledger as CSV or paste it from the spreadsheet. Client codes are fake. Simulated ledger used in the test (shortened; the full version has 24 lines):
txn_id, date, client_code, package_code, type, sessions, amount, used_by, notes
G01, 2026-06-03, KH01, P01, sale, 10, 2500000, , 10-session package; expires 2026-12-03
G03, 2026-06-12, KH01, P01, use, -1, 0, KH01, 10:00 slot staff Thu
G04, 2026-06-12, KH01, P01, use, -1, 0, KH01, 10:00 slot staff Thu
G06, 2026-06-10, KH02, P02, sale, 5, 1750000, , 5-session package; expires 2026-09-10
G07, 2026-06-10, KH02, P02, bonus, 1, 0, , 1 bonus session
G11, 2026-07-08, KH02, P02, return_session, 1, 0, , Spa canceled the booking after the session was deducted
G12, 2026-05-01, KH03, P03, sale, 10, 1200000, , Package shared by KH03 and KH03-M; expires 2026-11-01
G18, 2026-04-28, KH03, P03, use, -1, 0, KH03-M,
G22, 2026-07-26, KH04, P04, refund, -4, -600000, , Refund 50% of the value of 4 unused sessions
G23, 2026-09-15, KH02, P02, use, -1, 0, KH02,
G24, 2026-08-02, KH05, P05, use, -1, 0, KH05, Work it out first in a spreadsheet: P01 has 6 left, P02 has 3 left, P03 has 4 left, P04 has 0 left, P05 is minus 1.
Step 2: ask AI to reconcile
You are reconciling the remaining sessions in a spa's prepaid packages. Reconciliation date: [date]. PACKAGE LEDGER (CSV): [paste]. TASKS: 1. Per-package table: package code | client code | total sessions granted (sale + bonus + returned) | used | refunded/canceled | remaining per ledger | expiry | status. 2. A spreadsheet formula (Google Sheets) to calculate "remaining per ledger" for each package from the sessions column, so the checker can recalculate it instead of trusting your numbers. 3. A list of EXCEPTIONS: each unusual transaction line, why it is unusual, and who needs to check which document. Do not delete or edit any line yourself. 4. For each package with an exception, say how the remaining count changes if the checker confirms that exception is an entry error.
Results from the test run
Tổ Thạo Việc (the AI Thạo Việc editorial team) ran it on 2 Oct 2026 in ChatGPT web, temporary chat, Medium level.
The per-package table matched the answer: P01 has 6 left, not expired; P02 has 7 sessions granted (5 + 1 bonus + 1 returned), 4 used, 3 left, expired on 10/09; P03 has 4 left; P04 6 - 2 - 4 = 0; P05 is minus 1 because there is no sale line.
The formula to calculate it yourself, assuming the ledger is in a sheet named PACKAGES, column C client code, D package code, F sessions:
=SUMIFS(PACKAGES!$F:$F, PACKAGES!$D:$D, $A2, PACKAGES!$C:$C, $B2)AI advised against calculating "granted minus used" from numbers typed in by hand, because it is easy to miss returned-session and refund lines.
All exceptions found:
| Line | Reason | Who checks |
|---|---|---|
| G03, G04 | Same client, package, date, slot, staff member: suspected duplicate | Receptionist and cashier check the booking and service slip |
| G23 | Used 15/09, package expired 10/09 | Manager checks whether an extension was approved |
| G18 | Used 28/04, before the sale date 01/05 | Cashier and accountant check the invoice, conversion of single sessions |
| G24 | Package P05 has no sale line | Cashier checks the sales slip or a wrongly entered code |
AI did not treat KH03-M's use as an error because the package is recorded as shared, and did not treat the returned-session line G11 as an error because it has a reason. The "if it is an error" part was also right: P01 only becomes 7 if one of the two suspected duplicate lines is removed, not 8. AI also added a point worth keeping: P02 has 3 sessions left per the ledger, but the package has expired, so the number of sessions the client is still entitled to use may be 0, depending on policy.
What AI missed: it did not check the refund amount in G22. 600,000 VND is only right if calculated as 4 sessions x 300,000 VND (average price 1,800,000 VND divided by 6) x 50%. If the spa uses the single-session price, the number is different. The table in item 4 also repeated P01 on two lines, which is easy to misread.
Step 3: check and finalize
- Compare AI's table with your spreadsheet. Use the formula, not the numbers AI gives.
- Make a reconciliation sheet for each exception: line, reason, person in charge, documents to check, deadline to respond.
- The person in charge checks the documents and writes the conclusion on the sheet: correct, or an entry error.
- Correct with an adjustment line, without deleting the original line: for example "G25, adjustment +1, removes duplicate G04, approved by manager".
- Check the refund amount against the agreed refund policy, not against AI's math.
- Tell the client their session count only after the sheet is closed.
Self-check
- Does the remaining count come from the formula, and does it match AI's table?
- Does each exception have a specific checker and document?
- No original line deleted?
- For expired packages, are "remaining per ledger" and "still entitled to use" kept separate?
- Has the refund amount been checked against the policy?
Put it to use and hand it over
Reconcile at the end of every month or before answering a client who asks about their sessions. Exceptions that keep coming back (for example duplicates in the same slot) are a sign to fix how entries are made: record right when the client signs the slip, with one person recording. Keep the reconciliation sheets by month so next time you know what was already handled.
Next
If your ledger is not yet in event-line form, go back to article 1. To see whether prepaid packages really keep clients coming back, read the article on measuring results at a spa.
Sources & notes
- Square Support Center: Sell and redeem packages: point-of-sale software redeems packages when clients use a service; the page does not detail how to track remaining sessions or expiry, so a spa still needs its own rules like those in this article. Checked 2 Oct 2026.
- The ledger is simulated and the client codes are fake; the prompt was run once on ChatGPT. No commercial relationship with the sources above.
