Need Help with Update to Budget Plan

[jpfieber]

As I have worked on follow ups for the Budget Plan template, like Budget Status and a still to be finished sheet likely to be called “Budget Forecast”, I found I was having to regenerate a lot of the Budget Plan calculations in a different way to get the data I required. This was resulting in a lot of duplicate effort.

When I initially started creating the Budget Plan sheet, one of the goals was to make it not need any helper columns (extra columns of data hidden off to the side). This was primarily so it was easy to sort. The result was some very complex formulas that are hard to troubleshoot, which is a burden every time someone reports a bug. In the end, the way it was structured didn’t allow for easy sorting anyway.

I’ve revisited how Budget Plan’s backend works, resulting in a structure that is much easier to troubleshoot, and offers easier opportunities to build upon as well. I’m not sure if it’s more or less efficient in its need for processing power. Plus, sorting, though not simple, is improved (can sort by “Importance” or “Notes” which wasn’t possible before).

I’m hoping a few people that are already using Budget Plan and are comfortable with doing manual updates (Super Heros?) could give this a try to help confirm that it is working well before we push it through the Community Solutions plugin to lots of people.

The Changes

Behavior:

• The “Description” field is now required, and each description must be unique. If you enter a description that already exists, conditional formatting will highlight the two cells, in the same way the Categories sheet will handle duplicate categories.

Interface:

• The column headers for the budget period now include month/year headers, and cell M2 is changeable, so you can more easily set the beginning of your budget period.

• A “Helper Data” section was added to allow a new way of calculating the back end numbers.

Improvements:

• The Occurrences column now uses an array formula instead of a formula pasted down the column (now unlimited number of budget items).

Bugs:

• Weekly budget items would break if a multiplier of more than 4 was used.

• ExternalSource budget items weren’t behaving normally if the referenced sheet name had a space.

• Budget item “Type” is no longer hard coded, will find the column if others have been added.

Updating

1. Copy the Budget Plan sheet from the shared template below to your Foundation Template.

2. Copy all the Budget Item data you entered in the green cells of Budget Plan to the new sheet by copy/“paste as values” (don’t forget the Notes column!).

3. Check if the numbers in the new Budget Plan match those in the old Budget Plan. If you see a difference, let me know which frequency you’re using, and which template appears to have the correct numbers. I ran across a couple issues that were with the old sheet, but forgot to make note of them, I believe the new sheet will be the more reliable one.

4. If you’re comfortable with the new template and want to use it, redo the formula in the Categories sheet, changing “Budget Plan” to whatever the new sheet is currently called:

   =IF(ISBLANK($A2),"",IFERROR(SUMIF('Budget Plan'!$E$4:$E$200,$A2,OFFSET('Budget Plan'!$L$4:$L$200,0,MATCH(DATEVALUE(E$1),'Budget Plan'!$M$3:$X$3,0))),0))

5. If you want to delete the old Budget Plan sheet, you can then rename the new one and the formula in the Categories sheet should automatically update.

Let me know how it goes, and thanks for any assistance you can provide!

Budget Plan 2

[MarcC]

Installed, no issues so far.

[yossiea]

Looks good so far. The only change I did was put a sort(unique( on the categories so I don’t need to paste anything, but have all my cats on the page.

[jpfieber]

Glad to hear it’s working! Not following on your categories change, but sounds interesting, tell me more!

[yossiea]

Just getting a list from the Categories sheet, so I don’t need to pull them in manually.
My column E is just =unique(Categories!A2:A)

[jpfieber]

ahh, gotcha. If you only do one budget item per category that works, but you’re missing all the fun of breaking some of your categories down into details! Word of warning: if you ever add a new category to your Categories sheet, some of your Budget Plan info will be associated with the wrong category.

[yossiea]

Gotcha, I was actually thinking about it, but I might just use tags or different category. Right now I have over 50 categories and I want to pare it down, not increase.

[MidnightPrzm]

I just set this sheet up and have noticed that not all budgets get populated. Most of the budgets populate as long as I set up the correct description. Not setting the correct description just won’t populate the right budget in comparison to the transaction sheet but budget item 221 is set to monthly and just has zeros for every month even though I copied the exact same description from the transaction sheet and pasted it as values. Every budget before it and some after it is working as intended. Starting from budget 261 and all the way down, all of them show values of zeros just like 221 even though they have descriptions and a value under the amount column. I am unsure what I am doing wrong. This occurs regardless if they are monthly or annual amounts. When I look at column AF I notice that only the name of the budget populates. Everything else is empty. AG (status) all the way down are completely emtpy. Other budgets above and below it are filled up whether they are enabled/disabled, category & group info, occurrences, etc.

Also, is there an equation that I can use to indicate the month that would change based on the current year? The problem that I encounter is if I set up a date from a previous year nothing populates until I change it to 2023. But if I leave it blank like I have needed to do for all my monthly budgets, all the budgets are set for January regardless of what specific month they should be. I have to set the correct month within the current year otherwise the solution doesn’t recognize it. If you have a bunch of annual subscriptions then that makes it cumbersome to need to update the years manually every new year.

Those are the 2 issues that I have encountered.

[jpfieber]

As mentioned, the “Description” field is now required, and each description must be unique. I’m not really understanding what you’re describing. Column AF should show the ‘Description’ of each budget item, repeated the number of times shown in column J (Occurrences). If it’s stopping at some point and not showing all of the descriptions, you should look at the budget item where it stops to see if there is something out of the ordinary about that one (special characters in descriptions? Occurrence number not correct?). Let me know if you find something that is breaking it, perhaps I can find a way to prevent that from happening, or we can at least document what doesn’t work.

I understand your request for the second issue, and that does make sense. I don’t have a solution for that now, but will add to the list of improvements and will hopefully implement it in a future update.

[MidnightPrzm]

Most of my descriptions are copied and pasted directly from my transactions sheet. I had to edit a bunch of them so that they all match historically and moving forward. The likelihood of typos is probably minimal due to this I double-checked and I don’t think I see any. I sent some screenshots to you so you can see what I am speaking of.

[MarcC]

Hi @jpfieber, Have you made any updates to the 2.0 version of the budget template? Also, when do you think you might push out the 2.0 template?

[joeldodge]

I use the ExternalSource type heavily and update the external sheet frequently for some items. I noticed that I have to “force” a refresh per item to get the updated values (currently I change the type from ExternalSource to something else and then back to ExternalSource which causes a refresh). Is there a shortcut to refresh every ExternalSource item in the budget?

My use case: part of my compensation each month is variable. I can predict it somewhat but I like to go in and change the budget item each month to reflect my actual compensation so that I can adjust my expenses as necessary.

Love Budget Plan, thank you so much!

[jpfieber]

I hadn’t run across that problem, but Googling around, it appears that it’s due to the INDIRECT function that I use to make the ExternalSource frequency work. I’ve seen suggestions on how to make it refresh more often ( Why does my Google Sheets formula not automatically recalculate? - Stack Overflow) but I suspect this would really slow your template down. There’s some other ideas in that link on how to trigger a refresh, perhaps one of them would work, but their not much better than the process you’re currently using (switching the frequency then switching back).

[joeldodge]

Got it, thanks!

Another thing I noticed: some ExternalSource budget item occurrences are getting mis-counted (e.g. some have 24 entries, some have 11, when they all have 12 actual entries in the source sheet). I could see the incorrect number of lines in the helper data… I could try to setup a repro for you if you want… I really like the ExternalSource feature For now, the original Budget Plan (1.81) is working exactly how I need it to

[gary]

testing out the new update in order to get sorting of the the Importance column going. Unfortunately I get the error “You can’t sort a range containing vertical merges. There is a vertical merge at AE4:AE22” when attempting to sort the column Z-A.

if I unmerge the AE column, the sort works but is all wacked with disabled categories at the top and then blank and finally actual categories ALL THE WAY at the bottom of the 1000 row sheet

any suggestions? thanks!

[jpfieber]

You can’t sort by row, you instead need to sort by range. Select A4 through the last budget item in column AD, then choose Data/Sort Range/Advanced Range Sorting Options
This will allow you to choose the column you want to sort the range by, and which direction to sort by.

[brettanicus]

Version 2.0 is looking good. I like that I could sort without getting the #REF issue. Performance is very good too, even if you change a large number of Frequency to “Past Description,” which used to cause slowness to overall sheet recalc. I did run into an issue when trying to insert a column (between E and F), which didn’t cause a problem in version 1.81. All of the calculations to the right zero out. Any chance the formulas could be updated to handle column inserts?

[brettanicus]

@jpfieber , just wondering if you had a chance to look at the issue I found in Budget Plan 2 when adding a column. Thx!

[jpfieber]

I just took a look at it. One of the problems is related to a couple VLOOKUPs. VLOOKUP specifies a range, and then column index numbers to return from that range. If you add a column within that range, the ranges expands, but the column reference numbers don’t change. I was able to adjust them to work if a column is added:
Change AT3 (Amount) to:

={"Amount";MAP(
   AF4:AF,AH4:AH,AN4:AN,AM4:AM,AQ4:AQ,AR4:AR,AJ4:AJ,AW4:AW,L4:L,AO4:AO,AP4:AP,
   LAMBDA(
      description,type,multiplier,frequency,speriod,eperiod,category,date,amount,occurrences,notes,
      IF(ISBLANK(frequency),"",
         IFERROR(IFS(
            ((frequency="Monthly")+(frequency="Weekly")+(frequency="Annually")+(frequency="Workdays")+(frequency="Bi-Monthly")),
               VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1),
            frequency="Monthly-AVG-Year",
               ((VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))*ROUNDUP((DATEDIF(speriod,eperiod,"m") + (DATEDIF(speriod,eperiod,"md")/30))/IF(ISBLANK(multiplier),1,multiplier)))/12,
            frequency="Monthly-AVG-Period",
               ((VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))*ROUNDUP((DATEDIF(speriod,eperiod,"m") + (DATEDIF(speriod,eperiod,"md")/30))/IF(ISBLANK(multiplier),1,multiplier)))/occurrences,
            frequency="Weekly-AVG-Year",
               (VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1)*(ROUNDDOWN(DATEDIF(speriod,eperiod,"D")/(7*(IF(multiplier<>"",multiplier,1))),0)+1))/12,
            frequency="Weekly-AVG-Period",
               (VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1)*(ROUNDDOWN(DATEDIF(speriod,eperiod,"D")/(7*(IF(multiplier<>"",multiplier,1))),0)+1))/occurrences,
            frequency="Annually-AVG-Year",
               (VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))/12,
            frequency="Past-Category",
               (SUMIFS(INDIRECT($BG$6),INDIRECT($BG$7),"="&category,INDIRECT($BG$4),">="&EOMONTH(date,-13)+1,INDIRECT($BG$4),"<="&EOMONTH(date,-12)))*IF(ISBLANK(multiplier),1,multiplier),
            frequency="Past-Category-AVG-Year",
               ((SUMIFS(INDIRECT($BG$6),INDIRECT($BG$7),"="&category,INDIRECT($BG$4),">="&EOMONTH(speriod,-13)+1,INDIRECT($BG$4),"<="&EOMONTH(eperiod,-12)))*IF(ISBLANK(multiplier),1,multiplier))/12,
            frequency="Past-Description",
               (SUMIFS(INDIRECT($BG$6),INDIRECT($BG$5),"="&description,INDIRECT($BG$4),">="&EOMONTH(date,-13)+1,INDIRECT($BG$4),"<="&EOMONTH(date,-12)))*IF(ISBLANK(multiplier),1,multiplier),
            frequency="Past-Description-AVG-Year",
               ((SUMIFS(INDIRECT($BG$6),INDIRECT($BG$5),"="&description,INDIRECT($BG$4),">="&EOMONTH(speriod,-13)+1,INDIRECT($BG$4),"<="&EOMONTH(eperiod,-12)))*IF(ISBLANK(multiplier),1,multiplier))/12,
            frequency="Workdays-AVG-Period",
               ((VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))*(NETWORKDAYS.INTL(speriod,eperiod,IF(ISBLANK(multiplier),1,multiplier))))/occurrences,
            frequency="Workdays-AVG-Year",
               ((VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))*(NETWORKDAYS.INTL(speriod,eperiod,IF(ISBLANK(multiplier),1,multiplier))))/12,
            frequency="SpreadOverPeriod",
               (VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))/occurrences,
            frequency="SpreadOverYear",
               (VLOOKUP(description,{B4:B,L4:L},2,FALSE)*IF(type="Expense",-1,1))/12,
            frequency="ExternalSource", HLOOKUP(date,INDIRECT("'"&LEFT(notes,FIND("!",notes)-1)&"'!"&RIGHT(notes,LEN(notes)-FIND("!",notes))),2,FALSE)*IF(type="Expense",-1,1)
         ),"")
      )
   )
)}

Change AU3 (1st Occurrence) to:

={"1st Occurance";MAP(
   AF4:AF,
   LAMBDA(
      description,
      IFERROR(IF(
         ISTEXT(description),
         IF(
            VLOOKUP(description,{B4:B,F4:F},2,FALSE)<>"",
            IF(VLOOKUP(description,{B4:B,F4:F},2,FALSE)>=M2,VLOOKUP(description,{B4:B,F4:F},2,FALSE),EDATE(VLOOKUP(description,{B4:B,F4:F},2,FALSE),12)),
            $M$2
         ),
         IFERROR(1/0)
      ),"")
   )
)}

The other problem is with AV3. The range it is looking for is derived through combining text and a row count, so when a new column is added, the column being referenced in this cell doesn’t update. Not sure how to make this one more dynamic since the column it references isn’t the only one called “Description”. If you want to manually update it, and you’ve only inserted one new column, change AV3 (Occurrence) to:

={"Occurrence";BYROW(
   AG4:AG,
   LAMBDA(
      item,
      IF(ISTEXT(item),COUNTIF(
         INDIRECT("AG4:AG"&ROW(item)),
         INDIRECT("AG"&ROW(item))
      ),"")
   )
)}

If you add another column, the "AG"s would have to change to “AH”, etc. Hope this works/helps!

[brettanicus]

That will work, thanks. I’ll play around with the occurrence formula this weekend in case there’s something I can think of to make it dynamic. The column I added is “Split” to indicate if a budget description line is “split,” “Person A”, or “Person B”. Helps to see the budget outcome for shared expenses.