The Category Schedule allows you to plan, list and track the specific items of a budget category, roll up monthly totals, and dynamically link the plans to drive your budget on the Category sheet. The solution is a brainstorming/planning tool. Consider which of your categories have specific events, costs or activities for which you’d like to make a budget plan.
The tool assumes there are two, mutually exclusive, types of scheduled activities in a category: discreet and baseline.
- Discreet: These are the specific, namable, budget amounts associated with a category. In a category for Clothing, for example, a discrete item might be the proposed cost of a suit or jacket you plan to buy.
- Baseline: This is an amount set aside in your budget for general spending periodically in the category. In addition to specifically budgeted items (like the suit or jacket) you may wish to set aside a monthly amount for clothing to cover unknown expenses.
The idea is to list the proposed components of a budget category, both baseline and discreet, so that the planned activity becomes more visible and manageable, driving the monthly budget roll-up
Watch the screenshare demonstration video to see how this template works.
- Open your Tiller Money template
- Install or launch the Tiller Community Solutions add-on
- Open the add-on and choose "Builder Contest” from the tags dropdown on the Explore tab
- Click on “Category Schedule”
- Choose “Add to Spreadsheet”
Here’s a quick overview of the Category Schedule’s components, referencing the diagram below.
- Active: A checkmark in this box keeps the item active in your budget. Take the check mark out, and the item will remain in your list for reference, but the budget amounts associated with it drop out of all totals.
- Category: From your list of categories, select the one related to the item you’re scheduling.
- Subset: Create and enter a grouping reference for a set of items to schedule. (Note: this is different from the use of Groups throughout the Tiller universe.)
- Detail: Create an additional level of specificity to further identify the item to schedule.
- Tags: This item is created for you, on the fly, as a combination of the Subset and the Detail entries. The absence of spaces is intentional.
- Total: The automated sum of your monthly amounts.
- Jan-Dec: The monthly budget amounts of the schedule item.
All of the data in this section are dynamically created as you enter data in the lower, planning section. Don’t enter data in the upper section.
- Category: Each category associated with a schedule item in the lower section will appear here.
- Budget (time frame): The sum of all budgeted items in that Category
- Budget Trend: Graphical representation of the budget trends.
- Actuals: From your Transaction sheet, the sum of all actual activity associated with the Category.
- Budget Total: The sum of the scheduled items for this category.
- Jan-Dec: The roll-up sum of the scheduled items for this category (from the section below.)
There are three basic steps to using the Category Schedule to drive your budget:
- Create the Category Schedule Items
- Copy your Newly Created Tags
- Link the Category Schedule Roll up to your Category Sheet
- If your category will have both discrete and baseline amounts, add the baseline amounts first, by selecting a Category from the drop down, and then in the Subset column enter the word, “Baseline.”
- Note that, since a detail is not needed for the baseline amounts, the tool doesn’t create a customized Tag for the baseline items. Move to the right and enter the baseline budget amounts for this Category in the appropriate monthly columns. The Category’s roll-up begins to appear at the top of the tool.
- Now you’re ready to enter the discreet amounts for this category. On the next line down, select the related category, then enter the Subset name and then a Detail for more specificity. The related tag is created for you, driven by these entries. Enter the budget amount for this item in the month you’re anticipating the activity.
- Proceed in the same manner, listing as many category components as you wish.
Note: You may divide Subset activity across categories. This may require a bit of planning ahead of your data entry to identify the associated grouping, but the category roll-ups will work just the same.
- Once you have added all of the scheduled items, to make your newly created Tags available throughout the Tiller universe, highlight and copy all of the Tags in Column E on the Schedule and paste them into the next blank line on your Tags sheet. (You may delete any blank cells in your tag list.)
- Note: If you change the data that drive the tags in the Category Schedule, you’ll need to update the associated tag in your Tags sheet.
This is the heart of the Category Schedule. You need to connect your summary totals at the top of the Schedule to the appropriate Category line on your Category sheet.
Here’s how to do that:
- For each of the Categories for which you have a roll-up Category summary at the top of your Schedule, copy the formula below and paste it into each of the 12 monthly budget cells of that Category’s line in the Category sheet:
=iferror(offset('Category Schedule'!$G$5,match(offset($A$1,row()1,match("Category",$A$1:$1,0)-1),'Category Schedule'!$A$5:$A$16,0)-1,match(offset($A$1,0,column()-1),'Category Schedule'!$G$5:$5,0) 1),0)
- After pasting the formula into the cells, your Category Schedule’s roll-up budget is now linked to your Category sheet, and you should see the roll-up amounts from the Category Schedule sheet appearing as the 12 monthly budget amounts for the related category in the Category sheet.
- Note: Be sure to create some kind of visual cue on this Category line (bold the Category name or create a different shading) in your Category sheet to remind you that these budget numbers are being driven by the Category Schedule.
- Remember not to overwrite the data for this line in the Category sheet or it will break the references. (To repair, simply recopy and paste the linking formula.)
Follow these same steps for the other categories summaries at the top of your Category Schedule to complete your work.
- When you make any changes to the budget data in the Category Schedule (timing, amounts, active/inactive) these changes will dynamically be applied to your Category sheet and throughout the Tiller universe wherever budget numbers are reported.
- If you wish for more granularity in reporting and tracking, unhide rows 17-19 by clicking the plus sign to the right of the row number 16 to reveal a reporting tool for Subsets and Tags. Use the dropdowns to select the Subset or Tag of interest, and the tool will return actual amounts from the Transaction sheet among items associated with the subset or tag.
- Because of a limiting factor in the Google Sheets, you will see an application error when switching between Tags and Subsets in A18. This will resolve as you select the appropriate item in B18 and does not affect performance.