How to calculate cost based on resource rate? I have more than 5 rates per resource

[boxta]

Sponsored Links

HI guys, I am using Project 2007, and I am not able to get exactly what I need. Currently, in the resource information, one can assign 5 rate tables per resource (A-E). However, I have a requirement to assign up to 20 different rates to an individual. I am not able to get around with it. Hence, am not able to get the actual cost estimate for projects Does anyone have any thoughts? I am trying a few workarounds: By pass the rate table, and create a custom field with look up values in Project Plan, I can get the field to hold as many rates as I can, but ultimately you can only have 1 rate per row, which means 1 rate per task . When you have multiple resources to a task, then i gets tricky. I am also thinking about creating different resources for the same person. So say for mike, he has 20 different projects, hence 20 different rates potentially. I will create mike_1, mike_2, mike_3 and mike_4. Each carry 5 rates, total of 20. This would however be quite confusing as one would need to remember which project is assigned to which mike's profile. I am out of idea. Any thoughts would be appreciated!

[FRIESEN]

For that you will have to wok with 4 different identical files and I think it is possible. There can be a set of different rate tables available which can work on its own. What I think that you are asking to here to work with a set of 20 tables where each of them is having a set of different rates. And as per your query your job looks more related to some kind of accounting work rather a work of project management software.

[boxta]

Hi guys, thanks for all the responses so far! But I am still not sure I have the perfect solution yet. All the cost rates are stored in project server, and accessible throughout the company, so storing it across different projects won't work. The workaround I found as stated is to have resource_1, resource_2, but it's not ideal. This effect is actually for my client. They are a creative company, so the rates are the rates they charge their clients. They have 30+ clients at a time, and each individual can be assigned to a number of those. So from a project server perspective, each individual could be assigned any of the 30 clients, and each of those have different rate structures. So they are using the rate cost table more as a company wide contracting rate inventory system thing. They seem adamant about using actual rate, not average.. nothing i can do about it. Again, because it's actually different rate structures for the 30+ clients at a time they have, so the rates in each of the 5 tabs, where you ID by different effective date probably won't work as well I am kinda running out of idea. My current plan C is to create a customized field column, create a look up table with hierarchy structure that lists out all the projects and the role and the rates associated with them. Then just let the user select it in the project view jsut as another column. It works well and I can have another calculated column that calculate the daily $ based on the rate. The limitation is that it's really 1 rate per task in the project. When you have more than 1 resource assigned to a task at a different %, then it doesn't really work well.

[Patter-Son]

The process when goes manual in MS Project tracking work, it becomes a bit complicated to find the right solution. I had seen a number of time in my work I am not getting exactly what I am looking for. There is a difference always and where the job becomes complicated. There must be some kind of internal plugin that should work well to deal with accounting stuff also. I am still referring to manual but yet not found a detailed information on the same.

[boxta]

HI Rod,

Can you elaborate on how you use a standard rate to accomodate the different rates for each job??

Do you mean you ended up telling them to just quote the standard price for fixed amount of hours?? So they don't ever really have multiple rates?

Each project would then be standard + / - $x??

I don't think they will ever agree on a one rate structure... :|

thanks

[Theo-Dore]

If you are planing to get some kind of quote stuff in Project then you have to work with the rate and embed that properly to each task. Or else it is not going to work really well. Sometime it is annoying but there are chances of getting a option to work on the same. I am working on a test project file that can allow me to get a set of quote through adding rate and then specifying the task based on that. It is better to use a accounting software that that place. .

[boxta]

hmm.. agreed that the cost structure is definitely more geared towards internal cost versus bill rate.

However with that said, I am trying to explore the options till the end..
I have presented few options to the client so far:
* Using resource_1, resource_2, etc to extend the number of rates associated, and use a roll up to add them back to resource
* bypass the resource cost, and create a custom column using look up on the project main view, and allow user to use the drop down to select rate
* bypass project, build a custom .net application, and use MOSS / SSRS to report the project cost status

and pretty much all been shot down.... they would like the equivalent of the functionality of having more than 5 rates associated with a resource, that can be accessed via both PWA or Project no matter what. He's questioning if we can create an extra look up table that holds all the rate, and have some vba / .net script that runs in project to help populate the cost based on the look up. Basically in the new few days, I need to confidently tell him we have explored all the options. Forget about it's more geared towards internal cost for a sec, and forget about changing their way of accounting bill rate, the ultimate question is: is their requirement even possible?? if we are willing to do .net development? I need to know the answer for sure..

please help, and thanks in advance

[WillyBanks]

I think what you guys are trying to do is somehow possible in VBA and it might work. You will need to work on a database or on a spreadsheet. Later on you will need to configure a rate for each resource that you are using. Just activate the same when you are opening other different projects also. If you face any problem with that then you can simply restore the default back again and the issue would be resolved.

[TaniaR]

The best I could do without development was to add a rate field to the Task. We have resources whose rates differ per project based on what was negotiated for the project. Most of them work across more than 5 projects. However when we cost the project (as in selling price not cost price) we start with roles (i.e. generic resources) and then later replace the generic resource with a real resource, however by then the budget was based on the "role", as such it is easy for me to do a rate by task and I split the task into two (or more) for exceptions where I have more than one rate(resource) assigned to the same task. This is an exception rather than the rule.

To do this I've created 4 new columns in the server:
Task Rate (which is entered)
Task Cost = [Work] * [Task Rate] / 60
Task Actual Cost = [% Complete] / 100 * [Work] * [Task Rate] / 60
Task Remaining Cost = [Work] * [Task Rate] / 60 - [% Complete] /100 * [Work] * [Task Rate] / 60

Project Server advises that you should not exceed 5 calculated fields on TASK level. You will notice I did not re-use the Task Cost and Task Actual Cost when calculating Task Remaining Cost as my believe is it will slow the calculation down....just my view though...not tested.