Productivity formula for home health?

[MrsBliss22]

Hello! I need some help creating a productivity formula or at least someone pointing me in the right direction please.

My home health workers have 30 patients on their caseloads and are expected to see all patients once a month. Travel time is not reimbursable and that time should not be counted against them. I want to get them raises but the only way to do that is by calculating their productivity.

Any thoughts or suggestions? Thank you.

 

[etaf]

sounds good to me.
The billing code is that per client or per employee
will the data export into excel at all

 

[MrsBliss22]

sounds good to me.
The billing code is that per client or per employee
will the data export into excel at all

Unfortunately the data will not export into excel. I will have to put in a formula and enter each employee manually the first time.

The report is broken up by employees and the billing code represents the service that employee provided the patient with.

 

[etaf]

if you cannot export the data - are you planning on manually entering ?

 

[MrsBliss22]

Yes, I will have to manually enter the data.

 

[etaf]

then its important how you structure that data
its natural to setup different tabs for names or months
Do NOT do that
set it up as a database , then its easier to produce reports using pivot table

DATE ... Employee ... Bill Hours ....

Heres an example
https://www.dropbox.com/s/s5n146fr7008erb/productivity.xlsx?dl=0

 

[MrsBliss22]

Thank you!!! I'm glad you pointed that out! Any thoughts on the final formula to use for productivity measurement?

 

[etaf]

i have updated
Added some formula to the side of the pivot table - Thats usually not the best thing to do
as soon as you change the pivot table all the formulas will be wrong

but its a start
https://www.dropbox.com/s/s5n146fr7008erb/productivity.xlsx?dl=0

 

[MrsBliss22]

Thank you so much!! I will work on this tomorrow. It's impressive how you can put something like that together so quickly. It's a gift! I appreciate all of your assistance and your patience!