Assessing Costs in MS Project 2000

[DGD]

I am laying out a project plan and am trying to determine a few
things. First, I would like to get a roll up of the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmed to add
both resource and fixed costs, trying to separate the two costs is
really problematic.

When I use the fixed cost column, I can never get the fixed costs to
roll up to get a summary, regardless of level. They are there in the
task row, but just won't roll up to higher level tasks or the top
summary line. Don't understand what going on here.

The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totals up the numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixed cost
tables. Are there any easier ways of doing this?

Any help greatly appreciated.

Doug

 

[John]

I am laying out a project plan and am trying to determine a few
things. First, I would like to get a roll up of the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmed to add
both resource and fixed costs, trying to separate the two costs is
really problematic.

When I use the fixed cost column, I can never get the fixed costs to
roll up to get a summary, regardless of level. They are there in the
task row, but just won't roll up to higher level tasks or the top
summary line. Don't understand what going on here.

The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totals up the numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixed cost
tables. Are there any easier ways of doing this?

Any help greatly appreciated.

Doug

Doug,
OK, let's address your issues one at a time.

In Project the Cost field is designed to total up all costs, labor and
non-labor. For labor effort, cost = resource rate x resource work. Now
to clarify something. You don't "assign" resource costs, resource costs
are calculated per the formula above. However, if what you really mean
is that you would like a separate cost field (e.g. Cost1) that shows
only labor costs, then you will need either a formula or some VBA to do
that. A formula will work fine if there is only one resource per task.
The Cost1 field could be customized with this formula,
Cost1=[Work] x rate
Unfortunately the rate value will either need to be manually entered
into the formula or it can be copied from the Resource Sheet to another
spare field in the Gantt Chart and then that field used in the above
formula.

If there is more than one resource per task, which is often the case, a
spare field customized with a formula won't work because individual
resource hours are not available in a task view. The only way to get
what you want is through VBA. For reference, the following code will put
the labor hours only into Cost1 for each task and roll up those costs to
all summary lines.

Sub Labor_Cost()
Dim t As Task
Dim a As Assignment
Dim ch As Task
'first gather all labor costs for each task
For Each t In activeproject.Tasks
t.Cost1 = 0
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeWork Then
RRate =
Mid(activeproject.Resources(a.ResourceID).StandardRate, 2, _

Len(activeproject.Resources(a.ResourceID).StandardRate) - 4)
t.Cost1 = t.Cost1 + a.Work / 60 * RRate
End If
Next a
End If
Next t
'then sum up labor cost values to summary lines
FilterApply Name:="summary tasks"
Sort key1:="outline level", Ascending1:=False, outline:=False
SelectTaskColumn
For Each t In ActiveSelection.Tasks
t.Cost1 = 0
For Each ch In t.OutlineChildren
t.Cost1 = t.Cost1 + ch.Cost1
Next ch
Next t
Sort key1:="ID", Ascending1:=True
FilterApply Name:="all tasks"
End Sub

Now let's address your fixed costs. You don't say why you are using the
Fixed Cost field. It is normally used for such things as a fixed price
task performed by a subcontractor or the cost of material used in a task
(e.g. x cans of paint for a house). Fixed cost is anything that does not
change no matter how long the task takes or how many hours of effort are
required. Given that, Project does not sum up fixed costs to summary
lines because fixed cost is a cost that may very well be applied at the
summary line itself (labor cost are not or SHOULD not be assigned to
summary lines). For example, 50 gallons of paint for the interior rooms
in a house. Each performance task of the "Paint house interior" summary
line describes the variable labor for each room.

OK, final item, your per diem. Any resource that is a work type resource
will cause hours to accrue in the Work field. If you don't want your per
diem to generate hours then I suggest you make per diem a material type
resource. In the Material Label field enter "days." (don't forget the
period or Project will flag it). Then in the Standard Rate field enter
$200/day. Now when you assign per diem to your travel tasks, simply
assign the number of days the per diem will be paid. Be advised the per
diem days will NOT track the duration days so you will have to adjust
each accordingly, but you also will not see any hours appearing in the
Work field for per diem.

Whew! Hope all the stuff helps.
John
Project MVP

 

[DGD]

I am laying out a project plan and am trying to determine a few
things. First, I would like to get a roll up of the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmed to add
both resource and fixed costs, trying to separate the two costs is
really problematic.

When I use the fixed cost column, I can never get the fixed costs to
roll up to get a summary, regardless of level. They are there in the
task row, but just won't roll up to higher level tasks or the top
summary line. Don't understand what going on here.

The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totals up the numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixed cost
tables. Are there any easier ways of doing this?

Any help greatly appreciated.

Doug

Doug,
OK, let's address your issues one at a time.

In Project the Cost field is designed to total up all costs, labor and
non-labor. For labor effort, cost = resource rate x resource work. Now
to clarify something. You don't "assign" resource costs, resource costs
are calculated per the formula above. However, if what you really mean
is that you would like a separate cost field (e.g. Cost1) that shows
only labor costs, then you will need either a formula or some VBA to do
that. A formula will work fine if there is only one resource per task.
The Cost1 field could be customized with this formula,
Cost1=[Work] x rate
Unfortunately the rate value will either need to be manually entered
into the formula or it can be copied from the Resource Sheet to another
spare field in the Gantt Chart and then that field used in the above
formula.

If there is more than one resource per task, which is often the case, a
spare field customized with a formula won't work because individual
resource hours are not available in a task view. The only way to get
what you want is through VBA. For reference, the following code will put
the labor hours only into Cost1 for each task and roll up those costs to
all summary lines.

Sub Labor_Cost()
Dim t As Task
Dim a As Assignment
Dim ch As Task
'first gather all labor costs for each task
For Each t In activeproject.Tasks
t.Cost1 = 0
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeWork Then
RRate =
Mid(activeproject.Resources(a.ResourceID).StandardRate, 2, _

Len(activeproject.Resources(a.ResourceID).StandardRate) - 4)
t.Cost1 = t.Cost1 + a.Work / 60 * RRate
End If
Next a
End If
Next t
'then sum up labor cost values to summary lines
FilterApply Name:="summary tasks"
Sort key1:="outline level", Ascending1:=False, outline:=False
SelectTaskColumn
For Each t In ActiveSelection.Tasks
t.Cost1 = 0
For Each ch In t.OutlineChildren
t.Cost1 = t.Cost1 + ch.Cost1
Next ch
Next t
Sort key1:="ID", Ascending1:=True
FilterApply Name:="all tasks"
End Sub

Now let's address your fixed costs. You don't say why you are using the
Fixed Cost field. It is normally used for such things as a fixed price
task performed by a subcontractor or the cost of material used in a task
(e.g. x cans of paint for a house). Fixed cost is anything that does not
change no matter how long the task takes or how many hours of effort are
required. Given that, Project does not sum up fixed costs to summary
lines because fixed cost is a cost that may very well be applied at the
summary line itself (labor cost are not or SHOULD not be assigned to
summary lines). For example, 50 gallons of paint for the interior rooms
in a house. Each performance task of the "Paint house interior" summary
line describes the variable labor for each room.

OK, final item, your per diem. Any resource that is a work type resource
will cause hours to accrue in the Work field. If you don't want your per
diem to generate hours then I suggest you make per diem a material type
resource. In the Material Label field enter "days." (don't forget the
period or Project will flag it). Then in the Standard Rate field enter
$200/day. Now when you assign per diem to your travel tasks, simply
assign the number of days the per diem will be paid. Be advised the per
diem days will NOT track the duration days so you will have to adjust
each accordingly, but you also will not see any hours appearing in the
Work field for per diem.

Whew! Hope all the stuff helps.
John
Project MVP

Thanks for the feedback and the VBA code. I will certainly try all of
this out. Concerning the per diem and use of the Material label -
will look that up - I didn't know it even existed. That should
correct my issue. It's too bad fixed costs are not summarized like
resource costs. When planning out project the summary of labour and
fixed costs are the first lines looked at - how much is this going to
cost? Project, for all of its power, seems to miss this very basic
point.

In the resource sheet, one can enter a $/hr rate, or a $/usage rate.
It seems they are treated the same by MSP. A $/hr rate should
generate some entry into the work column and the $/usage rate should
not, but that is not what happens. Both generate a work entry in the
work column. Doesn't make sense to me.

Next task is to figure out how to enter your code and give it a
whirl. Thanks very much for your help.


Doug

 

[John]

I am laying out a project plan and am trying to determine a few
things. First, I would like to get a roll up of the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmed to add
both resource and fixed costs, trying to separate the two costs is
really problematic.

When I use the fixed cost column, I can never get the fixed costs to
roll up to get a summary, regardless of level. They are there in the
task row, but just won't roll up to higher level tasks or the top
summary line. Don't understand what going on here.

The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totals up the numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixed cost
tables. Are there any easier ways of doing this?

Any help greatly appreciated.

Doug

Doug,
OK, let's address your issues one at a time.

In Project the Cost field is designed to total up all costs, labor and
non-labor. For labor effort, cost = resource rate x resource work. Now
to clarify something. You don't "assign" resource costs, resource costs
are calculated per the formula above. However, if what you really mean
is that you would like a separate cost field (e.g. Cost1) that shows
only labor costs, then you will need either a formula or some VBA to do
that. A formula will work fine if there is only one resource per task.
The Cost1 field could be customized with this formula,
Cost1=[Work] x rate
Unfortunately the rate value will either need to be manually entered
into the formula or it can be copied from the Resource Sheet to another
spare field in the Gantt Chart and then that field used in the above
formula.

If there is more than one resource per task, which is often the case, a
spare field customized with a formula won't work because individual
resource hours are not available in a task view. The only way to get
what you want is through VBA. For reference, the following code will put
the labor hours only into Cost1 for each task and roll up those costs to
all summary lines.

Sub Labor_Cost()
Dim t As Task
Dim a As Assignment
Dim ch As Task
'first gather all labor costs for each task
For Each t In activeproject.Tasks
t.Cost1 = 0
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeWork Then
RRate =
Mid(activeproject.Resources(a.ResourceID).StandardRate, 2, _

Len(activeproject.Resources(a.ResourceID).StandardRate) - 4)
t.Cost1 = t.Cost1 + a.Work / 60 * RRate
End If
Next a
End If
Next t
'then sum up labor cost values to summary lines
FilterApply Name:="summary tasks"
Sort key1:="outline level", Ascending1:=False, outline:=False
SelectTaskColumn
For Each t In ActiveSelection.Tasks
t.Cost1 = 0
For Each ch In t.OutlineChildren
t.Cost1 = t.Cost1 + ch.Cost1
Next ch
Next t
Sort key1:="ID", Ascending1:=True
FilterApply Name:="all tasks"
End Sub

Now let's address your fixed costs. You don't say why you are using the
Fixed Cost field. It is normally used for such things as a fixed price
task performed by a subcontractor or the cost of material used in a task
(e.g. x cans of paint for a house). Fixed cost is anything that does not
change no matter how long the task takes or how many hours of effort are
required. Given that, Project does not sum up fixed costs to summary
lines because fixed cost is a cost that may very well be applied at the
summary line itself (labor cost are not or SHOULD not be assigned to
summary lines). For example, 50 gallons of paint for the interior rooms
in a house. Each performance task of the "Paint house interior" summary
line describes the variable labor for each room.

OK, final item, your per diem. Any resource that is a work type resource
will cause hours to accrue in the Work field. If you don't want your per
diem to generate hours then I suggest you make per diem a material type
resource. In the Material Label field enter "days." (don't forget the
period or Project will flag it). Then in the Standard Rate field enter
$200/day. Now when you assign per diem to your travel tasks, simply
assign the number of days the per diem will be paid. Be advised the per
diem days will NOT track the duration days so you will have to adjust
each accordingly, but you also will not see any hours appearing in the
Work field for per diem.

Whew! Hope all the stuff helps.
John
Project MVP

Thanks for the feedback and the VBA code. I will certainly try all of
this out. Concerning the per diem and use of the Material label -
will look that up - I didn't know it even existed. That should
correct my issue. It's too bad fixed costs are not summarized like
resource costs. When planning out project the summary of labour and
fixed costs are the first lines looked at - how much is this going to
cost? Project, for all of its power, seems to miss this very basic
point.

In the resource sheet, one can enter a $/hr rate, or a $/usage rate.
It seems they are treated the same by MSP. A $/hr rate should
generate some entry into the work column and the $/usage rate should
not, but that is not what happens. Both generate a work entry in the
work column. Doesn't make sense to me.

Next task is to figure out how to enter your code and give it a
whirl. Thanks very much for your help.


Doug

Doug,
You're welcome and thanks for the feedback.

With regard to fixed cost rollup, you can sum up the fixed costs with a
simple formula in a custom field. For example use the following formula
for the Cost2 field (assuming you use my macro for putting labor cost in
the Cost1 field).
Cost2=[Fixed Cost]
Then select the option to roll up costs for task and group summary rows.

When reviewing a project the main question that is asked is "what is
this going to cost"? That includes all costs - labor and non-labor.
Having the costs separated out is perhaps desired by some users but
apparently not the majority or the developers would have provided that
feature.

With regard to the rate versus the usage cost fields. Here's something
that will help you to understand how various Project fields are used.
Add the field of interest as a column in the view. Then hover your mouse
over the column heading. In a few seconds a pop-up will appear that
allows you to go directly to the help topic on that field.

With regard to loading the macro I wrote. I probably should have
included instructions in my initial response.
1. Copy the code I wrote to your clipboard
2. From Project, go to Tools/Macros/Macro
3. Select Global Template for the "Macros In" selection box
4. Type Labor_Cost as the macro name in the upper selection box
5. Hit "Create"
6. When the VBE windows opens, paste the code. You may need to do a
little editing depending on how your newsreader copies the code from the
server. And some lines may get broken. In VBA the symbol combination for
a line continuation is space-undrscore (i.e. " _"). You will see one of
those in the macro I wrote.

To run the macro, open the project file and then go to
Tools/Macros/Macro and select the Labor_Cost macro. Hit "Run".

Hope this helps.
John
Project MVP

 

[DGD]

I am laying out a project plan and am trying to determine a few
things. First, I would like to get a roll up of the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmed to add
both resource and fixed costs, trying to separate the two costs is
really problematic.
When I use the fixed cost column, I can never get the fixed costs to
roll up to get a summary, regardless of level. They are there in the
task row, but just won't roll up to higher level tasks or the top
summary line. Don't understand what going on here.
The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totals up the numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixed cost
tables. Are there any easier ways of doing this?
Any help greatly appreciated.
Doug
Doug,
OK, let's address your issues one at a time.
In Project the Cost field is designed to total up all costs, labor and
non-labor. For labor effort, cost = resource rate x resource work. Now
to clarify something. You don't "assign" resource costs, resource costs
are calculated per the formula above. However, if what you really mean
is that you would like a separate cost field (e.g. Cost1) that shows
only labor costs, then you will need either a formula or some VBA to do
that. A formula will work fine if there is only one resource per task.
The Cost1 field could be customized with this formula,
Cost1=[Work] x rate
Unfortunately the rate value will either need to be manually entered
into the formula or it can be copied from the Resource Sheet to another
spare field in the Gantt Chart and then that field used in the above
formula.
If there is more than one resource per task, which is often the case, a
spare field customized with a formula won't work because individual
resource hours are not available in a task view. The only way to get
what you want is through VBA. For reference, the following code will put
the labor hours only into Cost1 for each task and roll up those costs to
all summary lines.
Sub Labor_Cost()
Dim t As Task
Dim a As Assignment
Dim ch As Task
'first gather all labor costs for each task
For Each t In activeproject.Tasks
t.Cost1 = 0
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeWork Then
RRate =
Mid(activeproject.Resources(a.ResourceID).StandardRate, 2, _
Len(activeproject.Resources(a.ResourceID).StandardRate) - 4)
t.Cost1 = t.Cost1 + a.Work / 60 * RRate
End If
Next a
End If
Next t
'then sum up labor cost values to summary lines
FilterApply Name:="summary tasks"
Sort key1:="outline level", Ascending1:=False, outline:=False
SelectTaskColumn
For Each t In ActiveSelection.Tasks
t.Cost1 = 0
For Each ch In t.OutlineChildren
t.Cost1 = t.Cost1 + ch.Cost1
Next ch
Next t
Sort key1:="ID", Ascending1:=True
FilterApply Name:="all tasks"
End Sub
Now let's address your fixed costs. You don't say why you are using the
Fixed Cost field. It is normally used for such things as a fixed price
task performed by a subcontractor or the cost of material used in a task
(e.g. x cans of paint for a house). Fixed cost is anything that does not
change no matter how long the task takes or how many hours of effort are
required. Given that, Project does not sum up fixed costs to summary
lines because fixed cost is a cost that may very well be applied at the
summary line itself (labor cost are not or SHOULD not be assigned to
summary lines). For example, 50 gallons of paint for the interior rooms
in a house. Each performance task of the "Paint house interior" summary
line describes the variable labor for each room.
OK, final item, your per diem. Any resource that is a work type resource
will cause hours to accrue in the Work field. If you don't want your per
diem to generate hours then I suggest you make per diem a material type
resource. In the Material Label field enter "days." (don't forget the
period or Project will flag it). Then in the Standard Rate field enter
$200/day. Now when you assign per diem to your travel tasks, simply
assign the number of days the per diem will be paid. Be advised the per
diem days will NOT track the duration days so you will have to adjust
each accordingly, but you also will not see any hours appearing in the
Work field for per diem.
Whew! Hope all the stuff helps.
John
Project MVP

Thanks for the feedback and the VBA code. I will certainly try all of
this out. Concerning the per diem and use of the Material label -
will look that up - I didn't know it even existed. That should
correct my issue. It's too bad fixed costs are not summarized like
resource costs. When planning out project the summary of labour and
fixed costs are the first lines looked at - how much is this going to
cost? Project, for all of its power, seems to miss this very basic
point.

In the resource sheet, one can enter a $/hr rate, or a $/usage rate.
It seems they are treated the same by MSP. A $/hr rate should
generate some entry into the work column and the $/usage rate should
not, but that is not what happens. Both generate a work entry in the
work column. Doesn't make sense to me.

Next task is to figure out how to enter your code and give it a
whirl. Thanks very much for your help.

Doug

Doug,
You're welcome and thanks for the feedback.

With regard to fixed cost rollup, you can sum up the fixed costs with a
simple formula in a custom field. For example use the following formula
for the Cost2 field (assuming you use my macro for putting labor cost in
the Cost1 field).
Cost2=[Fixed Cost]
Then select the option to roll up costs for task and group summary rows.

When reviewing a project the main question that is asked is "what is
this going to cost"? That includes all costs - labor and non-labor.
Having the costs separated out is perhaps desired by some users but
apparently not the majority or the developers would have provided that
feature.

With regard to the rate versus the usage cost fields. Here's something
that will help you to understand how various Project fields are used.
Add the field of interest as a column in the view. Then hover your mouse
over the column heading. In a few seconds a pop-up will appear that
allows you to go directly to the help topic on that field.

With regard to loading the macro I wrote. I probably should have
included instructions in my initial response.
1. Copy the code I wrote to your clipboard
2. From Project, go to Tools/Macros/Macro
3. Select Global Template for the "Macros In" selection box
4. Type Labor_Cost as the macro name in the upper selection box
5. Hit "Create"
6. When the VBE windows opens, paste the code. You may need to do a
little editing depending on how your newsreader copies the code from the
server. And some lines may get broken. In VBA the symbol combination for
a line continuation is space-undrscore (i.e. " _"). You will see one of
those in the macro I wrote.

To run the macro, open the project file and then go to
Tools/Macros/Macro and select the Labor_Cost macro. Hit "Run".

Hope this helps.
John
Project MVP

John,

Thanks again for your help. Got it all into MSP with no problem and
found the broken line. However, when I run the macro I get error 1101
"argument value is not valid" and the debug screen highlights the line
"t.Cost1=0" I tried changing it to a different optional cost field
(cost4) and the same thing happened. The Cost1 column has zeros in
every task and summary line, so I'm not sure where to go from here.

I would like to try to modify your macro downstream to look at the
material resources and roll those up to a separate optional cost field
as opposed to using the Fixed Cost solution you indicated above and
then add the labour, material, and fixed costs together in another
optional field. i think that will work as well. I think I would only
have to modify your labor-cost macro to read "If a.ResourceType=
pjResourceTypeMaterial Then" and call it material_cost.

We are a services-based company and we use different overhead numbers
for labour and expenses/material, so it would be nice to be able to
manipulate each of these costs separately and then total them. I
think your macros will permit me to do that in the end, but it would
be nice if MSP let the user define what total means as opposed to pre-
defining it. I can see why they do it the way they do, but to me, it
overconstrains the process.

Thanks again.

Doug

 

[John]

I am laying out a project plan and am trying to determine a few
things. First, I would like to get a roll up of the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmed to add
both resource and fixed costs, trying to separate the two costs is
really problematic.

When I use the fixed cost column, I can never get the fixed costs to
roll up to get a summary, regardless of level. They are there in the
task row, but just won't roll up to higher level tasks or the top
summary line. Don't understand what going on here.

The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totals up the numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixed cost
tables. Are there any easier ways of doing this?

Any help greatly appreciated.

Doug,
OK, let's address your issues one at a time.

In Project the Cost field is designed to total up all costs, labor and
non-labor. For labor effort, cost = resource rate x resource work. Now
to clarify something. You don't "assign" resource costs, resource costs
are calculated per the formula above. However, if what you really mean
is that you would like a separate cost field (e.g. Cost1) that shows
only labor costs, then you will need either a formula or some VBA to do
that. A formula will work fine if there is only one resource per task.
The Cost1 field could be customized with this formula,
Cost1=[Work] x rate
Unfortunately the rate value will either need to be manually entered
into the formula or it can be copied from the Resource Sheet to another
spare field in the Gantt Chart and then that field used in the above
formula.

If there is more than one resource per task, which is often the case, a
spare field customized with a formula won't work because individual
resource hours are not available in a task view. The only way to get
what you want is through VBA. For reference, the following code will put
the labor hours only into Cost1 for each task and roll up those costs to
all summary lines.

Sub Labor_Cost()
Dim t As Task
Dim a As Assignment
Dim ch As Task
'first gather all labor costs for each task
For Each t In activeproject.Tasks
t.Cost1 = 0
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeWork Then
RRate =
Mid(activeproject.Resources(a.ResourceID).StandardRate, 2, _

Len(activeproject.Resources(a.ResourceID).StandardRate) - 4)
t.Cost1 = t.Cost1 + a.Work / 60 * RRate
End If
Next a
End If
Next t
'then sum up labor cost values to summary lines
FilterApply Name:="summary tasks"
Sort key1:="outline level", Ascending1:=False, outline:=False
SelectTaskColumn
For Each t In ActiveSelection.Tasks
t.Cost1 = 0
For Each ch In t.OutlineChildren
t.Cost1 = t.Cost1 + ch.Cost1
Next ch
Next t
Sort key1:="ID", Ascending1:=True
FilterApply Name:="all tasks"
End Sub

Now let's address your fixed costs. You don't say why you are using the
Fixed Cost field. It is normally used for such things as a fixed price
task performed by a subcontractor or the cost of material used in a task
(e.g. x cans of paint for a house). Fixed cost is anything that does not
change no matter how long the task takes or how many hours of effort are
required. Given that, Project does not sum up fixed costs to summary
lines because fixed cost is a cost that may very well be applied at the
summary line itself (labor cost are not or SHOULD not be assigned to
summary lines). For example, 50 gallons of paint for the interior rooms
in a house. Each performance task of the "Paint house interior" summary
line describes the variable labor for each room.

OK, final item, your per diem. Any resource that is a work type resource
will cause hours to accrue in the Work field. If you don't want your per
diem to generate hours then I suggest you make per diem a material type
resource. In the Material Label field enter "days." (don't forget the
period or Project will flag it). Then in the Standard Rate field enter
$200/day. Now when you assign per diem to your travel tasks, simply
assign the number of days the per diem will be paid. Be advised the per
diem days will NOT track the duration days so you will have to adjust
each accordingly, but you also will not see any hours appearing in the
Work field for per diem.

Whew! Hope all the stuff helps.
John
Project MVP

Thanks for the feedback and the VBA code. I will certainly try all of
this out. Concerning the per diem and use of the Material label -
will look that up - I didn't know it even existed. That should
correct my issue. It's too bad fixed costs are not summarized like
resource costs. When planning out project the summary of labour and
fixed costs are the first lines looked at - how much is this going to
cost? Project, for all of its power, seems to miss this very basic
point.

In the resource sheet, one can enter a $/hr rate, or a $/usage rate.
It seems they are treated the same by MSP. A $/hr rate should
generate some entry into the work column and the $/usage rate should
not, but that is not what happens. Both generate a work entry in the
work column. Doesn't make sense to me.

Next task is to figure out how to enter your code and give it a
whirl. Thanks very much for your help.

Doug

Doug,
You're welcome and thanks for the feedback.

With regard to fixed cost rollup, you can sum up the fixed costs with a
simple formula in a custom field. For example use the following formula
for the Cost2 field (assuming you use my macro for putting labor cost in
the Cost1 field).
Cost2=[Fixed Cost]
Then select the option to roll up costs for task and group summary rows.

When reviewing a project the main question that is asked is "what is
this going to cost"? That includes all costs - labor and non-labor.
Having the costs separated out is perhaps desired by some users but
apparently not the majority or the developers would have provided that
feature.

With regard to the rate versus the usage cost fields. Here's something
that will help you to understand how various Project fields are used.
Add the field of interest as a column in the view. Then hover your mouse
over the column heading. In a few seconds a pop-up will appear that
allows you to go directly to the help topic on that field.

With regard to loading the macro I wrote. I probably should have
included instructions in my initial response.
1. Copy the code I wrote to your clipboard
2. From Project, go to Tools/Macros/Macro
3. Select Global Template for the "Macros In" selection box
4. Type Labor_Cost as the macro name in the upper selection box
5. Hit "Create"
6. When the VBE windows opens, paste the code. You may need to do a
little editing depending on how your newsreader copies the code from the
server. And some lines may get broken. In VBA the symbol combination for
a line continuation is space-undrscore (i.e. " _"). You will see one of
those in the macro I wrote.

To run the macro, open the project file and then go to
Tools/Macros/Macro and select the Labor_Cost macro. Hit "Run".

Hope this helps.
John
Project MVP

John,

Thanks again for your help. Got it all into MSP with no problem and
found the broken line. However, when I run the macro I get error 1101
"argument value is not valid" and the debug screen highlights the line
"t.Cost1=0" I tried changing it to a different optional cost field
(cost4) and the same thing happened. The Cost1 column has zeros in
every task and summary line, so I'm not sure where to go from here.

I would like to try to modify your macro downstream to look at the
material resources and roll those up to a separate optional cost field
as opposed to using the Fixed Cost solution you indicated above and
then add the labour, material, and fixed costs together in another
optional field. i think that will work as well. I think I would only
have to modify your labor-cost macro to read "If a.ResourceType=
pjResourceTypeMaterial Then" and call it material_cost.

We are a services-based company and we use different overhead numbers
for labour and expenses/material, so it would be nice to be able to
manipulate each of these costs separately and then total them. I
think your macros will permit me to do that in the end, but it would
be nice if MSP let the user define what total means as opposed to pre-
defining it. I can see why they do it the way they do, but to me, it
overconstrains the process.

Thanks again.

Doug

Doug,
My guess is that you have some blank lines in your file. I should have
put the t.Cost1=0 after the "If Not t is Nothing" statement. See if that
helps. If that does NOT help, can you send me your file so I can take a
look at it? My e-mail is below.

As far as modifying the macro for material cost, you are correct, first
change all the t.Cost1 properties to t.Cost2 or whatever, and then
change the pjResourceTypeWork to pjResourceTypeMaterial.

Rolling up fixed costs requires a little different approach since fixed
cost is a task property, not a resource property. However, you also
mention wanting to create a separate field to roll up all costs and that
is totally unnecessary since that is exactly what the built-in Cost
field already does.

With regard to different overhead rates for different resources. Project
has a total of 5 separate cost structures for any single resource. It is
in the form of cost rate tables "A" through "E". Cost rate table "A" is
the default that you see when you view the Std Rate on the Resource
Sheet. To access each cost rate table, select any resource on the
Resource Sheet and then hit the Costs tab. Individual cost rate tables
can be applied to each individual assignment either on the Resource
Usage or Task Usage views. Simply add the Cost Rate Table field as a
column in either view and then select the desired rate table for each
assignment. Note: If you do use different cost rate tables, the macro I
wrote will have to be further modified to pull the correct rate table
information in order to sum up labor and material cost.

The bottom line is that Project does already have a lot of flexibility
when dealing with cost data. It's just a matter of structuring the plan
appropriately.

John
Project MVP
jensenj6atatcomcastdotdotnet
(remove obvious redundancies)

 

[Billy]

Referring to the VBA code you offered, it looks like the results (i.e.
updated data) will be found in the COST1 field. If this is the case,
then I should see totals of all labor costs in the summary fields? I
looked through the thread above, but didn't necessarily see your
explanation for what you "should" see when you run the code. If I
missed it, I apologize.

I am laying out a project plan and am trying to determine a few
things. First, I would like to get arollupof the labour costs
associated with a project. I have added an additional column and
assigned the "Cost" field to it. This is the only way to list the
costs of the project that I can find. The problem that I have is that
the "cost" field appears to be pre-programmed to to calculate total
costs, meaning the sum of resources and fixed costs allocated to a
task. I want to be able to assign resource costs to a user
configurable field, like "cost1, cost2, etc" but the resource costs
never appear when I try this. They seem to only be programmed to
appear in the "cost" field, and given that it is pre-programmedto add
both resource and fixed costs, trying to separate the two costsis
really problematic.
When I use the fixed cost column, I can never get the fixed costs to
rollupto get a summary, regardless of level. They are there in the
task row, but just won'trollupto higher level tasks or the top
summary line. Don't understand what going on here.
The third problem I am having is trying to allocate daily expenses.
For instance, on a trip there are $200/day per diem I want to assign
for the duration of the trip. If the trip was 6 days long, I would
allocate 600% of the per diem rate to generate a cost of $1,200
dollars. The resource called "per diem" has a $200/use identified in
the resource sheet. It totalsupthe numbers nicely, but for some
reason, it also allocates 6 days of work in the "work" column, skewing
my level of effort numbers. I can treat them as fixed costs, but it
is a pain to go in and get to the tables to enter fixed costs. If I
change the length of the trip, I have to go to 2 different places to
update the project plan - the resource allocation and the fixedcost
tables. Are there any easier ways of doing this?
Any help greatly appreciated.
Doug
Doug,
OK, let's address your issues one at a time.
In Project the Cost field is designed to totalupall costs, labor and
non-labor. For labor effort, cost = resource rate x resource work. Now
to clarify something. You don't "assign" resource costs, resourcecosts
are calculated per the formula above. However, if what you reallymean
is that you would like a separate cost field (e.g. Cost1) that shows
only labor costs, then you will need either a formula or someVBAto do
that. A formula will work fine if there is only one resource per task.
The Cost1 field could be customized with this formula,
Cost1=[Work] x rate
Unfortunately the rate value will either need to be manually entered
into the formula or it can be copied from the Resource Sheet to another
spare field in the Gantt Chart and then that field used in the above
formula.
If there is more than one resource per task, which is often the case, a
spare field customized with a formula won't work because individual
resource hours are not available in a task view. The only way to get
what you want is throughVBA. For reference, the following code will put
the labor hours only into Cost1 for each task androllupthose costs to
all summary lines.
Sub Labor_Cost()
Dim t As Task
Dim a As Assignment
Dim ch As Task
'first gather all labor costs for each task
For Each t In activeproject.Tasks
t.Cost1 = 0
If Not t Is Nothing Then
For Each a In t.Assignments
If a.ResourceType = pjResourceTypeWork Then
RRate =
Mid(activeproject.Resources(a.ResourceID).StandardRate, 2, _
Len(activeproject.Resources(a.ResourceID).StandardRate) - 4)
t.Cost1 = t.Cost1 + a.Work / 60 * RRate
End If
Next a
End If
Next t
'then sumuplabor cost values to summary lines
FilterApply Name:="summary tasks"
Sort key1:="outline level", Ascending1:=False, outline:=False
SelectTaskColumn
For Each t In ActiveSelection.Tasks
t.Cost1 = 0
For Each ch In t.OutlineChildren
t.Cost1 = t.Cost1 + ch.Cost1
Next ch
Next t
Sort key1:="ID", Ascending1:=True
FilterApply Name:="all tasks"
End Sub
Now let's address your fixed costs. You don't say why you are using the
Fixed Cost field. It is normally used for such things as a fixed price
task performed by a subcontractor or the cost of material used ina task
(e.g. x cans of paint for a house). Fixed cost is anything that does not
change no matter how long the task takes or how many hours of effort are
required. Given that, Project does not sumupfixed costs to summary
lines because fixed cost is a cost that may very well be applied at the
summary line itself (labor cost are not or SHOULD not be assignedto
summary lines). For example, 50 gallons of paint for the interiorrooms
in a house. Each performance task of the "Paint house interior" summary
line describes the variable labor for each room.
OK, final item, your per diem. Any resource that is a work type resource
will cause hours to accrue in the Work field. If you don't want your per
diem to generate hours then I suggest you make per diem a material type
resource. In the Material Label field enter "days." (don't forgetthe
period or Project will flag it). Then in the Standard Rate field enter
$200/day. Now when you assign per diem to your travel tasks, simply
assign the number of days the per diem will be paid. Be advised the per
diem days will NOT track the duration days so you will have to adjust
each accordingly, but you also will not see any hours appearing in the
Work field for per diem.
Whew! Hope all the stuff helps.
John
Project MVP
Thanks for the feedback and theVBAcode. I will certainly try all of
this out. Concerning the per diem and use of the Material label -
will look thatup- I didn't know it even existed. That should
correct my issue. It's too bad fixed costs are not summarized like
resource costs. When planning out project the summary of labour and
fixed costs are the first lines looked at - how much is this going to
cost? Project, for all of its power, seems to miss this very basic
point.
In the resource sheet, one can enter a $/hr rate, or a $/usage rate.
It seems they are treated the same by MSP. A $/hr rate should
generate some entry into the work column and the $/usage rate should
not, but that is not what happens. Both generate a work entry in the
work column. Doesn't make sense to me.
Next task is to figure out how to enter your code and give it a
whirl. Thanks very much for your help.
Doug
Doug,
You're welcome and thanks for the feedback.
With regard to fixed cost rollup, you can sumupthe fixed costs with a
simple formula in a custom field. For example use the following formula
for the Cost2 field (assuming you use my macro for putting labor costin
the Cost1 field).
Cost2=[Fixed Cost]
Then select the option torollupcosts for task and group summary rows.
When reviewing a project the main question that is asked is "what is
this going to cost"? That includes all costs - labor and non-labor.
Having the costs separated out is perhaps desired by some users but
apparently not the majority or the developers would have provided that
feature.
With regard to the rate versus the usage cost fields. Here's something
that will help you to understand how various Project fields are used.
Add the field of interest as a column in the view. Then hover your mouse
over the column heading. In a few seconds a pop-upwill appear that
allows you to go directly to the help topic on that field.
With regard to loading the macro I wrote. I probably should have
included instructions in my initial response.
1. Copy the code I wrote to your clipboard
2. From Project, go to Tools/Macros/Macro
3. Select Global Template for the "Macros In" selection box
4. Type Labor_Cost as the macro name in the upper selection box
5. Hit "Create"
6. When the VBE windows opens, paste the code. You may need to do a
little editing depending on how your newsreader copies the code from the
server. And some lines may get broken. InVBAthe symbol combination for
a line continuation is space-undrscore (i.e. " _"). You will see one of
those in the macro I wrote.
To run the macro, open the project file and then go to
Tools/Macros/Macro and select the Labor_Cost macro. Hit "Run".
Hope this helps.
John
Project MVP

John,

Thanks again for your help. Got it

...

read more »- Hide quoted text -

- Show quoted text -