Budget Builder issues with Baseline Override

[hage.sara]

I’ve added the Budget Builder worksheet and followed the steps. I have removed one time line items from the previous year. But when I get to the “Set Your Baseline Monthly Budgets” step it doesn’t work quite like the instructions.

I have a minimized budget based on prior year actual, but if I enter a baseline override in to AW it doesn’t override the minimized prior-year actuals but instead adds them. For example, if I minized December by 500 and then set my average to $50 per month, the value for December is now -$450 instead of $50.

[heather]

@hage.sara,

I’m not super familiar with this solution from Labs, but @randy can probably help. He’ll chime in soon.

[randy]

I’m sorry for the delay in getting back to you, @hage.sara.

When you remove one-time items from the previous year, you should set those adjustment in the “Change” column AC to “Remove (2020)”. When set this way, they should be pulled from the 2021 baseline total in column AS and monthly budgets AX3:BI.

If you are seeing your adjustments added after setting a baseline in AX3:BI, you likely have your one-time modifiers to “Add (2021)” in column AC instead of “Remove (2020)”.

Can you check your sheet for this issue?

When you override with a “Baseline Override” in AW, you should see the same value in AX:BI unless you have a modifier for the category set with “Add (2021)” in column `AC. Does this make sense?

I hope we can get this process to work for you.
Again… really sorry I couldn’t offer help sooner.
Randy

[hage.sara]

Thanks Randy for the reply.

I do have it set the way you suggest, but it’s still not quite working. I’d love to show you a screen shot, but I don’t see a way to do that.

Here’s a summary of what I have:

column E: Home Repairs - 2020 total -$5090
column Z: box is checked
AB: Home Repairs category is selected
AC: Remove 2020
AD: $4390
AI May 2021 $595
AJ June 202: $1665
AL August: $924
AN October: $1206

AR 2020 total: 5090 AS 2021: -3790 (note that this looks really wrong. It should be $700.
AV (Average): -$316
AW: $50
BB May 2021: -$545
BC June 2021: -$1,615
BE August 2021: -$874
BG October 2021: -$1156

I’d appreciate any help?

[hage.sara]

Hi Randy - thanks and sorry for missing this. Yes, I’m happy to share. Will do so now.

[hage.sara]

I’d be thrilled if you all looked at the split transaction tool!
I’ve been trying to split transaction 601e7c103af894001a3146b3 to split out $20 and it won’t work. And I can’t split any transaction without getting an error message!

My files have been shared with support.

[randy]

I’m seeing the same issue with the Splitter (which is not normal):
Tiller Money Labs: Master transaction has changed— split operation cancelled

My guess is that the reason is that you have two Transaction ID columns. I’d recommend renaming or archiving the first one. Can you give that a try?

Randy

[randy]

Thanks for your patience, @hage.sara. I can see the bug you are talking about.

In retrospect, I’m not sure the Baseline Override functionality is super intuitive. I added it for situations where the monthly numbers just got too weird or lumpy. For example, if a non-seasonal category like Groceries had an aberrant month in 2020, there is no reason to reproduce that spike in 2021— it just mades sense to peanut-butter the average monthly cost across 2021.

This column can be left blank if you are satisfied with a lumpy budget based precisely on 2020 actuals net your modifications from the “2020 & 2021 Budget-year Modifiers” section. But, if you feel like the result is just to rollercoaster-y to be useful, adding a value in AW allows you to normalize the month-to-month values to something that is rounded and less fussy. Make sense?

I think the desired behavior is this…

• AX7:BI should start with monthly actuals from the prior year.
• If there are modifiers set to “Remove (2020)” in the “2020 & 2021 Budget-year Modifiers” section, they should be removed from the values in AX7:BI.
• If there are modifiers set to “Add (2021)” in the “2020 & 2021 Budget-year Modifiers” section, they should be added to the values in AX7:BI.
• If there is a baseline override in AW7:AW, it should replace the baseline (which is prior year actuals minus the “Remove (2020)” modifiers) for all months but the “Add (2021)” modifier should be preserved and added to the baseline in each month.

I think you understood all this… I’m just playing it back so we’re on the same page.

Digging into this, I noticed that the add & remove modifiers were being treated the same when a baseline override was set. Per the fourth bullet above, I think setting a baseline override should “override” the remove modifier but preserve the add modifier. Do you agree?

To implement this, I changed the formula applied to AX7:BI from:
=if(isblank($A7),iferror(1/0),if(isblank($AW7),if($A7="Expense",-1,1)*H7,$AW7)+sumproduct(($Z$3:$Z)*($AB$3:$AB=$C7)*AE$3:AE*if($AC$3:$AC=$BM$32,-1,1)))
To this formula:
=if(isblank($A7),iferror(1/0),if(isblank($AW7),if($A7="Expense",-1,1)*H7-sumproduct(($Z$3:$Z)*($AB$3:$AB=$C7)*AE$3:AE*($AC$3:$AC=$BM$32)),$AW7)+sumproduct(($Z$3:$Z)*($AB$3:$AB=$C7)*AE$3:AE*($AC$3:$AC=$BM$31)))

Because I know the Budget Builder is a hard sheet to restore (so many cells to migrate), I made this change in your shared spreadsheet.

Can you let me know if the new behavior makes sense? Happy to make further changes if I don’t have it right yet.

Hope this helps.
Randy

P.S. Many sections reference the category order in column C… but you do not need to preserve that order in the “2020 & 2021 Budget-year Modifiers” section. You can just make a list and the formulas will line up the modifiers with the correct category rows. In other words, you can remove the empty rows if you’d like.

[hage.sara]

Thanks Randy. Deleting the second Transaction ID column worked. That was a leftover from when I migrated from the previous version to the new Foundation template.

[randy]

Great news, @hage.sara. Any update on the Budget Builder fix?

[hage.sara]

Yes! Thanks so much. That works and makes sense. I also appreciate the P.S. tip about maintaining consistency across the rows. That is also helpful. I thought that might be the case, but wanted them to be consistent just in case. I really appreciate the help and patience!

[randy]

It’s kind of a personal process and not necessarily the most intuitive… but it is how I think about planning.
I’m happy the tool worked for your, @hage.sara.

Thanks for your patience with the fix.
(You can unshare your sheet if you haven’t already.)