Separting costs for internal and external resources

[ProjectFly]

I have what appears to be a simple problem. In my project plan, I have a mix
of internal resources and contractors. All of them have different rates.

I can very easily find the total cost for a task and nice rollup into
summary tasks etc. What I need is the separation of costs for internal and
external resources. I don't have any material costs. The output I am looking
for is as follows
Task Name Cost Internal External
Summary Task $450 $220 $130
Task 1 $100 $20 $80
Task 2 $150 $0 $150
Task3 $200 $200 $0
Thanks in advance for your help. I have tried adding a flag to resource
sheet to identify internal and external resources. How do I use this flag to
do the cost calculation. Are there any other simple ways of getting this
information.

 

[Mike Glen]

Hi ProjectFly ,

Welcome to this Microsoft Project newsgroup

How would Project know how to apportion the costs? Unless you have a
standard ratio, you'll (or is yule!) have to enter the data yourself.

FAQs, companion products and other useful Project information can be seen at
this web address: <http://www.mvps.org/project/>

Hope this helps - please let us know how you get on )

Mike Glen
MS Project MVP

 

[JulieS]

Hi ProjectFly,

I can arrive at a partial answer using the Resource Group field in a task
view. Enter the Internal or External information on the resource sheet in
the Group Field. You may then add the Resource Group field to any task view
and see which group the assigned resources are from.
You can then add one of the custom Cost fields and show costs where only
internal or external resources are assigned using a formula like:
IIf([Resource Group]= "Internal", [Cost], 0)
Use another cost field to test whether the resource group equal "External".

The challenge becomes when you have both internal and external resources
assigned to a single task. I have not yet be able to come up with a way to
parse that information and determine which part of the cost belongs to which
resource in Project.

Another option is to start with the beginning suggestion (use the resource
Group field) and then export that information to Excel. I used the Export
Map "Task List with embedded assignment rows" and modified the map to add the
Resource Group field. Once in Excel, it becomes much easier to manipulate
the data.

Hope this helps partially and perhaps another reader can respond with a
better answer. I'll keep plugging away and will post with a possible
solution when I find one.

Julie

 

[toast88]

I, too, have the need to track costs by different categories of resources as
ProjectFly stated. I have not found a way (or invested enough time yet to
find it!) to avoid exporting to Excel for the calculations.

Julie, hopefully you will be able to identify a solution within MS Project.

Hi ProjectFly,

I can arrive at a partial answer using the Resource Group field in a task
view. Enter the Internal or External information on the resource sheet in
the Group Field. You may then add the Resource Group field to any task view
and see which group the assigned resources are from.
You can then add one of the custom Cost fields and show costs where only
internal or external resources are assigned using a formula like:
IIf([Resource Group]= "Internal", [Cost], 0)
Use another cost field to test whether the resource group equal "External".

The challenge becomes when you have both internal and external resources
assigned to a single task. I have not yet be able to come up with a way to
parse that information and determine which part of the cost belongs to which
resource in Project.

Another option is to start with the beginning suggestion (use the resource
Group field) and then export that information to Excel. I used the Export
Map "Task List with embedded assignment rows" and modified the map to add the
Resource Group field. Once in Excel, it becomes much easier to manipulate
the data.

Hope this helps partially and perhaps another reader can respond with a
better answer. I'll keep plugging away and will post with a possible
solution when I find one.

Julie

I have what appears to be a simple problem. In my project plan, I have a mix
of internal resources and contractors. All of them have different rates.

I can very easily find the total cost for a task and nice rollup into
summary tasks etc. What I need is the separation of costs for internal and
external resources. I don't have any material costs. The output I am looking
for is as follows
Task Name Cost Internal External
Summary Task $450 $220 $130
Task 1 $100 $20 $80
Task 2 $150 $0 $150
Task3 $200 $200 $0
Thanks in advance for your help. I have tried adding a flag to resource
sheet to identify internal and external resources. How do I use this flag to
do the cost calculation. Are there any other simple ways of getting this
information.

 

[John]

I, too, have the need to track costs by different categories of resources as
ProjectFly stated. I have not found a way (or invested enough time yet to
find it!) to avoid exporting to Excel for the calculations.

Julie, hopefully you will be able to identify a solution within MS Project.

Hi ProjectFly,

I can arrive at a partial answer using the Resource Group field in a task
view. Enter the Internal or External information on the resource sheet in
the Group Field. You may then add the Resource Group field to any task
view
and see which group the assigned resources are from.
You can then add one of the custom Cost fields and show costs where only
internal or external resources are assigned using a formula like:
IIf([Resource Group]= "Internal", [Cost], 0)
Use another cost field to test whether the resource group equal "External".

The challenge becomes when you have both internal and external resources
assigned to a single task. I have not yet be able to come up with a way to
parse that information and determine which part of the cost belongs to
which
resource in Project.

Another option is to start with the beginning suggestion (use the resource
Group field) and then export that information to Excel. I used the Export
Map "Task List with embedded assignment rows" and modified the map to add
the
Resource Group field. Once in Excel, it becomes much easier to manipulate
the data.

Hope this helps partially and perhaps another reader can respond with a
better answer. I'll keep plugging away and will post with a possible
solution when I find one.

Julie

People,
I know I answered a very similar post recently but for the life of me I
can't find the response. Maybe it was deleted or lost.

The problem of course is dealing with multiple resources on a single
task. As long as the number of different resource groups is not large,
say in excess of 10 or 15, an export to Excel is not needed, but VBA is
needed. The psuedo code to do what you need is as follows:
1. Set up custom fields for the cost of each resource group. There are
10 spare cost fields and 30 spare text fields so Project can actually
support more than the 10 to 15 resource groups I noted above.
2. In VBA set up a loop to cycle through all tasks with assigned
resources.
3. Within the task loop, set up another loop to cycle through each
assignment of that task.
4. For each assignment get the appropriate pay rate, apply it to the
work hours for that resource, and store the result in the designated
custom field as a running total.

That's it, pretty straightforward actually.

Hope this helps.
John
Project MVP