PnL Analyzer - Profit and Loss Analyzer Solution for Google Sheets

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:

  1. Analyze a year’s worth of monthly income and expense data, broken down by Group and Category, to identify missing data, inconsistencies, and outliers
  2. Compare the current year to the previous year to identify changing trends
  3. Visualize total monthly PnL both in tabular format and on a dynamic chart
  4. View Groups and Categories in a condensed pivot style fashion, and evaluate Unused Categories
  5. 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:

Loom Overview - https://www.loom.com/share/f141f432716049209dc501400ecbd83e?sid=b8c25acb-4325-491f-9a7a-36a748acb802

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 “:arrow_backward:*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.

Updates and New Features

A detailed changelog is posted in the shared template workbook.

January 2024
Version 3 is available with a new feature that automates the Category selection in the Transaction Selector. This automation is activated using the dropdown in cell AA31. When activated, simply check off a line item using the Column A checkbox and the transactions of the highlighted category will be automatically displayed.

PnL Analyzer - Cat Sel by Highlighter

Version 3.1 adds an additional feature to improve the visuals :eyes: in the Transaction Selector area - it applies banding to the rows based upon matching values in the sorted column. The :white_check_mark: in cell AI32 toggles the feature on and off. With the feature turned on, row banding is applied automatically whenever sorting is applied to the Date, Amount, or Account columns. For the Date column, the banding is based on the MONTH, so all dates of the same month are highlighted together.

You can apply this to an existing version without downloading a new sheet- it just requires the checkbox and one formula in the cell below the checkbox. Additionally it requires one conditional formatting rule to be added (find it at the file link above - select cell AA33 in the template sheet and navigate to Format/Conditional Formatting). The banding color is gray, but you can adjust to whatever color you like in the conditional formatting rule.

PnL Analyzer - Transaction Sel Banding

This is awesome! Thanks for sharing :slight_smile:

2 Likes

Wow! This is cool. :grin:

1 Like

Thank you so much @KyleT ! I just came back from vacation and this was a welcome surprise! I am headed out again in a few days, so won’t have time to work on this until next week. I will report back after installation. I really appreciate the time to build this out, and record the loom to explain it!

Matt

1 Like

My God, man; it’s beautiful.

Tiller, hire this guy.

3 Likes

I like it a lot.

6 Likes

Thanks @heather @morgan @ThinkEagle @chrisgp123 @MarcC Glad you all like it :+1:

1 Like

Kyle, this is fantastic. Very comprehensive and detailed way of managing the categories. Looking forward to playing with this. I know you have a ton of work into developing your idea. Great job.

1 Like

Hey @KyleT! Thanks so much for sharing this. It’s amazing.

I like this template for so many reasons.

  • It’s well built and well documented.
  • It is broadly useful and solves everyday personal-finance workflows.
  • Many features are not currently available through other templates.
  • And you anticipate and solve for some new and interesting problems— like highlighting+drilling into categories under review, projecting and comparing year end numbers, linking to transactions from the dashboard, and cleaning up categories based on usage.

Further, I love the accompanying video demonstration. What an efficient way to spin up on all the neat features you packed into this template!

Through our Builder Rewards Program, we offer cash awards for template submissions. Because this template provides new and compelling solutions to everyday personal-finance problems, Tiller is excited to award you $500.

:trophy:

P.S. Sorry for the late review. Have been looking forward to digging into this for a couple weeks now.

P.P.S. I’m intrigued by the way you dynamically reference columns in other sheets. It’s different from what I’ve seen previously. The most clever part about it is that the INDIRECT() references include strings for easier debugging.

1 Like

Hi @randy. Awesome :smiley:, 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!

1 Like

Great stuff, @KyleT! The YOY is so concise, and it’s great to be able to sub-total an array of categories regardless of group affiliation. The Unused Categories tool is a whole new level of meta-analysis!

I’m getting some anomalies. My Transactions sheet data goes back to 1/1/2017. All 12-month periods starting with Jan 2017 - Jan 2021 display clearly in your sheet. However, any starting month in 2022 yields only blanks for all 12 months, including months that run into 2023. A 12-month period starting in Jan 2023 displays blanks in the Jan column, but January totals appear in the Feb column, etc. Start in Feb 2023, and Feb is blank, but February totals appear in the Mar column, etc. Start in Mar 2022, or any subsequent month up to now, and all available data is correctly represented.

I viewed the helper data referenced in cell B8 (AR7:BI300), and found that this helper data displays the same omissions as the analyzer table for the various periods. I did find that for all the 2022 starting months, cell AU7 displays a #REF! error (Array result was not expanded because it would overwrite data in BH7.) For the Jan-Feb 2023 starting months, cell AU7 displays “null”, rather than the YYYY-M-D string typically shown. I nudged some things around from there, but quickly got in over my head.

My Transaction sheet Is pure Foundation worksheet, and I’ve got any number of other solutions that are functioning as advertised. Downloaded your sheet three times just to make sure it wasn’t an ID ten T error! :slightly_smiling_face:

Apologies for all the verbiage, and I hope it is comprehensible. Much appreciation for the thought and effort you’ve put into this!

1 Like

Hi @GregC, thanks so much and glad you like it!

You’ve uncovered something really interesting. I spent some time studying this and although I couldn’t reproduce the issue initially with my data (running back to 2019), I could recreate what appears to be a similar behavior by introducing a mismatch between the Date and Month column entries in the Transactions sheet.

I’m not :100: 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).

The good news is that the sheet can now be made more robust by no longer relying on the Month column. Instead we can create the always correct months in the QUERY function directly.

However, I don’t know what other solutions use the Month column so it’s probably a good idea to clean the data just in case.

The change to the sheet is simple - if you could please try it out and let me know if this fixes the issue, it would be much appreciated.

In cells AU7 and BP7, replace the Col4 array element

INDIRECT(VLOOKUP(“Month”,CI8:CK,3,FALSE))

with the following:

MAP(INDIRECT(VLOOKUP(“Date”,CI8:CK,3,FALSE)),LAMBDA(d,DATE(YEAR(d),MONTH(d),1)))

I’m including a before and after screenshot that explains it in detail. I will update the source sheet with this change as I think it makes the sheet more robust even if this turns out to not be the answer to your issue.

By the way if you do have several line items where the Month is in need of correction, you could add a temporary column to the far right end of the Transactions sheet and use a variant of the same formula to generate the Month data in one fell swoop and then use it to correct the mismatched data.

Something like…

=MAP(B2:B,LAMBDA(d,DATE(YEAR(d),MONTH(d),1)))

…assuming your Date column is B.

You may also want to check the data in the Week column as well.

Thanks for bringing this to my attention! Please let me know if it helps.

1 Like

Hi again @GregC,

After my initial response, I also determined that my solution wasn’t handling historical months within the selected time range that have no transaction data at all. This could be a contributor to the behavior that you were seeing and needed to be corrected in any case for a more robust solution. These changes did not require much additional modification, but enough that rather than trying to walk through it here, I would recommend simply copying the latest sheet again. I have saved a Version 2 at the original link. I made a change log there where the affected cells are mentioned. In addition to the modification that I shared in the previous response, I updated the same QUERY functions in AU7 and BP7 to handle empty transaction months. Thanks again and let me know how it goes!

1 Like

Fantastic! Ship-shape and Bristol fashion! I found no mismatches in my Month/Week index columns, but your revision eliminated the problem.

Your illustration of the issue is equally impressive. Thanks for the fix!

1 Like

This is awesome! Well done!

Hi @KyleT

Thanks for your great work on this! and congratulations on being recognized by Tiller for your efforts.

In this thread… Profit and Loss by Project

you discuss wanting to modify this by adding columns per property, which could be used for multi-entity / multi-project or multi-department P&L tracking.

I am wondering how that is coming as the current lack of this in Teller is keeping me on the sidelines.

Thanks

PS: I asked a similar question on the other thread so please reply wherever you prefer.

Hi @ccclapp, Thank you and thanks for inquiring - yes, I’ve made significant progress toward the following extensions to the PnL Analyzer:

  1. Alternative layout replacing monthly columns with any aggregator from the Transactions sheet (it could work with Account, Project, Entity, Department, Tag, etc.), including the ability to custom name the columns with aliases. It will also display items not assigned to an aggregator. It is set-up for up to 11 items plus an unassigned column in keeping with the current twelve column layout, but it could be expanded or duplicated.

  2. Analyze by Tag, or any aggregator from the Transactions sheet (for my purposes I have a Property column as mentioned in the referenced post). Other examples might include Account, Project, Entity, Department, etc.

These align with the same numbered items in the other post that you referenced.

Additionally, I’ve worked on:

A) Ability to toggle on and off “Totals by Group”

B) Ability to toggle on and off a Transfer category section in the main view (I want to report some transfers like Owner Distributions sometimes)

C) Ability to order Categories in any desired sequence within the Group

D) Ability to show and hide Categories using an in-sheet configuration , including overriding the Category sheet “Hide” designator

Whereas the original PnL Analyzer was geared towards personal finances, the impetus behind most of these extensions and enhancements was small business reporting. The second list of lettered items was developed especially to build customized reports such as an income statement. So for example, a snapshot of the live PnL can be taken after year end in preparation for tax season. Not all of these features are included or will be necessary in each variant.

The volume of changes is significant and I don’t necessarily see myself updating the original release here with all of them. Perhaps I would post a new version with some additional features after I’ve taken the time to clean it up, depending on any community interest. Otherwise I think it could be a bit overwhelming, overcomplicated, and onerous to support dumping a lot of these changes here.

There are a few threads floating around looking for a similar solution to item 1) so I do believe it would have a certain level of interest and a high value to small business owners.

I’ve also seen threads with users who have a workflow that splits financial oversight duties and I think solution 2) is a perfect fit where transactions could be tagged by name and then each person has their own PnL Analyzer view selected by their Tag.

The possibilities for customization are nearly endless and for that reason I’ve also considered creating a video-based tutorial, or some blog posts, walking through the layout and steps to develop these enhancements starting from the baseline PnL Analyzer.

I’ll create another reply soon with a few screenshots of those extensions.

Here are some screenshots of what’s possible by extending the original PnL Analyzer solution:

  1. Alternative layout replacing monthly columns with any aggregator from the Transactions sheet

The screenshot shows a sample by Project with an “Unassigned” column plus 4 projects using aliases for the headings - full project names are shown in the chart. Unfortunately the dataset categories are more relevant to personal finance because I don’t yet have a shareable business dataset, but it shows the concept.

  1. Analyze by Tag, or any aggregator from the Transactions sheet

The screenshot shows a sample by Tag called “tagit 2” - refer to selection box in Cell C3. Again the dataset is not impressive, but shows the concept.

Both of the above screenshots are prepared with Totaling rows by Group. The following gif shows the concept of toggling on and off based on user preference.

PnL Analyzer with Totals_r1

When the new year rolled around I flipped the start year to 2024 and noticed that cell B7 gave an error because my new dataset included less than 1 month of data - only a partial Jan 2024. So I fixed that to display a warning for datasets of less than 1 month. You can’t really get a monthly average or projected total with less than a month of data, so instead you might just move up the start month to February and leave the start year in 2023 until some more data comes in for 2024.

While I was at it, I decided to add a new feature to the Transaction Selector section. You can now automate the display of this data based upon highlighting a row using the ColA checkbox. A further description and gif is posted in the original write-up above. If you want to use the feature but have done some modifications to the template that you don’t want to lose by pulling the new version, then just have a look at the change log in the shared template. There were minimal changes involved so you could go to the impacted cells that I’ve referenced there and manually drop in the couple updated formulas. Good luck, and let me know if any questions!

1 Like

One additional improvement has been applied to the Transaction Selector area - it’s intended to help identify patterns and more easily distinguish differences in the sorted values. Let me know if it helps or if there are any other suggestions, thanks! :+1: