🤔 "Rollover Adjustment" in the new Savings Budget

Thanks for diving in, @aronos.

The partial savings are definitely not intuitive.
There are basically 3 categories of transactions that need to be handled differently to keep a budget true:

  • Budgeted transactions with rollovers turned on
  • Budgeted transactions with rollovers turned off
  • Unbudgeted transactions (e.g. transfers, uncategorized or no-longer-used categories)

I THINK the equation I shared above handles all three cases but I’m happy to make improvements if anyone has suggestions.

You ask why I made the Savings setting optional, @aronos? I guess just because I can imagine users who are comfortable using the Monthly Budget but want to have one or two of their categories accrue as long term goals. For the most part, having the Savings be optional doesn’t introduce complications… except for the riddle of truing up budget issues.

You ask “is it too late to change the name of this?”… do you mean the template or the Track category?

We picked the term Savings Budget for the template because we wanted something a little more general and less jargony that “envelope” or “rollover”.

I’m open to “Error Adjustment”. When I use “Rollover Adjustment” it is simply as a placeholder for something better. :wink:

As for the test sheet, once we settle on the analysis and inputs, I’ll add the equations to the Savings Budget master and we can see how they work in practice. We can continue to adjust and refine until we are happy with them.

1 Like

Okay I guess that makes sense… I’ve never done any sort of budgeting aside from envelope so it seems weird to me.

Yes, I meant the template. Same bias as above. :slight_smile:

As far as I can tell that’s correct, and I couldn’t find a way to break your demo sheet, especially with the separated “Available” and “Rollover Adjustment” calculations. But I think you’ve advanced this past the point where I can provide much useful input or insight and only have more questions.

I just set this up, and want to say kudos to the Tiller team. I absolutely love it - such a simple and intuitive addition. And now I can easily track things like our travel expenses which are extremely lumpy in nature. Great job!

2 Likes

Really glad to hear it, @jpkica. Thanks for sharing.

Hi @randy,

Your 6 point classification looks reasonable to me, though I can’t claim to have thought about the problem deeply. :wink:

However, my primary concern for the Savings Budget sheet continues to be the manual management of savings amounts themselves.

Case in point: I discovered last week that my total savings was about $300 too high due to what was probably human error on my part when setting their initial values.

I can also easily introduce errors when “transferring” savings amounts across categories, yet the sheet doesn’t (currently, by default) warn me about the mistake.

So, as they stand today, the savings amounts in the Savings Budget are too error prone for my taste. I think what is missing is a concept of an “opening savings balance,” and some way to pick a reasonable value for it based on real account balances. Maybe? With that, and good error checking heuristics for the running balances (no “leaky” savings transfers), I think I could trust the sheet.

Thanks again for the feedback, @aronos & @matta.

I just published a new version of the Savings Budget that includes an offset/“Budget Health”/“Rollover Adjustment” calculation per this calculation. It’s hidden to the right of the main content body— just make columns I and J visible.

I’m hoping this health check will address some of the questions you raised, @matta.

Let me know what you think.

1 Like

Looking forward to trying it out, @randy.

I tried to upgrade, but it looks like there is an issue with the publishing. I currently have v0.68 installed and can see v0.69 in the version history, but the Tiller Money Labs add-on isn’t flagging the solution for upgrading.
image
I was able to restore sheet instead to pull down the new version, but my add-on still says my installed version is v0.68.

Good catch, @cculber2. I found an issue with how I updated the version information. It is fixed now but may take 10’ for the cache to update.

Chances are you have the 0.69 version— just check for columns I & J— but that it is mis-versioned as 0.68.

Yes, that’s exactly what happened. :slight_smile: Thanks for the quick turnaround!

What exactly do these numbers mean? My “Offset, All Periods” matches that error from earlier in the year that I haven’t hunted down yet, but then my “Offset, Oct 2020” is “-15396.84” which seems wildly inaccurate since I’m balanced.

Also, the “Savings” calculations in my Income and Expense bars (the dark grey ones) are both off now. The expenses one is wildly off, coming in at over 3.4x the sum of all the light grey bars. I think it might be including hidden categories?

I also haven’t been able to get the prior column working in this version.

Hey @randy,

I think there is something off with the background calculations for Offset, <period>. I duplicated my budget journal categories sheets and cleared the journal and budget entries to get a fresh start. All transactions are still categorized and all income and expenses have a budget entry and net $0. My Net Actuals, All is correct, but the Net Actuals, Savings is yielding unusual results. Looking at the Integrated Categories columns, it appears you are calculating the Period Actuals and Total Actuals differently because of how Period Actuals supports the main income and expense report. Period Actuals is negating non-income transactions, which works for the main report, but breaks the current period Net Actuals, Savings and Offset, <period> results. The All Periods calculations don’t suffer from this problem because the Total Actuals don’t do any negation to display only positive numbers. I was able to overcome this problem by emulating the sumproduct() calculation for Net Budgets in to the below formula in J15.

=sumproduct(if(AD7:AD="Income",1,-1),if(AG7:AG="Savings",1,0),AH7:AH)

I think this might address some of your issues, @aronos.

@cculber2 Yes, that fixed the “Offset, Oct 2020” issue.

I see now that those calculations in J8, J9, J14 & J15 are including hidden categories, which is why that number was so far off. Which I think is fine because they’re balancing out in the “offset.”

There also seems to be an issue with the Budget Health header formatting.

image

=if(J2<>0,"🤒 ","🤗. ")&"Budget Health"&if(J2<>0," 🤒"," 🤗")

J2 is clearly 0, but something is preventing the formula from properly formatting the header. Manually setting J2 to 0 gives the correct behavior, so I believe there is some hidden rounding error.

I changed the formula for the header to the following

=if(ROUND(J2,2)<>0,"🤒 ","🤗. ")&"Budget Health"&if(ROUND(J2,2)<>0," 🤒"," 🤗")

And the formula for the conditional formatting of the cell to

=ROUND($J$2,2)<>0

and that seems to fix the issue.

1 Like

I made another change on my copy to the period Net Budgets formula in J13 so unapplied changes in the Adjust column G for budget adjustments are previewed in Offset, <period>.

=sumproduct(if(AD7:AD="Income",1,-1),AK7:AK)+if($BC$6="Budget",sumproduct(if(AD7:AD="Income",1,-1),AJ7:AJ),0)

It probably isn’t very useful to say this, but “me to” on the odd results from the per-month calculations in column J2 and J16. @cculber2 and @aronos are way ahead of me on this so I won’t bother investigating further until a fix is out.

I was able to use the new stuff to find a few months where my budget didn’t net to zero.

Great feedback, everyone, and formula edits, @cculber2. I should have recognized that the Actuals calculation is signed differently in the two Integrated Categories section, as you noticed @cculber2.

(I turn them positive for the current period because that is how they are rendered in the dashboard section, but I didn’t do the same in the all-periods calculation because that content is not shown in the visible area— it is just a datapoint for the budget health calculation. Probably would make sense to make this consistent in the Integrated Categories section but I’ll save that for another day…)

Hope we’re getting closer. Let me know if you find anything else.

P.S. I’m not sure on the appropriate offset behavior for hidden categories…

1 Like

Good point about the prior period, @aronos. The prior period formula needs to change since the new template master has new columns in the hidden area that have pushed over the selected period cell.

I updated the formulas in the original prior period column post to reflect the new cell locations.

1 Like

Sooo…it might be helpful for dummies like me if you explained exactly what all these numbers mean? I’m not really sure what I’m looking at when I look at the “budget health” scoreboard, with all of these nets and actuals and offsets and whatnot, and don’t know if I should be concerned or not. I thought my savings budget was set up pretty well and now I’m not sure!

@kevinwholland I was somewhat confused at first as well, but I think I have a good handle now after spending some time fixing the formulas. Here is my understanding of each value.

  • Net Budgets: This is a simple balance of your budgeted income minus your budgeted expenses. If this is $0, you have allocated every dollar of income to expenses or savings goals. A positive value means you still have income to allocate, and a negative value means you have budgeted more expenses than you expect to earn. I believe this is how the Rollover value previously behaved.
  • Net Actuals, All: This is the net sum of all transactions on the Transactions sheet for the specified period, including income, expense, and transfer types. This is similar to Cash Flow on the Monthly Budget sheet.
  • Net Actuals, Savings: This is similar to the value above, but only categories marked as Savings in the Track column of the Categories sheet are summed.
  • Offset: This is a more resilient version of what was previously called Rollover. As @randy explains it:

There are basically 3 categories of transactions that need to be handled differently to keep a budget true:

  • Budgeted transactions with rollovers turned on
  • Budgeted transactions with rollovers turned off
  • Unbudgeted transactions (e.g. transfers, uncategorized or no-longer-used categories)

The Offset formula is Net Budgets - Net Actuals, Savings + Net Actuals, All. $0 means you have no errors in your budget. A positive value represents unallocated, but available savings. All spending from non “savings” categories removes from this bucket. All income adds to this bucket. Phantom savings show as a negative [Offset] and is always an error. I.e. you’ve “saved” more than you actually have (Thank you, @matta, for the excellent phrasing).

I hope that helps!

3 Likes

Ok, so I’m a bit late to the party but I wanted to throw in my two cents, as well as ask some questions.

First, I do find rollover adjustment useful, although I agree with previous commenters that “uncategorized transactions” and “total transfers that don’t net to 0” are data errors, rather than something that needs adjusting with a rollover adjustment. And “categories that are not set as savings categories” doesn’t really affect me since I have everything roll over, but I get that it’s an important issue for you to tackle. Also, it did take my a while to understand how it works so I’m glad you guys are spending time rethinking it. I’m really looking forward to being able to use the Foundation template, but rollover is a requirement for me.

Ok, so now on to my weird use case. My problem started when my wife left her job. While she was looking for a new one our budget exceeded our income, but that was ok since we have the savings to cover it. The question was how to account for that in Tiller (I’m open for other suggestions, this is just what I came up with). First, I have an expense account called “Savings” that carries a large rollover amount and a budget of 0. Basically, this just represents all the cash we have in the bank. Then at the start of each month I look at the rollover adjustment and transfer the appropriate amount from “Savings” rollover to rollover adjustment. That lets me see how the cash in our account is decreasing while still allowing for me to budget more than our income. And have all of the numbers make sense. The biggest downside is that this transfer isn’t particularly visible, and thus could be confusing to look back at.

And now my question, how do you think I should handle this type of situation in the new Savings Budget? I know it’s a specific case, but I think it would help me understand the new system. Also, maybe I’ve missed it but with the Savings Budget “Budget Health” that you added, is there a way to adjust it at all or is it just an indicator? I definitely like it though, and while it’s a bit tough to wrap my head around it all I appreciate all of the thought and streamlining you’ve done.

1 Like