Forum Discussion
Smart Tuition Management
Silver_Mary This is REALLY hard without having an example sheet from you (please no personal info) to know what format you are using and what data you are collecting and such. That said, I would like to say a couple things:
You can use conditional formatting to highlight cells/lines that meet certain conditions
Instead of highlighting based on 10th visit you might want track and highlight based on amount owed. I say this because at some point you may need to have someone on a payment plan or maybe would prefer to pay as they go. So I would track how much they owe and then use conditional formatting to highlight if it is >=10 x cost per session
For a good flexible system you may want
1 sheet purely be session records (i.e. date, client ID, duration or units used so you could track double sessions and such)
1 sheet be payments (i.e. date, client ID, payments)
Then you can have 1 or more reporting sheets that tabulate the data. You can have 1 showing how much each client owes, you could have others to do all kinds of things like create individual summaries for clients to show how many sessions they attend per month, how much business you are doing, etc..
I don't know if that is helpful.
Hi Dear mtarler
Here I tried to give you all the specific data, so that it be easier for you to help me. I hope that I can show you my need clearly. And please notice that these are absolute raw data and explanation and I didn’t know the best way for making different tables and maintenance. To be really honest : I don't know anything at all...
I’m really grateful in advance because of your kind help and your time and knowledge
These are our data :
- Classes : Time, Tuition, Coach Name, Class Name
- Payment : Date (everyone paid), Expire Date (which I must count for 12 session after everyone paid)
- Members : Name, Class they choose, Payment Info, 12/13 Sessions (Present and absent /sometimes I cancel the session)
Now, What I NEED :
I Need 2 Things
- I just need to be able to manage all of data in a glance
For example, I Open my sheet, and I see : Mary and 2 Others, Has been used the class for 10 session and I must tell them to pay for the next 4 day - I need to pay to my coaches Based on the count of present sessions in that class Until the last day of that month
For example, Ellen Have 3 yogini in her class and the total count of present sessions is 14. I must pay 14*(tuition/12)
Important : everyone start the class in a different day and they pay tuition before they start the class, It’s not like they come and use the class and pay at the end of month for the sessions they've been used
Thank you so much, you have no idea that how much I'm in need of good formulas and different tables for maintaining y data in a clean way...
I'll be happy ever after you respond me :))
Wish you the bests in the world
Silver_Mary Hi. So I started playing with that file last night and noticed a few things and had a few questions:
- You have a Cancelled and you also track Absent, so how do both of those items factor into the Member bill and Coach paycheck
You are locking member to class to coach and only show/track the last payment. Here are some concerns about that:
- What if the coach is sick or something and there is a substitute?
- What if there is a discrepancy on payment history?
So in the ideal case you would have multiple tables to track class - teacher, class - attendance, member - payment, teacher - info, member - info, member - class registration, etc...
But this is excel and not a relational database so I was thinking how to keep it most simple but keep needed functionality and flexibility. My thoughts below would keep it close to the format you have and are comfortable with but add some flexibility. That does mean it lacks additional flexibility/function but I am also not looking to recreate a relational accounting database in excel for your company (I don't have that much volunteer time and besides then you would have something much more complicated that YOU would have to maintain). So for example, if Mary takes both Swing and Yoga in my suggestion below she would be treated like 2 separate people, a Mary in Swing and a Mary in Yoga (so her $590 payment would need to be split and entered separately). Also, there is some inefficiencies like having to repeat information that normally in a relational database you would set once in a table that has a 1 to many relationship with another table. That said, my thought is to keep your main table but:
- remove coach name column and instead of a "1" for attendance, using a unique ID for the coach (e.g. "ES" for Ellen Smith) EDIT: I forgot to mention the reason for this is so you could track if the class had a substitute or change in coaches for some reason.
- change Class Name to Class ID (could be essentially the same but instead of Yoga make it Yoga-0001)
- change tuition paid and payment date to Last payment and/or Next payment due or ... AND locking that column since it would be formula based columns
- EDIT: also maybe add class tuition and class session count to help with calculations for payments and give flexibility for different prices and # of sessions
Then:
- adding a new SHEET for payments that would simply be the Date, Personal ID, Payment, and maybe comments (e.g. payment type, check number, credits, etc...)
- maybe adding a new sheet for coaches for their info and payment due formulas