← All articles

Music school tuition reconciliation spreadsheet: a practical layout

A music school tuition reconciliation spreadsheet should connect each family’s opening balance, lesson charges, payments and money credits without mixing them with makeup-lesson counts. Keep source records in separate tabs, summarize by a stable family ID, and hold uncertain entries for review. A family total is ready to explain only when you can trace its movements back to those records.

Set up five tabs with different jobs

Use one row per event, not one cell containing a month’s notes. Keep your original exports unchanged and build the working tabs from copies.

Tab One row represents Useful fields
Attendance One scheduled lesson Family ID, lesson ID, date, status, original charge, cancellation evidence
Payments One receipt Payment ID, family ID, date, amount, currency, source reference
Opening balances One family at the cutoff Family ID, as-of date, amount, owed or credit direction
Adjustments One approved change Adjustment ID, family ID, linked lesson or receipt, reason, amount, approval
Family summary One family for the period Opening balance, charges, payments, money credits, closing result, review status

For a multi-teacher school, include a teacher code in attendance if it helps you find missing records. The family ID must remain the same when a student changes teacher. Don’t use the teacher-and-student combination as the payment account.

How does a tuition reconciliation spreadsheet calculate the balance?

With positive numbers representing amounts owed, the operational family-balance calculation is:

Closing = opening + charges − payments − money credits

An opening credit is negative inside this calculation. Translate the result into “Amount owed” or “Credit remaining” on the statement; the spreadsheet’s sign convention should not become the family’s problem.

All amounts and IDs in this example are fictional. FAM-101 starts 1 Nov 2026 with USD 30.00 credit remaining. Four lessons are billed at USD 45.00 each. The family pays USD 150.00, and one already-billed lesson receives an approved USD 45.00 money credit.

Movement Calculation value Meaning
Opening −USD 30.00 Credit brought forward
Charges USD 180.00 Four billed lessons
Payments −USD 150.00 Receipt PAY-101
Money credit −USD 45.00 Reversal linked to LES-104
Closing −USD 45.00 Credit remaining: USD 45.00

If you instead exclude LES-104 from charges, charges are USD 135.00 and no separate reversal is needed. Don’t both omit its charge and subtract the credit. See the worked cancellation-credit calculation for this double-credit check.

Use SUMIFS only after the records are matched

Suppose Payments columns A, B and C contain family ID, payment date and amount. Summary cell A2 contains the family ID; H1 and H2 contain real spreadsheet dates for the period start and end. In Excel or Google Sheets, a payment total can use:

=SUMIFS(Payments!$C$2:$C$1000,
 Payments!$A$2:$A$1000,$A2,
 Payments!$B$2:$B$1000,">="&$H$1,
 Payments!$B$2:$B$1000,"<"&($H$2+1))

The end test includes the whole final day if the source has timestamps. Adjust the row limit to include your data. Amounts must be numeric, dates must be date values, and the tab should contain only the school currency being summarized.

SUMIFS adds matching rows; it doesn’t prove the payment belongs to that family or that the row appears only once. Resolve those questions before relying on the total.

Add controls that catch errors totals can hide

Before issuing statements, compare the total payments assigned to families with the included receipt total. List unassigned receipts separately; don’t force them onto a family to make the numbers agree.

Then check:

  1. Every attendance family has an opening record, including an explicit confirmed zero.
  2. Each payment and adjustment has one source reference.
  3. Credits reverse charges actually included in the ledger.
  4. Scheduled lessons have a recorded outcome, not a blank treated as absence.
  5. Opening balances and current movements use a documented, non-overlapping cutoff.
  6. Each unresolved item has a reason and an owner who will resolve it.

Matching school totals can hide two families’ payments being swapped. Check family identity and source rows as well as the grand total.

Freeze the approved period before starting another

Save the included files, rules, reviewed summary and statement version together in your secure school records. Start the next period from the approved closing balances, rather than copying last month’s payments into a new tab and hoping the filters exclude them.

Tufton’s public sample shows this source-to-balance structure with fictional records: select a movement to inspect the evidence and rule behind it. Use it to compare the explanation with what your own spreadsheet makes visible.