🏆 Budget Plan - Google Sheets

Wow, @jpfieber! You’ve been hard at work. This is super cool.

I built the Budget Builder to do something similar for my own budget a few years ago and I love how far you’ve gone from there. It’s really easy to use.

Some ideas and feedback with no need to implement… just first impressions:

  • Do your “Frequency” options include a way to spread a cost over the provided period? For example, what if I’m doing a $20k bathroom remodel April through July? Is there a way to spread those dollars across those months?
  • Your frequency options are SUPER POWERFUL… but the words aren’t super intuitive. I don’t have any great ideas, but there may be different words that require less referencing of the documentation (which is great btw).
  • Is the idea on Changeability that it isn’t so much a dollar amount as a power ranking combining both magnitude of change * ease of change? I wonder if formatting as dollars confuses this concept? Would it be easier to understand if it were just a unitless magnitude?
  • I’d consider freezing horizontally up to column B (at least).
  • I’d consider a 2-axis lookup (for the months) on your category formula to address an issue where the date order in Categories doesn’t match up with the date order in Budget Plan (e.g. Budget Plan starts in February). I think the formula would look something like this:
    =IF(ISBLANK($A2),"",iferror(SUMIF('Budget Plan'!$E$4:$E$200,$A2,offset('Budget Plan'!$K$4:$K$200,0,match(F$1,'Budget Plan'!$L$3:$W$3,0))),0))
  • Consider adding a version number so people users can keep track of your changes— maybe just static text in K1.
  • The note in L3 should change from d/m/yy to m/d/yy.
  • I’m a huge fan of Google’s checkboxes in A4:A… but I imagine maybe you are trying to harmonize the interface with your Excel version.
  • I think your tool may accommodate non-month-aligned periods but, as a budgeting tool, that flexibility will likely break down downstream as the data works its way through Categories then into the existing budget dashboards.

I really love this. Will use it personally.

You are doing an incredible job taking so many workflows to the next level with intuitive and powerful improvements, @jpfieber! :trophy:

2 Likes

I believe there is bug the budget/categories formula. When I used @randy formula, it fix data appearing in the wrong month in the sheet but not in the excel version. So I change the “=IF(ISBLANK($A2),”“,SUMIF(‘Budget Plan’!$E$4:$E$200,”=“&$A2,‘Budget Plan’!K$4:K$200)” to =IF(ISBLANK($A2),”“,SUMIF(‘Budget Plan’!$E$4:$E$200,”=“&$A2,‘Budget Plan’!L$4:L$200) and it fix the problem.

Thanks for the feedback!

  • For your first question, no, can’t currently start with the end cost and spread that over a period, but that’s an interesting idea for another ‘Frequency’ option. Will give it some thought!
  • I agree on the Frequency names. I’m not happy with most of the current names, but haven’t been able to think up anything better so far. If anyone has suggestions, I’d love to hear them!
  • Yes, the Changeability tool is as you suggest. I thought about just turning the dollar sign off, but it’s still going to look like currency. I’m open to suggestions on how to make this look less like dollars, but still make them function as they are. Also, the values I chose to change the magnitude were pulled from nowhere (1.5,1.25,1,.75,.5), so if anyone has suggestions for different numbers, or a different way to handle this, I’m all ears.
  • I have a large monitor, but agree on the horizontal freeze, I added it to the template.
  • Good idea on the Categories formula. I updated the documentation. The same formula didn’t work in Excel (will have to research), but I updated that formula to at least account for the new column I added, as @adekunledauda suggested.
  • Added a version number to the template, good idea since changes are happening frequently
  • Thanks for catching the error in the note, updated
  • Yeah, using the dropdown instead of checkboxes to keep the look/functionality/documentation as similar as possible between the two platforms
  • I wanted to make it flexible enough to work in situations other than the standard year, but haven’t tested it at all in that scenario, and as you mention, the further from the norm it gets, the harder to make it work with the Categories sheet it’s feeding.

Glad you like it, let me know if you run into other issues or have other ideas on how to improve it!

Feature Update: (version 1.6) Based on @randy suggestion, I’ve 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!

Your conditional formatting has yellow for both easy, very easy and neutral.

Neutral is Yellow, Easy is Yellow/Green and Difficult is Orange. I initially had the full colors, but it got a bit overwhelming, so I made them more ‘pastel’ to be easier on the eyes, but you’re right, they do tend to then blend together a bit.

That makes sense, but you might want to just edit the instructions, since it says green>red. Unless it’s screen dependent, but my easy/veryeasy/neutral were the same colors.

@jpfieber bravo! I’ve been having fun working with Budget Plan and it’s fantastic. I also appreciate all the thought you put into cross-platform (Excel and Google Sheets) and existing conventions with the Foundation Template. That combined with your documentation, quick responses, and fixes is just awesome. Thanks for this outstanding Community contribution!

2 Likes

Love this template. Thanks for the hard work on it.

I noticed when using “SpreadOverPeriod” that it doesn’t seem to like it when the end date goes past the end of the budget year. For example, I pay my auto insurance every 6 months, but I budget for it monthly. If I put in a start date of 11/01/2022 and an end date of 04/30/2023 I was hoping the monthly amount would show up for Nov and Dec, but it doesn’t.

Yeah, I don’t think any of the frequencies will support dates outside the current budget period. I can see where your scenario makes sense, I’ll give some thought to how it might be possible to make that happen.

I made a small change to the documented formula to paste into the Categories sheet. I finally got the Excel formula to work, and this adjustment allows the same formula to be used in both versions. If you already have things setup, no need to update, it’s more for making documenting new installs easier.

I just finished an overview video which has been added to the first post. Hopefully it starts to explain what’s possible with this template, but I definitely don’t have time to delve into every option. That scratching noise you’ll hear is one of our dogs making a guest appearance.

4 Likes

Hello, I stumbled on the budget plan tonight; and very helpful, and the video is also easy to use. It seems like all my income values are negative. Do you know how this can be adjusted? I don’t want to mess up any formulas. I tried formatting the data, and I must be doing something wrong,
I love the input, and thank you for your time!
Thank you, Mic

You should input all values as positive, whether it’s an expense or income. This is so it can feed into Tillers budget templates. Did you enter any negative numbers in the Budget Plan? Where are you seeing the negative numbers?

Hi again, and thank you for your quick reply.
In my transactions tab, all my expenses have a minus sign in the value field, and income doesn’t show a minus sign by the dollars—the budget plan tab and my categories tab mirror the same values. Negative for the expense (i.e., deductions) and income displays no minus sign.
Open to suggestions, and again thank you!

The Transaction sheet shows the actual value of the transaction, so if it’s negative, it will show up as negative. The Budget sheet (and Categories) shows “how much are you budgeting for this…” I’d check your Categories sheet which should have your original budget and make sure the numbers are in without signs, and then the Type should be Expense/Income/Transfer, which will then tell Tiller what to do with those numbers.

As I and @yossiea mentioned, the budget shouldn’t use negative values, all values should be positive. Update column K in the Budget Plan sheet to only have positive values and everything should then work.

The template is now available through the Tiller Community Solutions Add-on, I’ve updated the documentation to reflect this.

1 Like

Hello - I just added Budget Plan and the data in input is corresponding to the Categories tab as expected, but I am not seeing results on the Monthly Budget tab. It’s showing a budget of $0 for each month. Do you have any advice? Thank you.

Did you add the formula to all the cells in the Categories sheet? That’s an important step since that’s where the Monthly Budget sheet pulls data from.