Hey there,
I’m trying to figure out how to show transfer categories on the Live P&L report, even though it’s not listed as ‘hide’ on the category tab.
I’m plowing through all the formulas and can’t seem to find where it’s hidden. It’s even not showing “hide” next to it on the Live P&L hidden area.
Any ideas?
The Transfer category type tends to get “hidden”, not because it’s marked Hide, but because reports process Income and Expense types. I haven’t looked specifically at the Live P&L report though.
Yep. Just trying to figure out how to show it - thanks!
I don’t think you can, I believe it was built intentionally without the ability to show transfers.
Huh. That seems like a miss, given you can do it in the script generated report (or used to be able to anyway), you literally have a feature for “hide from reports” to hide anything you don’t want to see.
But where would they go in a P&L? Transfers are neither income nor expense. I suspect that’s why they’re not included. They would have to have been designated as a separate section of the P&L, which would, I think, look odd.
They would go in the transfer section.
There are 3 different types of categories - income, expenses, and transfers. On the Live P&L, there is an income section, and an expense section, which is to be expected and is normal. But to be thorough, you would include transfer items as well, (to do things like ensure they equal zero, etc) - simply because you can hide the transfer categories from reports on the categories tab. I want the sheet to act fully, without hidden exceptions. So you should be able to hide/show all categories (including transfer types) based on that column in all reports, without hidden exception. The original P&L that uses scripts includes transfer categories, and it also includes uncategorized (which was a nice touch). I was expecting a similar behavior from the Live P&L. Not including the uncategorized makes some amount of sense to me because you can easily see that in other places, like on the spending trends tab - although I liked it on the P&L because then I knew my P&L was (or wasn’t) accurate. Same with Transfers. If you have a massive imbalance in your transfers - or if you use transfers for a different purpose (for instance I use transfer type to track investments), you want an easy way to see it, and the P&L is a great place for that.
I agree that Transfers can be useful to see. I use a different P&L report as a starting point to create additional financial statements for a business where I want to see Transfers such as Owners Distributions.
In the Live P&L, cell Y9 is the gatekeeper. It’s filtering out any transactions with Type marked “Transfer”. If you delete INDIRECT(S7)<>"Transfer" there you’ll get your Transfers to show. Note that they still have to be in a Category that’s not marked “Hide” in the Categories sheet. And it’s still not the end of the story, as at least in my case the display would need some work. For me it showed a section Transfers, but then Income (at least the header) didn’t show anymore. Probably some modifications would also be needed in the Category layout area.
Thanks, that’s helpful Kyle. I don’t know why I didn’t find that earlier, and I looked. Hard. And yes, now it’s messing up the category layout as you predicted, so now I get to go figure out how to navigate that challenge. I wish there were tutorials on how to do all these things - someone to walk me through how each of the areas are organized and what the formulas do so I can learn and edit.
Curious, what report do you use for financial statements for a business showing transfers like owner distributions? This is the exact kind of thing I’m trying to figure out. I want a standard P&L, filtered by tag (see below), plus transfers which would include straight transfers between accounts (category transfer, type transfer), owner investments (category Owner Equity Investments, type transfer), owner distributions (category Owner Distributions, type transfer), and finally Investments (category investment, type transfer) and Investment Returns (category investment returns, type transfer). Having it all one one report, the P&L, makes tax time super efficient.
I figured out how to filter the transactions by tag, that took me a while, because I’m tracking a number of different LLCs, plus my family expenses, given they all roll up to my own tax return, and having this all in one place rather than across 3 different QuickBooks files PLUS YNAB for personal has been a game changer - absolute game changer, but damn it’s taking me a long time to spin it and get it working. (and honestly I love supporting Tiller rather than Intuit).
Hi @nicoleg - You’re welcome. Sorry it’s only a partial answer. I’m sure the layout issue can be worked through but may take some more troubleshooting time. I had a quick look but didn’t have the time to invest at the moment.
The P&L report I referred to was something I created for a Show and Tell here. The basic version that I shared doesn’t include Transfers, but I came to realize that I needed it, along with a bunch of other features, to support some small business reporting. I’ve mentioned some of the extra features further down in that post in follow-up comments in December.
A more advanced version looks something like the screenshot below, with column headers representing individual properties and an LLC. You can see there’s a checkbox to include Transfer categories. (note that it’s just a sample business dataset so the data may not make sense).
I’m glad to hear that you’d find value in a tutorial because I agree that there is a lot of powerful reporting that can be done and I think there’s value in explaining it in detail to allow someone to customize the reports to their liking.
I am planning to release some educational tutorials in the future that step through how the basic P&L Analyzer is organized and then how to build atop it step-by-step with more advanced features. Hopefully sooner than later!
I am looking to do similar stuff. The best way I’ve found to do a p and l and keep up with distributions is via a pivot table described in this YouTube video: https://youtu.be/_cuodrk6P-k?si=n_uF6X1v-Xh90mLE
I have found it very hard to customize the live profit and loss. I love the live profit and loss at baseline but I haven’t had any success customizing it to what I’d like to. If anyone has any recommendations, please let me know. Thanks!
Thanks Kyle, your report is a thing of beauty, and I originally tried to use it but unfortunately it didn’t work for me simply because I need the ability to filter the report by tags. I just installed your sheet again and I don’t see the same checkbox options that you have (using v3.1) and it looks like it can group by tag, but not filter by tag. (Also I should add these are not tags on the category sheets, they are tags on the transaction sheet).
If you feel like starting your first tutorial with how the Live P&L category layouts work - I’ll happily be your first guinea pig! And if you have a version of your sheet that lets you filter by tags, I’m all ears.
Thanks @nicoleg - it’s correct that the screenshot I included in this thread is an advanced version that has more features than the baseline version in the original post. I’ve put a significant amount of additional effort into further expanding the capabilities. Your use case of “filtering by Tag” (those entered on the Transactions sheet) is already developed. Columns C&D, Row 3 of the screenshot have a dropdown with the title “Tag Selection” - that’s the feature that you’re looking for. It could also filter by a completely separate column in the Transactions sheet (ex. “Property” for real estate or “Project” for project-based businesses).
Because I’ve put so much time into it, I don’t have immediate plans to continue releasing updates at the original template link. I’ve started to develop some video-based tutorials as I mentioned, but these will take additional time to complete. The template itself is already complete with the advanced features. If you wanted some more details right away, please contact me via e-mail at info@sheetstopia.com.
Just to summarize the features:
P&L Analyzer v3.1 (available for download at post linked above) displays up to one year of data with comparison to the previous 12 month period. It includes a Transaction Selector that shows all of the transactions within a Category with option to hyperlink each one back to the Transaction sheet. Additional features include automated display of Transaction details when checking a box to highlight a Category row, as well as the option to apply alternating colors to those transactions for a better visual.
Advanced P&L Analyzer includes the following already-developed features:
Future plans (not yet developed):
Feel free to reach out to me if you have any other feature requests or suggestions. Thanks!
