Expense Forecasting and seeing upcoming expenses on a Calendar

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.

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:

  1. Add a column to the Transaction worksheet called “Expense Tag”
  2. Open both the Expense spreadsheet and your Tiller worksheet
  3. Right-click the name of the three worksheets in the Expense spreadsheet named Expense Setup, Expense Transactions, and Calendar. Select Move or Copy, and select your Tiller spreadsheet from the drop down list.
  4. In your Tiller spreadsheet, move to the Data tab and click Workbook Links. This will open a window on the right.
  5. 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.
  6. 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.

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.

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…