This Year to Date Comparison sheet provides budget versus actual analysis for the year to date period. The sheet is very simple and easy to use. It works for the current year.
Open the add-on and choose "Analysis” from the tags dropdown on the Explore tab
Click on Year to Date
Choose “Add to spreadsheet”
How it works
Aggregate By: Options are Category or Group.
Sort By: The report can be sorted by any of the columns.
Sort Order: The report can be sorted A to Z or Z to A.
The sheet summarizes in your actual Transactions by Category and Group from the Transactions sheet for the year to date period.
The sheet works best if you are using a Categories sheet that includes budgets by month. The Foundation template includes this. If you don’t have a Categories sheet with budgets by month starting in Jan of this year, then you won’t have any YTD Budget data.
If a category or group doesn’t have an actual amount or a budget amount, it won’t appear in the report. Categories that are set to Hide won’t appear either.
I think your engineers should be conscious that Tiller is used in other countries apart from the US. The date should be easily customised. It is not all countries or individuals that want to use January to December?? In AU, yearly income, payment & Expenses are captures based on financial year July-June. This spreadsheet should be updated to accommodate customised date.
Thanks for your suggestion. We are only officially registered in the U.S. and while our data provider does support institutions from around the world, making it possible to use Tiller if you live outside the U.S. (except with EU banks), we’re focused on U.S. date formats, USD, and other U.S. specific conventions. We hope to provide better support for non-US customers in the future. Templates like this one (and all others aside from the Foundation Template) are experimental examples of what’s possible with Tiller’s bank data feeds and not a part of our core product offering at this time.
All that being said, this sheet is specifically built to be installed on top of the Foundation Template, which does allow you customize the fiscal year.
Check out the heading “Adjusting the start month of the budget” in this help article:
Hi @adekunledauda,
If you want to adjust the YTD Comparison sheet to use any time frame you want, you could make these changes.
Unhide Columns H, I, J and K
In cell I15, manually enter the start date you want. It should always be the first of the month. For example, 7/1/2019.
In cell I16, manually enter the end date you want… OR just keep it as is to use the end date of today.
Make sure you have a budget column in the Categories sheet for the starting month and then the next 12 months. If starting in July 2019, make sure there is a July 2019 column.
J15 and J16 should correctly identify the column number for the first and current months and K15 and K16 should contact the column letters. If they don’t, you can manually override the automatic results.
Re-hide Columns H,I,J and K.
Then the sheet should work for the time period you enter.
OK. That’s good. But you helped me find a related bug.
I’ve fixed it. The new version has been published.
Anyone who installed the sheet before Oct 24, 2019 should go to the Tiller Labs add-on, then Manage solutions and update the sheet. The filter for the date range is now working correctly.
@adekunledauda , Let us know if it’s now working for you.
Hm. I just added the labs add-on, and went through the process of setting that up (it had me “adopt” three sheets?), and it tells me I can’t add this new sheet because I already have a categories sheet that will conflict? Any ideas?
Good question, @slcronin. There are two Tiller variants of the Categories sheet. One has the traditional four columns and the other has those same columns + budget information off to the right. The YTD sheet is built for a Categories sheet with budget columns off to the right.
When you adopt a sheet, the add-on assumes you have the variant without the budget. Can you let me know which variant you have?
Hi @GullHawk57,
This sheet was designed simply be a Year to Date comparison.
But using the technique I wrote about above, you can adjust cells I15 and I16 to make it work with any start and end dates. If you made those cells into date dropdowns for the date options you want using the Data Validation setting, then you should have what you are looking for.
Hi:
This is a great tool. Thanks for designing it. I had been using it in 2019 without any issues and then noticed today after the switch over to 2020 that the last nine categories in my budget don’t appear on the YTD sheet. Each one has budget amounts in the Categories sheet.
Any thoughts on how to get the remaining nine rows of my Category list to appear on the YTD sheet?
Ha! Apologies! I just noticed that, while I do have budgeted amounts for Feb-Dec in one of the categories that is not appearing on the YTD sheet, the budget for the missing line in January (the current month) is $0, and there are no actual expenses for this line.
When I added a budgeted amount for this month (Jan), the missing category magically appeared on the YTD sheet. GREAT!
Best.
(The other missing lines are marked with the “hide” flag. )
Hi @dbrvskgd,
The Year-to-Date Comparison sheet is designed to work with a Categories sheet that has 12 monthly columns to the right. Make sure the top row of those 12 monthly columns contains the correct year that you want to compare.
If you originally made budgets for 2019, you will need to update these columns to 2020 for it to work.
If you haven’t made any changes to the month name columns, you should be able to adjust the year in the January column and the rest of the months will get updated.
,