🏆 Budget Plan - Excel

Microsoft Excel Show & Tell
21

I have not seen anyone else complaining about the performance aspect of the sheet. I wonder if it could be a configuration kiddie on my laptop. I am using Microsoft 365 and I do have it set up always keep the spreadsheet on my laptop.

22

Maybe it is my lack of Excel knowledge, but am havingrtrouble sorting by group. I get an error message that merge cells not the same size. Am using Excel 365 on Mac. Also your instructions do not match what i see when trying to sort, maybe that is a Mac difference?

23

I updated the documentation with a new formula to paste into the Categories sheet. The formula does a better job of matching the date headers in the Categories sheet to those in the Budget Plan sheet. If things are working OK for you already, you probably don’t need to change, but it’s not much work, so it wouldn’t be a bad idea.

24

Yeah, sorting isn’t as friendly in Excel as it is in Google Sheets. The problem is that it is trying to also sort the merged cells in the header area, which it should be smart enough to know not to do, but it’s not. To sort in Excel you’ll need to select the entire range you want to sort, and it’s easier if you include the header row. So for example, click on the Row3 header and drag down which selects the entire rows you pass by. Don’t select any blank rows or they will likely sort to the top. Then click the Data menu, select “Sort”, check “My data has headers”, the choose the Sort by option. I usually first sort by Group, then I add another level and also sort by Category. Click OK and it should sort only what was selected. In Google Sheets, you click anywhere in the column you want to sort by, and click either sort A-Z or Z-A and it does it. I think Excel used to work like that, but they took it backwards for some reason. Be careful to select the entire rows! If you only select a few columns, only those columns are sorted, the rest stay where they were, and you likely end up with a mess.

25

Thank you. That worked!

1 Like
26

Hi @jpfieber …I have only one question.
When. When do you sleep?

Great work.

1 Like
27

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.

3 Likes
28

First of all, thank you for this great option to improve the monthly budget planning, it motivated me use it to feed the current year in my Yearly budget sheet. So, I incorporated your sheets into my own file, but used the “year” column to extract from the transactions and income input from my retirement planning sheets and money market transfers". I thought all was well until I reviewed Tiller Yearly Budget report sheet. Since expenses come into the transaction sheet as “negative” numbers, I entered my expenses in the Budget Plan sheet as negative numbers. Surprisingly, this in turn resulted as positive values in the expense line items in the budget report causing incorrect “available” values. As a work around, I reworked the YEAR column in the budget plan sheet to multiply all rows with “Expense” in the TYPE column by “-1”.
Thanks for your thoughts!

29

Hey @jpfieber!

I’ve been meaning to congratulate your amazing & comprehensive video demonstration of the Budget Plan template. I appreciated not only your thoroughness in walking through the tool, but all the incredible features and workflows that you managed to pack into the template. (I had forgotten how feature-packed it is!)

It’s such a great tool and I know it will help many many budgeters & trackers in this community.

Thanks for your hard work on the template and for making the support content so accessible.

Best,
Randy

1 Like
30

The Budget Plan sheet is designed as a way to feed the budget info in the Categories sheet, so it works with all current budget tools. The budgeting numbers in the Categories sheet are all positive, and in general, other budgeting tools I’ve used use positive values as well to represent the amount you will allocate to spend or receive, so I kept or converted all the values in Budget Plan to be positive. I think if you restore the sheet to default and enter positive values you should get the expected behavior.

31

New to Till and just added the Budget Template - fantastic work! I have one small question that I could not find. How do I budget for a Semi-annual paycheck? It is slightly different then that of a bi-weekly frequency. I get paid on the 15th and last day of the month. Do I use monthly and use [.5] as the multiplier? Or is there another way?

Thanks again on amazing work!

Carl

32

Interesting use case I hadn’t considered. I don’t think you can do it in a single budget item with the current options. Instead, I’d break it into two budget items. ‘Paycheck 1’ could be set to monthly with the start date ‘1/15/22’ and ‘Paycheck 2’ could be set to monthly with the start date of ‘1/30/22’ (this seems to work even in February). I’ll consider adding a bimonthly option in a future update. Thanks for letting me know!

2 Likes
33

Thanks Joseph - yes great idea and yes, add it to the list for next revision. Its popular to be paid bi-monthly so it would get some use. Now, back to the Paycheck Deduction Generator…

34

I am also having trouble with performance with this template. Once I include it, other activities such as creating/editing categories take several seconds longer than they used do. If I delete the “Budget Plan” sheet from the workbook, the performance is significantly better.

I am going to start playing around with removing formulas from unneeded rows to try to make it faster.

Not looking for any specific action, but just want to report that the performance issue is ongoing. Thank you.

35

I definitely noticed the same. This sheet is processor intense, so once you get your budget planned out pretty well, removing unneeded rows will hopefully help some.

1 Like
36

I just updated the Budget Plan template for Excel. I added a few community requests, and a couple of my own. The version is now 1.7, here is the quick list of changes:

  • Budget period no longer limited to calendar year
  • Start of budget period automatically determined by Categories sheet
  • Added Frequency “Bi-Monthly”
  • Added Frequency “ExternalSource”
  • Added column to show number of occurrences

See the updated documentation for a more in depth explanation.

To migrate from an earlier version:

  • Rename the Budget Plan sheet to “Budget Plan Old”
  • Follow all the instructions at the top of this post, just as if you were starting from scratch.
  • Once you have the new sheet imported, do the following to copy the budget data from your “Budget Plan Old” sheet to the new “Budget Plan” sheet.
  • In the old sheet, Click into A4, drag down to B200 (or at least past the end of your data) and let go to select that data. Choose “Copy” from the “Edit” menu. Go to the new sheet, click in A4 and choose “Paste Special\Values Only” from the “Edit” menu.
  • In the old sheet, Click into E4, drag down to I200 (or at least past the end of your data) and let go to select that data. Choose “Copy” from the “Edit” menu. Go to the new sheet, click in E4 and choose “Paste Special\Values Only” from the “Edit” menu.
  • In the old sheet, Click into J4, drag down to K200 (or at least past the end of your data) and let go to select that data. Choose “Copy” from the “Edit” menu. Go to the new sheet, click in K4 and choose “Paste Special\Values Only” from the “Edit” menu.
  • Delete the “Budget Plan Old” sheet
37

When I updated Budget Plan to version 1.7, I neglected to notice that you need to update the formula in your Categories sheet, since I added a column to Budget Plan which moves everything over. If you’ve updated to 1.7, change your Categories formula, just as you initially did, with this formula:
=IF(ISBLANK($A2),"",IFERROR(SUMIF('Budget Plan'!$E$4:$E$200,$A2,OFFSET('Budget Plan'!$L$4:$L$200,0,MATCH(DATEVALUE(F$1),'Budget Plan'!$M$3:$X$3,0))),0))

1 Like
38

Possibly a naive question as I am new to Tiller and just jumped right in with this budget plan.

As we approach the end of the year, how do I start budgeting for 2023? My columns only go to Dec 2022.

39

I hadn’t thought much yet about moving to the new year, but as I give it thought now, I’m seeing two options.

If you want to save the 2022 budget info in your Categories sheet for use with templates that can view historic budget data:

  1. Select the month headers in the Categories sheet
  2. Grab the fill handle and drag to the right across 12 columns to get all the 2023 months listed
  3. Select the cells in your Dec 2022 column
  4. Drag the fill handle to the right to fill the empty new columns and point them to the Budget Plan sheet
  5. Select all the budget cells for 2022 that currently contain formulas filled by Budget Plan
  6. Copy
  7. Paste as Values, which will replace the formulas with the results of the formulas, so they will no longer change
  8. Go to the Budget Plan sheet and change M3 (the January column header) to 1/1/23
  9. You should now see all the Budget Plan data in the Categories sheet in the new columns you added for 2023

If you don’t need historic budget data, I haven’t seen the official Tiller process for updating your Categories sheet in Excel, but I think it would just involve changing all the '2022’s in the sheet to '2023’s. The Budget Plan sheet should automatically follow.

40

I had some issues in my template so I wiped it out and reloaded everything. It is working fine but the category sheet only fills to November. Any thoughts?