Calculate Revenue and Profit Per Employee Over Time

This is not a detailed how-to post. Rather it is short, you will have to do most of the work yourself, but here is some help to calculate revenue and profit per employee over time. Specifically this is for a small business running Quickbooks.

My main goal was to be able to see head counts integrated with financial results by quarter. So if revenue was X and profit was Y in the third quarter, how many employees did we have at that time? Seems a reasonable question to me that was left unanswered by Quickbooks and Microsoft. So here goes:

Using Quickbooks

  1. run the "Employee Contact List" – this is on the reports menu
  2. click "modify report"
  3. Select the following fields:
    1. Employee
    2. Hire Date
    3. Release Date
    4. (uncheck everything else)
  4. go to the "filters" tab
    1. click "Active Status" and then "All" so you get a report of ALL employees
  5. At this point check that all employees that are "inactive" have a "release date" – if not I recommend you fix it here. Refresh your report.
  6. Export to Excel
  7. I’d also recommend you "memorize" this report to get back to it easier next year.

The next steps take place in Excel. More after the jump:

Excel_employee_counts_4
In Excel you have to do some data clean up. Specifically everyone with a "blank" release date, meaning they are currently employed, needs one.  I did a filter for "blank" and filled them in that way in Excel.

At this point you should have something that looks like the image on the right. Note these are clearly NOT numbers for our company, rather this is a fake smaller version I typed up.

From there you have to add the quarterly start/stop dates. In this case
I just filled in the first two and did an "autofill" with the fill
handle to extend the following quarters.

Now for those counts. Because this is a detailed list you just need to
have a "1" or a "0" in that quarter for a given employee name. to do
that you need the following for cell E2

E2 =IF(IF(E$1>$C2,1,0) + IF(E$1<$D2,1,0)>1,1,0)

F2 =IF(IF(F$1>$C2,1,0) + IF(F$1<$D2,1,0)>1,1,0)

E3 =IF(IF(E$1>$C3,1,0) + IF(E$1<$D3,1,0)>1,1,0)

etc… again – use Auto Fill for this

Note the "$" signs are important or it won’t "fill" correctly. A dollar
sign before a given part of the named reference, meaning before either
the "E" or the "1" says don’t change that part. then just fill over and
down.

Last but not least, still pretty manual I guess, is to SUM the totals
by quarter. From there I did a Copy/Paste Special (values) into my
revenue by quarter spreadsheet.

What I would LIKE is to be able to show employee counts integrated with the profit and loss / income statement. So if you could whip that up for me Intuit I’d appreciate that.

Closing thoughts:

  1. Surely there is a way to do this with a pivot table but I couldn’t figure it out.
  2. Technically if you hired someone and they left in that same quarter the above formulas would exclude them. Be aware of this and perhaps extend the formula depending on your needs.
  3. If I did this again – I would have just put it into a SQL database table and done it with SQL statements in a loop as that would have been faster in hind sight.
  4. Another thing to look at is employee turnover rates, but luckily we haven’t had a problem with that lately.
  5. It blows me away that there isn’t an easier way to do this. What a basic question! If people are our biggest asset why is this not integrated better into financial reporting?! Sheesh.

DOES ANYONE KNOW A BETTER WAY TO GET THIS INFORMATION? SOME HR SOFTWARE? PLEASE POST A COMMENT!