Did you add the dates to the āNotesā column? You need to specify which two dates you want by adding something like ā1,15ā, ā1,16ā, ā15,Lastā or ā16,Lastā.
Create two budget items, one with the start/end dates of the first period and itās amount, and the second with the start/end dates of the second period and itās amount.
To try and balance your income/expenses, I look to the totals in M1:X1. If the total is negative, you have to many expenses, if the total is positive, you have excess income that should be budgeted to something else (savings?).
@jpfieber Oops, I misunderstood and did not read your documentation on Bi-Monthly. What you call Bi-Monthly I have always referred to as Bi-Weekly, I was assuming Bi-Monthly was every other month.
Turns out on a Google search, either terminology works. My fault for not reading the documentation before asking the question!
Great idea to create two budget items with start/end dates, thank you. Also, thanks for pointing out the M1:X1 totals for balancing the budget. Appreciate the help!
Hey there jpfieber .
Iām probably stumbling all over it, but can you advise where the referred to documentation is for this template?
The very first post in this discussion, just keep scrolling to the top of this page.
Using version 1.8.1 of Budget Plan in Google Sheets.
Changing the date in Categories F1 from 7/1/2022 to 1/1/2022 makes the values in Budget Plan disappear except for the month of Dec 2022 which hasnāt happened yet. (Writing this on 2022-11-22)
Changing the value in Categories F1 back to 7/1/2022 makes them reappear in the Budget Plan.
I have transactions in the Transactions tab for the entire year.
Any idea what Iām doing wrong?
Thanks
ScottC
What does āBudget Plan!M3ā say? It looks to the Categories sheet header row and is supposed to return the smallest number, which should be the first budget month. You can over-ride this on the Budget Plan sheet by typing 1/1/22 in the cell M3, but it would be good to know why it isnāt working right so I can fix it.
ANSWERED my own question about missing values in the Budget Plan when I changed the date in Categories F1.
Changing the value in Categories F1 from 1/1/2022 to 1/1/2023 fixed the problem because I was using āPast Categoryā in the Frequency column of Budget Plan. I only have a few transactions for 2021 because I started using Tiller in 2022. So, when Budget Plan was looking at the Past Category, there were no transactions for the Past Year (2021). Makes perfect sense.
Iām sure this is mentioned somewhere in the documentation but I just missed it.
ScottC
Thanks. I feel a little dumb right now! Thereās nothing wrong with your spreadsheet. I just didnāt think the problem through completely. In short, I should have changed the value in Categories F1 to 1/1/2023 since I was using āPast Categoryā in the Frequency column.
I donāt have any transactions in the first 11 months of 2021 so getting $0 values for those months was 100% correct!
Thanks very much for a terrific template. I have to add that the formula in Budget Plan M4 is pretty intimidating!
ScottC
Disabling Transfers doesnāt change the Annual Total in Column Y of Budget Plan.
Several of the Categories I copied from Categories are of the Type, āTransfer.ā
Enabling or Disabling them in column A of Budget Plan doesnāt change the value shown in Budget Plan Y1.
Why is that?
Transfers are just movements of money from one account to another, itās not an income or expense item, so itās not something you would need to budget. Y1 simply subtracts all your budgeted expenses from your budgeted income. Since transferring money from one account to another doesnāt change how much youāve earned or spent, you shouldnāt expect any difference.
I am really loving your creation! I have 2022 on the template sheet from Community Solutions, but Iām not sure how to start planning for 2023. Do I use a new template sheet or add more columns onto the one Iām currently using?
Thanks for the kind words. Iām not totally sure yet on the best way to do this. Hereās my thought so far, which I posted in the Excel versions post:
If you want to save the 2022 budget info in your Categories sheet for use with templates that can view historic budget data:
- Select the month headers in the Categories sheet
- Grab the fill handle and drag to the right across 12 columns to get all the 2023 months listed
- Select the cells in your Dec 2022 column
- Drag the fill handle to the right to fill the empty new columns and point them to the Budget Plan sheet
- Select all the budget cells for 2022 that currently contain formulas filled by Budget Plan
- Copy
- Paste as Values, which will replace the formulas with the results of the formulas, so they will no longer change
- Go to the Budget Plan sheet and change M3 (the January column header) to 1/1/23
- You should now see all the Budget Plan data in the Categories sheet in the new columns you added for 2023
If you donāt need historic budget data, change the Jan 2022 cell header in the Categories sheet to 1/1/23 and Budget Plan will automatically update. Youāll probably want to wait till 1/1/23 to do that though.
Sorry I missed the 1.81 update to the master, @jpfieber. Just updated that in our template library. For anyone who updated or installed the template in the past 2 weeks, you may have the 1.81 content but it will show as 1.80.
Trying to budget something that occurs every 6 weeks, but the multiplier for the weekly frequency doesnāt appear to work if itās greater than 4. Any way to fix this?
I see what youāre saying. If I set the multiplier to 8, it tells me their are 8 occurrences, but it shows the amount for each month of the period, which isnāt correct. The weekly part of the formula is the most difficult, Iāll look into it and try to track down the issue. Thanks for pointing it out!
Hi all,
Loving the spreadsheet and the ability to tailor it to what I need.
I am having an issue where the December column in the Categories sheet is not populating from the Budget Plan and I cannot see the reason why. All the data cells for that month in that column are showing zero. Because of that, the monthly and yearly budget sheets do not have any values.
In the categories sheet, I do note in the formula bar that the last part of the formula, āBudget Planā!$M$3:$X$3ā, is a different color from the the rest of the months. From January to November, the characters are blue. For December they are brown. This is the only difference I can see from the rest of the months.
Am I missing something here?
Double-check for all your formulas that the part that says (DATEVALUE(F$1)
matches the column that your first month column (usually January) is. If your first month isnāt in column E, then change the āFā in āDATEVALUE(F$1),ā part of the formula to the letter of your first month column. I just updated the formula in the documentation to use āEā instead of āFā since I think E is the default if you havenāt changed anything. Change the first row in January to be right, then use the fill handle to copy it across all the columns, and then down across all the rows.
FYI, I had to do this after adding the Pool column.
Hmm, weird. Usually Sheets is smart enough to update column reference when a new column is added, especially if I donāt have a $ locking it. I wonder if it makes a difference where the new column is added. I added Pool (used for the new Budget Status template) two columns to the left of my āJanuaryā. Maybe adding it immediately adjacent to the January column that is referenced in the formula causes it not to update the formulas. Where did you add yours?