Monthly Budget And Spending With Income/Expense Groups

What is the goal of your workflow? What problem does it solve, or how does it help you?
I use cash, credit card, check, and PayPal to send and receive money. I use Tiller to take those things and put them in a sheet for me so I can use this monthly budget sheet to track all of them and manage spending and savings.

How did you come up with the idea for your workflow?
I kept losing track of money, even with a budget. Once I could integrate the sources and destinations, the whole picture became much easier to see.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
There are three relevant sheets: Transactions, Categories and Template
Transactions: The Transactions list that Tiller creates and manages.
Categories: A list of categories and expense/income groups that feed into the Template.
Template: The monthly sheet. This sheet should be adjusted and duplicated on a monthly basis to account for changes in the budget for next month.

Anything else you’d like people to know?
White-background cells are editable by you and should be modified.
Grey-background cells can be edited if necessary, but the value is the result of a formula so it is not advised unless something is wrong.

The Category column has validation applied to make choosing a transaction category easier. I have not tried using the AutoCat plugin, but it should be possible to integrate with this setup.

This sheet gives you a customizable way to define expense and income categories, as well as assign each category to a wider aggregation group. Here is an example of what this sheet might look like:

This sheet lays out the budgeted expenses and income for the month as well as the actual spend and income as it happens. There are a few things to know about:

[Month Name] (Cell A1): This month name is calculated from the sheet name. Duplicate the template and name your duplicate sheet “Month YYYY” (e.g. October 2019). If this does not cause the cell to update, click the “Recalc” checkbox (cell M1)

Recalc (Cell M1): This is a recalculation checkbox. It is tied to the formulae in the sheet, so if you check or uncheck it the formulae will all recalculate. Using INDIRECT references are what allows this to work, so if you modify anything and reference another sheet, make sure to use INDIRECT.

Budget columns (B and G): The Expenses and Income Budget columns are used to determine your budget for that month. At the end of each month, go through the template Budget values and adjust them for the next month before duplicating the template sheet and naming it.

Groups are automatically added and removed from this sheet. If you add or remove a group in the Categories sheet, it will be added or removed from the aggregations starting in cell F15 and from the charts.

Actual Income was a tricky one. I get paid at the end of the month, so if Actual Income only looks at the current month it will nearly always be $0. Instead, if the sheet cannot find that income source in the current month, it checks the previous month of transactions for that income source and uses that value if found.

There are a couple custom formulas, so take a look at Tools > Script Editor.

Here is an example template:

Here is a duplicated template sheet for October 2019 with partially-categorized Transactions:

How do you use this workflow?
I believe this workflow is flexible enough to function with both the Dave Ramsey envelope system as well as cashless spending.

At the end of every month, I sit down and adjust the template budget values to account for next month’s projected expenses (i.e. I might increase “Gifts” in October, November, and December to account for Christmas). This usually takes between 30 minutes and an hour to plan. After adjusting the values, I duplicate the Template sheet and rename it [Month] 2019. I then visit the bank and withdraw cash for some categories for the month, distributing that cash to envelopes. I then update these categories for the amount withdrawn in its corresponding Cash column.

My budget is now ready for the month. Whenever I use a credit card for a cash category, I make sure to move the cash to a “Spent” envelope before categorizing that transaction in the Transactions sheet; an empty categorization is how I can know if there is anything unaccounted for. The same process goes for checks, PayPal, or bank transfers.

At the end of the month, repeat this process.

Is it ok for others to copy, use, and modify your workflow?
You may copy, use, and modify this workflow for anything other than re-entry into this contest.

If you said yes above, please make a copy of your workflow and share the copy’s URL: