Modeling task-based revenue instead of resource-based cost

[Patrick]

Hi
I'm trying to use Project to manage resources across lots of projects and
want to be able to forecast revenue for those projects as well. Project is
great at calculating cost based on the $/hr rate of a resource, but I want to
see revenue based on a $/hr rate of a task.

I have created custom cost fields - one for Rate and one for Revenue which
uses a fomula of Rate*Work. (Acutally work has to be divided by 60 as it's in
minutes, I guess??? so it's Rate*(Work/60)). This works great - I can see the
revenue for any task as long as I put a rate in for the individual task.

The problem is that I in the Task Usage, I want to be able to see the Work
and the Revenue in the grid on the right. If I were tracking Cost, I could
easily add Cost there and see it ... want the same thing with my new custom
field, but I can't add a custom field. Nor can I access those fields in any
of the custom reports that I know of.

I have played around and transposed the project, so that resources are tasks
and clients are resources with their run rate as the cost/hr of a resource.
Then I assign a resource (re: client) to a task (re: person) in the Task
Sheet. It sort of works but there is no way to rollup resources in the
Resource Usage to get total $ per region or anything like that.

Is there any way for Cost to be calculated my way?

Thanks a ton!

 

[John]

Hi
I'm trying to use Project to manage resources across lots of projects and
want to be able to forecast revenue for those projects as well. Project is
great at calculating cost based on the $/hr rate of a resource, but I want to
see revenue based on a $/hr rate of a task.

I have created custom cost fields - one for Rate and one for Revenue which
uses a fomula of Rate*Work. (Acutally work has to be divided by 60 as it's in
minutes, I guess??? so it's Rate*(Work/60)). This works great - I can see the
revenue for any task as long as I put a rate in for the individual task.

The problem is that I in the Task Usage, I want to be able to see the Work
and the Revenue in the grid on the right. If I were tracking Cost, I could
easily add Cost there and see it ... want the same thing with my new custom
field, but I can't add a custom field. Nor can I access those fields in any
of the custom reports that I know of.

I have played around and transposed the project, so that resources are tasks
and clients are resources with their run rate as the cost/hr of a resource.
Then I assign a resource (re: client) to a task (re: person) in the Task
Sheet. It sort of works but there is no way to rollup resources in the
Resource Usage to get total $ per region or anything like that.

Is there any way for Cost to be calculated my way?

Thanks a ton!

Patrick,
First of all time related data is stored in Project's underlying
database in minutes, so yes, you do need to divide by 60 to get hours
when using a formula.

The second thing you need to understand is that Project's database is
divided into three parts - task data, resource data and assignment data.
Each of these entities has several spare fields that can be used (e.g.
Text1, Number1, etc.), however each of the spare fields for Project's
data types are independent. In other words Task Text1 is not the same as
Resource Text1 nor is it the same as Assignment Text1.

When in a task based view (e.g. Gantt Chart) Text1 is Task Text1. When
in a mixed view (e.g. Task Usage) Text1 is both Task Text1 and
Assignment Text1 depending on whether the data is on a task row or an
assignment row.

You CAN add a custom field to the Task Usage view (left side only) but
it will only show up on the Task rows. Unfortunately custom fields (with
formulas) cannot be created for Assignments, however that doesn't mean
you can't get what you want - it will just require the use of an
advanced feature of Project, namely VBA. Be advised however that in
either the Task Usage or Resource Usage views the data on the right is
timescaled data. Project cannot create timescaled data for any custom
fields. If you really need your custom data in timescale format, it will
have to be generated by appropriate algorithms in VBA and the data will
then have to be exported to another application (e.g. Excel) to display
it in timescaled format.

The bottom line is, yes you can get what you want but it isn't
necessarily straightforward and does require VBA.

Hope this helps.
John
Project MVP

 

[John]

Hi
I'm trying to use Project to manage resources across lots of projects and
want to be able to forecast revenue for those projects as well. Project is
great at calculating cost based on the $/hr rate of a resource, but I want to
see revenue based on a $/hr rate of a task.

I have created custom cost fields - one for Rate and one for Revenue which
uses a fomula of Rate*Work. (Acutally work has to be divided by 60 as it's in
minutes, I guess??? so it's Rate*(Work/60)). This works great - I can see the
revenue for any task as long as I put a rate in for the individual task.

The problem is that I in the Task Usage, I want to be able to see the Work
and the Revenue in the grid on the right. If I were tracking Cost, I could
easily add Cost there and see it ... want the same thing with my new custom
field, but I can't add a custom field. Nor can I access those fields in any
of the custom reports that I know of.

I have played around and transposed the project, so that resources are tasks
and clients are resources with their run rate as the cost/hr of a resource.
Then I assign a resource (re: client) to a task (re: person) in the Task
Sheet. It sort of works but there is no way to rollup resources in the
Resource Usage to get total $ per region or anything like that.

Is there any way for Cost to be calculated my way?

Thanks a ton!

Patrick,
Just an afterthought. What exactly do you mean by "$/hr rate of a task"?
You are using Work in your custom formula but "tasks" don't do work,
resources do. Keep in mind that Project is a scheduling application not
a financial application.

John
Project MVP

 

[Patrick]

True, John. Tasks don't do work ... but they do bring revenue! That's what I
want to model, is the revenue, not the cost. It's not that we don't care
about the cost, but I need to be able to look at a project plan and forecast
revenue out.

I figured out a solution, a hack sort of. Given that I have the custom
calculated field - at the task level - that can calculate revenue for a task
(work * custom Cost1, in which I put the hourly revenue for a project) and as
I remembered that I can turn off automatic cost calculations and "import" my
own costs, I realized that I can allow the my custom fields to calculate and
in the Gantt or Task Sheet just copy my custom revenue column and
paste/overlay it onto the Cost colum! It works great! So now I can show cost
and work in the Task Usage view and roll it up. It's beautiful. Will try and
generify it into a template and am happy to share if anyone is interested.

Thanks for the insight and inspiration!

Patrick

 

[John]

True, John. Tasks don't do work ... but they do bring revenue! That's what I
want to model, is the revenue, not the cost. It's not that we don't care
about the cost, but I need to be able to look at a project plan and forecast
revenue out.

I figured out a solution, a hack sort of. Given that I have the custom
calculated field - at the task level - that can calculate revenue for a task
(work * custom Cost1, in which I put the hourly revenue for a project) and as
I remembered that I can turn off automatic cost calculations and "import" my
own costs, I realized that I can allow the my custom fields to calculate and
in the Gantt or Task Sheet just copy my custom revenue column and
paste/overlay it onto the Cost colum! It works great! So now I can show cost
and work in the Task Usage view and roll it up. It's beautiful. Will try and
generify it into a template and am happy to share if anyone is interested.

Thanks for the insight and inspiration!

Patrick

Patrick,
I don't agree that tasks of themselves bring in revenue. In a
manufacturing environment for example, a series of tasks are required to
build a product which can then be sold to generate revenue. Or, a series
of tasks can be completed to remodel a house after which the contractor
bills the homeowner - thus generating revenue for the contractor. Even
in a law firm wherein each task represents work for a client, it is the
lawyer assigned to the client that accrues billable hours and generates
the revenue.

I also take issue with the fact that your are using the Work field in
your formula. The only entity in Project that relates to work is a
resource. That being the case your custom "revenue" field is no more
than a different pay rate (effectively) for whatever resource (or
composite rate for multiple resources) is assigned to the task.

I got a little confused when you tried to explain how you "imported"
cost into the Cost field and then pasted it. Be advised that directly
entering (or pasting) a cost value into the Cost field is equivalent to
assigning a Fixed Cost to a task. It will be in addition to any cost
generated by assigned resources (labor and/or non-labor).

Nonetheless I'm glad you found a solution that works for you. Good luck.

John
Project MVP

 

[Patrick]

Hello

I should have prefaced this with more about our business, but in a service
business revenue is definitely based on completing tasks. When we sign a new
client, we craft a project plan to match the scope and estimate the hours
necessary to achieve that scope. We can then bill and/or recognize revenue
based on hours completed and/or percent complete on a monthly basis. So our
revenue is directly and solely connected to a task's hours. Each client
could have a different rate, depending on the value of their contract and the
hours in the plan, so I can't say that Sally's rate is a flate $150 (like in
a lawyer example) because in one project it may be $150 but in another it
could be $300. And that rate is based on the project, regardless of who
completes an hour of work.

No worries on the overlay of cost info - we're not calculating cost in any
other manner or use, so it's fine. I realize it's a bit heretical but it
works!

Thanks for the help and insight.