Categories: Adding Monthly pricing and subscriptions

Google Sheets
1

I manage a gymnastics school and have put all of our class options into the Categories section. I would like the ability to add a tab with pricing and a number of students that we have buying each category. How can I do this so that it accurately adds up all of our income? Is there a way to do it month over month with a dropdown similar to the monthly budget view? Maybe keep the formatting the same but make the columns to the right of the cost of the class, estimated student count, and actual student count.

2

:wave: @chris4!

You can create a new sheet in your Google Sheet and add data validation to column A for example and just have it reference the Categories sheet where you can then select the categories associated with the pricing option. From there just add the extra columns you’d need to get the totals you want to see. Perhaps one for “price” as an amount, “estimated students,” and “actual students” - you could even have one for each month (estimated vs actual) and build some calculations there

OR if these are income type categories, you could just use the budgeting features on the Categories sheet to budget for your estimated students per each category and then each transaction that comes through for a payment for a specific class when categorized with the appropriate class name would get you the actuals. Then you’d get that month over month view available in the Monthly Budget sheet.

There are many ways to do it, but if you’re just looking for a way to estimate what the budgets should be I’d recommend the separate tab option. Just click the plus icon in the lower left corner of your Google Sheet to add a new tab, and you can read more about data validation columns here:

Or just look at the way the data validation is set up on the Transactions sheet for the category column and mimic it.

3

Hi Heather,

Here is what I have done so far:

I have added a new tab by duplicating the “monthly Budget” tab and naming it the “Monthly Enrollment” tab. I then made the following changes so far:

  1. I added a price column that will list the price of each class or offering at our facility. I then changed “Budget” to “Estimated Enrollment”. This will be the number of students we estimate to have enrolled in that particular class. This will need to be changed to just a solid number rather than $, but I am wondering if this will break anything for the rest of the Sheets.

  2. I changed “Actual” to “Enrollment”. This will be the actual number of students enrolled for that month.

  3. I changed “Available” to “Income”. This will multiply the price cell for that offering by the actual enrollment to show what our income is for that month. I would like this to then link to the Yearly tab for the actual for that month. How do I do that? And how can I link the price and the estimated enrollment number to the categories tab for our budget?

4

Hi @chris4,

I’m not sure how you’d do that. These modifications are well beyond the “out of the box” solution that I know how to support.

Most of the Monthly Budget sheet is powered on formulas so the modifications you’ve made, while they might get the aesthetic and layout aspects you desire, are unlikely to totally meet your need here for the calculations to work.

If it’s a matter of labeling you could well just think of budget = estimated enrollment and actual = enrollment and just understand that and layout your budget using the tool the way it’s built vs trying to re-invent it. These would all be “income” type categories. :slight_smile: