🏆 Budget Plan - Google Sheets

[jpfieber]

Overview

I’ve been struggling with budgeting. I’ve jumped between using the Categories sheet, the Budget Builder template, and the Category Scheduler template, and though each has their strengths, each falls short of the way I’d like to handle budget planning.

I created the Budget Plan template in an attempt to meet all the budget planning needs I could think of:

• Keep a list of all budget items so you don’t have to remember what each category includes
• Create a budget for items based on any multiple of weeks, months or years
• Create a budget for items based on last years transactions using Description or Category, and increase/decrease using a multiplier
• Create a budget for items in the month they occur, average them over the entire year, or average them over a specified period
• Set specific starting and ending dates to increase the accuracy of when funds are allocated
• Allow budget items to be temporarily disabled so you can keep track of them without their funds affecting the budget
• Quickly see the annual cost of each budgeted item
• Quickly see the averaged monthly cost of each budget item
• Quickly see the minimum and maximum amounts over the year for funds allocated in the month they occur
• Quickly see the budget amount each month for each budgeted item

It’s very easy to start out with, but offers powerful options to allow you to customize your budget to your needs. It can feed into your Categories sheet, which means it’s compatible with any Tiller budgeting templates, including Monthly Budget, Yearly Budget and Savings Budget (Note: If you update your budget using the Savings Budget workflow, it will alter your Categories sheet so those categories no longer show the Budget Plan information so if you use this template, DO NOT do “Budget” changes in the Savings Budget template. “Savings” changes in the Savings Budget template are fine).

Installation

Install the template from the Tiller Community Solutions Add-on

Once installed, go to your “Categories” sheet where we’re going to replace all the cells below each month with a formula that grabs info from the Budget Plan sheet. If you have existing budget data, you may want to copy it as we’re about to erase it. Select cell E2, which should be just below your January heading in a default sheet (yours may differ if you made changes). Click into the formula bar, paste the following formula and press ENTER:

=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))

Note if you’re first month column (usually January) isn’t in column E, then change the ‘E’ in “DATEVALUE(E$1),” part of the formula to the letter of your first month column.

To fill this into the rest of the cells, select cell E2 again, then grab the “fill handle” in the lower right corner corner of the cell, and drag to the right until you cover all the way to the right of the sheet. Let go, then grab the handle again and drag down until you get to the bottom of the sheet. That formula should now exist in all your cells, and they should all show $0.00 since you haven’t filled anything into the Budget Plan yet.

If you have more than one year of budgeting info on the Categories sheet, should only put the above formula in the last 12 months, and you should change the ‘Jan’ date header in the Budget Plan sheet to the first month of your budget period (eg. 1/1/23).

Usage

If you’ve used other Tiller templates, this one should be fairly intuitive. A couple points to mention:

• Only edit cells that have a light green background. Modifying any other cells will likely break the template.
• The ‘+’ above the J/W columns expand/contracts to show or hide the Budget Schedule.
• All budget items, either expense or income, are entered as positive numbers, the same way the basic “Categories” sheet budget items are.
• Have a budget item you don’t need this year, but you might need it again next year? Set the Status to ‘Disable’ and it won’t appear in the Budget Schedule.
• The Category for a budget item must be chosen from the drop-down menu, typing anything else either won’t work, or will break that budget item.
• Once you have a list of budget items, you can sort them to make navigating/understanding them easier. I like to sort first by Group, then by Category (you can of course sort any way you like, it won’t affect your budgeting amounts). To sort, click on the row 3 header and drag down until all the rows you’ve filled have been selected. Go to Data\Sort Range\Advanced Range Sorting Options. Check “Data has header row”, then choose “Group” from the “Sort by” dropdown. Click “Add another sort column” and choose “Category” from the dropdown. Click Sort.

Frequency Options

• Weekly: Choose Weekly when budget items occur on the same day every week or multiple of weeks. You can use the Multiplier column to choose every other week (2), every three weeks (3), etc. You can use the Start Date and End Date to limit the budget to a portion of the year.
• Weekly-AVG-Period: The same as Weekly, but the total for the period is averaged over the months in the period set by the Start Date and End Date.
• Weekly-AVG-Year: The same as Weekly, but the total for the period is averaged over the entire year (eg budgeted for weekly hockey lessons in November and December but the cost is spread over all 12 months of the year).
• Monthly: Choose Monthly when budget items occur on the same date every month or multiple of months. You can use the Multiplier column to choose every other month (2), every three months (3), etc. You can use the Start Date and End Date to limit the budget to a portion of the year.
• Monthly-AVG-Period: The same as Monthly, but the total for the period is averaged over the months in the period set by the Start Date and End Date.
• Monthly-AVG-Year: The same as Monthly, but the total for the period is averaged over the entire year (eg budgeted for monthly hockey lessons in November and December but the cost is spread over all 12 months of the year).
• Bi-Monthly: Choose Bi-Monthly when you get paid twice a month. You can use the Start Date and End Date to limit the budget to a portion of the year. Use the “Notes” column to define which days you get paid. You should include the two days of the month that you get each check, separated by a comma. If you get paid on the last day of the month, use “Last” instead of a date since it varies by month. The most common examples would be “1,15” , “1,16” , “15,Last” and “16,Last” (don’t include the quotes).
• Annually: Choose Annually when budget items occur in the same month every year, or multiple of years. You can use the Multiplier column to choose how many years until the budget item occurs again (eg. 2 for every other year). It will stay in your budget this way as long as you don’t change the Start Date.
• Annually-AVG-Year: The same as Annually, but the total for the year is averaged over each month of the year. The Start Date and End Date don’t affect this one.
• Past-Category: Choose Past-Category when you want to use transactions from exactly one year ago as a basis for your budget. The total spending for each month in the selected category is listed in the corresponding month. If you want to increase the budget over what you spent last year, enter a number in the multiplier column (eg. 1.1 = 10%, 1.25 = 25%, 1.5=50%, etc).
• Past-Category-AVG-Year: The same as Past-Category, but the totals for each month are added up, and divided evenly over the year.
• Past-Description: The same as Past-Category, but uses the Description field to exactly match transaction descriptions instead of using the Category field (eg. Set the description to “Walmart” to find every transaction in that month with a description of “Walmart”).
• Past-Description-AVG-Year: The same as Past-Description, but the totals for each month are added up, and divided evenly over the year.
• Workdays: Choose Workdays when you want to use transactions that occur only on days that you work (all days minus weekends). If you leave the Mult column blank, it will assume a Saturday/Sunday weekend. If you want to change that, enter a number using this pattern: 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and this pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and this pattern repeats until 17 = Saturday is the only weekend.
• Workdays-AVG-Period: The same as Workdays, but the total for the period is averaged over the months in the period set by the Start Date and End Date.
• Workdays-AVG-Year: The same as Workdays, but the total for the period is averaged over the entire year.
• SpreadOverPeriod: Enter the Start Date and End Date that you’d like to spread a total amount of money and enter the total in Amount. eg. Enter $200 in Amount, set the Start Date to 4/1/22 and the End Date to 7/1/22 and each month gets $50 budgeted.
• SpreadOverYear: Enter a number in Amount and it gets split evenly across the year. eg. Enter $200 in Amount and leave the dates blank, and each month gets $17 budgeted.
• ExternalSource: Choose ExternalSource when you want to get the data for the budget period from a different sheet. The source of the budget data should be included in the “Notes” column in the form ='Sheet Name'!A1:L2. On the source sheet, the data can be located anywhere on the sheet, but needs to include two rows of 12 columns. The first row should include the same dates that appear at the top of the Budget Period in the Budget Plan sheet in “m/d/yy” format. The second row should include the budget amounts for that month and should always be a positive number. Here’s an example of what it should look like:
  

Here’s a video overview, that will hopefully get you started. That scratching noise you’ll hear is one of our dogs making a guest appearance.

How to Use the Budget Plan Template for Tiller

Suggestions

• Are you paid bi-weekly? Instead of figuring out which months have 3 pay periods, simply add the first pay date of the year as the “Start Date”, choose a frequency of “Weekly” and add a multiplier of “2”. You’ll see the actual total income each month, whether it’s from two or three checks. If you prefer your budget items to be averaged over the entier year, change “Weekly” to “Weekly-AVG-Year” and each month will have the same amount, though the yearly total remains the same.

• Paid monthly but your pay rate is changing half-way through the year? Create two budget items. For the first, set the “Start Date” of your first pay date, and set “End Date” to the last pay date at the initial rate. Then create a second budget item with the “Start Date” of your new pay rate (“End Date” is optional since it will assume the end of the year).

• Need to budget your electric bill, but it varies every month? Set the “Start Date” to the date of your first payment, and either choose the frequency “Past-Category” or “Past-Description” depending on how to best find your electric bill transactions from last year. No need to enter an Amount as it will be calculated based on existing transactions. Maybe your rates went up since then? Add a multiplier like 1.1 for a 10% increase, or 1.25 for a 25% increase.

• Trash collection bill every quarter? Enter the “Start Date” of the first payment, choose the frequency “Monthly”, and set the multiplier to 3. Want to average that out over the year? Change the frequency from “Monthly” to “Monthly-AVG-Year”.

• License plate renewal every year? Enter the “Start Date” of the payment and choose a frequency of “Annually”. Want to average that out over the year? Change the frequency from “Annually” to “Annually-AVG-Year”.

• Have an asphalt driveway that needs to be sealed every other year? Set the “Start Date” of the first payment, choose the frequency “Annually” and set the multiplier to 2. When you update the first month of the Budget Schedule next year, you won’t see any budgeted funds for the driveway, but the year after you will (as long as the “Start Date” of the budget item remains unchanged).

• Stop at Starbucks on the way to work? Use the frequency “Workdays”. Use the Start Date and End Date if it’s only seasonal, and use the “AVG-Period” or “AVG-Year” options to balance your spending over the entire period or year.

• Have a bathroom remodel your doing this summer with a $20,000 limit? Use the frequency “SpreadOverPeriod”, set the Start Date and End Date to the timeframe of the remodel, and enter $20,000 for Amount. Want to balance that amount over the entire year? Change the frequency to “SpreadOverYear”, the dates will be ignored.

Adjusting your Budget

If, like me, you end up with a budget that isn’t very balanced, I’ve added a feature that hopefully will help point you to the budget items that will make the most difference. If you set a value in the “Changeable?” column, the rows in your Budget Schedule will change color, in a range from green to red (easiest to change to hardest to change). When you’re need to trim a few dollars, look for the green entries as the easiest to change. You can also look to column AB, “Importance”. If you sort by this column from Z to A, the budget items that are weighted highest, based on their value and their changeability will sort to the top. Focus on the items at the top to make the biggest difference in your overall budget.

Finally

The formula that forms the basis of this sheet is very long and complicated (the formula contains 3,674 characters, and is repeated in 2,352 cells to make this template possible). I’ve done the best I can to troubleshoot it and make sure everything is working as it should, but I can’t guarantee it is 100% accurate, so please use with caution. If you find any problems, please let me know so I can try to fix it. Also, if you have ideas on how to make this even better, I’m glad to hear them! There is also an Excel version here.

[yossiea]

=IF(ISBLANK($A2),"",SUMIF('Budget Plan'!$E$4:$E$200,"="&$A2,'Budget Plan'!K$4:K$200))

You need to use pre-formatted text, not quote.
Also, just an FYI, my January 2022 was in column V or something since I kept my columns from prior year.

[jpfieber]

Thanks, just made the changes to the docs!

[yossiea]

Looks good so far, I would add to the instructions to copy your existing category sheet data to the Budget Plan and work from there.
Also, I added a column for last month’s spend to make it easier to budget.

=abs(sumifs(Transactions!$E:$E,Transactions!$D:$D,E4,Transactions!$L:$L,text(EDATE(today(),-1),"M-1-Y")))

[jpfieber]

Good idea, I added a note about copying existing data. I’ll have a look at the “last month” data, thanks for the suggestion!

[yossiea]

I’m not sure how many people this will impact, but the VLOOKUP in Column C didn’t work for me at first. My “Type” is in Column D not C in Categories.

[jpfieber]

Probably won’t affect many, but still a good idea to make the formula smarter, I’ll look into that, thanks!

[jpfieber]

I just updated the template to be smarter about finding ‘Type’ info

[adekunledauda]

Does the formulae “=IF(ISBLANK($A2),”“,SUMIF(‘Budget Plan’!$E$4:$E$200,”=“&$A2,‘Budget Plan’!K$4:K$200)” account for income as a negative or positive? My categories income have minus (-) trail instead of normal positive figure. And because many template populate budget through categories, the income is distorted and incorrect. Just want to confirm if this a bug as it happen in both the sheet and excel. To get around it, I have to put minus( -) before IF i.e. “=-IF(ISBLANK($A2),”“,SUMIF(‘Budget Plan’!$E$4:$E$200,”=“&$A2,‘Budget Plan’!K$4:K$200)” to convert the negative income to normal positive figure in the categories sheet. The income categories in the Budget plan remains negative. Thanks

[jpfieber]

I think I see where the problem is. You’re probably using one of the “Past” frequencies, which reverses the negatives to positives, but doesn’t account for income types, so it reverses those to negative. I’ll work on updating the template to fix that. Thanks for pointing out the problem!

[jpfieber]

Was an easy fix, I’ve updated the template. Two options to fix yours:

1. You can copy the updated formula from K4 to your K4, and then fill it across to column V, and then fill it down to the last row, then fix the formatting since the border is going to fill onto the cells as well. Also, remove the ‘-’ from the formula on the Categories sheet.
2. Rename your current Budget Plan template to something different. Copy the updated Budget Plan sheet to your file. Copy/Paste any budget items from your old sheet to the new one. Update the formula in your Categories sheet to be sure it’s pointing to the new sheet (no need for the ‘-’ part).

Thanks again for finding this bug!

[adekunledauda]

Thank you for fixing the bug. It works perfectly. I hope excel version was also fixed. Keep up the good work

[jpfieber]

Excellent, glad it’s working for you now. Yup, fixed the Excel version as well. Thanks again, let me know if you find any other issues!

[adekunledauda]

I am not sure if you break some formulae in excel version during the fix. None of the frequency is working. But when I input figure in the amount cell, it populate categories sheet.

[jpfieber]

Woops, IF($C4=“Income”,1,-1) doesn’t help if you leave out the ‘*’ to do the multiplication! Excel template is updated, thanks again!

[jpfieber]

@jpfieber

I just updated the template to be smarter about finding ‘Type’ info

I think in the process of fixing this I broke the Group column (just noticed mine was showing Type in that column as well). Fixed the template, and hopefully didn’t break anything else!

[jpfieber]

Adjusting your Budget

If, like me, you end up with a budget that isn’t very balanced, I’ve added a new feature to the template that hopefully will help point you to the budget items that will make the most difference. If you set a value in the new “Changeable?” column, the rows in your Budget Schedule will change color, in a range from green to red (easiest to change to hardest to change). When you’re need to trim a few dollars, look for the green entries as the easiest to change. You can also look to column AB, “Importance”. If you sort by this column from Z to A, the budget items that are weighted highest, based on their value and their changeability will sort to the top. Focus on the items at the top to make the biggest difference in your overall budget.

The forum won’t let me post an identical post in two different categories, so here’s a little extra text to make it slightly different.

[jpfieber]

I added another 'Frequency" option: Workdays. The template has been update to include “Workdays”, “Workdays-AVG-Period” and “Workdays-AVG-Year”. It will calculate how many workdays (days minus weekends) in the period. If you leave the Mult column blank, it will assume a Saturday/Sunday weekend. If you want to change that, enter a number using this pattern: 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and this pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and this pattern repeats until 17 = Saturday is the only weekend.

The forum won’t let me post an identical post in two different categories, so here’s a little extra tex tto make it slightly different.

[adekunledauda]

It seems there is a bug in the sheet version. The formulae that filed the categories appear to jump the first month and filed in first-month data into the second month e.g. January data is empty but appears in February. It happens mostly when the past frequency is used.

[jpfieber]

I’m looking at the formulas, and everything looks as it should, and I updated my file with the new template as well and it seems to be working fine. Maybe you grabbed the template while I was tweaking something, could you try downloading again? If you still have the problem, let me know which cell isn’t showing right, and copy the part of the formula that relates to which frequency you have chosen.