Tutoring business spreadsheet: track lessons, packs, make-ups and payments
By the BizDocs Studio team · Updated
- •A tutoring business spreadsheet needs four lists: students, a lesson log, purchases (packs, tuition and fees) and payments. Everything else, from pack balances to invoices, should be calculated from those four.
- •Log every scheduled lesson with what happened: attended, cancelled late, cancelled with notice, no-show, make-up or teacher cancelled. Your policy decides which of those use a lesson or cost money.
- •Take pack lessons from the oldest pack first and give each pack an expiry date, so a family can always see lessons used, lessons left and the date they run out.
- •For monthly tuition, track make-up credits apart from money: teacher cancellations always earn one, student cancellations with notice earn one up to a limit per term.
- •Divide what you collected by the hours you actually taught to get your real hourly rate. Look at three months or more, because prepaid packs make single months jumpy.
Most independent tutors and music teachers start with a calendar and a notes app. That works until the first family asks how many lessons are left in their pack, or a parent cancels the morning of the lesson and you cannot remember whether your policy counts it.
A good spreadsheet answers those questions in seconds and turns each month into an invoice you can send. This guide covers what to track, how pack balances and make-ups should work, a worked example with real numbers, and the mistakes that make free templates fall apart once you have more than a dozen students.
What to track
Keep the inputs small and let formulas do the rest. Each row below is something you type; the right column is what the spreadsheet should work out from it.
| List | What you type | What it should calculate |
|---|---|---|
| Students | Name, parent or guardian, contact, lesson type and length, billing plan, start date, weekly day and time | Rate, lessons taught, pack lessons left, make-ups owed, balance or credit |
| Lesson log | Date, student, minutes, what happened, notes | Hours taught, which pack the lesson came from, lesson charge, make-up credit |
| Purchases | Date, student, pack, tuition month or fee | Amount, lessons bought, expiry date, used, left, pack status |
| Payments | Date, student, amount, method | Balance per family and money collected per month |
Settings hold the rules: rates by lesson type and length, your packs and their expiry, your notice period, whether a late cancellation uses the lesson, and your make-up limit.
How lesson pack balances should work
Packs (for example 4, 8 or 10 prepaid lessons) cause most of the arguments, so the rules need to be written down and applied the same way every time. A sensible default: Attended, Make-up, No-show and late cancellations use a lesson. Cancellations with enough notice and teacher cancellations do not. Lessons come out of the oldest pack that has not expired.
Pack: 10 lessons of 60 minutes for $680, valid 120 days. Bought 08/05, so it expires 12/03.
August to early October: 9 lessons attended, plus a no-show on 09/16. The no-show uses a lesson, so 10 are used by 10/07 and the pack is used up.
10/14: the family cancels with notice (no lesson used) and buys a second pack for $680, paying $400 by Venmo.
10/21 and 10/28: two lessons attended, taken from pack #2. Pack #2 now shows 2 used, 8 left, use by 02/11.
October invoice: previous balance $0, lesson charges $0 (covered by the packs), pack $680, payment $400. Balance due: $280.
Two edge cases trip up most templates. First, a pack that expires with lessons unused: show it as expired with the unused count, and do not quietly move those lessons into the next pack unless your policy says so. Second, a lesson taken after a pack runs out but before the family renews: bill it at the single-lesson rate until the next pack is bought, then take it out of that pack.
Monthly tuition and make-up credits
Many music studios charge flat monthly tuition instead of packs: lessons per year x rate / 12. At 42 lessons a year and $58 for 45 minutes, that is $203 a month, rounded to $205. Tuition does not change when a student misses a lesson, so missed lessons are handled with make-up credits, tracked apart from money.
- Teacher cancels: the student is owed a make-up, always.
- Student cancels with notice: a make-up is owed, up to a limit. Two per term is common.
- Student cancels late or does not show: no make-up.
- Make-up lesson taught: one credit used.
Example: a voice student cancels with notice on 09/15, 09/22 and 09/29, all in the fall term, with a limit of 2 per term. The first two earn credits, the third is marked 'allowance used'. One make-up is taught on 10/03, so the student is owed one more. In January the new term starts and the count resets.
Students, lesson packs, make-ups and payments in one workbook, with a one-page monthly invoice per family.
The numbers worth watching
- Collected per teaching hour: money received divided by hours actually taught. In our sample studio, October brought in $4,665 for 72.75 hours, or $64.12 an hour. Over three months it was $15,125 for 195.5 hours, about $77 an hour. August looked like $101 an hour only because several families prepaid packs.
- Prepaid lessons still to teach: the unused lessons in active packs and what they are worth. This is money you have collected for work you still owe.
- Missed lessons: late cancellations and no-shows per month. A rising count usually means your policy is not being enforced or not being communicated.
- Students kept from last month: the share of last month's students who had a lesson this month. A drop in September or January is normal. A drop in a normal month deserves a phone call.
- Results by lesson type: hours and income for piano vs math tutoring vs test prep. Some subjects fill your best hours at a lower rate.
Mistakes to avoid
- Tracking only payments. Without a lesson log you cannot answer 'how many are left?' or prove a no-show.
- Subtracting lessons from a single running total. With two packs, one expired, the total is wrong. Track each pack with its own expiry.
- Mixing make-up credits and money. A make-up is time you owe, not a refund. Keep them in separate columns.
- Rules that live in your head. If late cancellations sometimes count and sometimes do not, write the rule down, and record exceptions as exceptions so the log stays honest.
- Invoices typed by hand. Build the invoice from the log, purchases and payments, so the balance on paper matches the balance in the spreadsheet.
- No previous balance on the invoice. Families pay what the invoice says. If last month's unpaid amount is missing, it is usually never paid.
Frequently asked questions
Should a late cancellation count as a used lesson?
Most tutors and music teachers say yes when the notice is shorter than their window (24 hours is common), because the time was reserved and cannot be resold. Whatever you choose, put it in your written policy and apply it the same way for every family. Allow yourself a documented exception for emergencies.
How long should a lesson pack be valid?
Long enough to use at the planned pace, plus some slack: around 45 days for a 4-lesson pack and 90 to 120 days for 8 or 10 lessons at one lesson a week. State the expiry date on the invoice so it is never a surprise.
What should a tutor invoice include?
Your business name and contact details, an invoice number and date, a due date, the student and who pays, each lesson in the month with its date and result, packs, tuition or fees bought, payments received, the previous balance and the balance due. Add how to pay and a one-line summary of your cancellation policy.
Can I use Google Sheets for this?
Yes. Use functions that Excel and Google Sheets share (SUMIFS, COUNTIFS, INDEX and MATCH). Avoid newer Excel-only functions if families or a partner use Sheets.
Do I need software instead of a spreadsheet?
Studio software adds online booking, autopay and a parent portal for a monthly fee. If you invoice families yourself and want to own your records, a well-built spreadsheet covers lessons, packs, make-ups, balances and invoices for a one-teacher studio.
This guide is general information for US small businesses, not legal advice. Rules vary by state and city; have a local attorney review contracts and notices before use.
Students, lesson packs, make-ups and payments in one workbook, with a one-page monthly invoice per family.
