Forum Discussion
Smart Tuition Management
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