Love the tool that youāve created and have been working this past week to get it ready for 2023. I have a question that I searched for but canāt tell if itās been discussed already:
I do freelance work where the income in the year is variable (dependent on how many clients I can land in a month) and the payment arrives at the start and end of the work (typically 3 months apart). I can use Past-Category or Past-Category-AVG to generally plan the coming year based on when I landed clients the previous year. However, is there a way for me to change the budget during the year? For example, if in Feb 2023 I realize Iām going to land more clients than I had in Feb 2022, can I update the Budget Plan somehow to reflect that the payments that come in May 2023 will be higher?
I canāt think of a simple way of doing this without creating something incredibly complex, like multiple rows, one for each month of 2023ā¦
If you use one of the āpastā frequencies, you could always add a multiplier on it to increase the numbers. For example, if you start out just using āPast-Categoryā and then realize youāre income is going to be higher than it was last year, try to estimate by how much it will be higher. If it will be 10% higher, add a 1.1 multiplier. If 25% higher, add a 1.25 multiplier.
You could also use multiple rows with start and end dates and each could have whatever frequency and multiplier makes the most sense.
@faisal Iād recommend looking into using the ExternalSource option. I am personally using this for items that fluctuate from month to month throughout the year (Electric and Gas for home). This allows you to create your budget category different from one month to the other as different parts of the year one will be higher than the other, etc.
This would allow you to adjust as well when needed pretty easily. There are some items above from myself and another who used the ExternalSource but I know @jpfieber did mention he was going to try to create some examples after the holidays of this feature. Itās a thought that may help you with what you are looking to do.
If you expand the + above column AE, do you see a letter in AJ1? Thats where Budget Plan looks to see what column your categories are in on the Transactions sheet. If that looks correct, Iād go to your Transactions sheet and use the Filter tool to filter down one of the months from last year where youāre expecting to get numbers from to ensure there are actually transactions during that period for that category. Itās happened before where someone thought they had transactions, but then realized they were categorized differently than they thought, or didnāt realize the transactions didnāt start until a later date.
Sorry, but Iām not sure I understand what you mean with the below. Can you explain a little more. āIf you expand the + above column AE, do you see a letter in AJ1? ā
In the Budget Plan sheet, if you look at the column headers, scroll over until you see a column labeled āAEā. Above the letters āAEā you should see a ā+ā symbol. If you click on it, it should expand and show you a bunch of hidden columns, one of which is āAJā. In the first row of column AJ should be a letter (in my case it says āDā). Does yours show you a letter, and then to the right of it, it should show something like 'Transactions'!$D$2:$D?
Yes. It does show by me. Thanks for explaining that. I confirmed to have transactions of the category and it still doesnāt bring any numbers to the budget plan.
I appreciate your help with this.
Iām trying to sort by importance, but every way I do it seems to break the other formulas. Any recommendations for how to sort without a result of #REF all over the sheet? Thanks in advance! This budget is a great help as I set up for 2023!
Where you see my picture and name on the left side of this message, click on ājpfieberā and a window will open, click on āMessageā and it will open a window to compose a message directly to me instead of to this post.
Iām looking at using this for 2023, but Iām wondering what happens at the end of 2023. Based on the formulae, it looks like you canāt have multiple sheets (there are refs to the āBudget Planā sheet). Would I just update the date in Budget Plan to Jan 2024? What happens to my 2023 budget in the Categories sheet? Is there a way to keep it. Do I āfreezeā my Categories for 2023 by overwriting the formulae with values? Trying to help out future me.
Yep.
The formula looks at the Jan to get the numbers. After 2022, copy/paste special values (so the numbers donāt get overwritten), and then replace the formula with the new 2023 Jan column.
So just replace the āe1ā with 2023 Jan.
This is really great tool! When using the āPast-Descriptionā in the Frequency drop-down, is there a way to make it match the description of transactions only within the currently selected Category, instead of matching all transactions with that description regardless of category? I found that I can use some form of regular express matching using things like āZelle*ā in the Description field. It would be really helpful to see monthly actuals breakdown for only those Zelle transactions that I had categorized as, say, āSportsā.
Okay. This sheet is wonderful. And it may be the first time Iāve been able to do my budget, account for changes in the coming year, and not have to create a dozen extra categories to manage it. That said, Iāve hit a snag. I filled it in and then reordered the sheet so my Income was at the top and then expenses organized by category underneath so it was easier to see similar items and make sure things matched. I was careful not to reorganize the top row which matches to the array formula in M4. Unfortunately, Iām now getting an error message Iāve never seen before and canāt find any help from Google. The error, which is in M4 through AB4 and M1 through Z1, reads: Error Calculation limit was reached while trying to compute this formula. What did I do wrong and how can I fix it? Thank you.
I got that error a couple days ago myself while doing some rearranging. Based on what Iāve Googled, I think itās a bug in Sheets, and itās likely something Google will fix at some point. Iād recommend using Version History to go back to before you had the error. When sorting on Budget Plan, just select the cells to the left of M A4:L??, and do a Data/Sort Range/Advanced Range Sorting Options
This allows you to pick which column to sort by, and then only sorts within the selection, which will avoid messing with the formulas on the right hand of the sheet.