Generated Recurring Expenses Workflow

[randy]

Thanks for sharing and documenting this solution to generating transactions, @1Email2RuleThemAll. As with your Income Settings sheet for Google Sheets contribution, you have done a really nice job making a complex tool accessible to others with extensive in-cell notes, granular intermediate calculations of layered outputs, and thorough documentation in this thread.

You’ve done a great job integrating Tiller core sheet conventions and leveraging header-row and ARRAYFORMULA(). I also appreciate your clear statement of purpose— that existing templates don’t allow for finer-than-monthly intervals.

Especially if the output of the “Generated Expenses for Check Expense Page View” were mapped to the user’s Transaction’s sheet header order, a tool like this could have been helpful in creating sample data.

As part of the Tiller Builder Rewards Program, the Tiller team is awarding you $25 for building this unique tool for developers and builders. Thanks again for sharing your creations and your process with us.

P.S. You mentioned an upcoming Report template…? Any hints on what you will be reporting on?

P.P.S. Your “grain” terminology is intriguing. What is the origin of that?

[1Email2RuleThemAll]

The report I mentioned is more of a projection of recurring expenses, broken into pay periods. Essentially, it uses my Income Settings Sheet to determine your pay date and frequency. You then select a date on the report, as well as how many “paycheck lanes” you want to generate (from 1 to 6). The report will generate all of the recurring expenses expected to be due within the given period, and place them under the paycheck that the expense will be due from. It also uses my Credit Card Settings Sheet to optionally pull in the totals due for each credit card, and maps them to the paycheck they will be due from as well. There’s a section at the top that aggregates the final totals of each period to give an overall total due/available.

Black entries are upcoming expenses that have yet to be paid, and shows the budgeted amount, which is used in the calculation to show what’s available. Green entries are expenses already paid, and shows the actual amount spent. When you mark a transaction as an expense in the new Expense Tag column on the Transactions sheet that the Generated Recurring Expenses Workflow introduces, the expense turns from black to green, and the budgeted amount is replaced with the actual amount of the transaction marked. The amount available is then updated. Red entries are expenses that have not been marked in the Transactions sheet, and the due date has passed. Blue entries are credit card totals due, and can be excluded by the control in cell W3. Additionally (for periods that are current or in the past), you can set the control in cell W4 to Yes to include all transactions not marked as expenses that occurred within that paycheck period, and they will show up in Italicized grey.

This allows you to project the available amount of money that you will have left per paycheck after all of your recurring expenses and credit card debt is covered. This, in turn, allows you to better plan for what you can stash back into savings, and also allows you to see where exactly your budget might be tight. So if you see that you have $400 projected to be available after covering all expenses this check, but only $100 available after the next check, it’s likely a good idea to cover one of the expenses due out of that check to free up some money in that next period. So instead of thinking “I have $400 to spend or save”, you can think “I should earmark some of this money for a future expense and keep my spend/save amount to about $250 instead so next week isn’t so tight.”

[1Email2RuleThemAll]

As for the use of ‘grain’, I’m a Business Intelligence Developer. I deal with databases, cubes, reports in various systems, etc… The word is really just a synonym for ‘granularity’. Data granularity is a measure of the level of detail in a data structure. The “grain” of a report or a table is important in determining what analysis can be performed on the data.

Most of the Tiller foundation workflow is at the monthly grain. This was no good for my reporting purposes, as I wanted to see which expenses were due out of each of my paychecks. Since I was paid more frequently than monthly, I had the need to drill down to a more precise grain. The report I mentioned in my previous reply does that by drilling down to a custom sort of shifting granularity (grain). The granularity of the data is at the “Paycheck” level. If you get paid weekly, the grain of the data is weekly. If you’re paid bi-weekly, the grain is bi-weekly.

With the Generated Recurring Expenses workflow, this type of analysis on expected expenses is no longer cut off at a monthly grain. Projections can be analyzed at any grain you wish. For instance, you can build a report using this workflow that graphs your daily expected burndown of money for the entire year on the left, and another graph with an actual spend-to-date analysis on the right. To your point, this could be used to build out a more robust sample data set for developers. I know I’ve had troubles when building reports because the current sample data is a bit too random (such as paychecks are not entered in any reliable cadence). The use cases are wide, and I feel its value cannot be overstated.

On that note, are there any resources available that explain the reward system better than this snippet?

Prize level will be determined based on the quality, ambition, and value of your solution:

Tiller t-shirt
$25 gift card
1-year Tiller subscription extension
$100 gift card
$500
$1,000

I do appreciate the $25 gift card, but I’m just a little perplexed on how rewards are actually determined. For my first submission, I changed two formulas in an existing workflow and received a $150 reward, which I just don’t feel is even on the same level as this solution in terms of quality, ambition, or value.

[MidnightPrzm]

Not all my credit card accounts are showing up. My Sam’s Club Credit Card, which I just recently added (not sure if that has anything to do with it) is correctly listed on my Tiller Accounts page and is categorized as a liability so I am unsure what is wrong.

[1Email2RuleThemAll]

Please check out the Notes section. The issue is likely due to the institution sending over a Type other than Credit.

@1Email2RuleThemAll

• The accounts that can be selected on the Credit Card Settings sheet are filtered down to accounts with a Type of ‘Credit’ on the Accounts sheet. Unfortunately, account types are set by the institution, and cannot be changed. If your institution sends an account through as a different type (i.e: “OTHER”), there is a section in the hidden area (Y7:Y) that will allow you to select additional types. Adding a type to the list will add all accounts of that type to the dropdown.

[hickson567]

This looks really helpful. Has it been released yet? I like the Sample Report in the Recurring Expenses solution, have you thought about integrating it with Income as well? Then it would give a great overview, while this report you have sampled here provides even greater visibility per pay check.

[1Email2RuleThemAll]

Thanks! The check expenses report has not been released. I’m trying to work out a few areas that I’m not satisfied with. There’s a lot going on, and I’m still trying to tweak some things in it to make it better. I’ll either be adding a lot to this report, or scaling it back before I decide to release.

But I created a sort of “Account Health” report that also uses the Generated Recurring Expenses solution as a base. I’m more happy with the state that one is in, and would likely release that first. It shows your checking, savings, and credit card totals, as well as expenses due by your next check (which disappear after being marked as paid). For savings, it shows the total amount in savings, the amount allocated to savings goals, and the amount available that’s not allocated. For checking, it shows the amount you have in each account, and the amount available after you pay all expenses due by your next paycheck that you set as being paid from those accounts. There’s also an option to change the amount available from the “Total Due” (total after current paycheck expenses are paid) to “Total Pending” (total after current paycheck expenses and all credit card totals are paid).

I have thought about integrating income and expenses into a report, and might work towards making that in the future. The sample report was just a straight pull from the Generated Expenses sheet, ordered by date, as a way to kind of show how to pull the data in. You could easily use my Income Settings sheet to set up your paycheck cadence, then use that to generate a list of paychecks between the start/end dates of the report, then combine the expenses and paychecks together in one list, ordered by date.

[hickson567]

@1Email2RuleThemAll

You could easily use my Income Settings sheet to set up your paycheck cadence, then use that to generate a list of paychecks between the start/end dates of the report, then combine the expenses and paychecks together in one list, ordered by date.

Not sure I am skilled enough for the combo part of this exercise but am going to try =) And looking forward to your release of the “Account Health” and “Check Expenses” sheets in the future.

[1Email2RuleThemAll]

I can try to work on a solution to this soon and send you a personal link via direct message. But to be honest I’m not really feeling up to developing for Tiller right now after being ignored for two weeks. If I build the above report, please do not upload it to the site. It would be for personal use only.

Thank you for showing so much interest in my workflows. I apologize, but any solutions I build in the future will likely not be making their way to Tiller. I’d rather they stay where they’re appreciated.

[jjfelton4]

If you do build this solution, I would also greatly appreciate a DM with that included.

Your workflows are extremely impressive, and the closest tool to planning for the future. My wife and I pay for everything with credit cards, and pay off total balance each statement. The hardest thing to do is visualize how much cash we will have left over to invest or put into savings.

I haven’t quite figured out how to get the Recurring Expenses setup (I’ll be working on that this afternoon). If I were to have a dashboard with an overview of previous/next statements, future paychecks and future expenses incoming, this would be the ticket to finally get our finances organized how my brain likes it and to be able to maximize savings.

Thank you for your hardwork on these solutions!

[annaisakiwi]

Sorry to hear this - I’m happily using your current solution and loving it - and can’t wait for the next part!

[1Email2RuleThemAll]

Thank you for the kind words. If you need any help setting up this workflow, I will be happy to assist. The hardest part is the installation. Once that’s finished, everything should run smoothly. If you like the solution, please give it a vote up at the top. I doubt it matters to @randy, but at least it would show that there is more interest in this solution than he gave credit for. This solution has already caused two new users (including yourself) to engage in the community, and also caused a long time Excel Tiller user to switch to Sheets (which I take as a high compliment, and feel is a true testament to how useful this workflow is).

I am absolutely with you on those dashboards. Those were the exact same things I was missing for my overall planning health to be whole. Honestly, it is quite a major hole in Tiller’s ecosystem, and my workflows were aimed to fill that.

The two reports I mentioned above work together to give me this data. I look at my “Account Health” dashboard (which in my solution is actually just called “Home Page”) to see what I have currently, what’s coming out before my next paycheck, and the total I have available after those deductions. When I have that, I go to the “Check Expenses” sheet with the available total, set the first paycheck to the next one I’m going to get, then start working to figure out what I can do, and the best way to allocate the income.

The “Check Expenses” sheet is pretty involved, and there’s still a few things I would like to change with it before showing anybody else. But if I can manage to get around to that, I will send you a DM with the solution/install instructions. The “Account Health” sheet is ready to be released, but I haven’t formatted it for other people to use. I only have about 5 spaces for Checking/Savings accounts to appear, when users on the site might have more. I made it 5 in my solution because I never plan to exceed that. There are 10 spots for credit cards. If those limits would work for you, I might be able to type some install/user instructions up and send that one your way when I get some free time.

[1Email2RuleThemAll]

I’m glad to hear you love the solution! Thank you for being the first to test it!

[jjfelton4]

I would love some assistance in setting the workflow up, I got to the part of setting up the data validation code in the Expense column, but still couldn’t get everything to populate. I curbed the work for the time being, the Credit Card balance “snapshot” is the biggest hole I needed to fill. My “expenses” are all on my card balances, we have 3 cards that we use for certain categories of our lives. So my expense categories are almost arbitrary at first glance, since they all are encompassed in my credit card balance.

Now that’s not to say that budgeting for all categories of life is actually arbitrary, but I think I actually need two different sheets, one is a sheet that’s tracking my monthly expenses by category , and the other sheet to track the total balance of each card along with my paychecks. From the examples of your Paycheck Expense workflow, it looks like it does indeed solve that problem, but I do worry because I feel like it’s going to be counting all individual categories of expenses as well the total balances on credit card statements.

For the time being, I did find a solution utilizing your Credit Card Settings & Income Settings workflows in conjunction with Tiller’s Projected Balances sheet.

If you do not want this posted please let me know, however this isn’t really changing any of your card so I thought it would be ok. But do not want to cross any lines.

I’m basically using this sheet to reference your two sheets.

Starting Balance This is referencing total checking accounts sheet.

Payment & Credit Cycle Flow This used to be the Yearly Recurring Expenses section, but I am able to have specifics months and days, making it easier for me to accomplish the goals that I’m trying to achieve.

This is where your Credit Card Settings sheet has been invaluable to me. I have all cells referenced for the upcoming balances due and then the current picture of where all of my statements are sitting.

I then have your Income Settings sheet to reference the next payday and amount for paychecks that are incoming (for the 2nd pay day, I just referenced the above cell and +14).

The red savings text is actually the only number that needs to be manipulated. This is basically for me to enter what amount of savings I could theoretically put into our high yield account. The hardest part about living on cards is making sure we have enough cash in our accounts to pay off the balances when all cards are due.

Balances after upcoming/current statements This is the final part that tells me where we’re sitting with our finances, it references the 17th of each month (this is when the 3rd credit card balance is due) and give me a snapshot of how our account health is looking. This is also where I use the red text savings input to make sure I never put too much into savings before our statement is due.

So, as you can see, it’s clunky, but it’s working for now. I would love to able to track our actual categorical expenses somehow too. It’d be even better to be able to have a dashboard that can look at all of that (your Paycheck Expenses is the closest thing!)

Anyway, if you’d like me to detail the recurring expenses part where I got stuck, I’d be happy to as well!

Thank you again for all of your hard work, these solutions alone have been an IMMENSE help and with our peace of mind as we financially plan for the future.

[1Email2RuleThemAll]

I’m glad my prior solution have been working out for you!
Yes, please post where you are stuck in this install, and I will help out as best as I can.

From the examples of your Paycheck Expense workflow, it looks like it does indeed solve that problem, but I do worry because I feel like it’s going to be counting all individual categories of expenses as well the total balances on credit card statements.

You are correct. This is actually the one thing that has held me up with releasing the Paycheck Expense workflow. Initially, this sheet was developed to just display the recurring expenses that were due for each paycheck that was generated. In that iteration, it worked great, but was lacking credit card totals. I changed the workflow around to allow those totals to be added when they were due, but that’s still not the “true” picture. This is because the recurring expenses due that are paid on a credit card are still being generated under the lane they’re due and not the lane the credit card that they’re on would be due.

To fix this, I would have to figure out the best way to take expenses set up on a credit card and project the totals out to the date they would be due. I’m sure it’s possible, but it would be very involved and time consuming. The sheet itself is already a bit laggy and takes a little to catch up when you change the date (this is mainly due to conditional formatting that makes things look way better). As it is, I just subtract those expenses out of the columns to get the true picture. It’s not hard, but I’m a fan of automating everything I can.

[1Email2RuleThemAll]

@randy basically said that nobody wants this solution, it doesn’t have a flashy dashboard, and that’s why it was awarded $25.

[heather]

@1Email2RuleThemAll

I wanted to chime in here because I’m sensing your frustration and totally get it. You worked hard on this solution and felt like your inquiry about the reward criteria and amount was ignored. It definitely wasn’t intentionally ignored. Our response monitoring here in the community is not focused on Show & Tell topics that are waiting for a response because generally those are from people using a solution and it’s on the builder to reply so we don’t have a system in place to catch all those replies. Even your direct reply to Randy is easy for him to miss because there is so much going on here in the community, and its intentionally a little like the wild west Basically, it’s a little messy here to keep up with what needs a reply. Sorry about that.

I personally do see value in your solution and I totally understand your confusion around the reward system and frustration. Your speaking up about this is prompting us to re-evaluate this and many aspects of this program. It’s not just Randy evaluating things, there are a group of us that collectively decide on this based on Randy’s initial review and input.

It could be that we didn’t understand the full intent of your solution here and we potentially misjudged its overall value to the greater community as well as others’ ability to integrate it into their own workflows. These things can be tricky to assess, but generally solutions that provide value to a broader and more diverse population, and that are easier to use/setup/understand are going to receive higher rewards even if they took much less time, thought, and energy to build. We recognize this judging criteria is a little ambiguous and arbitrary, and perhaps that’s not the best way to judge/award

We’re open to your suggestions as we continue to evaluate this program. It’s always been experimental, as are most things here in the community.

In the end we really and truly do appreciate and value your efforts and solutions. The monetary value awarded is not at all a reflection of our gratitude or the value of your solutions. We encourage builders to share not for the monetary reward, but because they want to help others or are just excited to share their work. It’s clear that some community members are finding value and appreciate your work as do we!

However, It looks like you’ve revoked the permissions for this solution. If you truly no longer want folks to be able to use your solution and you don’t want to share further solutions, we respect that and I would recommend that you remove the link to solution earlier in the post. We can also just completely take it down, but then folks who already have it won’t have any documentation, but that may be okay if you don’t intend to support it anymore.

Once again, sorry for any misunderstanding and we hope you’ll reconsider your decision to stop sharing solutions and revoking permission for this one in particular. Let us know how we can make this right for you.

[diogo6]

Wow, quite the thoughtful well articulated response from you and the team Heather! For the record, absolutely zero sarcasm meant, as often can be missed in this medium.

Reading this thread was quite the e-ride… I was thinking of possibly even trying out the solution itself at the start, and here’s hoping someday I might get to.

Just my 2c, there’s value in leaving said post up as it at the very least shows:

1. a cool original idea, even if it’s just to be ideated from at this point.
2. a genuine human interaction between a company and its customer base.

[jjfelton4]

I just want to commend your efforts in hopefully bridging this gap that has been created.

I can understand both sides of this equation, I’ve been the creator of something that has felt overlooked, while pouring all of my time and energy into it.

I have also been the operator of an organization, had the best, most genuine intentions with all of my decisions for everyone to be affected positively, and somehow they have (always) fallen short of someone’s expectations!

At the end of the day, Tiller is a wonderful tool that has helped so many people get their finances either back on track or even more organized. I for one am so grateful for this, my wife and I just introduced our first child into the world two weeks ago today. Tiller has unlocked so many roadblocks that we have struggled with on our finances for years - we’re now able to truly forecast our future income, savings and expenses. Wrap all that up with being located within one Google Sheets workbook, and I’m set for life.

Furthermore, @1Email2RuleThemAll has literally built the solution to a problem that I have been looking for and has literally caused my wife YEARS of confusion on our finances. So please know that if you have decided to stop working on any of these solutions, you have made an impact on a brand new family’s lives for the better. I’m eternally grateful for you!

I don’t have a magic 8 ball to predict the future from here, but I do hope we can resolve these issues, quite selfishly for my own benefit!

Have a good night everyone

[1Email2RuleThemAll]

I’m glad to hear that these workflows have helped you better plan your new life! I don’t plan on taking any solutions down, and fully plan to support all the solutions that I’ve posted. I just changed the permissions to be for personal use only. This workflow in particular was meant as a scaffolding that can be used to build reports and dashboards (both solutions I talked about above use this as a source in some fashion). It has a lot of value to offer in this regard. Since I felt that the value was not adequately recognized, I decided that taking that aspect of the solution away was a fair trade. If somebody wants to use the solution, they can. If they want to build a report on top of it so they can gain better insights into their finances, they are free to do so.

I like the markup that the forums utilize, and I use the Show & Tell posts as a sort of doc repo for myself. I might post the other workflows at some point. As they sit, they’re only good for a subset of the user base. For instance, my Check Health/Home Page solution only accounts for a max of 5 savings accounts. That’s more than enough for my situation, but others may have 7 savings accounts, which would break the logic of that workflow. Getting those workflows into a state that’s usable by more people requires some effort, and I’ve just felt recently that those efforts were better spent on other things.

Again, thank you for the kind words, and let me know if you have any questions about the workflows!