Using Resource database fields within Task table calculations

[John Flynn]

I have a client who wishes to use some additional resource cost information
to work out the costs per task. However, I am stuck with the fact that the
base costs are in the resource table, while the required calculations must
be done in the Task tables.

For your reference: the client deals with contractors who quote a wholesale
price at their hourly rate, and these are sold into projects with a mark-up.
The objective is to calculate both the task "wholesale" and "retail" costs
but to do this at the task level.

e.g. Contractor A charges an external rate of $40 per hour and clients are
billed at $100. We set up the resource sheet so that the Contractor A std
rate = $100, and add another cost column eg Cost1 showing the cost rate at
$40. We also can calculate the hourly margin in another column, of course.
Std (retail) rate = $100
External (wholesale) rate = $40
Internal margin = $60

I have set up additional resource columns to store the "internal"
(wholesale) and "external" (retail or customer price) rate within the
resource table, but I am at a lost how to set up calculated fields within
the task table which utilise the corresponding information contained within
the resource tables. Are these properties accessible via VBA or do I have
to export the resource information plus assignment information to Excel and
do the desired calculations outside of Project? Have I missed something
obvious within the database design?

All advice welcomed - I am at a loss!

John Flynn

 

[Rod Gill]

Hi,

You need to calculate task costs using one rate, then copy the cost column
to a Costn field such as Cost1. Then in the Resource or task usage views
insert the cost rate table field and overwrite the A with B for all
assignments (your other cost). Now the difference between the task cost and
task.cost1 fields is your margin.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[John Flynn]

Thanks, Rod. That all makes sense - I will try it today. (BTW, enjoyed
your columns in Woody's Watch over past few years).
John

 

[Rod Gill]

Hi,

Thanks. Woody's Project Watch seems to have cut right back now. Lost
contact with them and haven't seen another issue for a long time.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.project-systems.co.nz/
Email rodg AT project-systems DOT co DOT nz