As a recent migrator from Envelope Budget to Savings Budget sheet in Foundation Template, one feature I’m missing is the text/calculation in the first few rows of the Envelope Budget that tells you if you have over- or under-allocated your budgeted income for the month. For example, if I had budgeted expenses of $1200 against expected income of $1000, I would get a bright red warning.
Can I do something - or use another tool - to have this data at the quick and ready? If not, is this something that could be built into the sheet at some point?
@Adam_B, if you unhide the columns to the right, there is a box you can check to show rollover adjustment in the report header. I use this value to ensure I’ve budgeted an expense for every dollar I have budgeted to earn. The formula is still being tweaked ( “Rollover Adjustment” in the new Savings Budget), but it is still useful if you are keeping everything categorized and up to date.
@cculber2 - Thanks so much for this tip! Just what I was looking for. Not as flashy as the tool in the Envelope Budget, but the same calculation - so I’ll take it!!
@susandennis - Thank you! I haven’t even glanced at the Monthly Budget sheet - went straight into the Savings Budget sheet. Nice to know it’s there if I need it.
@Adam_B, @randy has made some tweaks to how the formula works and where it is displayed. You will need to unhide columns H thru K to see the new data. You can read more starting from his post here.
I’ve gotten some feedback— and help! — from @cculber2, @aronos & @matta on the Rollover Adjustment/Budget Offset concept and have implemented it in the hidden area. Documentation is still thin, but @cculber2 has done a great job writing up where we stand here.
Once we see that these calculations net out and are helpful across a diverse set of community budgets, we can figure out how to render them in the unhidden area of the Savings Budget.
And for November we see the Offset, All Periods go non-zero. I have no transactions or Savings Journal activity in November, so I doubt I have a real issue. Is the computation wrong here?
@matta, from what I can guess based on your screenshots there is a negative savings of $52.78 carrying over from October to November. I looked at my own spreadsheet and I noticed a similar error, but for a positive offset. I was able to track it to a single transfer that despite being balanced on my transactions sheet, it was not balanced in O:P Actual Priors to Period. After some experimentation, I discovered an error in the QUERY() function populating these columns. The third parameter of the function tells it how many header rows are supposed to be excluded from the range specified in the first parameter. The parameter is set to 1, but this is an error as the range is already eliminating the header row on the Transactions sheet. As it turns out, my balancing transfer transaction was in row 2 of Transactions, the first row of the dataset. I changed the formula in O6 to the following, replacing 1 with 0 and the error is gone.
=if(BC21,iferror(QUERY({INDIRECT("Transactions!"&BC28&"2:"&BC28),INDIRECT("Transactions!"&BC29&"2:"&BC29),INDIRECT("Transactions!"&BC30&"2:"&BC30)},"SELECT Col2, SUM(Col3) WHERE Col2 <> '' AND Col1 >= date '"&TEXT(BC3,"yyyy-mm-dd")&"' AND Col1 < date '"&TEXT(BC2,"yyyy-mm-dd")&"' GROUP BY Col2 ORDER BY Col2 LABEL Col2 'Category', SUM(Col3) 'Actuals To Period'",0),{"Category","Actuals To Period"}),{"Category","Actuals To Period"})
Bingo! Thanks @cculber2. Yep, the first transaction in my Transactions sheet is currently -52.78, identical to the error amount I am seeing, and your fixed query solved the problem.
I can’t keep up with you two, @matta & @cculber2. I’ve implemented the fix in the master.
For consistency, I also inverted the polarity of the Total Actuals calculation in the hidden Integrated Categories section and adjusted cell J9 in the budget health section accordingly. (Polarity now matches the neighboring Period Actuals column.) This fix effectively addresses the problem you flagged here, @cculber2, at its root.
“Rollover Adjustment” in the new Savings Budget
— from 
