Analyzing projects in terms of effective hourly revenue

Hi there,

another question has come up where I am racking my brain for the best solution.

We have self-contained projects, for which we tend to bill clients at a fixed price, and others where we bill our clients hourly. We track the time needed for each project, whether fixed-price or not, using Clockify, which can export CSV timesheets. This data could enable us to calculate the effective hourly rate we charge the fixed-price clients (or the per-hour clients, but including non-billable hours) and allow us to optimize our pricing policy. Also, with data such as expenses and salaries, we could also calculate the effective hourly profit.

I am looking for something in this vein, altough I think this has been split out into a separate module in ERPNext 14 (maybe Frappe HR, which we haven’t looked at yet and don’t want to add just yet):

…, except with an additional column that shows the hourly revenue.

I tried using the following approach in terms of data entry:

  • Adding a project and associating it with an existing customer

  • Adding a timesheet with entries referencing the aforementioned project, adding hours per type of work done (since we do not even need detailed timesheet data, only the sum of hours per type of work and project). For fixed-price projects, we set all entries as non-billable, for hourly-price projects we set the billable entries as billable :wink:

  • Somehow associating the timesheet with a sales invoice, however this seems to be a misuse of the feature, since I think it is only for importing billable timesheet data if I am not mistaken. Also this does not work for already submitted invoices.

Anyway, I haven’t been able to get a useful report out of ERPNext so far, since we can’t get the actual billed amount factored into the calculation (basically have not found a default report we could “piggy back” on). Can someone put me on the right track? Being a developer I am comfortable writing some custom Python code, but there seems to be a basic data/domain model issue at the heart of this.

For the time being, an hourly rate (revenue) calculation is totally sufficient, as opposed to profit.

Thank you in advance!