TOTAL COST COLUMN (IN TASK USAGE VIEW)

[Adele]

This column is calculated by multiplying "work" column
by "standard rate" column (from resource sheet). I want
to customise a cost column in the Task Usage view by
multiplying "work" by "overtime rate" and it does not
allow me to because "overtime rate" is a resource field
from a resource table and not a task field in a task
table. How can I do this as it is being done in the
default column "total cost"????

 

[John]

Adele,
The first question is, why? Why not simply change the base rate to be
the same as the overtime rate and you're done, the Cost field will then
give the value you want.

However, what you want to do raises a couple of issues. A custom field
can be created with the necessary formula so the value you want is
available on Task views. Although this is relatively straightforward if
each task has only one resource, things get a little more complicated
when more than one resource is assigned to a task. If indeed your file
has just one resource assigned to each task, take a look at FAQ 37 -
Custom Fields in Tables on the MVP website at:
http://www.mvps.org/project/faqs

If more than one resource is assigned to one or more tasks, my
suggestion is to use a little more advanced form of the short macro
detailed in the FAQ. Namely, it would be relatively easy to develop a
macro that took the Overtime Rate for each resource and apply it as
necessary to each resource assigned to each task. The value can be put
into a spare field called "Total Cost", or whatever.

Hope this helps.
John

 

[Steve House [MVP]]

Overtime will be included in the total cost if you schedule overtime work
for the assigned resource. Split the screen and view the Resource Work
table in the bottom window. You'll see there is a column for o/t work
assigned. If you enter something there, Project's calculations are:

Remember Duration only counts Working Time, that is, work done during the
working hours as they're defined by the controlling calendar. O/T work is
defined as work performed at hours other than those included in working
hours according to the controlling calendar, usually the resource calendar.
O/T Work does not count towards task duration. So someone who works an 8
hour day who puts in 10 hours on a task in one day should be shown as doing
10 hours Work and 2 hours O/T Work, regardless of whether he gets paid an
O/T premium or not.

Duration = ([Work]-[O/T Work]) * Effort
Total Cost = ([Work]-[O/T Work])*[Std Rate] + [O/T Work]*[O/T Rate] + [Cost
per Use] + [FixedCost]

Does this do what you need?

 

[Guest]

Hi John

Thanks for your answer. Not sure if it will help
though. Let me explain rather what we are trying to do
here. (Perhaps I shouldn't have asked about Overtime
Rate, because I don't think this field is going to help
me anyway)

In the task view the total cost column is giving me
answers for the standard rate x by hours worked in Rands
(we have worked out a cost rate and a charge out rate for
each resource and from that we want to work out %
profitability).

- Task usage view only shows total cost column (our
standard rate which is our cost rate x by number of hours
worked)
- I want to add in another column which will show me
total revenue (our charge out rate x by number of hours
worked) and then;
- I want to add in another column which will show me
Revenue less cost and then;
- I want to add in another column which will show me %
profit.

I can do all this using custom fields in the resource
sheet, but I want to see the cost at a task level not at
a resource level.

In the past we have been copying from Task usage sheet
into excel template which has all the formulas in it, but
it seems silly to do it that way when MS Project carrys
these fields and fields can be customised.

I'll go back and try your suggestions though. Please
investigate further for me.

Kind regards

Adele

 

[John]

Adele,
You mention the Task Usage view but you don't talk about whether you
want the new fields to be timescaled. If you don't care about the
timescale values my previous suggestions of implementing a relatively
simple macro to transfer the custom field values from a Resource view
(i.e. Resource Sheet) to a Task view (i.e. Task Usage). I don't recall
if formulas can be transferred (I suspect not) but all you really need
transferred is the custom rate values. The formulas can either be
replicated in the custom Task fields once the data is transferred or,
they can easily and more efficiently be included in the VBA code.

If you are interested in timescale values then you have no choice but to
write a custom VBA macro to export the data to Excel. Custom fields are
static, that is, Project cannot timescale them since it has no way of
determining how to make the allocation over time. But, an algorithm can
be developed to effectively timescale custom field values in any way
necessary. I have done it several times.

Hope this helps.

John
Project MVP

 

[Steve House [MVP]]

You can do it in the Gantt chart views but not in the usage views as best I
can figure. Take the task field Cost 1 and use it to hold the charge out
rate. Take task Cost 2 and set it a formula [Cost2] = [Cost 1]*[Work] / 60
(work is stored in minutes). That will give you the total revenue per task
and from there you can create your other fields. Usage views give values
distributed over time which doesn't seem appropriate to me for this sort of
calculation anyway - would waxing the widgets have a 15% profit margin on
Monday but a 22% profit margin on Tuesday? Doubtful - OTOH, waxing widgets
could have a 15% profitablility while polishing fids could have a 20%
margin. Surely you don't bill your client based on the specific resource
you assign to the task? If I'm paying to have some widgets waxed, all I
care about is that they're done on time and according to spec. I'm not
going to be a happy camper if you adjust my cost based on the specific
widget waxer you happen to have available that day.

I have to wonder what the benefit is of this sort of analysis though.
Whether tasks are done or not is usually not dependent on their
profitability. A task is something required to build the deliverable -
including it or not including it is rarely an option. If you don't do it,
the deliverable doesn't get built and your project's reason to exist
evaporates. Analysis the overall profitablity of the project, revenue
generated from creating the final product for the client versus your
internal total cost of doing the project plus overheads makes a lot of
sense. But to say that Task A has margin X and Task B has margin Y, or for
that matter, Resource A's time has margin X while Resource B's has margin Y
seems to be generating a lot of numbers that have little or no meaning in
actually managing the firm, the project, or the resources. Just one
iconoclasts view, I suppose.

Actually, I confess to having a vested interest that makes me a bit
sensitive to the issue of tracking profitability by resource these days. I
work on contract. One of my employers charges their clients the same rate
regardless of who they send to the job, as they should. They have some
junior staff who have a lower hourly rate than the one they been paying me
who are now being preferentially booked to assignments because it raises
their profit margin. Makes me furious and is something I consider highly
unethical.

 

[Adele]

Hi Steve

Thanks for your comments. Our company is a "consulting"
company. We don't have physical items that we sell and
therefore our entire pricing model is based on resource
and intellectual property. That is why we work out our
profit on how much we charge the client vs. how much it
costs us to do the analytical work.

I have done what you said below before I posted the
question and when I wanted it in the task usage view that
was when I realised it didn't work. Currently we are
doing this in Excel, which is time consuming and that is
why I have decided to go down this route.

Thanks anyway.

Kind regards

Adele

-----Original Message-----
You can do it in the Gantt chart views but not in the usage views as best I
can figure. Take the task field Cost 1 and use it to hold the charge out
rate. Take task Cost 2 and set it a formula [Cost2] = [Cost 1]*[Work] / 60
(work is stored in minutes). That will give you the total revenue per task
and from there you can create your other fields. Usage views give values
distributed over time which doesn't seem appropriate to me for this sort of
calculation anyway - would waxing the widgets have a 15% profit margin on
Monday but a 22% profit margin on Tuesday? Doubtful - OTOH, waxing widgets
could have a 15% profitablility while polishing fids could have a 20%
margin. Surely you don't bill your client based on the specific resource
you assign to the task? If I'm paying to have some widgets waxed, all I
care about is that they're done on time and according to spec. I'm not
going to be a happy camper if you adjust my cost based on the specific
widget waxer you happen to have available that day.

I have to wonder what the benefit is of this sort of analysis though.
Whether tasks are done or not is usually not dependent on their
profitability. A task is something required to build the deliverable -
including it or not including it is rarely an option. If you don't do it,
the deliverable doesn't get built and your project's reason to exist
evaporates. Analysis the overall profitablity of the project, revenue
generated from creating the final product for the client versus your
internal total cost of doing the project plus overheads makes a lot of
sense. But to say that Task A has margin X and Task B has margin Y, or for
that matter, Resource A's time has margin X while Resource B's has margin Y
seems to be generating a lot of numbers that have little or no meaning in
actually managing the firm, the project, or the resources. Just one
iconoclasts view, I suppose.

Actually, I confess to having a vested interest that makes me a bit
sensitive to the issue of tracking profitability by resource these days. I
work on contract. One of my employers charges their clients the same rate
regardless of who they send to the job, as they should. They have some
junior staff who have a lower hourly rate than the one they been paying me
who are now being preferentially booked to assignments because it raises
their profit margin. Makes me furious and is something I consider highly
unethical.

--
Steve House [MVP]
MS Project Trainer/Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Hi John

Thanks for your answer. Not sure if it will help
though. Let me explain rather what we are trying to do
here. (Perhaps I shouldn't have asked about Overtime
Rate, because I don't think this field is going to help
me anyway)

In the task view the total cost column is giving me
answers for the standard rate x by hours worked in Rands
(we have worked out a cost rate and a charge out rate for
each resource and from that we want to work out %
profitability).

- Task usage view only shows total cost column (our
standard rate which is our cost rate x by number of hours
worked)
- I want to add in another column which will show me
total revenue (our charge out rate x by number of hours
worked) and then;
- I want to add in another column which will show me
Revenue less cost and then;
- I want to add in another column which will show me %
profit.

I can do all this using custom fields in the resource
sheet, but I want to see the cost at a task level not at
a resource level.

In the past we have been copying from Task usage sheet
into excel template which has all the formulas in it, but
it seems silly to do it that way when MS Project carrys
these fields and fields can be customised.

I'll go back and try your suggestions though. Please
investigate further for me.

Kind regards

Adele

.

 

[Adele Snoek]

Dear John

Thanks for the piece of code for the macro. I played around with it but
because I am not a VB programmer I no idea how to change and enhance your
code, so I got one of our developers to help me.

He has managed to get all the columns and calculations in the task usage
view that I want to see. Now my next question is:

In the task usage view the total cost column (MS Project default column)
adds up the cost of all the resources assigned to that task and puts it in
the task row. Then for all the roll ups it adds the totals for the different
levels, so if I were to roll up all the tasks and just have the main project
level displaying it shows the grand total. Now the customised columns that I
have put in - how do we write code to add the roll ups on the different
levels. My developer has managed to add up the resources attached to the
task, but from there on he doesn't know how to add the rest. Could you get
back to me on this one, I would really appreciate it as I am so close to
answering all my questions using MS Project instead of taking it to Excel and
doing the calculations there.

Thanks once again for your help.

Kind regards

Adele Snoek
Project Manager
Knowledge Factory
082 330 1882

 

[Steve House [MVP]]

You may have misunderstood - What I was saying is that a project is a group
of tasks that work together to produce a cohesive whole. The model is valid
regardless of whether one is talking about building physical items or
creating intellectual property.

You may be thinking of "tasks" in terms of larger blocks of activity than I
am - I'm thinking of the smallest possible incremental blocks of work done
by one skill set usually requiring between 8 and 80 man-hours and resulting
in the creation of the smallest identifiable deliverable in the project, the
project itself being the aggregate of dozens or even hundreds of such
deliverables. In a consulting business, that would be each individual
meeting, each individual interview, each specific report or report section
written, each file analysed, each presentation created, each presentation
delivered (preparation and delivery being separate tasks since their
deliverables are different) and each and every one of the individual detail
tasks that go into those deliverables. A single client presentation might
have dozens of individual items of research, graphic creations, powerpoint
slide show created, slide show proofread, etc , and they are all separate
tasks done by separate resources. Does it makes sense to be concerned
whether we have a 10% profit margin on creating the Powerpoint slide show, a
15% profit margin on an editor proofreading it, and a 20% profit margin on
presenting it at the meeting where it's delivered to the audience or should
we be concerned with the overall? Literally any more granularity and you'd
almost be tracking each individual bathroom visit.

If I'm hired to create an accounts payable system, the profitability of that
project is a function of the system as a whole and not each specifc task
within it. It just doesn't make sense to me to say that writing the user
menu would have X profit margin, writing the numeric to verbal dollar amount
conversion subroutine would have Y profit margin, or that creating the
checque printing routine is more profitable than creating the month-end
summary report or the bank deposit register - after all, it's not like you
can drop the lower profit margin modules from the system to improve your
overall margins. While it might make an impressive presentation to break
out all the costs and revenues for each individual task that each individual
worker does, in terms of the overall management of the project it doesn't
seem especially useful to come up with a separate profit margin for each
report and presentation within it.

Just one opinion, your mileage may vary <grin>


--
Steve House [MVP]
MS Project Trainer/Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Hi Steve

Thanks for your comments. Our company is a "consulting"
company. We don't have physical items that we sell and
therefore our entire pricing model is based on resource
and intellectual property. That is why we work out our
profit on how much we charge the client vs. how much it
costs us to do the analytical work.

I have done what you said below before I posted the
question and when I wanted it in the task usage view that
was when I realised it didn't work. Currently we are
doing this in Excel, which is time consuming and that is
why I have decided to go down this route.

Thanks anyway.

Kind regards

Adele

-----Original Message-----
You can do it in the Gantt chart views but not in the usage views as best I
can figure. Take the task field Cost 1 and use it to hold the charge out
rate. Take task Cost 2 and set it a formula [Cost2] = [Cost 1]*[Work] / 60
(work is stored in minutes). That will give you the total revenue per task
and from there you can create your other fields. Usage views give values
distributed over time which doesn't seem appropriate to me for this sort of
calculation anyway - would waxing the widgets have a 15% profit margin on
Monday but a 22% profit margin on Tuesday? Doubtful - OTOH, waxing widgets
could have a 15% profitablility while polishing fids could have a 20%
margin. Surely you don't bill your client based on the specific resource
you assign to the task? If I'm paying to have some widgets waxed, all I
care about is that they're done on time and according to spec. I'm not
going to be a happy camper if you adjust my cost based on the specific
widget waxer you happen to have available that day.

I have to wonder what the benefit is of this sort of analysis though.
Whether tasks are done or not is usually not dependent on their
profitability. A task is something required to build the deliverable -
including it or not including it is rarely an option. If you don't do it,
the deliverable doesn't get built and your project's reason to exist
evaporates. Analysis the overall profitablity of the project, revenue
generated from creating the final product for the client versus your
internal total cost of doing the project plus overheads makes a lot of
sense. But to say that Task A has margin X and Task B has margin Y, or for
that matter, Resource A's time has margin X while Resource B's has margin Y
seems to be generating a lot of numbers that have little or no meaning in
actually managing the firm, the project, or the resources. Just one
iconoclasts view, I suppose.

Actually, I confess to having a vested interest that makes me a bit
sensitive to the issue of tracking profitability by resource these days. I
work on contract. One of my employers charges their clients the same rate
regardless of who they send to the job, as they should. They have some
junior staff who have a lower hourly rate than the one they been paying me
who are now being preferentially booked to assignments because it raises
their profit margin. Makes me furious and is something I consider highly
unethical.

--
Steve House [MVP]
MS Project Trainer/Consultant
Visit http://www.mvps.org/project/faqs.htm for the FAQs

Hi John

Thanks for your answer. Not sure if it will help
though. Let me explain rather what we are trying to do
here. (Perhaps I shouldn't have asked about Overtime
Rate, because I don't think this field is going to help
me anyway)

In the task view the total cost column is giving me
answers for the standard rate x by hours worked in Rands
(we have worked out a cost rate and a charge out rate for
each resource and from that we want to work out %
profitability).

- Task usage view only shows total cost column (our
standard rate which is our cost rate x by number of hours
worked)
- I want to add in another column which will show me
total revenue (our charge out rate x by number of hours
worked) and then;
- I want to add in another column which will show me
Revenue less cost and then;
- I want to add in another column which will show me %
profit.

I can do all this using custom fields in the resource
sheet, but I want to see the cost at a task level not at
a resource level.

In the past we have been copying from Task usage sheet
into excel template which has all the formulas in it, but
it seems silly to do it that way when MS Project carrys
these fields and fields can be customised.

I'll go back and try your suggestions though. Please
investigate further for me.

Kind regards

Adele
-----Original Message-----

Adele,
The first question is, why? Why not simply change the
base rate to be
the same as the overtime rate and you're done, the Cost
field will then
give the value you want.

However, what you want to do raises a couple of issues.
A custom field
can be created with the necessary formula so the value
you want is
available on Task views. Although this is relatively
straightforward if
each task has only one resource, things get a little
more complicated
when more than one resource is assigned to a task. If
indeed your file
has just one resource assigned to each task, take a look
at FAQ 37 -
Custom Fields in Tables on the MVP website at:
http://www.mvps.org/project/faqs

If more than one resource is assigned to one or more
tasks, my
suggestion is to use a little more advanced form of the
short macro
detailed in the FAQ. Namely, it would be relatively easy
to develop a
macro that took the Overtime Rate for each resource and
apply it as
necessary to each resource assigned to each task. The
value can be put
into a spare field called "Total Cost", or whatever.

Hope this helps.
John
.

.

 

[John]

Adele,
Is there a reason why you can simply use the rollup option for the
summary rows of the custom field? Go to Tools/Customize/Fields. In the
lower half of the Customize Fields window, just check the option to
either rollup the Sum or Use Formula.

If the answer is not a simple as checking the option, perhaps I need to
see your file (or at least a representative sample) to know exactly what
you have. Then I can write a macro for you to get what you need. If you
can send me the file, zip it and send it via e-mail. If the file has
sensitive information I can send you a macro that will desensitize it or
you can go to Jack Dahlgren's website at:
http://masamiki.com/project/macros.htm
Jack's "Scrub" macro does essentially the same thing as my desensitize
macro.

Also if you send me the file or a sample, explain exactly what you want.

John
Project MVP