🏆 Budget Status - Google Sheets

Google Sheets Show & Tell
1

Overview

The Budget Status template is intended not only to let you know how your actual earning/spending relates to your budget, but also how it relates to the actual funds available in your accounts. Some of this functionality is present in and inspired by Savings Budget. It’s a great companion sheet for Budget Plan, but does not depend upon it.

Budget_Status_1 (1)
Budget_Status_1 (1)1185×564 139 KB

The template allows you to create ‘Pools’ of funds which are linked to one or more of your accounts. You then assign each Category to the Pool where the income will go or the expense will come from. The template then shows the currently available funds by Category or Group, and also shows the available funds in each Pool, so you can see if you’re in danger of overdrawing on an account.

You can sort your Categories/Groups/Pools based on the total amount of budgeted funds that are available, or you can sort based on the percentage of budgeted funds that are available (my preference).

Another tool that is available is a “Category Transfer Tool”. This is similar to the savings budget tool in the Savings Budget template, allowing you to make one time transfers of funds between budgeted categories. For example, if you go over budget on your Restaurant category by $100, but you’re $100 under-budget on your Groceries category, you can transfer $100 from Groceries to Restaurant. This doesn’t change your budget, and it doesn’t move funds between accounts, it simply tells Budget Status to show $100 extra for the Restaurant category, and $100 less for the Groceries category, keeping you within your overall budgeted amount, but allowing you to move some of those funds around a bit. If you need to do this often for the same categories, you should consider altering the budget directly to better align with your actual earning/spending.

Budget_Status_2 (1)
Budget_Status_2 (1)721×306 40.8 KB

Here’s a quick overview:

Dependencies

The Budget Status template relies on 3 different sheets in the Tiller Foundation template:

  • Transactions
  • Categories
  • Accounts
    I’ve attempted to make Budget Status work even if you’ve customized those sheets, but if it’s not working properly, it’s possible it’s not finding something it needs on those sheets.

Installation

Open the link to the “Budget Status” template below. On the “Budget Status sheet name at the bottom of the screen, click the triangle and choose “Copy To”, then “Existing Spreadsheet” and select your Tiller foundation template. Open your Tiller foundation template, find the new sheet (it’s likely the last sheet) and rename it to “Budget Status” (it’s likely named “Copy of Budget Status”) by clicking the triangle on the sheet name and choosing “Rename”.

Configuration

Step 1 is to create your Pools. Just type in a name for each funding source you’d like to use for your budget. If you’re only going to assign one account to each pool, you could use the Account name for the Pool. If you’re going to assign multiple accounts to some pools, you can use whatever name you’d like. Here’s an example of how I have our Pools setup:

Pool		Account
His		    My personal checking
His		    My personal savings
Hers		Her personal checking
Hers		Her personal savings
Joint		Our shared joint checking
Projects	High Interest Savings account

So you can see the His and Hers Pools are each going to have two accounts assigned, while the Joint and Projects Pools each have only one account assigned.

Step 2 is to assign accounts to your Pools. Select the Pool and the Account from the dropdowns in Section 2 and it will show the Account balance as well as the Pool Balance. These will be the same if you only have one Account assigned to a Pool, or if you have multiple accounts assigned, it will add them together for the Pool Balance.

Step 3 is to assign a Pool to each Category. You’ll do this on your Categories sheet. Add a column named “Pool” to your Categories sheet. Now you can either just type in the Pool names for each Category, or you can create a drop down menu to select a Pool from:

  • Select all the cells below the header in the Pool column of your Categories sheet
  • Go to the Data menu and choose Data Validation
  • In the field to the right of ‘List from a range’, enter ='Budget Status'!$D$5:$D
  • Click ‘Save’ and you should now have a dropdown menu in each cell that shows a list of your Pools.

Here’s how I assigned our categories:

  • Most categories are assigned to the Joint pool.
  • Categories related to our personal spending are assigned to the His and Hers pools.
  • Categories related to future projects (think “Replace Roof”, “Emergency Fund”, “New Car”) are assigned to the Projects pool.

With all that done, you should now see your Category, Group and Pool information sorted by the percentage of budgeted funds that are available. You can change this to sort by the total amount currently available by changing cell A22.

Notes

I haven’t been using this long but I think I have most of the big bugs worked out. That being said, don’t base any important financial decisions on this template without first confirming the accuracy of the numbers you’re seeing. If you notice anything that’s not showing up right, let me know! If you have any ideas on how to improve it, let me know! I don’t have an Excel version yet, once the bugs are worked out and the features set on this version, I’ll convert it to work in Excel.

2

This is amazing, and what I was really using the Savings Budget for, but I didn’t want to change my budget, just to see if I can balance the items somehow.
A couple of quickies:
1- In the post, you need to put the sheet name in CodeView, otherwise it uses smart quotes.
2- Is there a way to filter or start with a specific date? I have from 2020 onwards, but I only want to start from 2022 for example.

3

Thanks! I updated the docs to make the sheet name preformatted.
Good idea to allow the start date to be switchable. For now, you can change CT29 to your desired start date, (it currently comes from the first date found in your Categories sheet). I’ll add a changeable field somewhere that overrides that if set when I get a chance.

1 Like
5

Joseph, this is great. Be careful, you have almost built an envelope system :grinning: great job

Rich

1 Like
6

Thanks! That’s kind of what I’m working towards, but I haven’t really used an envelope system before. What do you think could be added to make it more useful for someone wanting to do envelope budgeting?

1 Like
7

Thats a good question. A good start would be to look at the Envelope Systems I built here :slight_smile: (slight plug)

I think the biggest difference between yours, the savings budget, and mine is I base my envelope values on the actual income and money in my accounts, and not the potential money that I have in my planned budget. Meaning, while I expect to get paid twice in a month, I cant add the money to the envelope until it hits my bank account. Yes I can plan for it in my budget, but it isn’t marked as available until payday. slight difference but a mighty difference. Which is great. I think the value proposition of Tiller is its ability to work with so many sheets and ways of doing personal finance.

Overall, I think what you are doing in all of your sheets is awesome. Keep it up.

1 Like
8

We are so grateful you continue to tackle and iterate on some of the hardest personal-finance budgeting challenges, @jpfieber. Budget Status is a cool & powerful mashup of an envelope budget with workflows linking savings balances to real-world accounts for improved workflow accountability.

Your “pools” concept looks like a nimble reimagining of what was attempted with the Savings & Debt template.

The Tiller Builder Rewards Program is excited to award you $750 for this powerful, new community template.

:trophy:

6 Likes
9

This is super cool and useful @jpfieber! Thank you for all you contribute to the Tiller Community and the Tiller experience. It’s getting to the point that I visit the Community just to see what you’ve recently built or shared.

1 Like
10

Really cool but I’m having weird issues trying to set this up. :face_with_diagonal_mouth:

  1. Like you I have created a Pool called “Projects” which is tied to our Online Savings Account. For whatever reason, the balance for my OSA is wrong in Budget Status once I link it. It seems to be showing the balance from before my last deposit (I think from what I recall of the number) to that account which I’m not sure how is possible. I deposited cash into that account about a month ago. My account balance for this savings account is correct on the Balances tab, so I have no idea how your sheet could pull an old/wrong number from a month ago?

  2. I’m unable to build a Dropdown list in Categories. I can build the Data Validation rule and it links to Budget Status and can see my Pool names in the rule, but once I click the Done button to complete and set the rule…nothing happens. Google won’t let me complete the rule. So it basically puts a blank drop down list in my Categories sheet with the generic Option 1 or Option 2 to pick from.

  3. “if you have multiple accounts assigned, it will add them together for the Pool Balance.” I have two pools assigned to this savings account and this Pool Balance feature isn’t working either. It’s not adding it together.

What in the world?

11

I pull the balance for the account from the Accounts sheets “Last Balance” column. Is the number showing there correct?
For the Data Validation, I’m just seeing the new interface for it, and I have to say so far I don’t like it. Make sure the changes you are making are applying to the range you want. In my attempt to redo it, I accidentally pointed a different column at Budget Sheet. Also, it seemed that instead of just pasting the ='Budget Status'!$D$5:$D, I then had to click on the grid button you usually use to go find the range before it properly recognized it and then showed entries for all of my pools.

12

Interesting. Never noticed that hidden part on Accounts. Found the issue there in that sometimes Accounts get changed on the backend by the financial institution. Your sheet was pulling an old balance from a month ago that was listed before the current, correct balance. I blanked out the old Last Balance line and it now pulls correct number. Pulled new balances just to make sure I didn’t break that and it seems to be fine.

For Drop Downs, it won’t let me type in =‘Budget Status’!$D$5:$D and hit the grid button. I had to hit grid button and drag over my Pool options before it would work. Thus, it won’t let me input your “infinity” option.

Pool Balance is still not working if I understand that correctly? I have two Pools pulling from one account so the Pool Balance should be 2x of that Account balance? Right?

13

After studying this for a while this is really cool. I’m assuming this will automatically roll into January 2023 when we hit January and switch over?

Also, I do have one recommendation unless I’m just missing how this might work…

Would like to see an additional column in Section 3 and/or 4 that calculates the difference between Prior Budget and Prior Activity. Could call it Prior Balance or Prior Gap or whatever. Having this column would quickly help you operate the Category Transfer Tool without having to do math. I’ve added this myself in a Copy of my Tiller workbook to test it by adding a new column to the left of Column X between Sections 3 and 4. It didn’t seem to break anything but maybe I’m not seeing something?

For example, I’m going to start using this in 2023 in conjunction with your Budget Plan. So at the end of January 2023 I would like to be able to move money around different categories to balance/reconcile the month, if you will. I can obviously do that as it is configured but will have to do the math myself whereas if we had a column to reference we could quickly plug into the Category Transfer Tool.

2 Likes
14

Just a few questions about the budget plan for 2023. I am looking to make a few changes in categories and also the budget designation for 2023.

  1. I want to separate my wife’s paycheck and my paycheck. She is an hourly employee and paid every 2 weeks, so there is a bit of variability in her check total. I’m salaried so it is more predictable. If i add a new category so I have two categories for income instead of one in 2023, how can I adjust that in the budget plan? Should I go back to 2022 and change that so it transfers into 2023 or can i make that change simply for 23?

  2. I am looking to make some home improvement costs, can i manually enter those totals and tailor them toward the months spent? I believe you used that example with a bath remodel but was not sure how to enter a custom amount that will be spent.

15

I have an ‘Income’ Group with a number of categories in it:

  • My Salary
  • Her hourly
  • My side income
  • Her side income

I have two budget items in Budget Plan:

  • One weekly with a multiplier of 2 (I get paid every other week)
  • One weekly (she gets paid once a week)

My salary is very regular, so budgeting is easy. Her’s is not as regular, so I do the best I can to balance it out. If you have transactions for her income from last year, you might just set the frequency to “Past - Category” or “Past - Description” and then you could always use the multiplier to increase it if her pay rate increased. Keep in mind you can also create multiple entries for one category and use the start and end dates. So if she had 20 hours a week until June, and then 30 hours a week after that, create one budget item with an end date of 6/30 and then a new one with a start date of 7/1. Each can then have a different budget amount.
For the home improvement, the example used “SpreadOverPeriod” frequency where you could input the total amount to budget, and then use the start and end dates to determine what period the spending is budgeted over. If you want to get more specific than that, you could create a separate budget item for each month, or you could create separate items for each part of the project. It’s very flexible that way, allowing you to have as many items in as many categories as you need.

1 Like
16

Thank you for this. Follow up, if i start a new category or income group on Budget Plan, does it automatically copy over to the categories page? I do like how you have different income descriptions under your income category. I might switch to that. I will play around with it now.

17

If you want to start a new category, you need to create it first on the Category sheet, that’s where Budget Plan (and all other sheets) pulls it’s category list from.

1 Like
18

Ok, I was finally able to install budget status, following with our last conversation on budget plan.

I currently have in the part 3 “Budget Infos based on categories” a beautiful N/A in currently available. I guess it is because my budget (in budget plan and categories) officially starts in January ?
It shows an error : Did not find Value 44896 in MATCH evaluation.

Here too it would be essential to have a date to be able to go forward and backward.
To compare, saving budgets does have more of a tiller look and I enjoy the progress bar much more than the % wheel of budget status visually. (I do come from YNAB, not a surprise there).

I don’t really use the pools… I just set my accounts in there (which I linked to the same name’s account to pull the current balance) and set these in the category sheet as to what account I would use to pay for X, Y or Z.

I am curious to see if the total rollovers at the end of a period as well.

19

Hi @jpfieber – I’m trying to switch over from the Savings Budget / Savings Goal & Debt Tracking worksheets to your Budget Plan and Budget Status worksheets.

I’m having a hard time getting my projected accumulation of unspent funds in each category (ie saving for a big expense 8 months from now).

My thought is that the “Budget Plan” worksheet is not the place to find (or add that in myself) that info because it doesn’t track the pools and transfer of funds to different categories–which your Budget Status worksheet does.

That said, “Budget Status” does not have any future projection functionality in it. It’s only a current state picture of categories, pools, and your bank accounts (which is great for that purpose).

Thanks for the help! -Kyle

20

I added a couple tweaks:

  • Added ability to set the start of the budget year (A25)
  • Fixed settings to ignore budget info from before budget period

The version is now 1.10

21

Dumb question…I think with this one we have to redownload it because I’m not seeing it in the Community Solution sidebar list of apps?