Discussion about enhancing Employee per hour rate management in our Time Sheet application

Recently we have included an option for specifying employee per hour rate in our Time Sheet application.

We are planning to enhance it further in our coming releases.

So, I am in the process of collecting inputs/suggestions from various sources.

As part of this effort, I have asked below question in LinkedIn.

What is the best way to handle Employee per hour rate in Time sheet application?

We are developing and selling web based time sheet application. Currently our application won't allow changing per-hour-rate of the employee. Because if we change the per-hour-rate, then it will affect the budget calculation of his previous projects.

Suppose if I want to allow the feature for changing the rate, what is the best way to handle?

(I think this feature is required in some situations, such as employee promotion)
I am thinking various approaches.

1. Just overwrite the existing rate. But it will affect previous project budget calculations.
2. Store the employee rate for each project individually. i-e store rate - project lookup.
3. keep note of date on which the rate has changed. Use the previous rate or current rate based on the date. i-e store the rate -date lookup.

Which is best approach or give any other good suggestion.

I am getting good answers for this questions. Find below some of them.

MS Project uses a date driven rate table. So if someone has a particular role or functionality for a period of time or if they get a raise or promotion it will only calculate assignments during that time period. Good luck.

As you know, typically internal estimates are computed as follows:
Hours * Pay Rate = Budgeted Line Item

Often times however, because estimators know the maximum market value:
Budgeted Line Item / Pay Rate = Hours

My point is that both the total ‘Hours’ and the ‘Budgeted Line Item’ are variables to monitor, and not so much ‘Pay Rate’. Realizing that the ‘Pay Rate’ can indeed fluctuate on any given task/job; it’s only important when calculating the other two.

In other words, who cares how much an employee gets paid, when the got a raise, etc.,

What is critical is monitoring TOTAL HOURS on the project (this can be divided between skilled and unskilled) and TOTAL BUDGETED LINE ITEM.

It’s always more important to finish a project AT or BELOW budget regardless of how much you paid someone to get it done or how long they took to do it.

This is what I do. Each item in time sheet has also option to select per hour rate - by default per hour rate of the employee. In case of any rate change, I set new per hour rate for the employee and just hide the old one. Each item from time sheet "knows" its own rate and it's easy to calculate cost. Hope this helps.

Most timesheet applications have a project-level override that implements #2 when there is data for an override, otherwise falls back to the employee's default rate. Usually, the rate change at the default rate level is also set up on a date range, so as that changes with their experience level it won't affect previous projects

the ideal solution would provide a cascading "override" capability from a system-wide default that would be "effective-date" based (i.e. multiple rates per resource over various periods of time), to project-level rates, to task-level rates (for example, a resource could be charged at a normal rate if they are performing normal duties, but might be billed at a lower rate if they are performing a lower valued function).

1) Definitely not, you wouldn't be able to use the system to track project profitability.

2/3) This depends on the detail of the data, how it is stored, how much there is and how the system accesses it. If the amount of data is fairly small then it probably doesn't really matter, option 2 is probably cheaper to build and maintain but option 3 may be a better overall data model. If the data is really large, you need to employ a DBA to analyse the system. Oh, and you might need to watch out for record locking issues with option 3.

The systems I have worked with have all stored the employee hourly pay rates in a separate table that is segregated by dates. In addition to the timesheets and budget rates, you might also need to consider hourly billing rates which are typically unique by customer and labor category with a different set of dates.
So you could potentially need to use three or more different rates per person.

I am still looking for more inputs from various users. Especially I am looking for suggestions/comments from our free Time Sheet Users and from the people who bought our earlier versions of our Time sheet.

As of now, we are planning to do below things.

- Store the "per hour Rate" of each employee in separate database table along with starting date and Ending date.

- And, while allocating the employees to an project, an option will be provided to overwrite the default "per hour rate" of the employee for the specific project.

- In Report page, actual budget will be calculated based on project specific rate if the default rate is over written, otherwise use the default rate corresponding to that particular date (i-e hours worked on which date)

It may add slight complexity to the code and flow. As we are planning to keep our Time Sheet Simple and powerful, I am still looking for better design to implement this feature effectively.

You can share your suggestions thro' the comments.

Updates:
After doing more analysis we have decided to keep  our timesheet simple. i-e It will allow updating/changing the employee hourly rate without doing any project based or date based implementation. Because most of our customers are buying it as it is simple comparing to any other available timesheet. I don't want to change. Anyway,  we can do any custom changes based our customers' own requirements for additional cost.

More Articles...
You can bookmark this blog for further reading, or you can subscribe to our blog feed.