Docs: Budget Builder

[randy]

Introduction

Creating your upcoming year’s budget is a personal process.

It’s Randy here … and I’m sharing my process (and my budget-builder personal worksheet). Maybe the sheet is helpful. Maybe you build on top of what I’ve shared. Maybe the concept helps you form your own (better) process.

Here is what I do…

I start by looking at my actuals from the prior year. If there are any surprises, I use a custom query tool to understand the top income or expense transactions within a category (e.g. an expensive meal, a forgotten home repair, etc). From there, I strip out one-time expenses (a trip, an expensive purchase, a home improvement, etc) in the timeframe where they occurred.

At this point, I have a baseline of my monthly actuals without the surprise items that I don’t expect to recur in the coming year. I like to look at the annualized average at this point and level it with a rounded “baseline override”. The Groceries category is an instructive example here. It’s not a seasonal expense so I don’t need to try to track the ups and downs of the prior year. If I spent $6,000 the prior year, I’m just going to override the month-to-month lumpiness (of the actuals) with $500 per month.

Finally, I add/budget in my new one-time expenses like planned travel, home upgrades, a new computer, etc.

Overview

Build next-year’s budget from the ground up with the Budget Builder template in a step by step process:

1. Consolidate prior-year actuals
2. Remove the prior year’s one-time expenses
3. Smooth month-to-month lumpiness with “baseline overrides”
4. Add the upcoming year’s one-time expenses

A category-query tool helps identify top expenses.

This solution works well with the Monthly/Yearly Budget and also with the Savings Budget.

The template contains four functional sections.

Prior-year Actuals

The Prior-year Actuals section automatically consolidates your actual expenses on a month-by-month basis. This is the starting point for your new budget.

Category-query Tool

Use the category-query tool to drill down into a category with surprising outliers— like an expensive month eating out.

Budget-year Modifiers

Use the budget-year modifiers section to remove one-time expenses and income from the prior year— to create a minimized baseline— and, later, to add one-time expenses for the coming year where you expect them to fall.

Upcoming Budget-year

Finally, use the Upcoming Budget-year section to refine and review your budget for the new year.

Installation

1. Open your Tiller Money Foundation template
2. Install or launch the Tiller Community Solutions add-on
3. Open the add-on and choose "Budget” from the tags dropdown on the Explore tab
4. Click on “Budget Builder”
5. Choose “Add to Spreadsheet”

Setting Up Your New Budget

Set the New Budget Year

Enter the budget year you are creating in cell C1. For example, for 2021, enter 1/1/2021.

Review Prior-year Actuals

Your prior-year will populate automatically into the Prior-year Actuals section. If you are budgeting for 2021 (in C1), actuals will populate for January to December 2020.

Drill Down with the Category-query Tool (Optional)

If you want to review the top line-item expenses (or income) for a category, enter the category name in W1. You will see the top 50 line-items. This can be helpful in understanding income or spending that drove your actuals for the prior year.

Remove One-time Line-items from the Prior Year

Most years, you will have some line-items that you won’t recur in the coming year— a remodel, a new computer, a vacation, etc. This is your opportunity to strip out these actuals to normalize your baseline.

Using Manual Entry

Skip row 3 since it has special formulas… see below…

To remove a one-time expense:

1. Check the checkbox in column Z
2. Enter a description for the line item in AA
3. Enter the category you’d like to modify in AB
4. Set the Change (mode) to “Remove…” (you will be removing the actuals from your prior-year baseline)
5. Enter the amounts and timing of the income/expense you’d like to remove in AE:AP
6. Repeat in subsequent rows as needed

Using Tagged Line Items

For advanced users who use tags, you can leverage the formulas in AE3:AP3 to recall the exact timing and amounts for tagged actuals in the prior year.

1. Copy down the special formulas in AE3:AP3 as needed
2. Check the checkbox in column Z
3. Enter a exact tag name in AA
4. Enter the category you’d like to modify in AB
5. Set the Change (mode) to “Remove…” (you will be removing the actuals from your prior-year baseline)
6. Watch as the tagged actuals appear in AE:AP
7. Repeat in subsequent rows as needed

Set Your Baseline Monthly Budgets

You now have a minimized budget based on prior year actuals without the prior-year’s one-time expenses. There is still some lumpiness that probably doesn’t belong in your next-year budget. Have a look at the data presented in AR:BI.

• Review your prior year actuals 2020 in AR
• Review your (minimized) upcoming-year actuals in AS
• Review the % Change in AT
• Review the Trend sparkline in AU
• Review the annualized Average in AV

At this point, for non-seasonal categories, (optionally) enter a baseline— I often use a rounded version of the average— in to AW. This value will override the minimized prior-year actuals when present.

Budget for One-time Line-items in the Upcoming Year

As with the one-time actuals you removed from the prior-year, you can add one-time expenses— a remodel, a new computer, a vacation, etc— to the coming year. Do your best to estimate amounts and timing. These values will be layered on top of your minimized prior-year actuals or your monthly override for the coming year.

1. Check the checkbox in column Z
2. Enter a description for the line item in AA
3. Enter the category you’d like to modify in AB
4. Set the Change (mode) to “Add…” (you will be adding budget to your upcoming-year baseline)
5. Enter the amounts and timing of the income/expense you’d like to remove in AE:AP
6. Repeat in subsequent rows as needed

Review Your Upcoming-year Budget

Finally, review your budget and see if it makes sense. Adjust your “baseline overrides”. Shuffle, add, deactivate expense for the coming year. Make it make sense.

Populate Your Category Budgets to the Categories Sheet

To map these budget values into your Categories sheet, enter this formula into the top left cell in the Categories sheet for your budget year. For example, if your January 2021 budget header is in cell G1, insert this formula in G2:

=IFERROR(OFFSET('Budget Builder'!$AX$7,MATCH($A2,'Budget Builder'!$C$7:$C,0)-1,MATCH(G$1,'Budget Builder'!$AX$2:$BI$2,0)-1))

If you are in a column other than, G, be sure to change the G$1 reference in the second MATCH() formula. For example, use T$1 if your upcoming budget year header is in T1 and the topmost budget is in T2.

I wrote up how this formula works here.