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
- If there are modifiers set to “Add (2021)” in the “2020 & 2021 Budget-year Modifiers” section, they should be added to the values in
- 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
To this formula:
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.
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.