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.
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!
Tried using this with Excel on Mac, formula added to category sheet not working. Get error message that am missing closing or opening parentheses.
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.
Thank you. Works great now
Congratulations, @jpfieber, on another incredible Excel Template.
The Tiller teamed really 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 )
- 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
- You have been incredibly responsive in responding to community feedback— both to address issues and add requested features
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.
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!
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.
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!
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.
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!
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.
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.
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.
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?
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.
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.
Thank you. That worked!
Hi @jpfieber …I have only one question.
When. When do you sleep?