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.
- Download the Budget Plan workbook.
- Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
At this point your new template should be functional and linked to your local workbook’s data. Here’s a video that covers the same steps:
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’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. Select the “Data” menu and click the “Custom Sort” button. Check “My data has headers”, then choose “Group” from the “Sort by” dropdown. Click the “+” button and choose “Category” from the “Then by” dropdown. Click OK.
- 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 should be included in the Notes column in the form
='Sheet Name'!A1:L2. On the source page, 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.
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.
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 a Google Sheets version here.