Budget Builder Baseline Override Modification

Google Sheets Show & Tell
1

I’ve been utilizing the Budget Builder worksheet . THANK-YOU @Randy, I appreciate the flexibility when updating a budget. However, I was surprised when sorting the Category sheet resulted in the Baseline Override values remaining in the original row, now tied to a different category. Worse, I had continued working awhile before noticing it. That set me off on a path to avoid this issue.

I decided to use the Modifiers section to first offset 2021 budget amounts per month, then use a separate row to add the new desired budget values. That was ok, but to much copying and modifying the formulas was required. The next iteration was to add a Column (AE) in the Modifiers section for “Budget Override” and adjusted the formula to subtract AE from the 2021 actual for each month. When applied the resulted in the desired budget amount. Finally, modifying the formula to match Category in the Modifier section with Column D to retrieving last years values. Voila, now Budget Overrides are linked to the Category, no matter what order they are in. The formula still needs to be copied into the January column and dragged over to December for rows this feature is desired, but I like the functionality of this implementation better.

Steps to use my modification:
Insert 1 Column to the left of the Total (AE in the original)
Name it Budget Override or ???
Paste this formula into the top modifier row in the January column (AG4). Copy and Paste the formula in to cells AH4 to AR4. This is your template to copy and paste into any rows you want to set the same budget amount for every month.

=sumproduct(($D$3:$D=$AC4)*I$3:I*-1)-$AE4
  • Select the Category you want to change as normal
  • Enter your description in the Modifier Column, I chose “Set Budget”
  • Use the Remove(YYYY) in the change column
  • Update the Budget Override column (AE) with your desired budget
  • Copy the template row above, AG4:AR4, into the current row
  • Select the Checkbox to apply the modification.
  • Verify your budget is as expected

I suppose adding a second checkbox might be used to apply the formula to any rows desired would make it even easer to use.

@Randy feel free to incorporate this idea into the Budget Builder sheet.

Dan

1 Like