Profit and Loss by Project

Hi all, new to Tiller. Has anyone developed a system they like for tracking profit and loss by project? I run a small marketing agency so seeing profitability by clients projects is my bread and butter. Being able to see live cash burn would be even better.

Thanks!

David

Hi @david1,
You might take a look at the Tag feature. You could tag each transactions by project or client.

Here’s an article about How to Use Tags:

Jon

1 Like

@david1

This is the first thing that comes to my mind. Please consider the following:

Go to the Categories tab and create a Group called Client #1/Project #1 and then make categories that represent your income and expense items for this client/project. Next, hide all categories except those for the Group called Client #1/Project #1. Then go to Tiller Labs/Tools/Create Reports/Profit & Loss, then select Period and Interval and then Create Report. I would test this out on one client/project first to see if you like it before building it out to other clients/projects. This might likely become too tedious.

Currently, what time and billing system do you use? How do you allocate payroll costs/hours to clients/projects? Do you have billing rates for your employees? Do you have write-ups and write-downs? In other words, what is your current process?

Blake

1 Like

Cool - yes, I’ve looked tags briefly and will do a deeper dive.

@Blake this is very helpful, thank you.

Currently I’m a sole prop and all expenses are 1099 contractors so I don’t have to worry about payroll reports yet. Currently all my time and billing is done through quickbooks. Trying a couple different options since I’m trying to avoid the full $70/mo quickbooks w/ project reporting. I would only use tiller for project reporting, so your solution might work.

@Blake more granularity in P&L reporting options would be a great feature!

I can see how you can tag transactions. is there a way to filter your P&L by tags?

@anthony.yeung ,
This certainly can be done. But you would need to add some custom formulas to filter by the tags.

The FILTER() function might help.

I don’t believe existing templates automatically filter by tags.

1 Like

@anthony.yeung wondering if you’ve found a solution yet? I’m having the same issue - and solving this issue for me will totally kill my much-hated Quickbooks nightmare. The Live P&L report could work, but I’m not yet smart enough to figure out how to use the Google Filter function on it. The Tags Report as stated above could be awesome too, but I don’t actually see a filter for tag in it (and yes, I have a tags column).

In the Tags Report, you can filter by a tag in cell F4.

@nicoleg I was not able to figure out how to modify the live P&L to filter by tags. I ended up creating separate Categories just for business expenses and assigned them to separate Groups.

Thanks Anthony. That doesn’t quite work for me because I have multiple businesses - so I’d have to create duplicate categories for each business and that would be unbelievably cumbersome. Still searching… I wish I was better at google sheets.

Thanks Jono, that’s not quite it either because I need a P&L, this is just a list of all transactions. There is some summarizing on the right, but it’s not in a P&L format.

I’m trying to figure out how to add the tag report formulas to the live P&L, since I can’t figure out how to edit the tag report to be a P&L.

Hi @nicoleg - I’m also looking to tackle a similar problem in the next couple of weeks. I’m making a switch from another reporting solution to use Google Sheets for my business PnL reporting, with the help of Tiller automation .

I plan to try and extend the solution that I came up with for my personal reporting called the PnL Anaylzer:

https://community.tillerhq.com/t/pnl-analyzer-profit-and-loss-analyzer-solution-for-google-sheets/

The use case is with real estate where I want to segregate by property, but I think the same concept could be applied to multiple projects, businesses, etc.

First off, I created a new column in my Transactions sheet (containing a drop down selection) where I classify/allocate each income and expense item to a property. It’s possible that Tags could work the same way but I haven’t gone too far utilizing Tags yet and I kind of see them as a possible supplement to this process if I ever decide on a way to use them.

I’m aiming to extend the PnL Analyzer in two ways:

(1) A report style similar to the existing one that replaces the Monthly columns with Property columns. It would give a 12 month view of each property in one report.

(2) A report style the same as the existing one with an additional selection by Property. It would give a monthly PnL for a single property.

I could try and share something once I make some progress if you think any of it sounds useful.

I was able to complete Extension (2) fairly quickly so I’ll just share the results here and you can implement it if you’d like.

I’ve just created a duplicate tab of the original PnL Analyzer with these changes for this extension.

In empty cell C3, I created a dropdown to pick the Property that I would use to filter the PnL results. It uses the same dropdown list as my Transactions sheet Property column.

In cell AU7, I modified the QUERY formula to further filter off of the selection in C3. The following image should be self-explanatory for the rest of the required changes.

Note that I simply did a CTRL-Enter in a few places to line break and rearrange the original formula for easier reading, but the highlighted parts are the only real changes.

Also note, the exact same changes need to be applied to cell BP7 if you want to do the comparison to the last 12 months (available to the right in the main report view by expanding at Column S). Apply the same changes manually and do not copy over the whole formula from AU7 as there are some differences.

It’s possible that this extension would work with the Tags column the same way. Please be aware that I have done minimal testing of these changes and you should carefully validate your results.

Update:
In order to have a consistent criteria in the Tranasction Selector area, you can also modify the query there in Cell AA33 as shown.

Update1:
Note that in my further testing of the steps I detailed above, I noticed that you must have all data in the reporting range assigned to a Property, otherwise it can skew the layout of the data. The data should appear but may not be placed in the right columns. There is a fix but it gets a bit more complicated to explain. I hope to share a solution at some point in the future.

Hi @KyleT
I am VERY interested in the modifications to you PnL Analyzer to achieve what you describe in #1 above, being

Did you ever do that?

I like many others are trying to use Tiller for multi-entity / multi-project / multi-department accounting and you seem to be the closest to getting us there for P&L reporting.

Thanks

Hi @ccclapp - You’ll see my more detailed response in the PnL Analyzer post linked below because I wanted to provide a further breakdown there about the work that I’ve been doing to build upon it. The ability to run a report by Project, as is the subject of this post and your inquiry, is definitely doable. I’ll soon put a screenshot over there. I also don’t know exactly how I’ll go about sharing it as it takes a tremendous amount of preparation to put something out for a wider consumption, but I’ll see what I can do. Thanks!

1 Like