Overview
The PnL (Profit and Loss) Analyzer was developed primarily as a pivot-table–like view of aggregated monthly transactional data. It serves up data in such a way as to enable the user to:
- Analyze a year’s worth of monthly income and expense data, broken down by Group and Category, to identify missing data, inconsistencies, and outliers
- Compare the current year to the previous year to identify changing trends
- Visualize total monthly PnL both in tabular format and on a dynamic chart
- View Groups and Categories in a condensed pivot style fashion, and evaluate Unused Categories
- Dig deeper while remaining in-sheet by using a Transaction Selector to pull transaction details by Category
My Tiller transactional workflow is primarily carried out on a monthly basis. I wanted to reconcile transactions and look for any inconsistencies in known expenses and analyze any outliers, as well as to compare totals against the previous year to see how income and expenses were trending over time. I also wanted to optimize categories to make sure that I was applying them consistently and that they met my budgeting needs. To achieve these workflow objectives, I found myself creating pivot table reports that were fairly time consuming and tedious to maintain. So at last I developed the PnL Analyzer as a no-more-fuss solution. I can now spend more time on analysis and planning and less time on report preparation.
Some users who carry out a comprehensive and frequent budgeting workflow may not derive as much value from the solution, but may still find it useful for planning purposes. My budget is mainly revised on an annual basis and does not track every category item - some are tracked at a higher group level and some new category items may arise that have yet to be allocated in the budget. Therefore I use this sheet to inform future budget adjustments. It allows me to review all groups and categories regardless of their budgeting status. Most of all, it provides a high level financial health check as to how well income is covering expenses throughout the year.
The following Loom video walks through some highlights of the solution:
Installation
To install the sheet, simply copy it into your Tiller workbook.
A read-only version is available at the following link:
The file contains no underlying data and so the sheet will initially show “No Data - Check Inputs”. This is normal until installed. Once opened, right-click the tab “PnL Analyzer” and choose “Copy To”–>”Existing Spreadsheet” and choose your Tiller workbook. Once copied to your workbook, you may rename the tab and reposition it wherever you like. Once copied to your workbook, the sheet will auto-populate all of the necessary data for 12 months beginning with the selected Start Month and Start Year.
The solution only requires the “Transactions” and “Categories” sheets to work properly.
The solution will honor Categories marked as Hide.
My use case did not have a need for the Accounts sheet and therefore the Accounts sheet is not utilized by this solution. Transactions for an Account marked as Hide will not be filtered out of the dataset.
The solution contains no scripts.
Setup & Usage
All cells with possible selections are in typical “User Editable Green”.
The solution is pre-populated with a Start Year and Start Month. However you can select any Month you desire from the dropdown and enter any Start Year. As long as there is underlying data to support the selected timeframe, it will serve up the results for 12 subsequent months.
The “Show Unused Categories” toggles the display of any Categories that are not marked Hide that have no transactions assigned to them in the current period. At the far right, a column “Cnt” also appears to show the count of total transactions using the given category across all time frames in the Transactions data set. Categories that have a zero count are totally void of any assignment in the entire Transactions data set.
Two columns are provided with checkboxes in each row:
Highlight (0) - Toggling the checkbox highlights the selected row as a visual aid. Checkboxes can be toggled using the spacebar. Multiple rows can be selected and highlighted simultaneously. The column can be quickly cleared by clicking the column header to highlight the entire column and toggling all checkboxes off with the spacebar. The number in parentheses indicates how many rows are currently highlighted. Highlighted rows also sum together and display a total at the top of the sheet, indicated as “Highlighter Total P/L”.
Do Not Project (0) - Toggling this checkbox turns off the Projection logic used to calculate the Proj. column for an incomplete data set. The Proj. (Projection) column is needed in order to compare an incomplete current year data set with a complete previous year data set. It is calculated using the current year Monthly column values to fill in for any future/incomplete months in the current data set. It is desirable to turn this projection off for line items that are known to be complete already for the year. For example, auto insurance might be a one-time payment in January with no need to project additional expenses for the remaining 11 months. Same as above, these checkboxes can be toggled using the spacebar. Multiple rows can be selected simultaneously. The column can be quickly cleared by clicking the column header to highlight the entire column and toggling the checkboxes off with the spacebar. The number in parentheses indicates how many rows are currently selected.
These checkbox columns should be used with some caution - understand that with the addition or removal of categories to the dataset, the target category may be shifted into another row while the checkmark remains on the row where it was set. The unintended consequence is that a different category is then highlighted and/or marked as Do Not Project. Fixing this would likely require a more involved solution and they are meant merely as a temporary use convenience item.
Under the Transaction Selector area, a Category can be selected to display up to 250 Transactions in the current year. Leaving the selection empty will show any Uncategorized transactions for the current year. The row at the top of the sheet labeled “Missing Categorization” will also show the totals, by month, of missing transactions.
The Transaction Selector results can be sorted by one of Date, Amount, and Account columns, in Asc (Ascending) or Desc (Descending) order. The default sorting order is by Date, Descending. An arrow indicator shows what sorting is currently applied.
If you would like to be able to click the “Ref Link” hyperlink to be taken directly to a given transaction in the Transactions tab, you should enter the GID for the Transactions tab in the input labeled “Transactions Sheet GID”. If it is not entered, the hyperlink will simply be invalid. The GID can be found by navigating to the Transactions tab and copying it from the end of the URL.
If you select a Start Month and Start Year that results in an incomplete data set, meaning there are some months still to be completed into the future, the Monthly data column will be based only on fully completed months. Therefore the column is marked with an Asterisk * to call attention to this fact. The Monthly data column is calculated through the given month as indicated in the top left part of the sheet labeled “*Monthly and Proj. data thru”. Likewise, the Proj. (Projection) column for comparing a projected annual value against the previous year of data is also using this limited monthly value.
The Latest Available Transaction Date at the top of the sheet informs the user when only a partial month of data has been collected. For example, even if the sheet is showing several values for September and today is October 5th, if the Latest Available Transaction Date is September 5th, then the user should be aware that there could still be a lot of data missing in September. It’s possible that some data would need to be loaded and categorized before the picture will be complete for the full month of September.
Permissions
Is it ok for others to copy, use, and modify your workflow?
Yes
Notes
This sheet employs Conditional formatting for a number of items in order to improve user readability. The amount of formatting should not be excessive to the point of slowing the performance of the sheet. However, if for any reason you would like to remove it, you can simply highlight the entire sheet contents (Ctrl-A or Cmd-A) and navigate to Format->Conditional Formatting and delete some or all of the conditional formatting. This should not affect the functionality of the sheet, but might have an effect on readability. For example, red text highlights in the “Compare w/ Prior Year” Delta Δ columns are intended to call attention to values that have trended in a negative direction (lower income, higher expense).
FAQ
None at this time.
, glad you liked it and thanks so much! I really appreciate that positive feedback and the award. I enjoyed building it quite a lot and hope that other people will find it to be useful too. Thank you!
that it’s the solution to the behavior you’re seeing, but please have a look into your Transactions sheet and see if perhaps the 2022 transactions (or any that appear to display incorrectly) have a date in the Month column with a mismatched month or year as compared to the actual Date of the transaction. I made a similar effect by altering my data and this indeed distorts the display kind of like you are describing. The reason it does this is because the QUERY function is pulling transactions by the Date column and then pivoting them by the corresponding Month column (under the assumption that the two are in sync).