Overview
Use the popular Monthly Analysis worksheet with your Tiller Foundation Template to drill down and visualize your budget, actuals, or remaining organized by type, group, and category and get a comparative analysis of how your budget and spending have changed over time. Note that this template is also available for Google Sheets .
How to Install the Monthly Analysis Worksheet
- Download the Monthly Analysis workbook v1.01 .
- Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data.
Changelog
- v1.01 on Oct 27, 2023: fixed Available calculation for income types in version
How to Use the Monthly Analysis Worksheet
Once you’ve categorized transactions you can immediately start gaining insights using this sheet. Use the controls at the top to customize your analysis
Date Range
Choose a specific year or the last 12 months. Years available in the selector are based on dates of transactions in your Transactions worksheet.
Cascading Selectors
The selectors below the date range cascade as you make your selection to help you get more granular.
Values to Show
Choose whether you want to see actuals, budget, or available values. Budget and available require that you have set budget values in the Categories worksheet.
This selector controls the comparison chart that starts on row 11 and the yearly chart that starts on row 26. E.g. if you choose actuals, your comparison will be of month over month actuals and the yearly chart will only show actuals. This selector does not control the Budget vs Actual chart in the upper right.
Type
Choose Income or Expense. This selector controls the comparison chart that starts on row 11 and the yearly chart that starts on row 26. E.g. if you choose income you’ll only see income month over month comparison comparison. This selector also controls the chart in the upper right.
All or Filter By
Choose whether you want to see all data from all categories or choose Category or Group to display a fourth selector that allows you to choose a specific Category or Group.
This selector controls the comparison chart that starts on row 11 and the chart in the upper right.
Category/Group
When you choose to filter by a group or category this option becomes available and allows you to select a specific category or group. This selector controls the comparison chart that starts on row 11 and the chart in the upper right.
Video demo
@randy and @heather did a Community Webinar session on the Google Sheets version of this template, which you can review for reference but note that the Excel version is not included in this demo video.
Understanding the Monthly Analysis Dashboard
Selectors
The selectors at the top of this dashboard allow you to control the way data is presented in other areas of the Dashboard.
Budget vs Actual Chart
Use the Budget vs Actual chart in the upper right to quickly visualize budget vs actuals across time for income or expense for all or specific categories or groups. This chart will always display budget vs actuals, but can be customized to review income or expense and further drill down by a specific category or group using the selectors.
For example, if you’d like to see your “Auto & Gas” category charted out over the last 12 months you would choose a selector configuration as depicted below.
Comparison Chart
The comparison chart that starts on row 11 can help you quickly understand how your budget has changed compared to the previous month. Use the Values to Show, Type, and Filter selectors to customize the data that’s presented.
You’ll see a % change from the previous month as well as the $ value difference.
Yearly Analysis
Use the Yearly Analysis table that the bottom to see the entire year’s actuals, budget, or remaining amounts for all types, groups, and categories.
Adjust the Values to Show selector to pick the data you want presented in this table. The Values to Show selector is the only selector used to change the data set for the Yearly Analysis.
Troubleshooting & FAQ
Can I use this with other templates?
This Monthly Analysis worksheet was specifically built for use with the Tiller Foundation Template so it cannot be used out of the box with other templates.
Why aren’t transfers included in the Type selector?
Generally transfers are ignored in most budgeting analysis tools because they typically net out to $0. If you feel having the transfer data available in this analysis sheet please add a feature request.
My data looks inaccurate, what should I do?
Reply to this topic and let us know and we’ll debug.
Ask a question or get help
If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Get Help > Spreadsheet templates category.
Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.
Shoutout
Many thanks to @dannypark for his work porting the original Google Sheets version to Excel.
