Need Help with Update to Budget Plan

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!

Installed, no issues so far. :smiley:

1 Like

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.

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

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)

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.

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. :slight_smile:

1 Like