Overview
Write a brief description of what the template does.
This template takes a list of recurring bills/expenses, and generates a list of upcoming dates for those expenses.
This template also provides an example of using the generated list of expenses to display the expenses on a calendar.
UPDATED 4/15/24 - I added a way to override individual projected expense payments, so you can modify the date or amount. This is helpful for bills that fluctuate wildly month to month, like utility bills, or if you have a paycheck that includes a bonus once a quarter, you can override the expected amount for that specific paydate, and leave the others at their normal amount.
What is the goal of your workflow? What problem does it solve?
This template allows you to forecast upcoming expenses to calculate expected cashflow.
How did you come up with the idea for your workflow?
I was inspired by this Google Sheets template - đ Generated Recurring Expenses Workflow - but heavily modified it to fit my needs.
Installation
Download the template from here (link).
To install the required sheets:
- Add a column to the Transaction worksheet called âExpense Tagâ
- Open both the Expense spreadsheet and your Tiller worksheet
- Right-click the name of the three worksheets in the Expense spreadsheet named Expense Setup, Expense Transactions, Expense Override, and Calendar. Select Move or Copy, and select your Tiller spreadsheet from the drop down list.
- In your Tiller spreadsheet, move to the Data tab and click Workbook Links. This will open a window on the right.
- Click the three dots to the right of the entry for Excel-Expenses-Tiller, and select Move links. Select your Tiller spreadsheet and click OK.
- Move to the Transaction sheet, and highlight the new column you created in Step 1 (Expense Tags). On the Data tab, click Data Validation. Choose List, and set the source to be the Expense column of the table on the Expense Setup sheet.
Setup
There are three worksheets, that each have a few options to configure:
Expense Setup
Fill in the green cells to configure the recurring bills/expenses. The first cell, Active, can be used to stop the expense from being calculated, but preserve the details for later.
Expense Transactions
Columns B and C are used to configure how far back and forward the sheet looks to find a payment that matches the expense.
F2 sets the starting month for calculating the upcoming expenses, relative to the current month. For example, 0 = the current month, -1 would be last month, etc. I leave it at -1, so that expenses at the end of the month that havenât been paid yet donât disappear when the first of the next month happens.
G2 defines how many months of expenses are calulated.
H2 controls whether the scheduled date for overdue expenses are recalculated. For example, if an expense is scheduled to happen on 3/15/2024, but itâs now 3/17/2024 and the payment hasnât been recorded, this option will set the scheduled date to the next day (3/18/2024). If you turn this off, the scheduled due date will remain 3/15/2024. I find this to be really helpful when calculating future cashflow, since the expense hasnât happened yet, but itâs still pending.
Calendar
This is an updated version of this calender I posted a while ago: đ Showing Transaction Data on a Calendar (Excel version)
B1 and B2 define the month to display.
B3 defines whether the calendar only displays data for the selected month (e.g. April 2024), or for all dates shown (e.g. include March 31)
C6-C11 allow you to set the groups that show summarized transactions on the calendar.
N2-N41 allow you to define the specific accounts you want to be included in the calendar.
Added 4/15/24:
Expense Override
The first column of the table has a dropdown of the current projected expenses. Pick the one you want to override. The next 3 columns will populate automatically. If you want to override the date, enter a new date in the Override Due Date column, otherwise leave it blank. If you want to override the amount, enter a value in the Override Amount column, otherwise leave it blank.
Usage
Once expenses are added to the Expense table on Expense Setup, all you need to do is tag the matching payment transactions on the Transactions sheet in the Expense Tag column. In my Tiller workbook I have them defined in the AutoCat settings to happen automatically, which is really convenient.
The calendar will show the upcoming expenses on the day they are scheduled so you can get a visual representation of when certain bills will happen.
To build your own sheet that uses the calculated upcoming expenses, use the variable name âexpenses_upcomingâ. This is a reference to a dynamic array on the Expense Transactions sheet, starting in cell M15. You can filter the array as necessary to get only the upcoming expenses you want. For an example of that, refer to cell AE2 on the Calendar sheet to see it in action. You can filter on scheduled date, bill name, amount, account, category, and group. Added 4/15/24 - I also added some additional variable names to make the filtering easier. To reference the individual columns, you can use expenses_upcoming_date, expenses_upcoming_name, expenses_upcoming_amount, expenses_upcoming_account, expenses_upcoming_category, and expenses_upcoming_group.
For example, I have a Dashboard sheet that breaks the upcoming expenses into chunks that align with my paychecks, so I can see how much of each paycheck is pre-allocated to a bill.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Of course!
Notes
Anything else youâd like people to know?
A hearty great big thanks to @1Email2RuleThemAll for the original inspiration.
FAQ
Optionally, add common questions and answersâŚ
