I have researched multiple templates, both Tiller and Community sheets. My objective is an “at a glance” review of Budget-to-Actual Expenses on a YTD basis. The Period Comparison sheet does this, but it does not sum the YTD categories. The Simple P & L shows YTD expenses (and cash flow) but not YTD budget. Perhaps I am missing a spreadsheet and/or a step within one of these sheets.
What I ideally would like is to modify the Yearly Budget sheet, something like this:
I have looked for one as well that doesn’t quite fit into what is available. I am actually working on a solution for myself that does something like what you need. It isn’t pretty yet, and its not quite finished. But it is Actual YTD, Budget to date, remaining budget to date, annual budget, annual remaining. I can share the work in progress with you if that sounds like what you are looking for.
Hello @casilverthorn.96 !
What’s the goal? How can you do to date if you only budget monthly in the categories sheet? Or does your solution enable daily or weekly budgeting?
Yes, thank you for your update. I would be interested to see your progress. What I did in the sample was to create a simple formula that summed the cells in question for each column (e.g., Budget, Actual) in the far right-hand columns. The downside is that I have to go in and add the latest month to the formula. I am sure there is a script that would solve that issue, but I’m not that good.
I have a formula that grabs =today and it uses that to figure actuals to date; my goal is to have that be able to put any date, but I haven’t made it that far yet. So I budget monthly, but it shows me actual expenses for year to date. I don’t mess with the budget part of it, that just comes from my budget. So budget to date is budget for Jan-May, but expenses is Jan 1 through today. I am sure as I get a handle on it better, the budget could include weekly, but it is not something I need. I have a regular income that drives my budget.
I will work on cleaning up what I have to share, but it is rough. I will try to get that this weekend.
Something like this?
It sums actuals and budget for any period you want.
You can find it here…
Thanks.
The Year-to-Date template works for me - Docs: Year-to-Date Comparison Sheet
Hi @jimwhite235 I modified this sheet to do a YTD. I add some columns to the end of the sheet and I use cell A2 to control the month because I wanted the option to analyze the last complete. Try this formula in cell AO7
=SUM(CHOOSECOLS(FILTER($E7:$AN7, $E$6:$AN$6=AO$6), SEQUENCE(1, MATCH($A$2, {“January”;“February”;“March”;“April”;“May”;“June”;“July”;“August”;“September”;“October”;“November”;“December”}, 0))))
I was following this thread and had to chime in, because I hit this exact same wall a few months ago. Trying to force a spreadsheet to calculate rolling ‘Today’ pacing without breaking array formulas is a nightmare.
I eventually moved my Tiller data into a relational database and built a BI dashboard over it. By using a true Date Dimension table instead of flat columns, you don’t need CHOOSECOLS or complex arrays. The engine just handles the Time Intelligence natively.
Here is what that looks like in my live production environment. It plots the cumulative actual spend (blue line) against an auto-calculated daily target pace (grey area) based on an average YTD 30 day monthly rolling expenses. I know when to pull back on monthly spending by this one simple chart based on spending patterns this year.
Because my public sandbox (linked in my Show & Tell post) uses static historical data from 2021-2024, I couldn’t include this specific live pacing visual in the sample sandbox demo—the math requires a live, moving calendar to track against a current date. But this is exactly what the engine does when hooked up to a live Tiller feed.
If anyone wants to get out of the spreadsheet array business and look at database modeling, feel free to check out the sandbox. Let me know if this is the type of modeling that suits you.
Thank you; I will try this fix.
I appreciate your efforts. I’ll give it a try. Thank you.
Hey @CHILL
Would you mind creating a copy of your sheet in another workbook and sharing it? I’m curious to give it a try.
Thanks I’ll give it a try
That look really good! Nice work and clean. Thanks for sharing.
I am not sure if you found a solution that works, but I finally finished what I am using in a hopefully sharable format. https://docs.google.com/spreadsheets/d/1KGrcy5vgvQ4MgdjJydpq2cmbm4tvdgXThflUyhM9oYs/edit?usp=sharing
You should be able to make a copy of it into your tiller sheet.
*edit to say that I used the monthly budget template as my template for this.
