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
Copy the Budget Plan sheet from the shared template below to your Foundation Template.
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!).
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.
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:
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.
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.
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.
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.
