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

[boxta]

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!

thanks

 

[Jim Aksel]

You will need to create four identical project files, use different rate
tables in each.

 

[Steve House]

Do you mean you actually pay this individual up to 20 different rates
depending on what they're doing?

Considering that the costs are *estimates* of your internal costs associated
with driving the work forward, how accurate do you really *need* to be? To
the penny cost accounting is a job for the accounting software, not project
scheduling software. How about using an average rate and simplifying to
something manageable? After all, even if you could explicitly account for
all the rates he earns, the costs being calculated using them are only
approximations anyway.

 

[Projectability]

Whilst the 5 cost tabs are a limitation you could consider varying the
resources cost using the effective from date value. You could do this on
each of the 5 cost rate tabs the only downside being if tasks are
re-scheduled the prevailing rate may be incorrect.

--
Dominic Moss

Projectability - Helping People achieve more with Microsoft Project

www.projectability.co.uk

 

[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 thingy.

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.

sigh

thanks for any idea! please keep them coming

 

[Steve House]

I know you're stuck in the middle but this is exactly why I get cold
shudders when I hear about someone using MS Project in an attempt to track
billings and revenues. I have never heard of it being done successfully -
some users might have figured out a way but I've never heard of it. If you
have a designer on staff that you pay $50000 per year, that's what you pay
him no matter what client's project he's working on at the moment. You
might bill client A $2500 for his services for a week and client B $3000,
but no matter what you charge the client (or even IF you charge the client)
you pay HIM $1000 for a week of his work. Your internal resource cost of
using him on the various projects for any and all clients is $1000 per 40
man-hours you use him. Project does a fine job of estimating that for you,
telling you the work he does for client A will cost you X dollars in salary
and the work he does for client B will cost you Y, based on the amount of
time he has put into each. But it knows nothing at all about billings and
revenues and really has no proper facility for even recognizing that those
parts of the project universe exist.

Best of luck

 

[Rod Gill]

I did get one client to finally change how they quoted for work. I used Rate
A as the standard rate billed for each Resource or grade and Rate B for the
internal average cost (including overheads etc).

Instead of providing different rates for each job they provided adjusted
quotes for fixed hours. The clinching argument was that most jobs extended
with extra work so if they quoted a cheap rate, they made little or no money
on that extra work. If instead they quoted their standard rate then extra
work was profitable and at "full rate".

It then took only some simple formulae and actions or VBA code to calculate
the margin for each job (applying first rate B then A).
--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[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

 

[Rod Gill]

One rate structure, but quote fewer hours. So if you fix on $100/h and you
want to quote $1000 then bill 10 hours, not 20 hours at $50. Any extra work
is therefore at $100 rather than $50. So change the number of hours to
control the total cost, not the rate.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Steve House]

IMHO, the rates in project are not what you quote/bill the client. They are
your internal WHOLESALE cost of using the resource - what you quote the
client is a RETAIL price for the project after adding facilities cost, cost
of capital, profit margins, etc, etc. It's just like any merchant - a car
dealer doesn't quote his wholesale price for a new car on the showroom
sticker. He adds his cost of doing business and profit margins, etc, and
that's the number he shows the customer. One guy might buy the car for
$20,000, another might get it for $18,000, and another for $22,000. But the
dealers internal cost might be $15,000 in all cases. The costs Project is
computing abd tracking are those internal costs, not what the client is
charged. They're your costs of doing the project for the client, not his
costs of having you do the project.

 

[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

 

[Rod Gill]

It is possible with VBA. I would have a database or spreadsheet set up with
rates for each resource for each project. A VBA macro can run that resets
the rate for each resource. This can run at the click of a button for the
active project or automatically on opening every project.

Note that when the project is next opened, all rates will have reset to
Global defaults.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[boxta]

Thanks...

Rob.. for the VBA approach.. it still sounds not 100%... Say I create
a look up table with project / role combination, and the associated
rate, I am still not sure how I can use scripts to look that up
information in the database.. because I am not sure how to expose the
role / project information when I assign the resources to my scripts, in
order to look up the rate.

the rates at this client is dependent on both the role and the
project...


alternatively, another approach is to go back to the approach where i
create 1 role per each resource_project .. e.g. Mike_ProjectA,
Mike_ProjectB.

Obiously this would be a huge task in entering the rates .. but if i
can create a script that import the rates in (from an excel??) that
could make this work..

how would this approach compare to the other alternatives?? what do you
guys all think??

i appreciate all the comments so far.. it's been real helpful!! we are
at the end here.. i am about to commit on my findings... and recommend
to the client whether it can or cannot be done.... if can.. then what's
the approach

thanks

 

[WillyBanks]

Take a look at this site. He may have something that could help...

http://iscra.pl/blog/?p=18

 

[Projectability]

I think Rod's suggestion on quoting the hours is elegant and can avoid scope
creep costing you in the long run. One option you might want to consider is
a Project level pricing "factor" - this would allow the customer to consider
the price sensitivity of their various clients and to bid accordingly, one
custom field with the factor value and another multiplying the Total Cost by
this factor would deliver a price to quote the client or a value of the
project.

Over time they may well decide to differentiate clients by market sector and
have this as a project level value too.

It may also be useful to analyse profitability on the various jobs too - the
ones with the biggest mark-up may not always be the most profitable again
providing management with useful insight in to the efficiency of their
operations.

--
Dominic Moss

Projectability - Helping People achieve more with Microsoft Project

Check the Downloads section on our Website for useful documents on both
Project Management and Microsoft Project

www.projectability.co.uk

Why not subscribe to our monthly newsletter for hints tips and views

http://visitor.constantcontact.com/manage/optin/ea?v=001WVdpo956d6lHqIISFtfgVw==