🤔 "Rollover Adjustment" in the new Savings Budget

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!

4 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

@matt Your workflow of moving rollover savings from your Savings category to supplement your budgeted expenses is spot on. I use the same workflow when it comes to tracking savings goals and finally making the purchase. You will be happy to hear that the Savings Budget actually logs your transfer of rollover savings from one category to another on the Budget Journal sheet. Take the simple example below of me moving $21.08 from my grocery savings to cover an excess of my restaurant spending.

To answer your question, the Budget Health section is indeed an indicator that doesn’t have any configuration. I went into some detail behind what it represents in my previous post above yours.

The one thing Budget Health doesn’t take into consideration is savings, as it only deals with budgeted and actual dollars, and savings is largely a derived value. Savings health can currently be tracked for individual categories using the Savings & Debt (Prototype) sheet. By tying the savings category to a specific account, you can get at-a-glance feedback on whether or not your savings categories are sufficiently funded by their associated accounts.

2 Likes

@cculber2 thanks for the explanation. Glad to hear that the Savings Budget logs the transfer, that’s pretty straightforward. I know in the envelope budget that was tracked in the Budget History, but this seems more clear.

My use case is slightly different than the one you described though, since I have been transferring from an expense category rollover directly to Rollover Adjustment, rather than another category. This has been helpful since I don’t really have any one specific category that needs the extra rollover. Instead, I know that my expense budget exceeds my income budget (and actuals) so I don’t really pay attention to which categories are getting the extra rollover, it all just gets lumped together in Rollover Adjustment. In other words, my spending is within my expense budget, but I need to reconcile the fact that my expenses are higher than my income. I see how I could do that by decreasing my budget until it’s under my income and then transferring rollover to the categories that need it, but I’d rather have realistic numbers in my budget. I hope that makes sense. I guess I could make a separate Income category called something like “Spent Savings” and transfer rollover from my “Savings” category rollover to “Spent Savings” rollover, thus balancing everything without messing up my budget. What do you think? Am I overcomplicating things? Of course this is a temporary situation (expenses exceeding income), but it’s something I do have to deal with.

I think I understand the Budget health (definitely with the help of your comments, thanks!), I was just asking to make sure I hadn’t missed any other adjustment feature. I used that regularly in the envelope budget, but honestly it might be better not to have it since visibility on what was actually happening behind the scenes was not great. I’ll just have to get used to doing things a different way.

I haven’t yet looked into the Savings & Debt sheet, but that definitely sounds interesting!

1 Like

Hi @matt,

I think the key is that the Savings Budget’s “Budget Health” checks expect a zero-sum budget (Income == Expenses). Account for this by using a negative budgeted amount for your “Savings” expense category. Your budget will then be zero-sum, and all the math works out fine. This is what I did when playing with the Savings Budget for my 2020 data and had to account for a series of months of planned over-spending.

I don’t fully understand how the Tiller Envelope Budget treats Rollover Adjustment, but I suspect that what I describe above amounts to the same math.

1 Like

If I understand what you’re doing correctly, I often handle this sort of situation by returning that allocation to an income category. Since I have extremely variable income I allow “excess” income in fat months build up rather than assigning it to an expense category. Basically that’s how I account for unallocated short term savings amounts. That way my expense budget accurately reflects what I spent (even if that spending is actually saving to spend later, like an annual insurance payment).

1 Like

@matta I hadn’t thought of budgeting a negative amount, but that makes perfect sense. That’s definitely simpler than what I was thinking, I think I’ll switch to your way. Thanks!