How would you answer this question? Our revenue per earned hour is down from last year. We want to dig into the jobs where this was the worst. How do we identify these jobs?
I have to link the job to the sales order to get the unit price. Then I have to link to ship detail to see the quantity shipped. Am I on the right track? Is this an solved problem already?
Thanks!
Nate
you might simply look directly in the Invoice Detail table. It should have both the price and the COST. This would be a good starting point to find out SHIPMENTS where the cost and price do not have the expected ratio.
I ended up with this BAQ. I had to make some concessions on the earned hours part of it. Since we could have earned hours for years before we ship the part, I couldn’t really relate the ship date with earned hours. So I took all the earned hours for the whole job, and compared that to the revenue we made on the job in a particular timeframe. As long as we assume most of the work was earned in the same year as the shipment was shipped then we can draw some conclusions about how much revenue was generated per earned hour.
Edit: reposted file with part number:
RevenuePerEH.baq (57.3 KB)
To use this, I export a list of jobs and the Revenue per EH for 2024. Then export another list of the jobs for 2025. Using vlookup, any jobs we ran in both years we are comparing the rev/EH to see if it went up or down, and maybe that will help us figure out why.
This is all exploratory at the moment. Trying to figure out why there is a difference.
We have a profitability report by part that we look at to show this information. That does help them, but they wanted this additional report to help potentially identify the issues by job number.
I slept on it, and wanted to come delete this post. The revenue per EH is just not a valid metric. So I am thinking about this again. When I pulled all my Invoice Details, I can see 4 shipments for the same part (2 invoices for 2 shipments with 2 lines each). The costs total the same exact value for all four lines. I would expect the cost to be different for each line as we shipped different amounts of parts.
Shouldn’t each shipment have a unique cost defined by the amount of work performed (and materials consumed) for the number of parts shipped? How can I figure out what level to roll up the costs under to get an accurate cost?
This depends on your costing method. if you are standard cost, and you didnt change the standard cost between each shipment, then the cost would be the same. if you are average cost, then the cost could still be teh same if you manufactured all the parts on the same job.
IF the items were “make direct” then the cost can come directly from the job. if the items were from stock, it gets a little tricky.
But… getting back to your original problem statement, you are trying to find out why your costs are higher than expected. This might be better to analyze separate from the invoice. Instead, look at the JOBS and compare the estimated costs (labor, material, sub contract, etc) to the Actual charges, and then dig into those jobs where the value is out of tolorance. This separates sales price from cost.
Then also compare the sales price to the cost later.. I have seen times where the cost to manufacture went up (labor rates, material purchases, etc), or, sometimes a maching breaks and labor is less efficient, or sometimes someone didnt increase the sales price when they should have.
I believe we are last cost. We mostly ship against the sales order releases (Make Direct), but sometimes we have to ship from stock.
Where do the estimated job costs get generated and stored? EDIT: Just looked at JobAsmbl %cost fields and found dozens of them!
Thanks for your valuable insight!
Importantly, if I use the JobAsmbly table I have to use the TLE and TLA costs to get “This level estimated/actual” costs. Once I realized that some numbers started making sense.
