Capturing Revenue

[seven_percent]

I have searched the group for some info on how to capture revenue in
Project.

There are several others who seem to think like I do: I am doing work
for a client and have a team of consultants, etc. If I can track the
cost of my people for every single hour they work (my cost), I ought
to be able to also track the revenue they are bringing in for every
single hour they work (client's cost).

However, there are others who say that Project isn't a financial
application. Bollocks. If that's the case, it shouldn't have any
financial fields in it at all.

At any rate, I am thinking about how Project calculates costs for
tasks. It seems to me that the Task Cost field is really a
calculation based upon the Cost field of the Resource(s) assigned to
the task and the work done by each. Thus, to create a custom Revenue
field for Tasks, you also have to have a custom Revenue field for
Resources which uses almost the same exact formula as that for Cost.

I defined a custom cost field for Resources called "Revenue (Cost1)".
Now, I want to define the custom cost field for Tasks called "Revenue
(Cost1)" as a formula based on the "Revenue (Cost1)" field for the
resources assigned to the task(s). However, I am not sure how to do
this.

I got to the point where I clicked the Formula button and the dialogue
popped up, but when I go to insert the custom cost field "Revenue" I
can't tell if it is inserting the custom Cost1 field for Resources or
the current custom Task field I am working on.

The questions are:

"How do I access custom Resource fields in custom formulas that I am
defining for custom Task fields?"
"How can I produce the same behavior, using custom fields, that is
seen in the hard-coded Cost field for Tasks?"

 

[Rod Gill]

Hi,

You're making it too complicated!!

In the Resource Sheet double-click each resource.
Click the Costs tab
Set Rate A to be that charged to the customer
Set rate B to the internal cost. This rate can't be hidden so give each
resource an average rate for their skill/role with an overhead component
added to disguise exactly what each person is paid.

Insert Cost1 and Cost 2 fields. Rename Cost1 as Internal Cost and Cost2 as
Margin
In Cost2 create a formula [Cost]-[Cost1]

By default each Assignment uses rate A.
Now to calculate current margin:
View, Resource usage
Insert Cost Rate Table column
set the first assignment to Rate B
Copy B down the entire column so every assignment uses Rate B
View the Gantt Chart again and copy the Cost column to the Internal Rate
(Cost1) field
Return to the Resource Usage View and reset all assignments to use Rate A

You now see your margin in Cost2

Project isn't a job costing or financial system (it doesn't show cash flows
or rates of return or use of money costs or....) but using this technique
you can show margin.
The process can be recorded in a macro to make it faster.

--

Rod Gill
Microsoft MVP for Project

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

 

[Steve House]

Adding a bit ...

You are close on how Project calculates task costs. It takes the man-hours
of regular work each resource does on a task and multiplies that by the
resource's Standard Rate field, plus the number of man-hours of overtime
work times the Overtime Rate, plus the number of units of materials consumed
times the material cost-per-unit, plus finally any fixed cost, and the
result is the task's cost. These costs are then summed into the summary
tasks.

The problem with using it to track revenue is that revenues are often not
solely and directly dependent on man-hours of labour performed by a
resource. They MIGHT be but there are many other billing arrangments that
can occur. For example, a job might have a fixed-price bid of $XXXX
regardless of the man-hours spent by the resources. But your cost to get
the task done is still directly related to the amount of work the resources
put in on it. Or since you're providing consulting services it wouldn't be
unusual that you might bill the client at a fixed day-rate covering each day
or any portion thereof, yet your internal cost of employing the resource you
send to the client is still dependent on the exact time he spends on the
work. Let's say you have a resource who earns $5000 a month and you bill
him to the client at a rate of $500 per day, one day minimum. He goes to
the client site and does a task lasting 8 hours. Your cost of that one
activity = $250, your revenue = $500. But your send him to a client and the
task takes him 1 hour to complete. Your cost = 250/8 or ~$32 but your
revenue is still $500. You send him to the client and he does 4 tasks of 1
hour each. Your cost = $32 each times 4 = $125 but your revenue is STILL
$500. See the problem? Internal costs are always related to the exact
amount work required on the task but the revenues really don't bear any
consistent relationship to either the task's duration or the man-hours of
work required to complete it.

Here's another issue regarding costing that leads to the assertion that
Project is not a financial program. You have your resource on salary at
$5000 per month. But this is a slow month and he only spends 5 days on
tasks related to projects underway for your clients. The rest of his time
is spent on personal development, paperwork in the office, taking a course
to upgrade his skills that you've sent him on, etc. Your cost for those 5
days of tasks is NOT the full $5000 salary you're paying him. Instead it's
about $1250, only that portion of his salary pro-rated to the 1/4 of his
total time that was actually spent on those tasks. The other $3750 you've
paid him for the month needs to be accounted for in places other than the
project budgets. But those other operating budget categories don't even
exist as far as the client project related universe that MS Project is aware
of is concerned. They're over there in the general operating expenses for
your firm along with rent, office furnishings, phone and utilities, etc,
that are also not a part of the direct project costs MS Project tracks. The
costs as far as MS Project is concerned is only the cost of the the specific
work required to create the deliverables in the project in question and all
of the other costs of doing business don't exist as far as it is concerned.

HTH

 

[seven_percent]

Adding a bit ...

You are close on how Project calculates task costs.  It takes the man-hours
of regular work each resource does on a task and multiplies that by the
resource's Standard Rate field, plus the number of man-hours of overtime
work times the Overtime Rate, plus the number of units of materials consumed
times the material cost-per-unit, plus finally any fixed cost, and the
result is the task's cost.  These costs are then summed into the summary
tasks.

The problem with using it to track revenue is that revenues are often not
solely and directly dependent on man-hours of labour performed by a
resource.  They MIGHT be but there are many other billing arrangments that
can occur.  For example, a job might have a fixed-price bid of $XXXX
regardless of the man-hours spent by the resources.  But your cost to get
the task done is still directly related to the amount of work the resources
put in on it.  Or since you're providing consulting services it wouldn't be
unusual that you might bill the client at a fixed day-rate covering each day
or any portion thereof, yet your internal cost of employing the resource you
send to the client is still dependent on the exact time he spends on the
work.  Let's say you have a resource who earns $5000 a month and you bill
him to the client at a rate of $500 per day, one day minimum.  He goes to
the client site and does a task lasting 8 hours.  Your cost of that one
activity = $250, your revenue = $500.  But your send him to a client and the
task takes him 1 hour to complete.  Your cost = 250/8 or ~$32 but your
revenue is still $500.  You send him to the client and he does 4 tasks of 1
hour each.  Your cost = $32 each times 4 = $125 but your revenue isSTILL
$500.  See the problem?  Internal costs are always related to the exact
amount work required on the task but the revenues really don't bear any
consistent relationship to either the task's duration or the man-hours of
work required to complete it.

Here's another issue regarding costing that leads to the assertion that
Project is not a financial program.  You have your resource on salary at
$5000 per month.  But this is a slow month and he only spends 5 days on
tasks related to projects underway for your clients.  The rest of his time
is spent on personal development, paperwork in the office, taking a course
to upgrade his skills that you've sent him on, etc.  Your cost for those 5
days of tasks is NOT the full $5000 salary you're paying him.  Instead it's
about $1250, only that portion of his salary pro-rated to the 1/4 of his
total time that was actually spent on those tasks.  The other $3750 you've
paid him for the month needs to be accounted for in places other than the
project budgets.  But those other operating budget categories don't even
exist as far as the client project related universe that MS Project is aware
of is concerned.  They're over there in the general operating expenses for
your firm along with rent, office furnishings, phone and utilities, etc,
that are also not a part of the direct project costs MS Project tracks.  The
costs as far as MS Project is concerned is only the cost of the the specific
work required to create the deliverables in the project in question and all
of the other costs of doing business don't exist as far as it is concerned.

HTH

--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visithttp://project.mvps.org/faqs.htmfor the FAQs

I have searched the group for some info on how to capture revenue in
Project.

There are several others who seem to think like I do:  I am doing work
for a client and have a team of consultants, etc.  If I can track the
cost of my people for every single hour they work (my cost), I ought
to be able to also track the revenue they are bringing in for every
single hour they work (client's cost).

However, there are others who say that Project isn't a financial
application.  Bollocks.  If that's the case, it shouldn't have any
financial fields in it at all.

At any rate, I am thinking about how Project calculates costs for
tasks.  It seems to me that the Task Cost field is really a
calculation based upon the Cost field of the Resource(s) assigned to
the task and the work done by each.  Thus, to create a custom Revenue
field for Tasks, you also have to have a custom Revenue field for
Resources which uses almost the same exact formula as that for Cost.

I defined a custom cost field for Resources called "Revenue (Cost1)".
Now, I want to define the custom cost field for Tasks called "Revenue
(Cost1)" as a formula based on the "Revenue (Cost1)" field for the
resources assigned to the task(s).  However, I am not sure how to do
this.

I got to the point where I clicked the Formula button and the dialogue
popped up, but when I go to insert the custom cost field "Revenue" I
can't tell if it is inserting the custom Cost1 field for Resources or
the current custom Task field I am working on.

The questions are:

"How do I access custom Resource fields in custom formulas that I am
defining for custom Task fields?"
"How can I produce the same behavior, using custom fields, that is
seen in the hard-coded Cost field for Tasks?"- Hide quoted text -

- Show quoted text -

Actually, it doesn't help. As I said, I have seen the arguments that
Project isn't a financial app. In saying so, I was hoping to save the
valuable time of anyone who might be inclined to post 4-5 paragraphs
of explanation intended to dissuade me from using Project in this
manner. Apparently, I failed.

My question is specifically related to the creation of formulae.
Putting aside your opinion on the usage of Project in this way, which
I respect, do you know the answer to the question that I asked; i.e.
how to use fields from Resources in the custom fields for Tasks?

 

[Jan De Messemaeker]

Hi,

Let me be very direct on this.
You can't.
Formulae only use data from the task itself or from the project.

You can do what you want with VBA programming, though.
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Adding a bit ...

You are close on how Project calculates task costs. It takes the man-hours
of regular work each resource does on a task and multiplies that by the
resource's Standard Rate field, plus the number of man-hours of overtime
work times the Overtime Rate, plus the number of units of materials
consumed
times the material cost-per-unit, plus finally any fixed cost, and the
result is the task's cost. These costs are then summed into the summary
tasks.

The problem with using it to track revenue is that revenues are often not
solely and directly dependent on man-hours of labour performed by a
resource. They MIGHT be but there are many other billing arrangments that
can occur. For example, a job might have a fixed-price bid of $XXXX
regardless of the man-hours spent by the resources. But your cost to get
the task done is still directly related to the amount of work the
resources
put in on it. Or since you're providing consulting services it wouldn't be
unusual that you might bill the client at a fixed day-rate covering each
day
or any portion thereof, yet your internal cost of employing the resource
you
send to the client is still dependent on the exact time he spends on the
work. Let's say you have a resource who earns $5000 a month and you bill
him to the client at a rate of $500 per day, one day minimum. He goes to
the client site and does a task lasting 8 hours. Your cost of that one
activity = $250, your revenue = $500. But your send him to a client and
the
task takes him 1 hour to complete. Your cost = 250/8 or ~$32 but your
revenue is still $500. You send him to the client and he does 4 tasks of 1
hour each. Your cost = $32 each times 4 = $125 but your revenue is STILL
$500. See the problem? Internal costs are always related to the exact
amount work required on the task but the revenues really don't bear any
consistent relationship to either the task's duration or the man-hours of
work required to complete it.

Here's another issue regarding costing that leads to the assertion that
Project is not a financial program. You have your resource on salary at
$5000 per month. But this is a slow month and he only spends 5 days on
tasks related to projects underway for your clients. The rest of his time
is spent on personal development, paperwork in the office, taking a course
to upgrade his skills that you've sent him on, etc. Your cost for those 5
days of tasks is NOT the full $5000 salary you're paying him. Instead it's
about $1250, only that portion of his salary pro-rated to the 1/4 of his
total time that was actually spent on those tasks. The other $3750 you've
paid him for the month needs to be accounted for in places other than the
project budgets. But those other operating budget categories don't even
exist as far as the client project related universe that MS Project is
aware
of is concerned. They're over there in the general operating expenses for
your firm along with rent, office furnishings, phone and utilities, etc,
that are also not a part of the direct project costs MS Project tracks.
The
costs as far as MS Project is concerned is only the cost of the the
specific
work required to create the deliverables in the project in question and
all
of the other costs of doing business don't exist as far as it is
concerned.

HTH

--
Steve House [Project MVP]
MS Project Trainer & Consultant
Visithttp://project.mvps.org/faqs.htmfor the FAQs

I have searched the group for some info on how to capture revenue in
Project.

There are several others who seem to think like I do: I am doing work
for a client and have a team of consultants, etc. If I can track the
cost of my people for every single hour they work (my cost), I ought
to be able to also track the revenue they are bringing in for every
single hour they work (client's cost).

However, there are others who say that Project isn't a financial
application. Bollocks. If that's the case, it shouldn't have any
financial fields in it at all.

At any rate, I am thinking about how Project calculates costs for
tasks. It seems to me that the Task Cost field is really a
calculation based upon the Cost field of the Resource(s) assigned to
the task and the work done by each. Thus, to create a custom Revenue
field for Tasks, you also have to have a custom Revenue field for
Resources which uses almost the same exact formula as that for Cost.

I defined a custom cost field for Resources called "Revenue (Cost1)".
Now, I want to define the custom cost field for Tasks called "Revenue
(Cost1)" as a formula based on the "Revenue (Cost1)" field for the
resources assigned to the task(s). However, I am not sure how to do
this.

I got to the point where I clicked the Formula button and the dialogue
popped up, but when I go to insert the custom cost field "Revenue" I
can't tell if it is inserting the custom Cost1 field for Resources or
the current custom Task field I am working on.

The questions are:

"How do I access custom Resource fields in custom formulas that I am
defining for custom Task fields?"
"How can I produce the same behavior, using custom fields, that is
seen in the hard-coded Cost field for Tasks?"- Hide quoted text -

- Show quoted text -

Actually, it doesn't help. As I said, I have seen the arguments that
Project isn't a financial app. In saying so, I was hoping to save the
valuable time of anyone who might be inclined to post 4-5 paragraphs
of explanation intended to dissuade me from using Project in this
manner. Apparently, I failed.

My question is specifically related to the creation of formulae.
Putting aside your opinion on the usage of Project in this way, which
I respect, do you know the answer to the question that I asked; i.e.
how to use fields from Resources in the custom fields for Tasks?