What is the goal of your workflow? What problem does it solve, or how does it help you?
Benefits:
- Track each recurring expense including Actual Spent MTD, Amount Budgeted
- Automatically know if a bill is Paid/Not Paid
- Know how much you still owe for your recurring expenses for each month
- See your relevant Projected Balance details for each bill’s due date.
- See your Projected end of month balance
- Charts where you can monitor the month’s Projected Balance
- It allows you to know when you can spend and when you should wait.
In this sheet, you can automatically track all of your recurring expenses and see your Projected Balances in one place. This requires some minimal configuration to add the expense from your category list and the ‘Due Date’ of each expense. This will then automatically populate your budgeted amount for the specific month, amount spent on the category, and the information from the Projected Balance sheet for the projected balance reporting.
The Due Dates will cascade across the sheet each month, similar to how the Categories tab Budget column functions. If a due date for a particular expense changes, you can overwrite the date fields with the appropriate date for that specific month.
Using this sheet will allow you to better prepare for your financial month. You can easily see from the graph in each month when your balance might dip or spike to better plan your discretionary spending.
If you’re like me, you’re working to break the paycheck-to-paycheck cycle and to feel in control of your finances. One of the hardest things about doing this is knowing how much of your bills are left, what’s been paid, and how much extra I might have for the day. So many times in the past have I thought I had some extra funds available, only to find myself broke after making payments on my recurring expenses.
Will I have enough funds on the 5th to go to a concert out of town or should I wait until the next payday? Should we wait to go out to dinner or will we be fine if we go today? If I were to save all of my discretionary funds, what might my balance look like at the end of the month? How much of my recurring expenses will I owe from the next paycheck?
All of these things can be answered in a quick look at my solution. Use this sheet to take control of your expenses and feel confident about the money that you’re spending.
How did you come up with the idea for your workflow?
Within all of the solutions that Tiller offered, I felt like the ability to track my recurring expenses was not an option. Even though I have been sticking to my budget since joining the Tiller community, there were still times when I might run dangerously low on funds due to the timing of recurring expenses and paydays.
Having this sheet where I can see when my bills are due, what I have left to pay, what my balance might be on the dates the bills are paid will be extremely useful for planning. Having this information gives me the confidence to know when I can spend my discretionary funds and when I might wait for a better opportunity.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
After selecting a Category from the Recurring Expense column, the amount that you have budgeted for the month will automatically populate. You will need to configure the bill ‘Due Date’.
The Paid/Not Paid information is determined if you have an ‘Actual’ amount for a category that is greater than or equal to the amount you have budgeted. For example, if you budget 500 for rent and the amount spent is 499, the bill is ‘Not Paid’. If your actual amount spent for the time frame is 500 or more, the bill will be considered ‘Paid’.
The ‘Actual’ is looking at the ‘Transactions’ sheet and pulling only transactions for the time frame that match the category that is chosen. This is a dynamic field that shows month to date totals for the category.
The ‘Due Date’ is what is used to pull the ‘Projected Balance’ field from the Projected Balance sheet.
The chart, projected balance details, and end of month projected balance is all dependent on the Projected Balance sheet being configured with specific settings. Also, all of your recurring expenses that are tracked in my solution need to be added to the Projected Balance sheet. There is no need to add income to the Recurring Expense Dashboard, but it does need to be added to the Projected Balance sheet.
The Projected Balance sheet needs to have the following settings configured:
Start Date: 01/01/19
Period Intervals: Days
Future Periods: 365
Starting Balance: Starting balance on ‘Start Date’ for tracking going forward.
If you want to see use this for October, November, and December, set the ‘Starting Balance’ to your opening balance in October and the Start Date to 10/1/2019. You will also need to update the ranges for the chart to point to the correct month’s data set in the Projected Balance sheet. (i.e. ‘Projected Balances’!O8:P38 for 10/1/2019 to 10/31/2019) to grab the range from the O and P columns in the Projected Balance that have the month’s data.
If you are already using a Projected Balance sheet and don’t want to edit it for use with this sheet, simply create a new copy to use with my solution. After the new copy is created, do a ‘Find and Replace’ in my sheet only and replace the name ‘Projected Balances’ with the name of the new sheet, like ‘Copy of Projected Balances’. Make sure to include formulas in the find and replace action. (should replace a few thousand cells of data and may take a minute to run).
Anything else you’d like people to know?
For this to be as accurate as possible, you’ll notice in my example that I have split the same category into multiple categories if the expense happens mutliple times per month. For example, I have 3 grocery categories so I can track each trip to the store independently. If you are making multiple payments on a category, split it to multiple categories going forward. Create one category for each payment that you will be making.
Is it ok for others to copy, use, and modify your workflow?
Yes, please use this and share any improvements you make to the solution with the community. I am a novice spreadsheet builder so some of the equations I used could potentially be done more efficiently. If you see something that could be done better, please share!
Some of the improvements I would like to see is to do the Projected Balance calculations in this sheet. This would allow you to adjust the ‘Starting Balance’ each month for increased accuracy for each month. Also, this would prevent the need to configure information in two sheets if you’re not interested in using the Projected Balance sheet in conjunction with my solution.
If you see the expected balance in the sheet being considerably more than your balance in the future, consider adding categories that you spend money on each month to the Projected Balances sheet, even if they don’t have an actual due date to track in my solution. For example, you usually spend $100 a month on resturants. Add this as a category to Projected Balance sheet to show that you will be spending this money by a specific date each month.
If you said yes above, please make a copy of your workflow and share the copy’s URL:
https://docs.google.com/spreadsheets/d/1ZVgLqQwRRaZJ9WIw-Nffq1bCsXzyVpnzG_WJwdg4ueQ/template/preview
When I began building this sheet, I barely knew how to do some basic functions in spreadsheets. This has inspired me to learn spreadsheets and I now have a solid understanding of a wide range of functions to call information from other sheets based of specific criteria like dates, ranges, and amounts.
I am working to understand how the architect of the Projected Balances sheet accomplished this solution, but building that on my own for my solutions is out of my current skill level. To achieve my desired end result, we must rely on the work in the Projected Sheet for my sheet to give the full picture.
Also, I have noticed that when copying this into other sheets, the graph details may be lost. If you see this happen, I have added the ranges for the chart in the label below each chart (if the Projected Balance start date is 1/1/2019). Copy the information after “(Range:” and double-click the ‘No Data’ chart and enter it as a range. This will fix the graph.
Additionally, I have seen that sometimes the equations will show an error. If you see an error, simply select the cell and press ‘Enter’ to resubmit it. This will correct the error. As of now, I am unsure of how to prevent these things from happening so there may be some manually updating needed after initially copying the sheet.