I added it right after Group, but I think it might be that I also added a column to theBudget Plan to view spend for the month. Iām going to do a clean install and get rid of some columns I donāt need and try again.
-edit- Also, in Categories, should you use Column E, or the first Jan in the year youāre budgeting? (Right now I have all my years in the categories sheet. Iāve been using that column, rather than the first January.)
For the Budget Plan formula in the Categories sheet, use the first column you want Budget Plan to work in, and donāt put the formula in columns before that since they wonāt do anything but slow things down.
I ended up installing it from Tiller, but my January is still showing $0. Did you change it after you added the occurrences column?
Never mind, I changed M from min() to 1/1/2023 and it now works.
1 Like
I did notice something strange though, if I use past for an income amount, depending on which PAST I use, I get negative numbers for every month.
Also, in the Budget Plan, your columns to lookup Categories is set to A:C, not to an indirect, so you do need to put the Pool after Type.
The formula does check if itās an āIncomeā type, and if not, multiplies what it fetches by -1. Are you sure the āTypeā for that category is āIncomeā and not āExpenseā? Also, are you sure you didnāt have more income in an expense category than expenses, which would also make the number returned negative?
Do you mean the Data Validation on column E? It looks to =Categories!$A$2:A (I think I tried an INDIRECT there but it wouldnāt work). I donāt see any other direct references to the Categories sheet.
Thanks, Iāll take a look.
What I meant was in the Budget Plan, column C is: ={āTypeā; ARRAYFORMULA( IFERROR( VLOOKUP( E4:E, INDIRECT(āCategories!$A$2:$Cā), 3, FALSE ), IFERROR(1/0) ) ) }
So if Categories A:C is something else, it will show up.
Ahh, youāre right. I can make that smarter with some INDIRECTs. In the next version Iāll update it to:
={"Type"; ARRAYFORMULA( IFERROR( VLOOKUP( E4:E, {INDIRECT(BD2),INDIRECT(BD3)}, 2, FALSE ), IFERROR(1/0) ) ) }
Thanks!
Awesome, that fixed it!! Thanks JP!
1 Like
Hi @jpfieber, I am playing with your Budget Plan after seeing it for the first time in the webinar today. Can you say a few words about why you have included the external source option? What need were you addressing when you included it and how have you conceived of people using it?
And if anyone else is using that option, how have you put it to use?
Though Budget Plan has a lot of options for frequency, I know it wonāt be able to meet every need, so I added External Reference as a way to point off to a different sheet where different processes can be used to determine the budget numbers. Iāll be releasing an example of this soon, maybe over Christmas break. Iād be curious as well if anyone else has made use of it!
Thank you!
I feel like I am regularly making adjustments to individual months based on unexpected income or the desire to move money from one budget category to another and I donāt see how to use this plan to do that without adding a new line each time. That said, I realized that I can still use it to better plan (like the name of the tab says) my budget. Iām excited to upgrade my planning from a simple yearly amount divided by 12 for every category. Your tool makes it so easy to have the actual amounts closer to how they come in or go out.
3 Likes
I do as well which is why Iāve liked using the Savings Budget sheet for much of 2022. Our spend varies widely month to month for several Categories and Iāve actually written out a month end process/script for how to use the Savings Budget template. Using that solved a persistent use case for me on how to account for and use excess cash month to month.
Having said that, the Budget Plan is kickass from a planning standpoint. Iāve set it up for 2023 after attending the webinar and watching jpfeiberās how to video. Itās the best budget planning tool Iāve ever used from the standpoint of being able to finetune your plan to the most granular level. The one con of Budget Plan tool (which can probably be solved somehow) is that budgeting from Past Categories that are Actuals wonāt be possible in Year 2 of using the Budget Plan.
A combination of jpfeiberās Budget Plan with the capability of moving money around in the Savings Budget would be the end all be all budget tool.
Thanks for the kind words! The Past Categories/Descriptions options wonāt work unless you have a full year of transactions. Year 2 though (and every year after), should work perfectly since it will be able to look back to last years transactions for each month.
I too like the ability to shuffle money around, and used that feature of Savings Budget. Iāve tried to replicate that ability in the new template I released recently, Budget Status.
The āCategory Transfer Toolā (section 6) allows you to transfer money from one category to another. This change wonāt show up on any other templates (would have to get into scripting like Savings Budget does, which Iāve tried to avoid), but on the Budget Status sheet it will display the currently available amounts considering the transfers you record. Give it a try and let me know if you have any ideas on how to improve it!
Dude! After reading up on your Budget Status and Category Transfer Tool that is awesome sounding and I think solves my use case of needing to move money around! And I think preserving the original budget number is actually the better outcome than Savings Budget which overwrites the old budget number when you move it.
Downloading and will let you know.
Sounds good! Do note, Savings Budget can move money around without affecting your budget if you set the āAdjust Ā± Modifiesā to āSavingsā (thatās how I used to track my stuff), so if Budget Status isnāt your cup of tea, Savings Budget may still do what you need.
I was just coming here to see if this issue had been dealt with already.
My solution was slightly different, I changed the formula in āBudget Planā!M3 to refer to the cell where my budget year starts, e.g. āCategoriesā!A01, which is where Jan 2023 starts. I have 3 years of past category/budget in the sheet already.
At the point in the documentation where you mention changing the reference in the formula pasted into the Categories sheet, you might also talk about having to change the Budget Plan sheet too. I think if you do one, you have to do the other, if Iām understanding this correctly.
Thanks for this sheet - terrific good work!
Iāve started trying to use the External Reference but have not had any luck. Granted, I have not spent much time on it but using it I am getting āan array value could not be foundā error at this time. I would be curious to see an example and review to see what I maybe missing in my original try. Iāll keep troubleshooting but overall, this new sheet is very powerful and covers many of the most common items for budgeting which I look forward to utilizing in the coming year. Very well done and appreciative of your work and contribution to the entire community.
When sharing the template to many others who may have customized their sheet, doing a search or directly entering the date seemed the two easiest solutions, but putting a reference to your Jan 2023 cell instead of typing in the date is certainly a valid option.
Making the change in the formula on the Categories sheet is done to orient the formula to which month it should look for on the Budget Plan sheet. If only one year of budgeting exists on Categories, no change is required on Budget Plan, but as you mention, having more than one year does raise the need for some tweaks. Iāll add something to the documentation, thanks for the suggestion!