🏆 Budget Plan - Excel

Microsoft Excel Show & Tell
1

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

BudgetPlan1
BudgetPlan12171×411 58 KB

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.

Installation

Install this template using Tiller Money Feeds using the following steps:

  1. Launch Tiller Money Feeds
  2. Under Templates near the bottom, click Browse
  3. Find the template in the list using search or scroll to find it
  4. Click the template name in the list to expand its card
  5. Click Install to add the template to your spreadsheet

Read more in our help center

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.

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. 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.

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 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:
    ExternalSource

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.

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 a Google Sheets version here.

2

Found a bug where ‘Past’ frequencies didn’t properly account for the +/- sign on income types. The template has been updated to account for this. If you already started using the template you can update two ways:

  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.
  2. Rename your current Budget Plan template to something different. Follow all the original instructions to bring in the updated template. Update the formula in your Categories sheet to be sure it’s pointing to the new sheet.
1 Like
3

Typo in the fix, just fixed the fix.

4

The frequency is fixed. All good.

1 Like
5

@jpfieber This is amazing. Wow. It appears there is no scenario you didn’t think of. Cool!

1 Like
6

I improved the formulas in the template for finding the Type and Group columns on the Categories sheet. If your Type and Groups columns are already showing up OK (should only be broken if you moved columns around or added new ones on the far left side), no need to update.

7

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.

8

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.

9

Feature Update: (version 1.6) I added two new frequencies that I’m calling “SpreadOverPeriod” and “SpreadOverYear” (I’m open to suggestions for better names!). Where all the other frequencies start with a small amount and work up to a total, these start with a total, and subdivide it over either a specified period, or the entire year. Give it a try and let me know how it goes!

1 Like
10

Tried using this with Excel on Mac, formula added to category sheet not working. Get error message that am missing closing or opening parentheses.

11

Woops, looks like I missed copying a parenthesis in the docs. I just updated the documentation to show the formula should be:
=IF(ISBLANK($A2),"",SUMIF('Budget Plan'!$E$4:$E$200,"="&$A2,'Budget Plan'!L$4:L$200))
Thanks for letting me know, hopefully this gets you off and running.

12

Thank you. Works great now

1 Like
13

Congratulations, @jpfieber, on another incredible Excel Template.

The Tiller teamed really :heart: the Budget Plan for a number of reasons:

  • It improves existing budgeting and planning workflows, among the the this community’s most popular & important workflows
  • It consolidates the Categories budget, Category Schedule and Budget Builder template concepts in an intuitive and even more-powerful way
  • It anticipates budget-refinement workflows (e.g. creating the powerful “Changeability” sorting metric)
  • It is well-designed & -built: intuitive, responsive, performant, easy to debug (as all of your templates are, @jpfieber :wink:)
  • It integrates well with existing budgeting dashboards (Monthly Budget, Yearly Budget, Savings Budget)
  • The documentation is excellent
  • It is mirrored & fully functional in Sheets also :star_struck:
  • You have been incredibly responsive in responding to community feedback— both to address issues and add requested features

:trophy: We are excited to offer you a $1,000 gift certificate, the largest prize we have awarded, as part of our 2022 Microsoft Excel Builders Challenge.

Keep up the great work.

6 Likes
14

Thanks @randy , glad everyone is finding it as useful as I have! I’m putting together a video to go along with the documentation to better explain how to setup up the template, and some strategies I’ve found useful. I will post it here when it’s ready!

3 Likes
15

I agreed with @randy. You have built and supported a powerful budget template that is available in Sheet and Excel . Tiller need to add the template to community solution and/ or even integrate it to foundation template.

2 Likes
16

Awesome Budget Plan! I want to give you my vote, but I ran out of my vote limit. Anyway, the plan looks very complicated, but I decided to go ahead and install it into my current worksheet and took me about an hour to set up the plan. Now it’s a beauty!

It’s evident this Budget Plan template requires many tedious hours to create! Good job, jpfieber! :slightly_smiling_face:

2 Likes
17

I absolutely love this template, but ever since I installed it, Excel is running really slowly. I tried my production file as well as a couple of new Foundation Templates and I get the same results.

Any thoughts?

18

It certainly is heavy on formulas. If the conditional formatting on the range L4:AA200 isn’t super important to you, you could remove that and see what effect it has. You could also try deleting formulas from any cells you’re not using in that same range. If you only have 20 budget items, maybe delete the formulas from rows 50 and down (they go down to ~200). Something to try and see if the situation improves. Let us know how it goes!

19

It’s cool to have the whole planner be live for ad-hoc editing throughout the year but I can also imagine it as a budget configuration tool where you write the Categories sheet budgets to values at the end (not too hard to put those formulas back in if needed). If the template itself is slow—not the formulas that render the budgets to the Categories sheet— that could be deleted, pasted as values or archived too.

20

Interesting idea. I could perhaps add a switch to enable and disable the template formulas, so a user could copy the budget info in the Categories sheet and “paste as values” to over-write the formulas, then turn off the Budget Plan template so it limits the calculations, but they don’t loose all their settings. In the future when they want to make a budget change, turn on the Budget Plan template, make the changes, copy the formula back to the Categories sheet to get the latest updated values, then copy/paste as values again, and turn off the template. Not nearly as nice as leaving it on all the time, but at least a compromise that allows the template to be used on less powerful systems.